Restoring from backup failing: failure creating postgres index

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 command line). "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.

1 Like

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

2 Likes

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.

1 Like

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

1 Like