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: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
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
-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.