Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id CC4CF9F95CB for ; Fri, 5 Jan 2007 11:09:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 43248-05 for ; Fri, 5 Jan 2007 11:09:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0FE819FA250 for ; Fri, 5 Jan 2007 11:09:02 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l05F90aF015672; Fri, 5 Jan 2007 10:09:00 -0500 (EST) To: "Andy Shellam (Mailing Lists)" cc: pgsql-admin@postgresql.org Subject: Re: Recovering a deleted database problem In-reply-to: <459E32DC.3030402@mailnetwork.co.uk> References: <459E32DC.3030402@mailnetwork.co.uk> Comments: In-reply-to "Andy Shellam (Mailing Lists)" message dated "Fri, 05 Jan 2007 11:13:32 +0000" Date: Fri, 05 Jan 2007 10:08:59 -0500 Message-ID: <15671.1168009739@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/35 X-Sequence-Number: 24191 "Andy Shellam (Mailing Lists)" writes: > (Note, after writing this, I tried restoring to a minute earlier (ie. > 18:57:40) and still have the same problem. The PITR recovery process in effect rolls forward until it finds a transaction-commit record >= the specified time. Now for normal database operations, stopping just short of the commit of the transaction is enough to ensure that the transaction has no effect. But for the XLOG_DBASE_DROP record, not so --- replaying that means "rm -rf base/whatever". So you've got to make sure the replay stops before it reaches that record, and that means you need a stop time <= the commit time of some *prior* transaction. I suppose this was a slow time of day and you didn't have any other commits in the prior minute :-( ... so take another look in the log and see what was the last commit before that, and use that time. regards, tom lane