Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 2C0D49FA38C for ; Tue, 28 Aug 2007 20:05:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 40211-01 for ; Tue, 28 Aug 2007 20:05:50 -0300 (ADT) X-Greylist: delayed 00:22:49.276361 by SQLgrey-1.7.5 Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.149.33.74]) by postgresql.org (Postfix) with ESMTP id 4F49F9FA469 for ; Tue, 28 Aug 2007 20:05:47 -0300 (ADT) Received: from [192.168.0.5] (84-12-84-169.dyn.gotadsl.co.uk [84.12.84.169]) by smtp.nildram.co.uk (Postfix) with ESMTP id 38BBE4E95F; Tue, 28 Aug 2007 23:42:53 +0100 (BST) Subject: Re: how is pitr replay interruption time determined? From: Simon Riggs To: Tom Lane Cc: Robert Treat , pgsql-admin@postgresql.org In-Reply-To: <4555.1188338360@sss.pgh.pa.us> References: <200708271742.57392.xzilla@users.sourceforge.net> <10268.1188256622@sss.pgh.pa.us> <200708281739.34094.xzilla@users.sourceforge.net> <4555.1188338360@sss.pgh.pa.us> Content-Type: text/plain Date: Tue, 28 Aug 2007 23:44:29 +0100 Message-Id: <1188341069.4218.93.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/372 X-Sequence-Number: 26703 On Tue, 2007-08-28 at 17:59 -0400, Tom Lane wrote: > Robert Treat writes: > > Is there some way to force checkpoints on a db doing wal replay? > > No, it's hardwired to do it when it sees a checkpoint record in the WAL stream. > > > pg_control last modified: Mon Aug 27 12:12:55 2007 > > Time of latest checkpoint: Mon Jul 30 19:17:37 2007 > > After looking again at the code, the "last modified" time is the time > that a recovery checkpoint was last done, and the "latest checkpoint" > is the timestamp of the WAL-stream checkpoint record that triggered it. > In a situation where you're catching up on historical WAL they could be > far apart, but when a slave is just following the master there shouldn't > be a huge difference --- not more than the maximum time to fill a WAL > record and ship it over to the slave, for sure. > > (BTW, I misread it before --- it looks like the "at log time" value > printed at startup *is* taken from the checkpoint record that it's > trying to roll forward from.) That's correct. Sorry for not replying earlier; just back from hols. Jumping back to original thought: Robert, you should be using the last checkpoint location, not the last time to decide which xlogs to remove. > Assuming that you're absorbing data from the master at a steady rate, > the only reason I can see for the timestamps to be so old is if the > "rm_safe_restartpoint" checks are always failing. I seem to remember > that we found and fixed a bug that could cause something like that, > but I can't find any trace of it in the CVS logs. Simon, do you > recall such a problem post-8.2.0? Yeh, we traced a problem with GIN indexes to this cause in early June; Teodor fixed it quickly in REL8_2_STABLE, but that won't be available until 8.2.5. I'd be happier with a log message to say ereport(DEBUG2, (errmsg("RM %d not safe to record restart point at %X/%X", rmid, checkPoint->redo.xlogid, checkPoint->redo.xrecoff))); to help trace such things in future. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com