CODEWITHSUNDEEP
postgresqlherokupostgresql-11postgresql-12
chooban
chooban

Reputation: 9256

Restoring PostgreSQL 11 backup to 12 hangs. How can I debug it?

I'm attempting to upgrade heroku PostgreSQL instances from pg11 to pg12 using the copy method as my testing environments are on hobby instances. At the end of the process it appears to be hanging for a long time (does not exit after >30 minutes for a 120MB database). The datastore view suggests everything is fine, I have the same number of rows, but there are issues.

It appears to be the fault of a materialized view. If I connect to the database and look through the tables and views, only one appears to be empty. Using postico, it waits and waits for the view's structure, but doesn't give the usual warning for an unpopulated view.

I can recreate the stalling behaviour by creating a local pg12 database and attempting to use pg_restore with a recent backup. Along the same lines, I appear to be able to get it working by creating an empty local database, running all the db migrations, truncating all tables and sequences, and then doing a --data-only --disable-triggers load from the same backup. Not a particularly smooth or inspiring migration plan plan. Using --verbose doesn't show up any obvious errors, the last thing I get is that it's creating the problematic materialized view.

I've also set log_statement to all, and the last one I get is that it's refreshing the problematic view. At this point, the postgres command starts using ~100% CPU.

Locally, I'm using this command to restore:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -d database_name database_backup.dump

This is the command we use regularly to restore production backups for local development.

Are there any known gotchas with upgrading from 11 to 12, or ways that I might be able to extract more information about what's going on?

Upvotes: 0

Views: 1063

Answers (1)

jjanes
jjanes

Reputation: 44287

It has probably chosen an appalling plan for doing the materialized view query, due to lack of statistics at the time the refresh was launched.

You could kill the process, then restart the refresh once stats are gathered (which they might already be.)

If starting from scratch, you could run pg_restore with --section of pre-data and data, then do an ANALYZE, then do post-data.

Upvotes: 2

Related Questions