PostgresBackupRecovery < CF

CF Web>Database>PostgreSQL>PostgresSchemaUpdate>PostgresBackupRecovery (2009-07-20, IsaacMorland) (raw view)

---+ 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:
     <verbatim>
     drop database odyssey_dev;
     </verbatim>
     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=):
     <verbatim>
     select * from pg_stat_activity;
     </verbatim>
   * Perform the recovery:
     <verbatim>
     \i /backup/postgres/apps-1247599621-alldb.sql
     </verbatim>

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.

-- Main.IsaacMorland - 15 Jul 2009

Topic revision: r2 - 2009-07-20 - IsaacMorland

Information in this area is meant for use by CSCF staff and is not official documentation, but anybody who is interested is welcome to use it if they find it useful.

Other Webs

My links
- People
- CERAS
- WatForm
- Tetherless lab
- Ubuntu Main.HowTo
- eDocs
- RGG NE notes
- RGG
- CS infrastructure
- Grad images

Edit