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

Understood all around - thanks to everyone for helping me clarify (in my
head/understanding) how these pieces fit together.

One last request - can you glance over the plan below and let me know if
it sounds sane? The goal again is to be able to recover to a PITR record
in the very recent past after a mistake (accidental deletion) occurs.
This is not meant to be a substitute for a real backup strategy:

1. On a daily basis, run a hot backup more/less as described in this
Wiki article on postgresqlforums.com: http://shorl.com/naprofamynone

2. But instead of creating a .tar.gz file, simply 'rsync' the entire
$PGDATA dir to another directory stored on a local disk.

3. When an accidental deletion occurs, shutdown the database, restore
the $PGDATA dir from yesterday's rsync snapshot (after saving any
unarchived pg_xlogs), and use the normal recovery process to effectively
'roll back' the database state to what it was just prior to the accident.

Thanks again for all the helpful comments and clarifications. I am now a
more clueful person as a result ;)
-jason

Erik Jones wrote:
> 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(at)myemma(dot)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
>
>