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

installation
1

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:

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?

2

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.

3

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!

4

Yeah I recommend just deleting the row, simplest way forward

2 Likes
5

Can someone give me some psql commands that I can use to identify and remove the offending rows?

Thanks, it’s appreciated!

6

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.

7

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:

4 Likes
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.

9

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.