X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C26B911F6235 for ; Sat, 29 Apr 2006 10:59:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 45342-07 for ; Sat, 29 Apr 2006 10:59:40 -0300 (ADT) X-Greylist: delayed 00:29:42.289626 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 63DD911F6204 for ; Sat, 29 Apr 2006 10:59:37 -0300 (ADT) Received: from sydney.mailnetwork.co.uk (server88-208-192-113.live-servers.net [88.208.192.113]) by svr4.postgresql.org (Postfix) with ESMTP id CFE315B379D for ; Sat, 29 Apr 2006 13:29:55 +0000 (GMT) Received: from server88-208-192-113.live-servers.net ([88.208.192.113]:49949 helo=webmail.mailnetwork.co.uk) by sydney.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1FZpWD-0000RD-Ce; Sat, 29 Apr 2006 14:29:53 +0100 MIME-Version: 1.0 X-Mailer: mail network WebAccess In-Reply-To: <1442.1146239314@sss.pgh.pa.us> References: <20060428152301.007F011F602A@postgresql.org> <1442.1146239314@sss.pgh.pa.us> Date: Sat, 29 Apr 2006 14:29:53 +0100 From: andy@andycc.net Cc: pgsql-admin@postgresql.org Subject: Re: WAL recovery question - 0000001.history X-Originating-IP: [193.25.116.40] Content-Type: multipart/alternative; boundary="=_3fbc71687e2eccd5f255c510c75d6112" Message-ID: To: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/350 X-Sequence-Number: 21519 --=_3fbc71687e2eccd5f255c510c75d6112 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit Hi Tom,Thanks for this.  Do you know why Postgres was asking for 00000001.history?  Surely it would know by the presence of the backup_label file, and its contents that it needs to go straight for the xxxxxx.backup file, which should exist?What other files is Postgres likely to be asked for during a PITR restore?  Obviously log files that don't exist do need to be waited for - hence the need for this script.  If you want PG to come back up without the log it's waiting on, you just set the flag file and it'll come up at the next interval.So far all I can see that's problematic is this 0000001.history file which I don't know what it is, and it'll never exist because it was never generated on the live.The other thing I'm building in as well, is a file-size check, so when shipping logs directly from another server (e.g. live to standby), when the script finds a log, it will read the file size, wait 5 seconds, then read it again - if the 2 values differ, the log is still being transferred, so it will wait for another interval and check again before doing anything - preventing itself from pinching the log while it's still being transferred, and causing errors when PG tries to read it.This will also remove as much human intervention as possible from the whole process.AndyTom Lane <tgl@sss.pgh.pa.us> wrote:> "Andy Shellam" <andy.shellam@mailnetwork.co.uk> writes:> > Basically I've taken a dump of my live database directory (between> > pg_start_backup and pg_stop_backup) calls - shipped this to my standby, set> > up a recovery.conf file (which calls the rolling-WAL script). This script> > is designed to wait until the log file arrives, or a flag file is set to> > return code 1 to postgres.> > > However as soon as the recovery starts, the script is waiting for a file> > called 00000001.history, which will never arrive because it was never> > generated on the live box since the base backup was taken.> > This is not a workable approach: your recovery script *will* be asked> for files that do not exist, and waiting for them to be supplied is not> always the right answer. Supplying an empty file instead is definitely> the wrong answer.> > You might be able to make it work by conditionalizing the behavior on> the format of the name being asked for.> > regards, tom lane> > ---------------------------(end of broadcast)---------------------------> TIP 4: Have you searched our list archives?> > http://archives.postgresql.org> > !DSPAM:14,445239a233692110055341!> > > __________________________________________________________ The mail network cannot accept liability for the content of this e-mail. It is recommended you scan it for viruses immediately upon receipt. Is this e-mail spam? Report it to abuse@mailnetwork.co.uk --=_3fbc71687e2eccd5f255c510c75d6112 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Hi Tom,

Thanks for this.  Do you know why Postgres was aski= ng for 00000001.history?  Surely it would know by the presence of the = backup_label file, and its contents that it needs to go straight for t= he xxxxxx.backup file, which should exist?

What other files is&n= bsp;Postgres likely to be asked for during a PITR restore?  Obviously = log files that don't exist do need to be waited for - hence the need for th= is script.  If you want PG to come back up without the log it's waitin= g on, you just set the flag file and it'll come up at the next interval.So far all I can see that's problematic is this 0000001.history file whi= ch I don't know what it is, and it'll never exist because it was never gene= rated on the live.

The other thing I'm building in as well, is a= file-size check, so when shipping logs directly from another server (e.g. = live to standby), when the script finds a log, it will read the file size, = wait 5 seconds, then read it again - if the 2 values differ, the log is sti= ll being transferred, so it will wait for another interval and check again = before doing anything - preventing itself from pinching the log while = it's still being transferred, and causing errors when PG tries to read it.<= br />
This will also remove as much human intervention as possible fro= m the whole process.

Andy

Tom Lane <tgl@sss.pgh.pa= .us> wrote:

> "Andy Shellam" <andy.shellam@ma= ilnetwork.co.uk> writes:
> > Basically I've taken a dump of m= y live database directory (between
> > pg_start_backup and pg_st= op_backup) calls - shipped this to my standby, set
> > up a reco= very.conf file (which calls the rolling-WAL script). This script
> = > is designed to wait until the log file arrives, or a flag file is set = to
> > return code 1 to postgres.
>
> > Howe= ver as soon as the recovery starts, the script is waiting for a file
&= gt; > called 00000001.history, which will never arrive because it was ne= ver
> > generated on the live box since the base backup was take= n.
>
> This is not a workable approach: your recovery scri= pt *will* be asked
> for files that do not exist, and waiting for t= hem to be supplied is not
> always the right answer. Supplying an e= mpty file instead is definitely
> the wrong answer.
>
> You might be able to make it work by conditionalizing the behavior on=
> the format of the name being asked for.
>
> reg= ards, tom lane
>
> ---------------------------(end of broa= dcast)---------------------------
> TIP 4: Have you searched our li= st archives?
>
> http://archives.postgresql.org
> =
> !DSPAM:14,445239a233692110055341!
>
>
>= ;
--=_3fbc71687e2eccd5f255c510c75d6112--