Postgresql base backup script
- by Terry Lorber
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
