Automated PostgreSQL Backups

Automated PostgreSQL Backups ∞

Introduction ∞

Since my first web application back in ’95 I am using PostgreSQL for all my DB needs with the sole exception of MythDB. Since I switched to the Mac I found this nice installer from Preparation ∞

In case you have an 8.3 installation of Enterprise DB you need to upgrade to 8.4 because 8.3 seems to have a broken library path for pg_dump if used as the root user. You can test that by using sudo -s to become the root user and then execute a pg_dump as such.

In order to automate it using launchd you need to have administration privilege in order to install it.

I would suggest that you create a particular directory holding the backups and then you need to decide if you want to keep a history of it. If you use Time Machine to backup you data and you are confident that you don’t need too much of a history I would suggest that you backup a DB with fixed name and let pg_dump overwrite that file every time you run it. Because I am a little bit more paranoid I will use date to make the file name unique so that it is not overwritten.

Create the Backup Script ∞

Even though we might be able to create a single command I would strongly suggest to use a script file because then one can change, add or remove certain actions if necessary without having to change the launchd configuration.

These are the steps to make it work:

Passwordless Login: in order to run pg_dump we need to be able to login to the DB without a password. I think it is best to use a .pgpass file for that purpose
Target Directory: create a target directory where the backups are stored
Backup Script: write the script that creates the backup file name and then executes the backup using pg_dump

In order to avoid that pg_dump is asking for a password and to avoid making the DB to unsecure here a file called .pgpass is used. The content looks like this:

HOST:PORT:DB_NAME:USER:PASSWORD

Everything except the password can be replaced with the * wildcard. I for example use this:

localhost:5432:*:postgres:????

After you created that file in the user’s home directory you need to make it only readable by the current user using:

chmod 0600 .pgpass

Because this is the root user we are talking about this file has to be placed inside /var/root directory.

To test that please try to login into the DB as root using this:

psql ~-~-username=postgres

or whatever user you specified in the .pgpass above. If you can login without being asked for a password we are good to go.

Backup Script ∞

The following script is a little bit verbose because I wanted to make sure that it is working from the command line first so that I did not chase an error with launchd when it was just a script error. The script takes a single argument which is the DB name to be backed up. Then I change the directory because the name of my disk contained a space (should not do that in the future). Then I used date function to create a unique file name and then finally I executed the backup:

#!/bin/sh

cd /Volumes/Mac\ Harddisk
pwd

NOW_STRING="`date +%Y-%m-%d_%H-%M-%S`"
echo $NOW_STRING

DB_NAME=$1
echo $DB_NAME

TARGET_FILE="postgres.backups/postgres.$DB_NAME.backup.$NOW_STRING.sql"
echo $TARGET_FILE

/Library/PostgreSQL/8.4/bin/pg_dump -f $TARGET_FILE --username postgres $DB_NAME

You can place that shell script anywhere but I would suggest to put it under the root’s home directory.

Automate the Execution using Launchd ∞

In order to execute this script at a given time in a given interval we need to do the following:

Launchd PList File: this files defines the execution
Load the PList File: in order to execute it without having to restart the server we load it using launchctl

Launchd Script ∞

Because this script must be run even when nobody is logged in we need to install this as a daemon. This means the plist file must be placed into this directory: /Library/LaunchDaemons/. Just give the file a name like this: .postgres.dbackups.. Add this content:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
        <key>Label</key>
        <string>com.madplanet.postgres.backups.xwiki</string>
        <key>ProgramArguments</key>
        <array>
                <string>/var/root/postgres.backup.sh</string>
                <string>GUGUS</string>
        </array>
        <key>StartCalendarInterval</key>
        <dict>
                <key>Hour</key>
                <integer>12</integer>
                <key>Minute</key>
                <integer>00</integer>
        </dict>
        <key>StartInterval</key>
        <integer>43200</integer>
</dict>
</plist>

Make sure that the string after the Label is named like the file. The strings for the PorgramArguments are:
1. Full Qualified Path to the Script
1. Name of the DB which is the first argument to the script

Then we specify the hour of when the script is executed first and in the StartInterval we define in minutes when the script is executed again (period).

Load and Test the Script ∞

In order to avoid to have to restart the server we load the launchd script by hand inside the /Library/LanuchDaemons directory:

launchctl load

Then we just need to wait until the start time of the script has passed and we see if the script yields the proper result.

Conclusion ∞

This is not rocket science to get it working but there are a lot of little steps which can go wrong. Please make sure that you test each step to avoid barking up the wrong tree.
If there is any mistake, spelling error or anything else not working as expected please let me know.

Cheers – Andy Schaefer

Automated PostgreSQL Backups