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:
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.