Re: [HACKERS] PITR - recovery to a particular transaction
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PITR - recovery to a particular transaction
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR - recovery to a particular transaction
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PITR - recovery to a particular transaction
G u i d o B a r o s i o wrote: 8.0 || 7.5?? 8.0 Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR - recovery to a particular transaction
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PITR - recovery to a particular transaction
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 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 [EMAIL PROTECTED] 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
Re: [HACKERS] PITR - recovery to a particular transaction
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR - recovery to a particular transaction
Oliver Elphick [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PITR - recovery to a particular transaction
On Wed, 2004-08-04 at 19:16, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PITR - recovery to a particular transaction
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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PITR - recovery to a particular transaction
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
