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