The first thing I did was create a script which would do the actual backup. Now don’t get the impression that I’m some kind of genius here. I’m basically lazy and my script is based off of some other fine work you can find here and here.

#!/bin/bash

logfile="/Where/I/want/the/log/Postgres_backups/backup.log" 
backup_dir="/Where/I/want/the/backups/Postgres_backups" 
databases=`/sw/pg/bin/psql -h localhost -U postgres -d test -l | sed -n 4,/\eof/p | grep -v rows\) | grep -v template | awk {'print$1'}`

rm -rf $logfile
touch $logfile

for i in $databases
do
        timeinfo=`date '+%T %x'`
        echo "Backup and Vacuum complete at $timeinfo for database: $i" >> $logfile
        /sw/pg/bin/vacuumdb -z -h localhost -U postgres $i > /dev/null 2>&1
        /sw/pg/bin/pg_dump $i -h localhost -U postgres | gzip > "$backup_dir/$i.$(date +%d).sql.gz" 
done

Essentially, what I’m doing here is creating a list of all of my databases, and then for each one I’m first doing a “Vacuum” and then a backup. The backups are being compressed by gzip and they all have a day stamp on them.

This should effectively give me a daily backup and with the new month the files should overwrite themselves. This way I have about 30 days of backups, but the directory won’t grow to infinity.

Now for OS X the trick is scheduling the script to run. For that I borrowed heavily on the plist file put together by alpha2zee in the link to Entropy.ch above. My first attempt looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
    <dict>
        <key>Label</key>
        <string>com.postgresql.backup</string>
        <key>LowPriorityio</key>
        <true/>
        <key>ProgramArguments</key>
        <array>
                <string>/Users/postgres/Documents/postgresql.bkup</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
        <key>RunOnDemand</key>
        <true/>
        <key>ServiceDescription</key>
        <string>Daily backups for all PostgreSQL databases</string>
        <key>StartCalendarInterval</key>
        <dict>
                <key>Hour</key>
                <integer>9</integer>
                <key>Minute</key>
                <integer>15</integer>
        </dict>
    </dict>
</plist>

I say this is my first attempt because I’m not all that familiar with the launchd facility. However I loaded the plist file with the launchd command and it didn’t give me an error. So assuming that I start seeing new backup files in my backup directory, everything will be working just fine.

So there you have it! If you don’t backup already, please start doing it! The more we all rely on these machines, the more it’s a real disaster when something happens to them.