Auto backup and restore PostgreSQL

I started using PostgreSQL on most of my new projects and for those in production I need some way of backing them up on my Ubuntu servers. There are many solutions, but the one that I found the simplest one was using the autopostgresqlbackup package.

To install it, run

sudo apt-get install autopostgresqlbackup

You can configure it by running

sudo vi /etc/default/autopostgresqlbackup

but I’m happy with the configuration that comes out of the box. The default backup rotation strategy is called grandfather-father-son and that means there are three backup cycles — daily, weekly and monthly.

The tool is configured to rotate daily backups after one week, weekly backups are rotated after 5 weeks and monthly stays until you delete them manually. It uses cron to schedule the backups, and if you look at the cron.daily folder you can find the script there.

Backups are stored in /var/lib/autopostgresqlbackup/ folder and you can download them with SCP command if you have an SSH access to the server.

scp server.com:/var/lib/autopostgresqlbackup/daily/database_name/database_name_2014-12-22_06h38m.Monday.sql.gz backup.sql.gz

Keep in mind, that you need have a read access to the folder to SCP the file and you can achieve that by running

sudo chmod o+r -R /var/lib/autopostgresqlbackup/daily

which gives other users read access to your daily backup folder.

So now you have the backup downloaded on your machine and you want to restore it on your local postgresql instance. Firstly, create a database with the same name that you are restoring - you can use pgAdmin. After that, run the following commands.

gunzip database_name.sql.gz
psql database_name < database_name.sql

Easy! You restored your database and you have a nice and robust backup rotation scheme in place. Keep in mind, that you should copy your backup files to another storage to make this backup strategy even more awesome.

Comments

Slach: best practice use pg_restore -d database_name database_name.sql