Rebuild PostgreSQL from backup after data corruption

postgresqlrestore

My PostgreSQL database seems to have suffered corruption after an unclean shutdown, and now I cannot figure out how to get it out of recovery mode. Since the database does not get very frequent updates, the last known good nightly backup should have a current copy of the data.

Running

sudo -u postgres pg_restore /path/to/backup.post

just dumps a long list of SQL commands on the console but nothing else happens.

Suspecting data corruption, I simply moved the entire data_directory (as referenced in /etc/postgresql/9.6/main/postgresql.conf) to a different path. Now I am wondering how to recreate a “virgin” version of the data dir where I can then restore the backup. I recreated the path and set its owner to postgres:postgres, but attempting to restart PostgreSQL, re-running pg_restore or even dpkg_reconfigure do not seem to do the trick – the dir is still empty.

What is the correct procedure to throw out a corrupted PostgreSQL data_directory, replace it with a fresh one and restore the last known good backup?

OS is Raspbian 9.

Best Answer

Running pg_restore without the -d $DATABASE option will just print the commands to restore the db to stdout but not actually touch any databases.

1. Rebuild the data dir

The command to rebuild the data dir is initdb. Full command:

sudo -u postgres /usr/lib/postgresql/9.6/bin/initdb $DB_PATH

This is for PostgreSQL 9.6; for any other version you will need to change the version number in the path and ensure the path to initdb is still correct.

$DB_PATH is the path to your data dir.

See the man page for additional options, such as setting a different locale or specifying credentials.

In my case, bootstrapping the database terminated with a segfault, indicating corruption of a binary (or script) involved. So I had to run

sudo apt-get install --reinstall postgresql postgresql-9.6 postgresql-client postgresql-client-9.6 postgresql-client-common postgresql-common postgresql-contrib-9.6

to reinstall PostgreSQL-related binaries from scratch. (If you are unsure which packages to reinstall, run dpkg -l | grep postgres and reinstall everything listed there.)

2. Restore data

sudo -u postgres pg_restore -C -c --if-exists -d postgres /path/to/backup.post

-C will create the database, if it does not exist already.

When using -C, postgres can be replaced with any database present on your system; it is only used for the initial connection but the database itself will not be touched. (Without -C, you would need to enter an existing database you wish to restore to.)

-c will drop any existing objects (e.g. databases, tables etc.) before creating them. This ensures the resulting DB contains exactly what was in the backup, with no leftovers from the database you are restoring into.

--if-exists prevents (harmless but confusing) error messages when using -c if some objects do not already exist in the database.

Related Question