MIME-Version: 1.0
In-Reply-To: <818050.20576.qm@web56001.mail.re3.yahoo.com>
References: <818050.20576.qm@web56001.mail.re3.yahoo.com>
Date: Wed, 4 Mar 2009 18:23:18 -0500
Message-ID: <16b031c0903041523l4cb2b52fg6e50068a043a5915@mail.gmail.com>
Subject: Re: warm standby, pg_standby, invalid checkpoint record
From: Lee Azzarello <lee@dropio.com>
To: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

does a file named 0000000100000000000000CD exist anywhere on your disk?

-lee

On Fri, Feb 27, 2009 at 2:47 AM, Brad Wiemerslage <wiemersl@yahoo.com> wrot=
e:
>
> I'm attempting to get warm standby up and running with a pair of servers =
running ubuntu 8.04 and postgresql 8.3. =A0Been 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-poin=
t-in-time.html
>
> I've customized the original script he refers to in the article, which is=
 here 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. =A0T=
he postgresql server on the standby is stopped first.
>
> 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/ $P=
G_DATA
> echo "$stop_backup" | $psql -h $PRIMARY -U myuser -d mydb -e
>
> The files seem to copied over to the standby machine just fine. =A0Succes=
s is reported with respect to the backup commands. =A0Permissions seem fine=
.
>
> Next, there are some steps which blow out some files. =A0As I understand =
it, you no longer need the files on the standby that were in pg_xlog on the=
 primary.
>
> 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.
>
> Then, the archives are pulled. =A0They are pulled to /mnt/postgresql_arch=
ives with this command:
>
> rsync --delete -avz -e "ssh -i /path/to/key" myuser@$PRIMARY:$PG_ARCHIVES=
/ $PG_ARCHIVES
>
> Everything looks good. =A0I end up with an up to date list of WAL files i=
n /mnt/postgresql_archives on the standby. =A0Here is a listing:
>
> root@standby:/mnt/postgresql_archives# ls
> total 688996
> drwxr-xr-x =A02 postgres postgres =A0 =A0 4096 2009-02-27 01:13 .
> drwxr-xr-x 14 root =A0 =A0 root =A0 =A0 =A0 =A0 4096 2009-02-27 01:17 ..
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 00:19 0000000100000=
000000000CB
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 00:29 0000000100000=
000000000CC
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 00:38 0000000100000=
000000000CD
> -rw-rw---- =A01 postgres postgres =A0 =A0 =A0245 2009-02-27 00:38 0000000=
100000000000000CD.00000020.backup
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 00:48 0000000100000=
000000000CE
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 00:54 0000000100000=
000000000CF
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 00:58 0000000100000=
000000000D0
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 01:01 0000000100000=
000000000D1
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 01:03 0000000100000=
000000000D2
> -rw-rw---- =A01 postgres postgres 16777216 2009-02-27 01:13 0000000100000=
000000000D3
>
> Then, the recovery.conf is put in place. =A0I've tried two different vers=
ions, which end up giving me the same error. =A0Here are the two different =
versions.
>
> #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 t=
o debug after #1 wouldn't work. =A0Now, I try to start up the server. =A0Fo=
r it to work in standby mode, additional archive files will be pulled from =
the primary machine on a periodic basis. =A0I'm using this command, which d=
eletes them on the primary when they are no longer necessary. =A0It also se=
ems to work fine.
>
> rsync -avz -e "ssh -i /path/to/key" myuser@$PRIMARY:$PG_ARCHIVES/ $PG_ARC=
HIVES
>
> I guess I'm a little confused about exactly what is happening here when t=
he server comes up, but here is the error message I'm getting. =A0It seems =
to be looking for the files in pg_pxlog, which is cleared out. =A0So, the e=
rror makes sense. =A0But isn't it supposed to be looking in /mnt/postgresql=
_archives per the restore_command(s)? =A0The files are available there.
>
> 2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,2,,2009-02-27 01:26:52 =
EST,,0,LOG,58P01,"could not open file ""pg_xlog/0000000100000000000000CD"" =
(log 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 =
EST,,0,LOG,00000,"invalid checkpoint record",,,,,,,,
> 2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,4,,2009-02-27 01:26:52 =
EST,,0,PANIC,XX000,"could not locate required checkpoint record",,"If you a=
re not restoring from a
> =A0backup, try removing the file ""/var/lib/postgresql/8.3/main/backup_la=
bel"".",,,,,,
> 2009-02-27 01:26:52.868 EST,,,7419,,49a787ab.1cfb,1,,2009-02-27 01:26:51 =
EST,,0,LOG,00000,"startup process (PID 7422) was terminated by signal 6: Ab=
orted",,,,,,,,
> 2009-02-27 01:26:52.868 EST,,,7419,,49a787ab.1cfb,2,,2009-02-27 01:26:51 =
EST,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,
>
> So, I tried copying the files in the /mnt/postgresql_archives over to pg_=
xlog. =A0This seemed to work, and the updates were applied. =A0Never at any=
 point did I get a recovery.done file. =A0Also, for whatever reason, I was =
never able to get any debug info from pg_standby in standby.log.
>
> Anyhow, I've burned up a couple days trying to figure this out. =A0Any he=
lp would be much appreciated.
>
> Thanks,
> Brad
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>