On Mon, 2007-07-02 at 11:06 -0400, Tom Lane wrote:
> "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.
There's a patch awaiting review that adds the time of the last committed
transaction into the LOG output. That should help in cases like this.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com