"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:

5 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.