Backup and Restore using pg_dumpall

This is an easy setup for backup of all Postgres 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 code below assume you want to take snapshot every hour. For this we will use pg_dumpall command. We first need to avoid the password prompt.

sudo -u postgres -s   
echo "*:*:*:e_luminate_user:e_luminate_password" >> ~/.pgpass
chmod 600 ~/.pgpass

Setup a cronjob to take backups hourly.

mkdir ~/pg_backups
crontab -e 

add entry like

0 * * * * pg_dumpall -U e_luminate_user > ~/pg_backups/backup-"$(date +\%H)"

If we want to safe guard against total system failure, then we need to consider copying these snapshots to another system, unless there is something better than nightly backup from the hosting company.

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

sudo -u postgres /usr/lib/....bin/.../initdb
sudo /etc/init.d/postgres start
sudo -u postgres psql postgres < shapshot_file