"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Sun, 2007-07-01 at 21:41 -0700, Jason L. Buberel wrote:
>> I am trying to learn/practice the administrative steps that would need
>> to be taken in a 'fat finger' scenario, and I am running into problems.
> Your example transactions are so large that going back 15 minutes is not
> enough. You'll need to go back further.
> recovery_target_time can only stop on a COMMIT or ABORT record. This is
> because it makes no sense to recover half a transaction, only whole
> transactions have meaning for recovery. So if the transactions are very
> large, you need to go back further.
No, that doesn't explain it. As long as he set the stop time before the
commit of the unwanted transaction, it should do what he's expecting.
It might uselessly replay a lot of the actions of a long-running
transaction, but it will stop before the COMMIT xlog record when it
reaches it, and thus the transaction will not be committed.
What's actually happening according to the log output is that it's
running all the way to the end of WAL. I can't really think of an
explanation for that other than a mistake in choosing the stop time,
ie, it's later than the commit of the unwanted transaction. Or maybe
the WAL file is a stale copy that doesn't even contain the unwanted
commit?
Jason, if you can't figure it out you might grab xlogviewer
http://pgfoundry.org/projects/xlogviewer/
and see what it says the timestamps of the commit records in your WAL
files are.
regards, tom lane