Restoring from backup failing: failure creating postgres index

AI-generated summary

Dustin_Lang encountered issues while upgrading Discourse from 2.4.0beta to 2.5.0beta5, leading to a failed PostgreSQL startup due to incorrect data directory ownership. Attempting to restore from a backup failed with a PostgreSQL index creation error related to duplicate keys in the incoming_referers table. After troubleshooting, Dustin resolved the issue by rebuilding the app container, though the underlying corrupt index remained. He later used the data explorer plugin to identify and drop invalid indexes, successfully reindexing the database Dustin_Lang, pfaffman, Dustin_Lang, Dustin_Lang. The community acknowledged the issue as a known problem with corrupt indexes in PostgreSQL 12, with developers working to address it pfaffman, codinghorror.

Hi all,

I’ve been running Discourse painlessly for several years.

Today, I tried doing a web ui upgrade from the 2.4.0beta-something version I was running to the current (2.5.0beta5). This… did not go well. I did not take good notes, but it seemed like postgres was not starting up correctly due to the postgres data directory not being owned by the correct user.

I thought I would try cloning my old containers/app.yml to app2 and restoring from a backup. The new container built and I followed instructions for running a command-line backup (Restore a backup from the command line - #12). “discourse restore ” fails with

ERROR:  could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
DETAIL:  Key (path, incoming_domain_id)=(//viewer/, 16) is duplicated.
EXCEPTION: psql failed: DETAIL:  Key (path, incoming_domain_id)=(//viewer/, 16) is duplicated.
/var/www/discourse/lib/backup_restore/database_restorer.rb:95:in `restore_dump'
/var/www/discourse/lib/backup_restore/database_restorer.rb:26:in `restore'
/var/www/discourse/lib/backup_restore/restorer.rb:49:in `run'
script/discourse:143:in `restore'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/thor-1.0.1/lib/thor/command.rb:27:in `run'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/thor-1.0.1/lib/thor/invocation.rb:127:in `invoke_command'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/thor-1.0.1/lib/thor.rb:392:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/thor-1.0.1/lib/thor/base.rb:485:in `start'
script/discourse:284:in `<top (required)>'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/cli/exec.rb:63:in `load'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/cli/exec.rb:63:in `kernel_load'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/cli/exec.rb:28:in `run'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/cli.rb:476:in `exec'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor/invocation.rb:127:in `invoke_command'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor.rb:399:in `dispatch'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/cli.rb:30:in `dispatch'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor/base.rb:476:in `start'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/cli.rb:24:in `start'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/exe/bundle:46:in `block in <top (required)>'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/lib/bundler/friendly_errors.rb:123:in `with_friendly_errors'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-2.1.4/exe/bundle:34:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'
Trying to rollback...
Rolling back...
Cleaning stuff up...

Any suggestions on how to proceed would be very welcome.

Thanks!
–dustin

Welcome Dustin. Sorry you’re having trouble.

You have a corrupt index. Have a look at PostgreSQL 12 update - #238.

Thanks for your reply. In the end, instead of trying to create a new app and restore from backup, I returned to my original app and ran ‘./launcher rebuild app’ a few times (as root, which I wouldn’t have thought would be necessary, but I think I set up my site as root originally so that would make sense), and now my site is back! Yay!

Remind me never to idly touch the friendly-looking Upgrade button!

thanks,
–dustin

You still have the corrupt index. You still want it not to be corrupt.

Wow, it would be really nice if the upgrade process could remedy some of these issues!

So after installing the data explorer plugin and poking around in the incoming_referers index, and dropping other indices listed by the query here:

But I’m still getting:

discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_19337_index_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_19337_index_ccnew1" concurrently, skipping

and those don’t want to be dropped!

# drop index pg_toast_19337_index_ccnew1;
ERROR:  index "pg_toast_19337_index_ccnew1" does not exist

Oh, need pg_toast prefix:

discourse=# drop index pg_toast.pg_toast_19337_index_ccnew1;
DROP INDEX
discourse=# drop index pg_toast.pg_toast_19337_index_ccnew;
DROP INDEX
discourse=# REINDEX SCHEMA CONCURRENTLY public;
REINDEX

Yes, the corrupt indexes is something we’re looking closely at, apologies for the difficulty.. this in theory should not be possible.. but you know what they say about theory versus practice. It’s my hope that PG12 will be more resistant to.. whatever this is.

That’s good to hear. This is basically the first bump in the road I have experienced after years of set-it-and-forget-it enjoyment (like, I often forget which machine it’s even running on).
Thanks,
–dustin