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?


today=`date +%Y%m%d-%H%M%S`

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`

echo "Begin CheckPoint is $CP"

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

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

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


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
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
  • 1
    I think the command substitution with echo is unnecessary either of the two times you set REF_FILE. Just do var="${vars} and text". You can eliminate the grep -v from your find by doing this: ... -not -newer ${REF_FILE} -not -name ${REF_FILE} .... Jun 14, 2010 at 18:49

1 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.

  • Great tip about doing filesystem-level backups on the slave. Jun 14, 2010 at 18:17
  • Note that the slave-dumping method isn't without complications -- The usual obligatory warning about doing a restore test in a sterile environment applies :)
    – voretaq7
    Jun 14, 2010 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.