Backing up postgreSQL databases
From GridPP Wiki
Making regular and frequent backups of the dCache databases is an essential part of operating a production level system.
Using PostgreSQL tools
Create a cron job on the postgreSQL host.
# cat /etc/cron.d/postgres-backup DATE=/bin/date DUMPALL=/usr/bin/pg_dumpall GZIP=/bin/gzip RSYNC=/usr/bin/rsync SSH=/usr/bin/ssh KEY=/var/lib/pgsql/.ssh/postgres-bak RUSER=remote-user RHOST=remote-host.ac.uk RPATH=/opt/pnfsdb.backup/postgres.dcache.dump.sql.gz LPATH=/var/lib/pgsql/pnfsdb.backup/postgres.dcache.dump.sql.gz LOG=/var/lib/pgsql/log/postgres_dump.ncm-cron.log 10 */3 * * * postgres ($DATE --iso-8601=seconds --utc; $DUMPALL | $GZIP > \ $LPATH; $RSYNC -e "$SSH -i $KEY" $LPATH $RUSER@$RHOST:$RPATH) >> $LOG 2>&1
Obviously ssh-keys have to be used, with appropriate security measures appplied.
Useful Links
- http://www.postgresql.org/docs/8.1/interactive/backup.html
- http://www.dcache.org/archive/user-forum/0322.shtml
Slony
Slony-I can be used to replicate the database to another host and then perform a file system level backup on the replica host. This allows them to have `hot swap' databases and does not slow down postgres PNFS during the backup.
