Restoring from backup failing: failure creating postgres index

Installation
1

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). “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

2

Welcome Dustin. Sorry you’re having trouble.

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

1 Like
3

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
4

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

1 Like
5

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
1 Like
6

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
7

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
8

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