pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHot Backup using WAL files
4+ messages / 4 participants
[nested] [flat]
* Hot Backup using WAL files
@ 2007-04-04 13:53 Damian Lubosch <dl@xiqit.de>
2007-04-04 15:07 ` Re: Hot Backup using WAL files Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Damian Lubosch @ 2007-04-04 13:53 UTC (permalink / raw)
To: 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@xiqit.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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hot Backup using WAL files
2007-04-04 13:53 Hot Backup using WAL files Damian Lubosch <dl@xiqit.de>
@ 2007-04-04 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-07 22:33 ` Re: Hot Backup using WAL files Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2007-04-04 15:07 UTC (permalink / raw)
To: Damian Lubosch <dl@xiqit.de>; +Cc: pgsql-admin
Damian Lubosch <dl@xiqit.de> writes:
> My problem now is, how to continually feed the slave server with new
> received WAL files?
You need to make the restore_command script wait until the next WAL file
is available, instead of failing immediately.
This is actually not that simple to get right. You might want to try
pg_standby which will ship with 8.3:
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_standby/
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hot Backup using WAL files
2007-04-04 13:53 Hot Backup using WAL files Damian Lubosch <dl@xiqit.de>
2007-04-04 15:07 ` Re: Hot Backup using WAL files Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-07 22:33 ` Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2007-04-07 22:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Damian Lubosch <dl@xiqit.de>; pgsql-admin
On Wed, 2007-04-04 at 11:07 -0400, Tom Lane wrote:
> Damian Lubosch <dl@xiqit.de> writes:
> > My problem now is, how to continually feed the slave server with new
> > received WAL files?
>
> You need to make the restore_command script wait until the next WAL file
> is available, instead of failing immediately.
>
> This is actually not that simple to get right. You might want to try
> pg_standby which will ship with 8.3:
> http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_standby/
Thanks Tom.
pg_standby will be back compatible with 8.2 also, though isn't available
as part of the 8.2 distribution. The README will contain information for
both 8.2 and 8.3 releases, since there will be one minor difference.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Hot Backup using WAL files
@ 2007-04-04 15:04 Filippi Ghislain <gfilippi@prosodie.com>
0 siblings, 0 replies; 4+ messages in thread
From: Filippi Ghislain @ 2007-04-04 15:04 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-04-07 22:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-04 13:53 Hot Backup using WAL files Damian Lubosch <dl@xiqit.de>
2007-04-04 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-07 22:33 ` Simon Riggs <simon@2ndquadrant.com>
2007-04-04 15:04 Re: Hot Backup using WAL files Filippi Ghislain <gfilippi@prosodie.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox