Hot Backup using WAL files

From: Damian Lubosch <dl(at)xiqit(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: Hot Backup using WAL files
Date: 2007-04-04 13:53:57
Message-ID: ev0al8$n8o$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello!

I already searched in the archives for similar problems but didn't find any.
I want to have a hot backup server of my master server's database.

I am using NetBSD 3.0.1 with Postgresql 8.2.3.

It works so far, that I receive the WAL Files on the slave every few
minutes from the master. I have a small Script that rebuilds the slave
server with master's data and feeds it with the WAL files that appeared
since then:

(I am doing a binary backup of the master with a perl script from
http://pgfoundry.org/frs/?group_id=1000253 )

Script on the slave:
# Source www.postgresqlforums.com/wiki/Backup_&_Recovery
# Adjusted by Damian dl(at)xiqit(dot)de

PGDIR=/var/databases/pgsql
/etc/rc.d/pgsql stop
cp -Rp $PGDIR/data/pg_xlog /tmp
rm -fR $PGDIR/data
umask 077
su pgsql -c "mkdir -p $PGDIR/data/pg_xlog/archive_status"
# get newest binary backup
FILE=`ls -t $PGDIR/bak/pg* | head -1`
cd $PGDIR/data
gzip -dc $FILE | tar xpf -
cp -Rp /tmp/pg_xlog/* $PGDIR/data/pg_xlog
su pgsql -c "cp $PGDIR/recovery.done $PGDIR/data/recovery.conf"
su pgsql -c "cp $PGDIR/postgresql.conf $PGDIR/data/postgresql.conf"
/etc/rc.d/pgsql start
rm -fR /tmp/pg_xlog

My problem now is, how to continually feed the slave server with new
received WAL files?
The slave just restores up to the state when I ran my script.

I also tried to stop the server rename recovery.done to recovery.conf
and start the server again (without deleting anything). It looks in the
received WAL files' directory and complains about some errors, but
starts after that. I am worried about the errors.

From the logfile:
LOG: received fast shutdown request
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2007-04-04 15:10:11 CEST
LOG: starting archive recovery
LOG: restore_command = "cp ../bak/%f %p"
cp: ../bak/00000001.history: No such file or directory
FATAL: the database system is starting up
LOG: restored log file "00000001000000010000004F" from archive
LOG: invalid xl_info in primary checkpoint record
LOG: using previous checkpoint record at 1/4F000020
LOG: redo record is at 1/4F000020; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 0/25133; next OID: 57344
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: automatic recovery in progress
LOG: redo starts at 1/4F000068
FATAL: the database system is starting up
LOG: restored log file "000000010000000100000050" from archive
cp: ../bak/000000010000000100000051: No such file or directory
LOG: unexpected pageaddr 0/32000000 in log file 1, segment 81, offset 0
LOG: redo done at 1/50000068
LOG: restored log file "000000010000000100000050" from archive
FATAL: the database system is starting up
LOG: archive recovery complete
LOG: database system is ready

Is this the way it works?

Bye, Damian

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Filippi Ghislain 2007-04-04 15:04:05 Re: Hot Backup using WAL files
Previous Message Igor Neyman 2007-04-04 13:17:50 Re: exception handling in postgres plpgsql