Starting db-sync fails on db migration scripts

Developers Cardano Integration
1

Kia ora folks,

I’ve recently updated cardano-db-sync as follows:

  • docker-compose down
  • git pull
  • docker-compose pull
  • docker-compose up

db-sync is now failing and it looks like an issue with updating the schema?:

cardano-db-sync_1  | [db-sync-node:Info:6] [2021-09-13 22:15:16.72 UTC] Schema migration files validated
cardano-db-sync_1  | [db-sync-node:Info:6] [2021-09-13 22:15:16.72 UTC] Running database migrations
postgres_1         | 2021-09-13 22:15:17.682 UTC [688] ERROR:  column "price_mem" of relation "param_proposal" does not exist
postgres_1         | 2021-09-13 22:15:17.682 UTC [688] CONTEXT:  SQL statement "ALTER TABLE "param_proposal" ALTER COLUMN "price_mem" TYPE DOUBLE PRECISION"
postgres_1         |    PL/pgSQL function migrate() line 7 at EXECUTE
postgres_1         | 2021-09-13 22:15:17.682 UTC [688] STATEMENT:  SELECT migrate() ;
cardano-db-sync_1  | ExitFailure 3
cardano-db-sync_1  |
cardano-db-sync_1  | Errors in file: /tmp/migrate-2021-09-13T221516.log
cardano-db-sync_1  |
cardano-db-sync_cardano-db-sync_1 exited with code 1

migrate-2021-09-13T221516.log:

Running : migration-1-0000-20190730.sql
 init
------
(1 row)
Running : migration-1-0001-20190730.sql
 migrate
---------
(1 row)
Running : migration-1-0002-20190912.sql
 drop_cexplorer_views
----------------------
(1 row)
Running : migration-1-0003-20200211.sql
 migrate
---------
(1 row)
Running : migration-1-0004-20201026.sql
 migrate
---------
(1 row)
Running : migration-1-0005-20210311.sql
 migrate
---------
(1 row)
Running : migration-1-0006-20210531.sql
 migrate
---------
(1 row)
Running : migration-1-0007-20210611.sql
 migrate
---------
(1 row)
Running : migration-1-0008-20210727.sql
 migrate
---------
(1 row)
Running : migration-1-0009-20210727.sql
 migrate
---------
(1 row)
Running : migration-2-0001-20210526.sql
 migrate
---------
(1 row)
Running : migration-2-0002-20210527.sql
 migrate
---------
(1 row)
Running : migration-2-0003-20210531.sql
 migrate
---------
(1 row)
Running : migration-2-0004-20210609.sql
 migrate
---------
(1 row)
Running : migration-2-0005-20210610.sql
 migrate
---------
(1 row)
Running : migration-2-0006-20210611.sql
 migrate
---------
(1 row)
Running : migration-2-0007-20210617.sql
 migrate
---------
(1 row)
Running : migration-2-0008-20210623.sql
 migrate
---------
(1 row)
Running : migration-2-0009-20210624.sql
 migrate
---------
(1 row)
Running : migration-2-0010-20210711.sql
psql:/nix/store/qb7w0098bifimpncqjyaq5nap5fwcfdw-cardano-db-sync-schema/schema/migration-2-0010-20210711.sql:20: ERROR:  column "price_mem" of relation "param_proposal" does not exist
CONTEXT:  SQL statement "ALTER TABLE "param_proposal" ALTER COLUMN "price_mem" TYPE DOUBLE PRECISION"
PL/pgSQL function migrate() line 7 at EXECUTE
ExitFailure 3

What is the correct way to update db-sync when using docker? Is there any way I can remedy this issue without a total rebuild? It took the best part of week to fully sync the DB so I’m trying to avoid this path :slight_smile:

Ngā mihi
Rhys

2

Update: I found that the schema version table was at 9 (for stage two). As above db-sync was trying to migrate to stage two version 10 however it was trying to alter a column that was supposedly created in stage two version 7 however my db didn’t contain that column. I dropped the stage two version back to 6 and it re-ran the migrations successfully up to stage two version 10 then failed trying to apply version 11 with more missing schema objects. I’m sure I’ve just made a huge mess of things by messing with the schema version table. I’ll blow everything away and start the sync over again. All good fun, learnt a few things.

3

The 11.0.0 release explicitly says that you need to recreate the db

4

Thanks Tom. RTFRN right! :roll_eyes: :rofl: Such newbie. :slight_smile: