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

 
Would anyone in the postgres community have a shell script that performs a full online backup of postgres?
 
Any help would be appreciated.
 
 
 
 
 
 
 
 

---

Windows Live: Keep your friends up to date with what you do online.

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/

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

 > 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
>

---

Get Windows 7 for only $39.99-CDN College or University students only. This offer ends Jan 3-upgrade now!