Bug: Restoring indexer service/agent db via pg_restore causes indexer service to crash

[Pete-LunaNova]

When migrating from an older Postgres 10 database (10.14) to a newer Postgres 12 database (12.5), I used pg_dump to create a backup of our indexer service/agent db and then pg_restore to restore this on the new instance.

However this procedure caused the indexer service to crash shortly after starting with the following error output:

error: insert into "signing_wallets" ("address", "private_key") values ($1, $2) - duplicate key value violates unique constraint "enforce_one_row"
Dec 14 19:01:19 graph-indexer-service[21797]:     at Parser.parseErrorMessage (/usr/lib/node_modules/@graphprotocol/indexer-service/node_modules/pg-protocol/dist/parser.js:278:15)
Dec 14 19:01:19 graph-indexer-service[21797]:     at Parser.handlePacket (/usr/lib/node_modules/@graphprotocol/indexer-service/node_modules/pg-protocol/dist/parser.js:126:29)
Dec 14 19:01:19 graph-indexer-service[21797]:     at Parser.parse (/usr/lib/node_modules/@graphprotocol/indexer-service/node_modules/pg-protocol/dist/parser.js:39:38)
Dec 14 19:01:19 graph-indexer-service[21797]:     at Socket.<anonymous> (/usr/lib/node_modules/@graphprotocol/indexer-service/node_modules/pg-protocol/dist/index.js:10:42)
Dec 14 19:01:19 graph-indexer-service[21797]:     at Socket.emit (events.js:315:20)
Dec 14 19:01:19 graph-indexer-service[21797]:     at Socket.EventEmitter.emit (domain.js:485:12)
Dec 14 19:01:19 graph-indexer-service[21797]:     at addChunk (_stream_readable.js:297:12)
Dec 14 19:01:19 graph-indexer-service[21797]:     at readableAddChunk (_stream_readable.js:273:9)
Dec 14 19:01:19 graph-indexer-service[21797]:     at Socket.Readable.push (_stream_readable.js:214:10)
Dec 14 19:01:19 graph-indexer-service[21797]:     at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {

Initial investigations point to the pg_restore process altering some aspect of the database that determines what order constraints are checked. As such, it appears to produce a different error message that the indexer-service is not configured to deal with properly.

Trying to directly insert a duplicate key into the signing_wallets table in the original db produces the following error:

ERROR:  duplicate key value violates unique constraint "signing_wallets_private_key_unique"
DETAIL:  Key (private_key)=(0x<REDACTED>) already exists.

However repeating this on the restored db produces this message:

ERROR:  duplicate key value violates unique constraint "enforce_one_row"
DETAIL:  Key (enforce_one_row)=(1) already exists.

If anyone needs any more info or help with reproducing this, please let me know.

[WayneWayner]

I am also seeing this error when changing mnemonic and/or indexer address

[trader-payne]

This happened to me as well on a simple dump/restore within the same version (13 to 13.1) && next release upgrades (12.4 to 13)