Restore fails: could not create unique index

Installation
1

Hi,

I’m trying to restore a backup on to a new Discourse instance as I am performing a server migration. Unfortunately I get this error:

I’ve had a read of this:

Restore fails - could not create unique index - #2 by simon and deleted the indexes.

Now when I do:

[2] pry(main)> IncomingReferer.where(path: "/m/search")
=> []

I get no result.

Is this OK now to take a backup and restore on the other server?

Thanks

Sam

2

Unfortunately this didn’t help, and I still get this message when restoring:

[2021-07-03 16:53:41] ERROR:  could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
[2021-07-03 16:53:41] DETAIL:  Key (path, incoming_domain_id)=(/search, 4502) is duplicated.
[2021-07-03 16:53:41] EXCEPTION: psql failed: DETAIL:  Key (path, incoming_domain_id)=(/search, 4502) is duplicated.

It’s not clear to me how to fix this.

I’ve found some more duplicates with IncomingReferer.where("path LIKE '%/m/search%'"), so used the destroy on these indexes as well. My instance now seems to not be working at all – even on the old server, so I’ll try and rebuild it.

Sam

3

Something is still wrong. I am getting:

[2021-07-03 17:28:53] ERROR:  could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
[2021-07-03 17:28:53] DETAIL:  Key (path, incoming_domain_id)=(/osmc/osmc, 2939) is duplicated.
[2021-07-03 17:28:53] EXCEPTION: psql failed: DETAIL:  Key (path, incoming_domain_id)=(/osmc/osmc, 2939) is duplicated.

when attempting to restore a new backup.
But my console shows that I have cleaned the instances on the running server (which is back up now after rebuild):

[1] pry(main)> IncomingReferer.where(path: "/m/search")
=> []
[2] pry(main)> IncomingReferer.where("path LIKE '%/m/search%'")
=> []

Can anyone advise what needs to be done to fix the database so that I can restore this on another machine?

Thanks

Sam

4

Can't restore due to corrupt indexes (with some clues on how to deal with corrupt indexes) might offer some clues.

5

Thanks.

I’ve read this, but I don’t know what commands or queries to run to solve my problem.

Sam

6

Basically, you want to try to rebuild the index and keep deleting stuff until you can reindex it. It looks like you’re doing the right stuff, you just need to keep doing it for all of the duplicate entries.

If you have a budget you can post in marketplace.

7

Hi Jay

It’s not legal in the UK to expose our database to third parties to fix these issues, and even if it was, I have no interest in doing so for the interests of protecting user’s privacy.

As an open source maintainer myself, the idea to take this to marketplace is disappointing, particularly when there seems to be a tacit acknowledgement that the issues are introduced by a Postgres update which Discourse bundled in their Docker container. We use Discourse in the Docker container because we understand the closely coupled dependencies and want to devolve the version and dependency management to the Discourse team’s expertise.

It seems to be accepted that this is a Postgres 12 regression and there are some potential mitigations. However, first reports came in over a year ago and I am sure there will be more affected users in the future if they try and restore a backup.

I would prefer to sponsor some development time to fix this in Discourse upstream so others can also benefit from this. In the interim, our forum is not functional and I will need to consult help with someone with Postgres DBA knowledge.

Cheers

Sam

1 Like
8

Hey Sam.

Sorry I wasn’t able to provide you with sufficient instruction to solve your problem yourself, but that’s how you made it sound. I thought it might be a relief rather than a disappointment that there are other ways to get your forum back online.

Cheers

1 Like
9

Hi Jay

Not at all. You don’t owe me anything – but I had hoped that you had a quick solution given your post history, and had assumed I’d missed something obvious given the fact that your posts seemed to be a definitive answer and the topic was closed.

I’ll let you know how I get on.

Cheers

Sam

1 Like
10

Unfortunately this is still a problem for us, and we cannot migrate servers, which is rather concerning.

Happy to pay someone to get this solved, but after this issue, and the acknowledgement of it numerous times without a clear solution in the forum, we are seriously considering migrating to a different forum software like Flarum. It might not be as feature filled as Discourse, but it’s LAMP and I (as a non web-dev) can get under it.

When we used the Discourse Docker container, we expected that there would be support. If we’d deployed everything separately and used our own version of Postgres, I could appreciate the response. But to date, all we’ve done is used the environment that you’ve recommended and shipped yourselves, and now we’re quite stuffed in terms of moving to a new server (we have an imminent deadline).

At this point, my best bet is a Docker volume backup and restore, because Discourse’s backups aren’t usable. This is also worrying: we have backups that are being made daily, but can’t actually be restored to a new Discourse environment. I wonder how many others will encounter such an issue in the near future.

Sam

11

There were indeed quite a few bugs in Postgres that would cause corrupted indexes. We think most of them are resolved in Postgres 13.x.