PostgreSQL automated daily backups
I needed to set up an automated backup of a Postgresql database on an Ubuntu Server 10.04 box and it took more Googling than should be necessary. I wanted to keep nightly snapshots for the past week which makes it a tiny bit more difficult than simply running pg_dump from a crontab. Here’s the one liner shell script I ended up with:
#!/bin/bash
pg_dump -U DBUSERNAME -b -F c -f "/path/to/backups/postgresql/`date +%w`.backup" MYDBNAME
Make sure to setup your .pgpass file if you haven’t already. Test it on the command line and if it’s all good just add it to your crontab file. I have mine running every morning at 2AM.
If you have to deal with daylight saving, you’d better start your backups before 2 AM or after 3 AM.
Check the (current) manual to get all information about pg_dump:
http://www.postgresql.org/docs/current/interactive/app-pgdump.html
That’s a good point, but in this particular instance the business using the database is always closed on Sundays so the Saturday and Sunday backups will be the same and it won’t matter if twice a year there’s only one copy of them.