Re: Hot Backup using WAL files

Hi,

Found on the dev mailing list (hidden well)...

On the master postgresql.conf:
archive_command = 'rsync -arv "%p"
slave_hostname:/.../wal_archive/"%f";'
(and maybe '&cp "%p" /.../wal_archive/"%f";')
archive_timeout= 900

So, you need to perform a ssh-keygen on both sides.

Now, the slave recovery.conf:
restore_command = '/.../RECOVER/restore.sh /.../wal_archive/%f "%p"'

and the restore.sh script:
************
#!/bin/bash

RESTORE_FROM=$1
RESTORE_TO=$2
DELAY=100000
TRIGGERED=0
TRIGGER_FILE="{$POSTGRES_PATH}/RECOVER /trigger"

copyfunc() {
if [ "$TRIGGERED" -eq "0" ]; then
        cp -v -i $RESTORE_FROM $RESTORE_TO
fi
}

k=`expr $1 : '.*\(history\)'`

if [ "$k" == "history" ]; then
copyfunc;
exit $?;
fi

while [ ! -f "$RESTORE_FROM" -a "$TRIGGERED" -eq "0" ]; do
                usleep $DELAY;
                if [ -e $TRIGGER_FILE ]; then
                        TRIGGERED=1;
                fi
done

copyfunc;
***********


finally, you just need to launch /etc/rc.d/pgsql start (nohup)

The logfile:
2007-03-26 10:19:02 CEST    LOG:  database system was interrupted at
200703-25 20:05:01 CEST
2007-03-26 10:19:02 CEST    LOG:  starting archive recovery
2007-03-26 10:19:02 CEST    LOG:  restore_command = "/.../restore.sh
/.../wal_archive/%f "%p""
cp: cannot stat `/.../wal_archive/00000001.history': No such file or
directory
`/.../wal_archive/0000000100000023000000AD.00000068.backup' ->
`pg_xlog/RECOVERYHISTORY'
2007-03-26 10:19:02 CEST    LOG:  restored log file
"0000000100000023000000AD.00000068.backup" from archive
`/..../wal_archive/0000000100000023000000AD' -> `pg_xlog/RECOVERYXLOG'
2007-03-26 10:19:02 CEST    LOG:  restored log file
"0000000100000023000000AD" from archive
2007-03-26 10:19:02 CEST    LOG:  checkpoint record is at 23/AD000068
2007-03-26 10:19:02 CEST    LOG:  redo record is at 23/AD000068; undo
record is at 0/0; shutdown FALSE
2007-03-26 10:19:02 CEST    LOG:  next transaction ID: 0/1068654; next
OID: 85648
2007-03-26 10:19:02 CEST    LOG:  next MultiXactId: 31; next
MultiXactOffset: 62
2007-03-26 10:19:02 CEST    LOG:  automatic recovery in progress
2007-03-26 10:19:02 CEST    LOG:  redo starts at 23/AD0000B0
`/.../wal_archive/0000000100000023000000AE' -> `pg_xlog/RECOVERYXLOG'
2007-03-26 10:19:02 CEST    LOG:  restored log file
"0000000100000023000000AE" from archive
`/.../wal_archive/0000000100000023000000AF' -> `pg_xlog/RECOVERYXLOG'
2007-03-26 10:19:02 CEST    LOG:  restored log file
"0000000100000023000000AF" from archive

today...
2007-04-04 15:50:38 CEST    LOG:  restored log file
"000000010000002700000071" from archive
`/.../wal_archive/000000010000002700000072' -> `pg_xlog/RECOVERYXLOG'
2007-04-04 16:05:38 CEST    LOG:  restored log file
"000000010000002700000072" from archive
`/.../wal_archive/000000010000002700000073' -> `pg_xlog/RECOVERYXLOG'
2007-04-04 16:20:38 CEST    LOG:  restored log file
"000000010000002700000073" from archive
`/.../wal_archive/000000010000002700000074' -> `pg_xlog/RECOVERYXLOG'
2007-04-04 16:35:38 CEST    LOG:  restored log file
"000000010000002700000074" from archive

And if you try to connect with psql:
FATAL:  the database system is starting up

Regards,
Ghislain