Here’s a little script I wrote to automate PostgreSQL database backups. The nice thing about this script is that it also deletes old backups after some (configurable) time. That way it prevents your hard disk getting full with old backups and no need to delete them manually.
Output format will be: “<databasename>–<Year-Month-Day.Hour:Minute>.pgsql”
Example backup file “pgdatabase-2011-09-18.15:10.pgsql”
#!/bin/sh # pgsql backups db_user="pgsql" db_name="pgdatabase" db_dumpdir="/var/backups/pgsql" date_format="`date +%Y-%m-%d.%H:%M`" # Delete dumps older then 'expire_time' # Format can be: # s second # m minute (60 seconds) # h hour (60 minutes) # d day (24 hours) # w week (7 days) # '+' indicates 'older then' # '-' indicates 'younger then' expire_time="+2w" # Begin Script # /usr/local/bin/pg_dump -U $db_user $db_name -f "$db_dumpdir/$db_name-$date_format.pgsql" /usr/bin/find $db_dumpdir -d 1 -Btime $expire_time -delete
Run pg_backup.sh hourly (at 10 minutes past every hour)
# crontab -e
10 * * * * /usr/local/scripts/pg_backup.sh > /dev/null 2>&1
This script is made to run on FreeBSD and works fine for me. If you want to make it run on GNU/Linux consider reading the GNU find documentation cause the implementation is different. Also, be _very_ careful when testing scripts that automatic delete your data! You have been warned!