nft_custody table index crashes

[bestmike007]

Describe the bug
api process crashes with error: duplicate key value violates unique constraint nft_custody_unique or table tid from new index tuple (13373,41) overlaps with invalid duplicate tuple at offset 4 of block 6211 in index nft_custody_recipient_asset_identifier_index

@rafaelcr @CharlieC3

To Reproduce
Steps to reproduce the behavior:

1. Clone https://github.com/alexgo-io/stacks-node-mainnet and run setup.sh, and restore the backup from https://github.com/alexgo-io/stacks-node-mainnet/releases/tag/block-140976
2. Start the postgres: docker-compose up -d stacks-blockchain-postgres
3. Run the sql to check if there is duplicates: select asset_identifier, value, count(*) from nft_custody group by 1,2 having count(*) > 1;
4. Replace the stacks-blockchain-api image in docker-compose.yml using hirosystems/stacks-blockchain-api:7.8.2
5. run ./start.sh
6. watch 'curl -s http://127.0.0.1:3999/extended/v1/status'
7. the api crashes at block height 141038: docker logs -f --tail=100 stacks_api
8. and then it restore by itself and crashes again at block height around 141064 and keeps crashing

You might possibly run into another issue described in #1878

Expected behavior
Stacks node sync until it reaches tip.

Screenshots

Screenshot from step 3:

Screenshot from step 7:

Screenshot from step 8:

Console log
N/A

Additional context
The vm I'm using to run the steps: 4vCPU, 16G memory, 320GB disk

[CharlieC3]

@bestmike007 It looks like there are some special considerations to take into account when performing a pg backup directly from a data dir as you're doing here that can cause problems in restorations, such as certain files and directories to omit from the backup.
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP-DATA

Are you able to try restoring your data using one of Hiro's archives via pg_restore as documented here? If you see the same problem occurring there, then it would provide something more for us to go off of.

[bestmike007]

What's wrong with the cold backup? https://www.postgresql.org/docs/current/backup-file.html#BACKUP-FILE

[bestmike007]

update: after applying those 2 PRs and redo the steps, there were still those similar errors but it went through.

Let me redo it again with the official image.

[bestmike007]

And again it crashes at block height 141064:

[smcclellan]

I understand that this is an issue with setup rather than the API. Closing for now. Please reach out on Discord if this is still an issue.