Re: Looking for a script that performs full online backup of postgres in archive mode

On Mon, Nov 09, 2009 at 09:15:03AM -0500, Chris Barnes wrote:
> Would anyone in the postgres community have a shell script that
> performs a full online backup of postgres?

Here's roughly what we do:

REMOTE="foo"
DATA="/srv/pgdata"
WAL="/var/lib/pgsql/wal-archive"
PSQL="/usr/bin/psql"
RSYNC="/usr/bin/rsync -e ssh -qxat --delete"

if [ "$1" == "data" ]; then
# Do full backup of data directory
${PSQL} -c "SELECT pg_start_backup('mirror');" >/dev/null
${RSYNC} ${DATA} ${REMOTE}/${DATA}
${PSQL} -c "SELECT pg_stop_backup();" >/dev/null

elif [ "$1" == "wal" ]; then
# Just copy the latest write-ahead logs
${RSYNC} ${WAL} ${REMOTE}/${WAL}
${RSYNC} ${DATA}/pg_xlog/ ${REMOTE}/${DATA}/pg_xlog

else
# Don't know what you want
echo "Usage: $0 [data|wal]"
exit 1
fi

--
Mark
http://www.lambic.co.uk