Contributed by Marcel Gsteiger

After a few hours of troubleshooting, I finally have my PostgreSQL database backup running. My backup job creates separate schema/data backups for each database, along with a separate backup of global objects. This is much easier and safer to handle than “all-in-one-file” backups. Moreover, my scripts backup the data through pipes. So there is no need for additional disk space for large database backups. The script automatically determines which databases are on the server. My server is a CentOS 4.2 w/SELinux enabled running bacula-1.38.5-4 with postgresql backend on a x86_64 dual xeon box with a ultrium-3 tape attached. The data is always spooled through a separate RAID 0 array (the tape is too fast for my other disks). My postgresql is 8.1.2, but my scripts should also work with versions >= 7.3 or perhaps 7.4 .

I hope this is useful for somebody else, too.

regards –Marcel

First, create the directory /var/lib/pgsql/data/dump and /var/lib/pgsql/data/dump/fifo , chown postgres:bacula, chmod 750. Ensure that the database user postgres running on the local host has “trust” access to all databases (no passwords needed). This script also works for backup of remote databases, but ensure that access rights are set properly.

If you prefer to have a password, you can uncomment the lines EXPORT PGPASSWORD=xxxxxxxx or, even better, add a ~/.pgpass file.

Create these files:

/etc/bacula/make_database_backup: owner root, group postgres, chmod g+x:

#!/bin/sh
exec > /dev/null
DUMPDIR=/var/lib/pgsql/data/dump
FIFODIR=$DUMPDIR/fifo
export PGUSER=postgres
#export PGPASSWORD=xxxx                       # only when pg_hba.conf requires it
/usr/bin/pg_dumpall -g >$DUMPDIR/globalobjects.dump   # hopefully never a big file, so no need for a fifo
rm -f $FIFODIR/*.data.dump
for dbname in `psql -d template1 -q -t <<EOF
select datname from pg_database where not datname in ('bacula','template0') order by datname;
EOF
` 
do
 mkfifo $FIFODIR/$dbname.schema.dump
 /usr/bin/pg_dump --format=c -s $dbname --file=$FIFODIR/$dbname.schema.dump 2>&1 < /dev/null & 
 mkfifo $FIFODIR/$dbname.data.dump 
 /usr/bin/pg_dump --format=c -a $dbname --file=$FIFODIR/$dbname.data.dump 2>&1 < /dev/null &
done

/etc/bacula/delete_database_backup: owner root, group postgres, chmod g+x:

#!/bin/sh
DUMPDIR=/var/lib/pgsql/data/dump
FIFODIR=$DUMPDIR/fifo
for dbname in `psql -U postgres -d template1 -q -t <<EOF
select datname from pg_database where not datname in ('bacula','template0') order by datname;
EOF
` 
do
 kill `ps aux | fgrep "/usr/bin/pg_dump" | fgrep " --file=$FIFODIR/$dbname.schema.dump" | awk '{print $2}'`
 rm -f $FIFODIR/$dbname.schema.dump
 kill `ps aux | fgrep "/usr/bin/pg_dump" | fgrep " --file=$FIFODIR/$dbname.data.dump" | awk '{print $2}'`
 rm -f $FIFODIR/$dbname.data.dump
done
rm -f $DUMPDIR/globalobjects.dump

….use this helper file to determine the backups needed: /etc/bacula/listdbdump

#!/bin/sh
FIFODIR=/var/lib/pgsql/data/dump/fifo
for dbname in `psql -d template1 -q -U postgres -h $1 -p $2 -t  <<EOF
select datname from pg_database where not datname in ('bacula','template0') order by datname;
EOF
`
do
 echo "$FIFODIR/$dbname.schema.dump"
 echo "$FIFODIR/$dbname.data.dump"
done

….create these entries in bacula-dir.conf:

Job {
  Name = "hymost-db"
  JobDefs = "DefaultJob"
  Level = Full
  FileSet="myhost-db"   
  Client = myhost-fd   
  Schedule = "WeeklyCycleAfterBackup"
  # This creates a backup of the databases with pg_dump to fifos
  Client Run Before Job = "su - postgres -c \"/etc/bacula/make_database_backup\"" 
  # This deletes the backup and fifo files
  Client Run After Job  = "su - postgres -c \"/etc/bacula/delete_database_backup\""
  Priority = 17                   # run after main backup
}

FileSet {
  Name = "myhost-db"   
  Include {
     Options {
        signature = MD5
        readfifo = yes   
     }
       File = "/var/lib/pgsql/data/dump/globalobjects.dump"
       File = "|/etc/bacula/listdbdump myhost-or-ip.mynet.lan 5432"
  }
}

When trying to backup my PostgreSQL databases with pg_dumpall through a pipe, i noticed that the little trick with ”exec > /dev/null” does not work for me. Probably because pg_dumpall sends data to stdout and ”exec > /dev/null” redirects stdout to ”/dev/null” (correct me if i am wrong). So i use a slightly different approach with a terminal multiplexer (screen)

backupPostgresqlBefore.sh

#!/bin/bash

FIFO="/var/tmp/postgresql.dump"

if [ -r $FIFO ]; then
        rm -f $FIFO
        if [ "$?" -ne "0" ]; then
                exit 1
        fi
fi

mkfifo $FIFO
if [ "$?" -ne "0" ]; then
  exit 1
fi

screen -d -m -S pgdump -t pgdump su -s /bin/bash -c "pg_dumpall -o -U postgres > ${FIFO}"

backupPostgresqlAfter.sh

#!/bin/bash

FIFO="/var/tmp/postgresql.dump"

rm $FIFO

You can also do backup with bpipe. The downside for bpipe is that you can't restore a database side by side (not with this configuration anyway). So the only way to restore is to drop the database and restore or restore to a separate host. The benefits when compared to FIFO or File based backups is that backups are made directly from the database and no pre and post scripts are needed. Also you don't need to prepare separately for the restore, as you do with FIFO backups.

Create the following script on the client: /etc/bacula/scripts/bpipe-pg-db

#!/bin/sh
# Define the command to get proper rights for postgresql access
SUCMD="su - postgres -c"

# Get databases to backup
for dbname in `$SUCMD 'psql -d template1 -q -U postgres -t'  <<EOF
select datname from pg_database where not datname in ('bacula','template0') order by datname;
EOF
`
do
 echo "bpipe:/PGSQL/$dbname.dump:$SUCMD '/usr/bin/pg_dump --format=c $dbname':$SUCMD 'pg_restore -d template1 -C'"
done

Make sure that your client is configured for plugins, the Plugins Directory must be set in the client configuration for bpipe to work.

Next create the backup fileset:

FileSet {
  Name = "PG Database Fileset"
  Include {
    Plugin = "\\|/etc/bacula/scripts/bpipe-pg-db"
  }
}

Run a backup and verify that you can see /PGSQL/* files.