Thread: 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
maintransaction table), just specify the required stopping point in recovery.conf. You can specify the stop
pointeither by date/time or by transaction ID. As of this writing only the date/time option is very usable,
sincethere 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@lfix.co.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
tribulationworketh patience; And patience, experience; and experience, hope."
Romans 5:3,4
8.0 || 7.5?? g:) > 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@lfix.co.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 6: Have you searched our list archives? > > http://archives.postgresql.org
Oliver Elphick <olly@lfix.co.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@lfix.co.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@lfix.co.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 tribulationworketh 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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
G u i d o B a r o s i o wrote: > 8.0 || 7.5?? 8.0 Regards Gaetano Mendola
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@lfix.co.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@lfix.co.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@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073