Updated PostgreSQL backup scripts for 10.8.2 / OS X Server 2.2

IMPORTANT (2013-11-19): All of this has changed in Mavericks / OS X Server 3.x. Each service has its own instance of Postgres. See some of the comments below for the new socket locations.

In Mountain Lion 10.8.2 along with OS X Server 2.2, Apple started employing two separate instances of Postgres, one for the built-in services, and the other for use by custom applications (e.g. Roundcube). My old backup script doesn’t properly understand the differences, or the new socket, so I have had to rewrite it. I also quickly banged up a second version that backs up the user databases too.

First, the system databases:

#!/bin/bash
backupdir='/Library/Server/Backups/PostgreSQL/';
server_root='/Applications/Server.app/Contents/ServerRoot';
psql_socket='/Library/Server/PostgreSQL For Server Services/Socket/';

day=`date +%u`;
mkdir -p $backupdir/$day;

echo "PostgreSQL Backup for $HOSTNAME";
echo "================================="
echo `date`;
for database in `$server_root/usr/bin/psql -U _postgres -h "$psql_socket" -lt | awk '{print $1}' | grep -vE '\||^$|template|postgres'`;
    do
    printf "Exporting $database...";
    $server_root/usr/bin/pg_dump -U _postgres -h "$psql_socket" -c $database | gzip -c > $backupdir/$day/$database.sql.gz;
    /bin/chmod 600 $backupdir/$day/$database.sql.gz;    
    printf "done\n";
done
echo `date`;
echo;
exit 0;

Obviously, update the backupdir to the directory of your choice.

Next, the script to backup any databases you’ve added:

#!/bin/bash
backupdir='/Library/Server/Backups/PostgreSQL/';
server_root='/Applications/Server.app/Contents/ServerRoot';

day=`date +%u`;
mkdir -p $backupdir/$day;

echo "PostgreSQL Backup for $HOSTNAME";
echo "================================="
echo `date`;
for database in `$server_root/usr/bin/psql -U _postgres -lt | awk '{print $1}' | grep -vE '\||^$|template|postgres'`;
    do
    printf "Exporting $database...";
    $server_root/usr/bin/pg_dump -U _postgres -c $database | gzip -c > $backupdir/$day/$database.sql.gz;
    /bin/chmod 600 $backupdir/$day/$database.sql.gz;    
    printf "done\n";
done
echo `date`;
echo;
exit 0;

The only difference is that the first script uses a custom socket.

Tuesday, March 5, 2013   ()