Backup and Restore using pg_dumpall

This is an easy setup for backup of all Postgres databases. This may be an over kill if we are using just one database. But its documented in case we get multiple databases

Advantages

• Simple to setup
• Can be used to migrate data between servers or versions of Postgres

Downsides

• Your restore goes to the point when you backed up. You can backup frequently so say every 30 mins, but if you then get corruption or system failure, it impossible to restore updates between backup and point of failure.
• You have to restore the entire database though pg_dump may be used to backup and restore specific databases, though if you did that then you have to does extra steps of recreating users, roles and tables prior to restore.

Backup process

The process is the same as for doing is as for Shared hosted Service see Backup on Shared Server. But slight changes

1. Your PUSER needs to have superuser access
2. Your crontab entry for pg_dump becomes

echo "0 * * * * ${PBIN}/pg_dumpall -U ${PUSER} postgres > ${PBACKUPDIR}/pg-backup-\$(date +\%Y\%m\%d\%H\%M\%S)"

To Restore

To restore the system, its done in its entirety, we initilise the structure, start the server and then with the server running do a restore. Its assumes the database files and folder are owned by user postgres so you need access or password to that account as well as root.

sudo -u postgres initdb
sudo /etc/init.d/postgres start
sudo -u postgres psql postgres < -f ${PGBACKUPDIR}/pg-backup-YYYYMMDDhhmmss

The paths to initdb and psql may vary on different Linux systems. On Ubuntu 12.10 its /usr/lib/postgresql/9.1/bin