I’m trying to migrate my server to a new host, so I downloaded a backup file, and uploaded it to the new host. Installed a fresh copy of discourse, and followed the instructions to do so from the command line: [image] Restore a backup from the command line Self-Hosting bookmark This guide explains how to restore a Discourse backup from the command line without using the Discourse web UI. person_raising_hand Required user level: Administrator wrench Console access required Here’s how to restore a Discourse backup from the command line, without ever booting the Discourse web UI. This is handy when you’re moving servers. Prerequisites Before you start, make sure you complete the following steps: Download the latest backup file from the source Disco… Unfortunately, the discourse restore databasename fails with: ALTER TABLE ALTER TABLE ALTER TABLE ERROR: could not create unique index "posts_search_pkey" DETAIL: Key (post_id)=(36946) is duplicated. EXCEPTION: psql failed: DETAIL: Key (post_id)=(36946) is duplicated. /var/www/discourse/lib/backup_restore/database_restorer.rb:92:in `restore_dump' /var/www/discourse/lib/backup_restore/database_restorer.rb:26:in `restore' /var/www/discourse/lib/backup_restore/restorer.rb:51:in `run' script/discourse:149:in `restore' Seems there’s something wrong with the database file? Can someone give me some direction on how I can fix this error? I still have access to the original server, which seems to be running fine. Is there some sql command or something I can run that will analyze (and fix?) the database before I download a backup?

Thanks for the help, guys. I was able to do the following: discourse=# reindex index concurrently "posts_search_pkey"; ERROR: could not create unique index "posts_search_pkey_ccnew2" DETAIL: Key (post_id)=(116038) is duplicated. discourse=# delete from post_search_data where post_id = 116038; DELETE 2 discourse=# delete from post_search_data where post_id = 116038; DELETE 0 discourse=# reindex index concurrently "posts_search_pkey"; ERROR: could not create unique index "posts_search_pkey_ccnew3" DETAIL: Key (post_id)=(9336) is duplicated. discourse=# delete from post_search_data where post_id = 9336; DELETE 1 . . . . discourse=# reindex index concurrently "posts_search_pkey"; REINDEX Once everything seemed ok, I did a full backup and restored it on a new server with no issues. I appreciate everyone’s time. I love discourse! Lesson learned: check backups once in a while to be sure they’re good :slight_smile: I keep daily backups, and then keep monthly “snapshots” too. The earliest backup I had that didn’t have a corrupted database was 4 months old :frowning:

It sounds like you have a corrupt index. What version of postgres is this? There are a few topics about this. You can try to reindex that table on the running instance and then delete or fix the duplicate IDs.

Yes, I think it’s corrupt. Would love it if someone could tell me how to fix it :slight_smile: Postgres is 13.6. I’m trying to move it to a new instance on a different server. I tried rake search:reindex inside the docker container, and it fails with: ........rake aborted! PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "posts_search_pkey" DETAIL: Key (post_id)=(86919) already exists. Is it possible to just drop the offending record and re-index or something? Any help appreciated!

Yeah I recommend just deleting the row, simplest way forward

Can someone give me some psql commands that I can use to identify and remove the offending rows? Thanks, it’s appreciated!

I think it’s something like ./launcher enter app su - postges psql discourse select id from post_search_data were post_id>86918 and post_id<86921; --- when you get the id ---- delete from post_search_data where id=ID_FROM_LAST_QUERY There could be more. Perhaps someone else can provide more help but that’s about all I think I can do without being logged in to your server. If you need much more help, you can post in #marketplace or contact me directly. Or perhaps it’s safe to just wipe it away and have it get regenerated, but I’m not quite sure about that.

[image] MiG: Once everything seemed ok, I did a full backup and restored it on a new server with no issues. Great! So glad you got it! [image] MiG: The earliest backup I had that didn’t have a corrupted database was 4 months old @sam What is disturbing is that I thought that the explanation was that this was an issue with PG<13, but you’re running PG13.

"EXCEPTION: psql failed: DETAIL: Key (post_id)=(36946) is duplicated."

Product Support Self-hosting

pfaffman (Jay Pfaffman) June 20, 2022, 8:30pm 8

Great! So glad you got it!

@sam What is disturbing is that I thought that the explanation was that this was an issue with PG<13, but you’re running PG13.

Topic		Replies	Views
Cannot restore database: sql key is duplicated Self-hosting	2	666	August 30, 2022
Error importing backup: "could not create unique index" Self-hosting	6	1212	November 1, 2021
Restore fails: could not create unique index Self-hosting	10	1375	February 13, 2022
Restore fails - could not create unique index Self-hosting	21	4244	July 4, 2020
Backup restoration fails Self-hosting backups	16	589	June 16, 2026

"EXCEPTION: psql failed: DETAIL: Key (post_id)=(36946) is duplicated."

Related topics