PostgreSQL Non-exclusive Base Backups in Bash

Here I'm posting a bash script that implements PostgreSQL's new (since 9.6) "non-exclusive" base backup.

I often find that new customers are shy about binary PostgreSQL backups and only schedule logical pg_dump backups. PostgreSQL provides a brilliantly simple binary backup solution that enables easy point in time recovery, unlike pg_dump backups. So I always try to steer people towards these binary "base backups", as they are called in the PostgreSQL world.

In version 9.6, this base backup feature became more flexible, with an additional option allowing more than one base backup to run at a time. For customers with aggressive (or badly scheduled) backup schedules, this allows both backups to succeed, where they would both fail before version 9.6.

The new option is a boolean argument to sql functions pg_start_backup() and pg_stop_backup(). However the new option isn't so brilliantly simple to use in shell scripts along with its common associates tar or rsync. It requires concurrent processes or threads -- because pg_start_backup() and pg_stop_backup() must be run in the same session -- and it requires parsing text output from pg_stop_backup() and writing out to files backup_label and tablespace_map. I wanted the flexibility of the non-exclusive backup behavior but I also wanted the simplicity of a shell script. Below is the solution I worked out. This is a minimal implementation without proper error handling, but it demonstrates a technique to solve the new problem.

The tricky thing was running the stop and start functions in psql in the same session while running the file backup in between them in a different process. I accomplished it by using both the --command and --file options of psql.  The --command option runs pg_start_backup(), and the --file option listens on a fifo for pg_stop_backup(). After tar/rsync completes, bash echoes the stop command to the fifo then uses bash's "wait" builtin command to wait for psql/PostgreSQL to stop the backup. Then at the end, use awk to parse the output of pg_stop_backup() and write it to files in the backup directory.

If you try it out, please let me know your experience!

#Minimal implementation of PostgreSQL 9.6+ "non-exclusive" base backup API.

set -e
set -x

TMP_OUT=$(tempfile -d /tmp)

if [ -n "$(jobs)" ] ; then
    echo "Background jobs are running. Please run this script in a different (or sub-) shell. Exiting"
    exit 1

#Make a fifo where psql will listen for the pg_stop_backup() statement:
mkfifo -m 600 $TMP_FIFO

#psql executes a command then waits in the background for data on the fifo:
psql --pset=tuples_only=true --pset=format=unaligned --pset=footer=false -c "select pg_start_backup('my backup', true, false)" -f $TMP_FIFO postgres > $TMP_OUT 2> /dev/null &

#Run rsync or tar here. The exact details will depend on your system. For example:
tar -c --exclude=$PGDATA/postmaster* --exclude=$PGDATA/pg_xlog/* -f $BACKUP_DESTINATION/base_backup.tar $PGDATA

echo "select * from pg_stop_backup(false)" > $TMP_FIFO

#wait for psql to return from background:
wait -n %1

#Treat the whole file as a single line delimited by pipes. Use sed to strip empty lines:
awk 'BEGIN { RS = "\x00" ; FS = "|" } { print $2 }' $TMP_OUT > $BACKUP_DESTINATION/backup_label
awk 'BEGIN { RS = "\x00" ; FS = "|" } { print $3 }' $TMP_OUT | sed -e '/^$/d' > $BACKUP_DESTINATION/tablespace_map