Re: PITR - recovery to a particular transaction

The PITR docs that have just been put up say:

But if you want to recover to some previous point in time (say,
right before the junior DBA dropped your main transaction
table), just specify the required stopping point in
recovery.conf. You can specify the stop point either by
date/time or by transaction ID. As of this writing only the
date/time option is very usable, since there are no tools to
help you identify which transaction ID to use.

How about adding a logging option to put the transaction id on the log
for every statement that modifies the database? Would that be a small
enough change to be allowed into 8.0?
--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"And not only so, but we glory in tribulations also;
knowing that tribulation worketh patience; And
patience, experience; and experience, hope."
Romans 5:3,4

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> How about adding a logging option to put the transaction id on the log
> for every statement that modifies the database? Would that be a small
> enough change to be allowed into 8.0?

I think we could get away with adding transaction ID as one of the
available %-items in log_line_prefix. I'm not sure how useful this
really is though --- timestamps are probably more useful overall to
have in your log. The direction I was expecting we'd head in is to
provide WAL logfile examination tools.

regards, tom lane

On Wed, 2004-08-04 at 19:16, Tom Lane wrote:
> Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> > How about adding a logging option to put the transaction id on the log
> > for every statement that modifies the database? Would that be a small
> > enough change to be allowed into 8.0?
>
> I think we could get away with adding transaction ID as one of the
> available %-items in log_line_prefix. I'm not sure how useful this
> really is though --- timestamps are probably more useful overall to
> have in your log.

Why not both?

You seem to be suggesting that using the id is less useful than the
time, but surely it's going to be easier to say "this disaster happened
in transaction 123 so lets do a PITR up to 122" than to say "this
happened at time x so do PITR up to x - 1 second"; the latter might miss
several tranactions. Have I got the concepts wrong here?

> The direction I was expecting we'd head in is to
> provide WAL logfile examination tools.

But that's not going to happen for 8.0, so any means of getting the
transaction id is better than none.

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"And not only so, but we glory in tribulations also;
knowing that tribulation worketh patience; And
patience, experience; and experience, hope."
Romans 5:3,4

> You seem to be suggesting that using the id is less useful than the
> time, but surely it's going to be easier to say "this disaster happened
> in transaction 123 so lets do a PITR up to 122" than to say "this

Transaction IDs are assigned at transaction start but what you really
want is some indicator of when the commit occurred.

Transaction 123 may have committed while 122 was still running.

Rod Taylor wrote:
> > You seem to be suggesting that using the id is less useful than the
> > time, but surely it's going to be easier to say "this disaster happened
> > in transaction 123 so lets do a PITR up to 122" than to say "this
>
> Transaction IDs are assigned at transaction start but what you really
> want is some indicator of when the commit occurred.
>
> Transaction 123 may have committed while 122 was still running.

True. In fact this brings up a problem of using the xid for recovery
stop. The interesting point is that you might recover to just before
xact 123, but that doesn't mean you get xact 122.

Still I think we need to add xid to the log_line_prefix for PITR and
make it clear that specifying a recovery xid doesn't always include
earlier xids. I have added this to the open items list.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

When we do a PITR recovery based on xid, does it stop recovery based on
the start of the xid or the commit of the xid? And if you say
recovery_target_inclusive =true, does it recover that xid while not
recoverying other xids that are higher but committed sooner than the
target xid?

---------------------------------------------------------------------------

Oliver Elphick wrote:
> On Wed, 2004-08-04 at 19:16, Tom Lane wrote:
> > Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> > > How about adding a logging option to put the transaction id on the log
> > > for every statement that modifies the database? Would that be a small
> > > enough change to be allowed into 8.0?
> >
> > I think we could get away with adding transaction ID as one of the
> > available %-items in log_line_prefix. I'm not sure how useful this
> > really is though --- timestamps are probably more useful overall to
> > have in your log.
>
> Why not both?
>
> You seem to be suggesting that using the id is less useful than the
> time, but surely it's going to be easier to say "this disaster happened
> in transaction 123 so lets do a PITR up to 122" than to say "this
> happened at time x so do PITR up to x - 1 second"; the latter might miss
> several tranactions. Have I got the concepts wrong here?
>
> > The direction I was expecting we'd head in is to
> > provide WAL logfile examination tools.
>
> But that's not going to happen for 8.0, so any means of getting the
> transaction id is better than none.
>
> --
> Oliver Elphick olly(at)lfix(dot)co(dot)uk
> Isle of Wight http://www.lfix.co.uk/oliver
> GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
> ========================================
> "And not only so, but we glory in tribulations also;
> knowing that tribulation worketh patience; And
> patience, experience; and experience, hope."
> Romans 5:3,4
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> When we do a PITR recovery based on xid, does it stop recovery based on
> the start of the xid or the commit of the xid?

You can stop either "before" or "after" that commit. See
recovery.conf.sample (I don't think it's documented anywhere else
yet :-(),

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > When we do a PITR recovery based on xid, does it stop recovery based on
> > the start of the xid or the commit of the xid?
>
> You can stop either "before" or "after" that commit. See
> recovery.conf.sample (I don't think it's documented anywhere else
> yet :-(),

Yea, my question is if you choose "after", do you get everything that
happens until the "after" transaction commits, or just when it begins.
If I stop after xid 125, and xid 126 starts and stops before 125
commits, does 126 get restored?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Yea, my question is if you choose "after", do you get everything that
> happens until the "after" transaction commits, or just when it begins.
> If I stop after xid 125, and xid 126 starts and stops before 125
> commits, does 126 get restored?

Yes. You don't get to be selective about what to keep: it's everything
up to a certain time instant, and nothing after that. Stopping by XID
is just a different way of identifying what that time instant is.

BTW, stopping "before" an XID actually means stopping just before its
commit or abort record, so transactions that ended before it did will
be included in the recovery.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Yea, my question is if you choose "after", do you get everything that
> > happens until the "after" transaction commits, or just when it begins.
> > If I stop after xid 125, and xid 126 starts and stops before 125
> > commits, does 126 get restored?
>
> Yes. You don't get to be selective about what to keep: it's everything
> up to a certain time instant, and nothing after that. Stopping by XID
> is just a different way of identifying what that time instant is.
>
> BTW, stopping "before" an XID actually means stopping just before its
> commit or abort record, so transactions that ended before it did will
> be included in the recovery.

OK, I added a mention of this in the docs.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073