Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 981E99FB21D for ; Tue, 9 Jan 2007 12:31:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 63379-08 for ; Tue, 9 Jan 2007 12:31:43 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 8A6D29FA3AC for ; Tue, 9 Jan 2007 12:31:41 -0400 (AST) thread-index: Acc0C6hd9pIhRLJsTmmwrJFL7dHURg== Received: from [192.168.0.22] ([62.232.55.118]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 9 Jan 2007 11:31:47 -0500 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757 Content-Class: urn:content-classes:message Importance: normal Subject: Re: PITR recovery From: "Simon Riggs" To: "Ben K." Cc: In-Reply-To: References: <1168097862.3655.249.camel@silverbirch.site> <1168273726.3951.202.camel@silverbirch.site> Content-Type: text/plain; charset="iso-8859-1" Date: Tue, 09 Jan 2007 16:30:39 +0000 Message-ID: <1168360240.3951.289.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 09 Jan 2007 16:31:47.0453 (UTC) FILETIME=[A832BAD0:01C7340B] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/77 X-Sequence-Number: 24233 On Tue, 2007-01-09 at 08:37 -0600, Ben K. wrote: > On Mon, 8 Jan 2007, Simon Riggs wrote: > > > There is a log analysis tool on pgfoundry that does something similar. > > > You can already stop recovery at a certain point. So there's nothing to > > stop you doing a recovery on a development machine up to a certain > > point, then dumping the deleted data using pg_dump and re-loading it > > into the live server. Then erasing the dev recovered database. > > What I looked for was something like "undo" to a state which includes some > changes from the last backup. > > What is the best practice in the following case? I'd appreciate to know > how this kind of situation is handled in general. > > - We keep daily backups. > - In the middle of the day we find there are problems in some of the > recent delete or update operations > - But we want to keep hours of other insert, delete or updates that preceded the problematic > delete/update > > We can do hourly backup of the production server to minimize the recovery > efforts, but it seems costly considering this situation is not frequent. > > Since the logs contains what's been done, it appears it should be possible > to go back in time - as PITR does - by relying only on sql dump and the > statements in the log, with a controlled scope. PITR doesn't go back in time. It only supports roll-forward from a prior base backup using the logs, stopping at a predefined time/xid/endoflogs. Going backwards using the logs is mostly impossible because the log records don't hold enough info to un-erase things. It is theoretically possible to enhance the server to be able to rewind the data in a table by uncommitting transactional changes, but again you can't put back data that has been emoved by VACUUM. That feature would be similar to flashback, but its more complex then it sounds at first hearing. I wouldn't hold your breath while waiting for that. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com