White dot for spacing only
The Dice Project


backup_and_restore.html,v 1.10 2012/09/20 21:19:03 gdutton Exp

PostgreSQL Service backup and restore

This document covers the backup and restore procedures for the machines involved in the RAT PostgreSQL service. Replace 'pgteach' with the appropriate alias in the instructions below.

machine alias role
burly (pgteach) teaching postgresql server
cracker (pgresearch) research postgresql server

Backup: Procedure

Backup is performed nightly, and consists of the following stages:

  1. At 05:30 every day, the entire database is dumped in SQL form to
    pgteach:/disk/dbdata/rsync/nightly.sql.gz
  2. Daily, an rsync is taken of the /disk/dbdata/rsync directory on pgteach. This is currently mirrored to:
    host:/path/to/backup

Restore: Worst-case scenario

The following should be all that is required to return the whole of service to working order[1], if a database server become inoperable:

For the new server:

  1. Restore pgXXXXX's lcfg profile to the new machine.
  2. Build machine and ensure it has been rebooted sufficiently to be in sync with its profile, and the postgresql service is running on the server.
  3. If the machine name changes: ensure that alias pgteach points to the new machine.
  4. Restore the /disk/dbdata/rsync directory from mirror or tape backup.
  5. Temporarily disable pgluser from automatically running by removing it from the postgres user's crontab.
  6. As postgres execute the following to restore the database information.
    $ cat nightly.sql.gz | gunzip | psql
  7. Check that pgluser is behaving in a sensible manner by testing it as postgres:
    $ pgluser --dry-run --verbose [--prune]
    and, if the results look sane, re-enable pgluser:
    $ om cron configure


 : Units : Research_and_teaching : Documentation : Postgresql 

Mini Informatics Logo - Link to Main Informatics Page
Please contact us with any comments or corrections.
Unless explicitly stated otherwise, all material is copyright The University of Edinburgh
Spacing Line