pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ben K. <bkim@coe.tamu.edu>
To: pgsql-admin@postgresql.org
Cc: Simon Riggs <simon@2ndquadrant.com>
Subject: Re: PITR recovery
Date: Tue, 9 Jan 2007 08:37:24 -0600 (CST)
Message-ID: <Pine.GSO.4.64.0701090749340.17891@coe.tamu.edu> (raw)
In-Reply-To: <1168273726.3951.202.camel@silverbirch.site>
References: <Pine.GSO.4.64.0701051218340.1164@coe.tamu.edu>
	<1168097862.3655.249.camel@silverbirch.site>
	<Pine.GSO.4.64.0701080915170.1259@coe.tamu.edu>
	<1168273726.3951.202.camel@silverbirch.site>

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



view thread (10+ messages)  latest in thread

Message-ID: <Pine.GSO.4.64.0701090749340.17891@coe.tamu.edu>
Permalink:  ../Pine.GSO.4.64.0701090749340.17891@coe.tamu.edu/
Also on:    postgresql.org/message-id/Pine.GSO.4.64.0701090749340.17891@coe.tamu.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: bkim@coe.tamu.edu, simon@2ndquadrant.com
  Subject: Re: PITR recovery
  In-Reply-To: <Pine.GSO.4.64.0701090749340.17891@coe.tamu.edu>
  References: <Pine.GSO.4.64.0701051218340.1164@coe.tamu.edu> <1168097862.3655.249.camel@silverbirch.site> <Pine.GSO.4.64.0701080915170.1259@coe.tamu.edu> <1168273726.3951.202.camel@silverbirch.site> <Pine.GSO.4.64.0701090749340.17891@coe.tamu.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox