Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2B3539FB1EA for ; Tue, 9 Jan 2007 10:40:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 24659-04 for ; Tue, 9 Jan 2007 10:40:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from coe.tamu.edu (edsun.coe.tamu.edu [165.91.232.6]) by postgresql.org (Postfix) with ESMTP id 56F309FA4B6 for ; Tue, 9 Jan 2007 10:40:46 -0400 (AST) Received: (from root@localhost) by coe.tamu.edu (8.11.7p2+Sun/8.12.11) id l09Ec8l20134 for pgsql-admin@postgresql.org; Tue, 9 Jan 2007 08:38:08 -0600 (CST) Received: from coe.tamu.edu (coe.tamu.edu [165.91.232.6]) by coe.tamu.edu (8.11.7p2+Sun/8.12.11) with ESMTP id l09EbOD20085; Tue, 9 Jan 2007 08:37:24 -0600 (CST) Date: Tue, 9 Jan 2007 08:37:24 -0600 (CST) From: "Ben K." To: pgsql-admin@postgresql.org cc: Simon Riggs Subject: Re: PITR recovery In-Reply-To: <1168273726.3951.202.camel@silverbirch.site> Message-ID: References: <1168097862.3655.249.camel@silverbirch.site> <1168273726.3951.202.camel@silverbirch.site> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by AMaViS 0.3.12 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/70 X-Sequence-Number: 24226 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. I guess it can possibly be done by replacing the basic operations like delete so that it inserts the deleted row into another database before deleting this row, etc.. Conceptually it seems simple though in generalizing it I guess there'll be problems like scalability and speed. Thanks. Ben K. Developer http://benix.tamu.edu