Recovering Postgres Databases from Backup

If it becomes necessary to recover the Postgres database from backups, the following should be useful.

The backups reside on the database server postgres.cs in /backup/postgres.

At present they are dumps produced by pg_dumpall in the SQL command format. This means that each file is a huge psql script containing all the commands required to restore a database cluster to the state it was in when the backup was taken.

Recovering can be done using the following steps:

• Connect to the database from postgres.cs as postgres, using the psql command-line utility.
• Drop all the databases (except for postgres, template0, and template1). For example:

       drop database odyssey_dev;
       

  Note that this will require dropping all current sessions on those databases; when we did it, there were very few sessions so we just had to re-try dropping some of the databases a couple of times. In general, however, actually stopping the database is a better idea. Existing sessions can be found using the following command (as postgres):

       select * from pg_stat_activity;
       

• Perform the recovery:

       \i /backup/postgres/apps-1247599621-alldb.sql
       

Doing the recovery this way should lead to output which can be described as follows:

• Some occurrences of the “SET” command tag.
• An error per existing role, complaining that it already exists. Also an “ALTER ROLE” command tag.
• A bunch of notices relating to the fact that group memberships already exist. This is because user definitions belong to the cluster, not the database, and we only dropped the databases while keeping the cluster.
• Lots of command tags related to creating the databases, adjusting permissions, and creating schemas and their contents within the databases. In addition we observed 1-row results labelled “setval” for each sequence, apparently the result of calling setval on each sequence to set its value to the appropriate next value.

-- IsaacMorland - 15 Jul 2009