Re: recovery_target_time ignored or recovery always recovers to end of WAL

On Jul 2, 2007, at 11:58 PM, Jason L. Buberel wrote:

> I am now learning that fact, but recall the original scenario that
> I am trying to mimic:
>
> 1. Person accidentally deletes contents of important table.
> 2. Admin (me) wants to roll back db state to just prior to that
> deletion.
> 3. (Me) Assumes that by creating a recovery.conf file and setting
> the target to a an earlier trxn id and restarting the database
> would simply do the trick.
>
> So now I think that my scenario should look more like:
>
> 1. Person accidentally deletes contents of important table.
> 2. Admin (me) wants to roll db state back to just prior to that
> delete.
> 3. (Me) Performs steps, in addition to creating the recovery.conf
> with the selected xid, that will cause the DB to restart at that PITR.
>
> Now all I need to lock down are those 'additional steps needed to
> force the recovery process to only recover up to the specified xid
> and no further'.
>
> Such as:
>
> - Remove from pg_xlog all of the log files containing transactions
> that come after the selected xid?
> - Other?
>
> -jason

Whoa.  If what you're asking is for a database level rollback or undo
type of function, I'm pretty sure that's not doable in the way you're
asking.  Once a postgres cluster (data/*) has committed or rolled
back transactions, you can not roll that particular cluster instance
back, i.e. you can not stop the database and have it start from some
previous transaction state and stop at an arbitrary point.  The only
way to do this is if you have a filesystem level backup from a point
in time previous to the point to which you wish to return along with
all of the transaction logs from just before the point where the
backup was taken up to the point to which you wish to return, which
you can then bring up in recovery mode and have it play up until a
transaction id you specify.

The main point here is that PITR requires and, is run on, a base backup.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com