PostgreSQL Autobackup and Autoremove Script

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

pg_backup.sh

#!/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

Important Note:
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!

Comments are closed.