Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
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} .... –  Dennis Williamson Jun 14 '10 at 18:49
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Great tip about doing filesystem-level backups on the slave. –  Terry Lorber Jun 14 '10 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 '10 at 21:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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