Database Reference
In-Depth Information
How to do it...
The rest of this recipe assumes the following answers to the key questions:
F
Archive is a directory on a remote server named
$OTHERNODE
F
We send WAL files to the archive using scp
F
Base backups are also stored on
$OTHERNODE
F
Base backups are made using
rsync
F
We'll keep two backups, overwriting alternate backups as we take new ones and
backups are taken weekly on Sundays
The following steps assume that a number of environment variables have been set:
F
$PGDATA
is the path to the PostgreSQL data directory, ending with
/
F
$OTHERNODE
is the name of the remote server
F
$BACKUPNAME
is either b1/ or b2/, and we alternate this each time we take a
backup. Two backups is the minimum; you may wish to use more copies.
F
All required PostgreSQL connection parameters have been set
The procedure is as follows:
1. Create the archive and backup directories on a backup server.
2. Set an
archive_command
. In
postgresql.conf
, you will need to add the following
lines and restart the server, or just confirm they are present.
archive_mode = on
archive_command = 'scp %p $OTHERNODE:/archive/%f'
3. Start the backup as follows:
psql -c "select pg_start_backup('my backup')"
4. Copy the data files (excluding the
pg_xlog
directory) as follows:
rsync -cva --inplace --exclude=*pg_xlog* \
${PGDATA}/ $OTHERNODE:$BACKUPNAME/$PGDATA
5. Stop the backup as follows:
psql -c "select pg_stop_backup(), current_timestamp"
6. It's also good practice to put a
README.backup
file into the data directory prior to
the backup, so that it forms part of the set of files that make up the base backup.
This should say something intelligent about the location of the archive, including any
identification numbers, names, and so on.
