Automating database backups with Tarsnap

Yesterday I wrote about my MathsPrompt tool which serves up questions to help me practice new skills I’m learning as part of my mathematics degree. Today I realised that all the data (both autogenerated and copy-pasted) is being stored in my database and that I hadn’t really given much thought yet to ensuring a long life for that data.

I put together a shell script that backs up my data to Tarsnap, my preferred cloud backup service for things like this. It has the tagline “online backups for the truly paranoid” and while it perhaps isn’t the best choice for rich media like photos and videos, it’s really great for compressible text data. I use it on all my personal cloud machines to painlessly back up critical files.

So my backup script goes as follows:

#!/bin/sh
pg_dump -U my_username -F t mathsprompt > /tmp/mathsprompt_backup.tar || exit 1
/usr/local/bin/tarsnap -c \
    -f "$(uname -n)-$(date +%Y-%m-%d_%H-%M-%S)-mathsprompt-db" \
    /tmp/mathsprompt_backup.tar 
rm /tmp/mathsprompt_backup.tar

Then I set up a cronjob to handle running this script every day at 5pm. Adding that was as simple as adding the following line (via crontab -e) to my previously configured jobs:

0 17 * * * path/to/tarsnap-db-backup.sh

There are fancier things I could do to backup this database, for instance configuring a streaming backup (a useful suggestion I learned about here), but that’d be overkill for my particular scenario, I think.

Tarsnap is also sufficiently cheap that I don’t need to worry about costs or using up too much space, but in the future I might consider doing some regular cleanup of really old database backups.