DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=UtaEikHXkeyBCdxGGgwtKLmIxOjgTG6nkz53q0Pwng+96Fsf/fx4Y3/kYpkuIEyBOGrqgSd+tIy2o/so3/Ly6b9vGhUN6FuMXqwlTSCBCvtAmi5qbXM7LrWq/Qr86vyUpKd/Tpao+6w4LveuUyQ+rjuYwtYSMQOfAg0zLgx/dJg=;
Message-ID: <818050.20576.qm@web56001.mail.re3.yahoo.com>
Date: Thu, 26 Feb 2009 23:47:44 -0800 (PST)
From: Brad Wiemerslage <wiemersl@yahoo.com>
Subject: warm standby, pg_standby, invalid checkpoint record
To: pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable


I'm attempting to get warm standby up and running with a pair of servers ru=
nning ubuntu 8.04 and postgresql 8.3.  Been following the docs:

http://www.postgresql.org/docs/8.3/static/warm-standby.html
http://www.postgresql.org/docs/current/static/pgstandby.html

Also, basically following the ideas here in this blog post:

http://scale-out-blog.blogspot.com/2009/02/simple-ha-with-postgresql-point-=
in-time.html

I've customized the original script he refers to in the article, which is h=
ere in its entirety for reference:

https://s3.amazonaws.com/extras.continuent.com/standby.sh

Here is the meat of my customized script, which runs on the standby.  The p=
ostgresql server on the standby is stopped first.=20

start_backup=3D"SELECT pg_start_backup('my_backup');"
stop_backup=3D"SELECT pg_stop_backup();"
echo "$start_backup" | $psql -h$PRIMARY -U myuser -d mydb -e
rsync --delete -avz -e "ssh -i /path/to/key" myuser@$PRIMARY:$PG_DATA/ $PG_=
DATA
echo "$stop_backup" | $psql -h $PRIMARY -U myuser -d mydb -e

The files seem to copied over to the standby machine just fine.  Success is=
 reported with respect to the backup commands.  Permissions seem fine. =20

Next, there are some steps which blow out some files.  As I understand it, =
you no longer need the files on the standby that were in pg_xlog on the pri=
mary.=20

rm -f $PG_DATA/recovery.*
rm -f $PG_DATA/8.3/main/logfile
rm -f $PG_DATA/8.3/main/postmaster.pid
rm -f $PG_DATA/8.3/main/pg_xlog/0*
rm -f $PG_DATA/8.3/main/pg_xlog/archive_status/0*

This step seems to work fine.=20

Then, the archives are pulled.  They are pulled to /mnt/postgresql_archives=
 with this command:

rsync --delete -avz -e "ssh -i /path/to/key" myuser@$PRIMARY:$PG_ARCHIVES/ =
$PG_ARCHIVES

Everything looks good.  I end up with an up to date list of WAL files in /m=
nt/postgresql_archives on the standby.  Here is a listing:

root@standby:/mnt/postgresql_archives# ls
total 688996
drwxr-xr-x  2 postgres postgres     4096 2009-02-27 01:13 .
drwxr-xr-x 14 root     root         4096 2009-02-27 01:17 ..
-rw-rw----  1 postgres postgres 16777216 2009-02-27 00:19 00000001000000000=
00000CB
-rw-rw----  1 postgres postgres 16777216 2009-02-27 00:29 00000001000000000=
00000CC
-rw-rw----  1 postgres postgres 16777216 2009-02-27 00:38 00000001000000000=
00000CD
-rw-rw----  1 postgres postgres      245 2009-02-27 00:38 00000001000000000=
00000CD.00000020.backup
-rw-rw----  1 postgres postgres 16777216 2009-02-27 00:48 00000001000000000=
00000CE
-rw-rw----  1 postgres postgres 16777216 2009-02-27 00:54 00000001000000000=
00000CF
-rw-rw----  1 postgres postgres 16777216 2009-02-27 00:58 00000001000000000=
00000D0
-rw-rw----  1 postgres postgres 16777216 2009-02-27 01:01 00000001000000000=
00000D1
-rw-rw----  1 postgres postgres 16777216 2009-02-27 01:03 00000001000000000=
00000D2
-rw-rw----  1 postgres postgres 16777216 2009-02-27 01:13 00000001000000000=
00000D3

Then, the recovery.conf is put in place.  I've tried two different versions=
, which end up giving me the same error.  Here are the two different versio=
ns. =20

#1: restore_command =3D '/usr/lib/postgresql/8.3/bin/pg_standby -c -d -s 2 =
-t /mnt/postgresql_archives/pgsql.trigger /mnt/postgresql_archives %f %p >>=
 /mnt/postgresql_archives/standby.log 1>&2'

#2: restore_command =3D 'cp /mnt/server/archivedir/%f "%p"'

I don't believe that #2 is suitable for warm standby, but just tried it to =
debug after #1 wouldn't work.  Now, I try to start up the server.  For it t=
o work in standby mode, additional archive files will be pulled from the pr=
imary machine on a periodic basis.  I'm using this command, which deletes t=
hem on the primary when they are no longer necessary.  It also seems to wor=
k fine.   =20

rsync -avz -e "ssh -i /path/to/key" myuser@$PRIMARY:$PG_ARCHIVES/ $PG_ARCHI=
VES

I guess I'm a little confused about exactly what is happening here when the=
 server comes up, but here is the error message I'm getting.  It seems to b=
e looking for the files in pg_pxlog, which is cleared out.  So, the error m=
akes sense.  But isn't it supposed to be looking in /mnt/postgresql_archive=
s per the restore_command(s)?  The files are available there.  =20

2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,2,,2009-02-27 01:26:52 ES=
T,,0,LOG,58P01,"could not open file ""pg_xlog/0000000100000000000000CD"" (l=
og file 0, segment 20
5): No such file or directory",,,,,,,,
2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,3,,2009-02-27 01:26:52 ES=
T,,0,LOG,00000,"invalid checkpoint record",,,,,,,,
2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,4,,2009-02-27 01:26:52 ES=
T,,0,PANIC,XX000,"could not locate required checkpoint record",,"If you are=
 not restoring from a
=A0backup, try removing the file ""/var/lib/postgresql/8.3/main/backup_labe=
l"".",,,,,,
2009-02-27 01:26:52.868 EST,,,7419,,49a787ab.1cfb,1,,2009-02-27 01:26:51 ES=
T,,0,LOG,00000,"startup process (PID 7422) was terminated by signal 6: Abor=
ted",,,,,,,,
2009-02-27 01:26:52.868 EST,,,7419,,49a787ab.1cfb,2,,2009-02-27 01:26:51 ES=
T,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,

So, I tried copying the files in the /mnt/postgresql_archives over to pg_xl=
og.  This seemed to work, and the updates were applied.  Never at any point=
 did I get a recovery.done file.  Also, for whatever reason, I was never ab=
le to get any debug info from pg_standby in standby.log.

Anyhow, I've burned up a couple days trying to figure this out.  Any help w=
ould be much appreciated. =20

Thanks,
Brad=0A=0A=0A