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

I think that I now see the error of my ways.

When I shutdown my server, the files under the ./data/ directory still
all point to 'now' and not 'a week ago when the backups were taken'. So
the recover process insists on bringing the database to a PITR equal to
'now'.

Instead, in order to achieve my goal I would have to restore to that
backup, and rely on the contents of the archive_logs to have the
recovery process return me to the selected xid PITR.

So is there any way to 'trick' or force the server to forget what it
thinks 'now' is and instead to step back to the selected xid and make
that the new version of 'now'?

-jason

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
>
>
> Tom Lane wrote:
>> "Jason L. Buberel" <jason(at)buberel(dot)org> writes:
>>
>>> ## stopped and started postgres, following syslog output:
>>>
>>
>> You seem to have omitted all the interesting details about what you did
>> here; but "stopping and starting" postgres is certainly not intended to
>> cause it to discard data. There would need to have been some steps
>> involving restoring a previous base backup and rolling forward through
>> archived xlog files.
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>