Postgresql base backup script

Question

I'm using the following script to do a file-level backup of Postgresql. I sometimes see that the last part, to do cleanup after "pgs_backup_stop" is called, hangs while it waits for the last WAL to be created. The REF_FILE to search for is sometimes wrong.

I'm also shipping these files to a different machine, every 5 minutes via rsync.

What do other people do to safely remove old WAL files?

#!/bin/bash

PGDATA=/usr/local/pgsql/data
WAL_ARCHIVE=/usr/local/pgsql/archives
PGBACKUP=/usr/local/pgsqlbackup
PSQL=/usr/local/pgsql/bin/psql
today=`date +%Y%m%d-%H%M%S`
label=base_backup_${today}

echo "Executing pg_start_backup with label $label in server ... "

CP=`$PSQL -q -Upostgres -d template1 -c "SELECT pg_start_backup('$label');" -P tuples_only -P format=unaligned`
RVAL=$?

echo "Begin CheckPoint is $CP"


if [ ${RVAL} -ne 0 ]
  then
  echo "PSQL pg_start_backup failed"
  exit 1;
fi
echo "pg_start_backup executed successfully"


echo "TAR begins ... "
pushd $PGBACKUP
tar -cjf pgdata-$today.tar.bz2 --exclude='pg_xlog' $PGDATA/*
popd
echo "TAR completed"

echo "Executing pg_stop_backup in server ... "
$PSQL -Upostgres template1 -c "SELECT pg_stop_backup();"
if [ $? -ne 0 ]
  then
  echo "PSQL pg_stop_backup failed"
  exit 1;
fi
echo "pg_stop_backup done successfully"

TO_SEARCH="*${CP:0:2}000000${CP:3:2}.00${CP:5}"

echo "Check for ${WAL_ARCHIVE}/${TO_SEARCH}.backup"

while [ ! -e ${WAL_ARCHIVE}/${TO_SEARCH}.backup ]; do
  echo "Waiting for ${WAL_ARCHIVE}/${TO_SEARCH}.backup"
  sleep 1
done
REF_FILE="`echo ${WAL_ARCHIVE}/*${CP:0:2}000000${CP:3:2}`"

echo "Reference file ${REF_FILE}"

# "-not -newer" or "\! -newer" will also return REF_FILE
# so you have to grep it out and use xargs; otherwise you
# could also use the -delete action
find ${WAL_ARCHIVE} -not -newer ${REF_FILE} -type f | grep -v "^${REF_FILE}$" | xargs rm -f


REF_FILE="`echo ${PGBACKUP}/pgdata-$today.tar.bz2`"

echo "Reference file ${REF_FILE}"

find $PGBACKUP -not -newer ${REF_FILE} -type f -name pgdata* | grep -v "^${REF_FILE}$" | xargs rm -f

Answer 1

For cleaning up archived WAL segments on the master I just delete anything in xlog_archive more than N days old (N=30 right now, because I have a lot of disk space and my activity doesn't come close to filling the archive within 30 days).

---

Re: your backup process in general --
If you are doing WAL-shipping replication with pg_standby you can let pg_standby deal with the WAL files on the slave (See http://www.postgresql.org/docs/current/static/pgstandby.html - Basically you only need to keep enough WAL segments around on the slave to get through startup/recovery).

If this is what you're doing I suggest making your filesystem-level backup on the slave (stop the slave server, back it up, restart it & let it catch up with the WAL replay) -- This avoids the pg_start_backup() / pg_stop_backup() checkpointing & disk activity that can lag your master server (the hang you referred to in your question is an artifact of that activity) and keeps the load on your master server down.