Backup and Restore using pg_dumpall

This is an easy setup for backup of all progress databases.

Pro

• Simple Setup
• can be used to migrate data bwteen servers or versions of postgress

Cons

• Your restore goes to teh point when you backed up. You can backup frequently so say every 30 mins, But if you loose data then restoring, it impossible to restore updates between backup and point of failure.
• You have to restore teh entire database though pg_dump may be used to backup and restore specif databases, thoug you then have to create roles and tables etc.

Backup process

The code below assume you want to take snapshot every hour. For thsi 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 teh hosting company.

To Restore

To restore the system in its entirety

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