Poor Man's Backup

(originaly posted in Sep 2009 at http://ruminations.ratonator.com/home/implementing-backup)

September 2009,  ratonator.com running alpha! Did a lot of tests during the day and it seems to be working nicely! Yes, yes... still ugly as hell, but that is not the point!

As a good IT pro a am unable to go forward without thinking about backups! I feel totally uneasy without them, even with low quality data ratonator have now.

Well, so let's get rid of this uneasiness... automated scheduled rotating backups!

To set this feature I need: pg_dump and cron. Check! Check!

First the hourly backup: I would make a script for rotating target every 5 or 6 hours. Where to put this script? PostgreSQL home (var/lib/postgresql)! Where to put the dumps? There is a /var/backups directory, seems a good place. To keep the things tidy I create a /var/backups/postgres directory to receive the hourly backups and create a daily and monthly directories under it.

This is a shell script for rotating backups, I'll use it for the hourly backup:

#!/bin/bash

cd /var/backups/postgres
test -f ratonator.dump.4.bz2 && mv -f ratonator.dump.4.bz2 ratonator.dump.5.bz2
test -f ratonator.dump.3.bz2 && mv -f ratonator.dump.3.bz2 ratonator.dump.4.bz2
test -f ratonator.dump.2.bz2 && mv -f ratonator.dump.2.bz2 ratonator.dump.3.bz2
test -f ratonator.dump.1.bz2 && mv -f ratonator.dump.1.bz2 ratonator.dump.2.bz2
test -f ratonator.dump.bz2 && mv -f ratonator.dump.bz2 ratonator.dump.1.bz2
pg_dump --file=ratonator.dump --format=custom ratonator
bzip2 -9 ratonator.dump

To have this script running each hour and some simple commands to save daily and monthly copies I use the good old cron. To edit a crontab (cron configuration) I use crontab -u postgres -e. The resulting configuration is:

# crontab -u postgres -l
# m h dom mon dow command
8 * * * * /var/lib/postgresql/ratonator/backup.sh
5 5 * * * cp /var/backups/postgres/ratonator.dump.5.bz2 /var/backups/postgres/daily/ratonator.dump.`date +'%d'`.bz2
3 3 1 * * cp /var/backups/postgres/ratonator.dump.5.bz2 /var/backups/postgres/monthly/ratonator.dump.`date +'%m'`.bz2

For the uninitiated, this configuration commands cron to run the backup.sh script every 8th minute of each hour, the daily copy every 5:05 AM and every 3:03 AM of the 1st day of each month. Those values are somewhat arbitrary, except for the fact that I make the daily and monthly copies just before of the real backup, to save the 6th oldest backup which will be overwritten during the hourly backup.

Simple and efficient and boring... later I will setup automatic copying to another server using SSH's scp (behind rsync) utility.

Note: yes, yes, apt-get install autopostgresqlbackup, similar crontab hacks and we're done! I learned about it 3 years later. But hey, how about the fun?