postgresqlI’ve been working with postgres in my free time for a while now. I have been playing with fire, so to speak. I haven’t had any sort of backup solution on my postgres server. So after a bit of googling and reading postgres docs, I’ve came up with a very basic backup script.

What it does is backup each database separately and zips them up in order to save on space. It puts an hour – minute stamp on the database file and backs the DB up to a directory that is the current date. I’m going to add more to the script later, like only keeping so many backups, etc. But at this point, I just wanted to get a script running that creates backups just in case the server’s main drive dies or I botch something.

So without further ado, here’s the code of the script. I do have to give credit to the author of this post, as I heavily modified that script to do what I needed it to do. The big thing was the sed-awk line, as my sed-fu is quite poor.

#! /bin/bash
# PGSQL backup script

# logfile loc
logfile="/var/log/pgbackup.log"
# backup loc
backup_dir="/backup/pg_backups/"
touch $logfile
timeslot=`date +%H-%M`
monthday=`date +%F`

# mount /backup, create the dir and get a list of dbs
/bin/mount /backup
/bin/mkdir $backup_dir/$monthday
databases=`psql -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

#backup the dbs
for i in $databases; do
        timeinfo=`date '+%T %x'`
        echo "Backup complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
	/usr/bin/pg_dump -U postgres $i | gzip > "$backup_dir/$monthday/postgre-$i-$timeslot-db.gz"
done

#unmount the backup drive
/bin/umount /backup

And that’s that. It’s a pretty simple script, but it gets the job done. I’m obviously running the script on the machine that postgres is on and the server I’m running it on is Ubuntu LTS with a backup drive in the server, so your milage may vary depending on your distro and your setup. You may want to test the script first before setting it as a cron on your DB server. Speaking of the cron, here’s the line you’ll want to set in your root crontab.

00 06 * * * /root/bin/pgbackup.sh > /dev/null 2>&1

I have the cron set to run at 6am since I’m asleep at 6am, so no one will be accessing the DBs. And now my DBs are safe from me and my postgres nubness.