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

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?

Have you tried pg_dumpall?

--
Sam http://samason.me.uk/

have a shell script that
performs a full online backup of postgres?

Full backup ex. pg_dumpall

Daily backup (dump) / database:
- - - - - - - - - - - - - - -
#!/someshellpath like /bin/sh or /bin/bash or /bin/ksh ...
#daily.sh
#default plaintext
#usage: daily.sh [c|t|p]
#
PORT=5432
HOST="localhost"
DB="mysomedb"
DB_USER="myusername"
FORMAT="p" # c t p=plaintext
TYPE="txt"
COMPRESS=1
case "$1" in
c) FORMAT="c"; COMPRESS=0;;
t) FORMAT="t";;
p) FORMAT="p";;
esac
case "$FORMAT" in
c) TYPE=comp ;;
t) TYPE=tar ;;
p) TYPE="txt" ;;
esac
day=$(date '+%d');

pg_dump -F "$FORMAT" -p "$PORT" -h "$HOST" -U "$DB_USER" "$DB" >
daily.$day.backup.$TYPE
[ "$COMPRESS" = 0 ] && exit 0

# compress daily.$day.backup.$TYPE
gzip daily.$day.backup.$TYPE

- - - - - - - - - - - - - - -
Remember - test also your restore procedure.

That gives me an idea what others are doing.

Thank you Mark, : )

Date: Mon, 9 Nov 2009 09:24:28 -0500
From: postgres@lambic.co.uk
To: compuguruchrisbarnes@hotmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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

_________________________________________________________________
Eligible CDN College & University students can upgrade to Windows 7 before Jan 3 for only $39.99. Upgrade now!
http://go.microsoft.com/?linkid=9691819