Re: [HACKERS] Documentation on PITR still scarce
Both added to TODO: --- Simon Riggs wrote: On Mon, 2004-11-29 at 13:10, Bruce Momjian wrote: Or TODO maybe worded as: * Allow the PITR process to be debugged and data examined Yes, thats good for me... Greg's additional request might be worded: * Allow a warm standby system to also allow read-only queries Thanks. --- Simon Riggs wrote: On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: Is this a TODO? Yes, but don't hold your breath on that feature. Gavin and I were discussing briefly a design that would allow something similar to this. The design would allow the user to stop/start recovery and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to implement than the proposal below, which I agree is desirable. We haven't hardly started that discussion yet though. I called this recovery console functionality. I'm not sure I like the Suspended Animation phrase, I thought maybe TARDIS or Langston Field sums it up better (kidding...) Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. That would be a great thing to have one day for other reasons aside from the ability to test out a recovered database. It makes warm standby databases much more useful. A warm standby is when you keep a second machine constantly up to date by applying the archived PITR logs as soon as they come off your server. You're ready to switch over at the drop of a hat and don't have to go through the whole recovery process, you just switch the database from recovery mode to active mode and make it your primary database. But in the until then the backup hardware languishes, completely useless. Oracle has had a feature for a long time that you can actually open the standby database in a strict read-only mode and run queries. This is great for a data warehouse situation where you want to run long batch jobs against recent data. -- Best Regards, Simon Riggs -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Documentation on PITR still scarce
On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: Is this a TODO? Yes, but don't hold your breath on that feature. Gavin and I were discussing briefly a design that would allow something similar to this. The design would allow the user to stop/start recovery and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to implement than the proposal below, which I agree is desirable. We haven't hardly started that discussion yet though. I called this recovery console functionality. I'm not sure I like the Suspended Animation phrase, I thought maybe TARDIS or Langston Field sums it up better (kidding...) Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. That would be a great thing to have one day for other reasons aside from the ability to test out a recovered database. It makes warm standby databases much more useful. A warm standby is when you keep a second machine constantly up to date by applying the archived PITR logs as soon as they come off your server. You're ready to switch over at the drop of a hat and don't have to go through the whole recovery process, you just switch the database from recovery mode to active mode and make it your primary database. But in the until then the backup hardware languishes, completely useless. Oracle has had a feature for a long time that you can actually open the standby database in a strict read-only mode and run queries. This is great for a data warehouse situation where you want to run long batch jobs against recent data. -- greg -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Documentation on PITR still scarce
OK, how would it be worded? * Allow PITR recovery to a read-only server --- Simon Riggs wrote: On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: Is this a TODO? Yes, but don't hold your breath on that feature. Gavin and I were discussing briefly a design that would allow something similar to this. The design would allow the user to stop/start recovery and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to implement than the proposal below, which I agree is desirable. We haven't hardly started that discussion yet though. I called this recovery console functionality. I'm not sure I like the Suspended Animation phrase, I thought maybe TARDIS or Langston Field sums it up better (kidding...) Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. That would be a great thing to have one day for other reasons aside from the ability to test out a recovered database. It makes warm standby databases much more useful. A warm standby is when you keep a second machine constantly up to date by applying the archived PITR logs as soon as they come off your server. You're ready to switch over at the drop of a hat and don't have to go through the whole recovery process, you just switch the database from recovery mode to active mode and make it your primary database. But in the until then the backup hardware languishes, completely useless. Oracle has had a feature for a long time that you can actually open the standby database in a strict read-only mode and run queries. This is great for a data warehouse situation where you want to run long batch jobs against recent data. -- greg -- Best Regards, Simon Riggs -- 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 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] Documentation on PITR still scarce
Or TODO maybe worded as: * Allow the PITR process to be debugged and data examined --- Simon Riggs wrote: On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: Is this a TODO? Yes, but don't hold your breath on that feature. Gavin and I were discussing briefly a design that would allow something similar to this. The design would allow the user to stop/start recovery and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to implement than the proposal below, which I agree is desirable. We haven't hardly started that discussion yet though. I called this recovery console functionality. I'm not sure I like the Suspended Animation phrase, I thought maybe TARDIS or Langston Field sums it up better (kidding...) Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. That would be a great thing to have one day for other reasons aside from the ability to test out a recovered database. It makes warm standby databases much more useful. A warm standby is when you keep a second machine constantly up to date by applying the archived PITR logs as soon as they come off your server. You're ready to switch over at the drop of a hat and don't have to go through the whole recovery process, you just switch the database from recovery mode to active mode and make it your primary database. But in the until then the backup hardware languishes, completely useless. Oracle has had a feature for a long time that you can actually open the standby database in a strict read-only mode and run queries. This is great for a data warehouse situation where you want to run long batch jobs against recent data. -- greg -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- 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 8: explain analyze is your friend
Re: [HACKERS] Documentation on PITR still scarce
On Mon, 2004-11-29 at 13:10, Bruce Momjian wrote: Or TODO maybe worded as: * Allow the PITR process to be debugged and data examined Yes, thats good for me... Greg's additional request might be worded: * Allow a warm standby system to also allow read-only queries Thanks. --- Simon Riggs wrote: On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: Is this a TODO? Yes, but don't hold your breath on that feature. Gavin and I were discussing briefly a design that would allow something similar to this. The design would allow the user to stop/start recovery and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to implement than the proposal below, which I agree is desirable. We haven't hardly started that discussion yet though. I called this recovery console functionality. I'm not sure I like the Suspended Animation phrase, I thought maybe TARDIS or Langston Field sums it up better (kidding...) Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. That would be a great thing to have one day for other reasons aside from the ability to test out a recovered database. It makes warm standby databases much more useful. A warm standby is when you keep a second machine constantly up to date by applying the archived PITR logs as soon as they come off your server. You're ready to switch over at the drop of a hat and don't have to go through the whole recovery process, you just switch the database from recovery mode to active mode and make it your primary database. But in the until then the backup hardware languishes, completely useless. Oracle has had a feature for a long time that you can actually open the standby database in a strict read-only mode and run queries. This is great for a data warehouse situation where you want to run long batch jobs against recent data. -- Best Regards, Simon Riggs ---(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] Documentation on PITR still scarce
Simon Riggs wrote: On Mon, 2004-11-29 at 13:10, Bruce Momjian wrote: Or TODO maybe worded as: * Allow the PITR process to be debugged and data examined Yes, thats good for me... Greg's additional request might be worded: * Allow a warm standby system to also allow read-only queries Yes, this will shift postgresql in Sybase direction. Did you solved also all your concerns on my two bash scripts ? Are that scripts eligibles to be putted in contrib ? Regards Gaetano Mendola ---(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] Documentation on PITR still scarce
Simon Riggs [EMAIL PROTECTED] writes: Greg's additional request might be worded: * Allow a warm standby system to also allow read-only queries Others have also asked in the past for a mode where a database could be run off read-only media like a CD-ROM. I would phrase it more like: * Allow truly read-only operation, could be useful for read-only media as well as for querying a warm-standby database or for inspecting a database without disturbing PITR recovery. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Documentation on PITR still scarce
Is this a TODO? --- Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. That would be a great thing to have one day for other reasons aside from the ability to test out a recovered database. It makes warm standby databases much more useful. A warm standby is when you keep a second machine constantly up to date by applying the archived PITR logs as soon as they come off your server. You're ready to switch over at the drop of a hat and don't have to go through the whole recovery process, you just switch the database from recovery mode to active mode and make it your primary database. But in the until then the backup hardware languishes, completely useless. Oracle has had a feature for a long time that you can actually open the standby database in a strict read-only mode and run queries. This is great for a data warehouse situation where you want to run long batch jobs against recent data. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- 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]
Re: [HACKERS] Documentation on PITR still scarce
On Sat, Nov 06, 2004 at 07:17:29PM +, Simon Riggs wrote: Once you have brought up a database in timeline N+1, you can't use it as the base to recover to a point in timeline N because the data file contents cannot be trusted to be identical to the way they were in timeline N. You mean in timeline N ... to a point in timeline N+1, don't you? Specifically not. The point is: you can't go back in time. Recovery is a rollforward operation, so you must start at an earlier point and rollforwards from there. Ok, that seems to be pretty intuitive. But could one extend the recovery mechanism such that one can go from PIT t_0 to PIT t_1 with t_1 t_0 without re-restoring the original backup? Joachim ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Documentation on PITR still scarce
On Sun, 2004-11-07 at 11:15, Joachim Wieland wrote: On Sat, Nov 06, 2004 at 07:17:29PM +, Simon Riggs wrote: Once you have brought up a database in timeline N+1, you can't use it as the base to recover to a point in timeline N because the data file contents cannot be trusted to be identical to the way they were in timeline N. You mean in timeline N ... to a point in timeline N+1, don't you? Specifically not. The point is: you can't go back in time. Recovery is a rollforward operation, so you must start at an earlier point and rollforwards from there. Ok, that seems to be pretty intuitive. But could one extend the recovery mechanism such that one can go from PIT t_0 to PIT t_1 with t_1 t_0 without re-restoring the original backup? Same question, just restated. When you stop recovery at point in time, t_0 is now in timeline N+1, though it does also still exist in timeline N. In the new timeline there is no such thing (yet) as a time/transaction t_0. In timeline N only, you can go from t_0 to t_1, but not starting from where you are now, because you are now at t_0 in timeline N+1. That's the general case. ...and you can see why Tom described it as like science fiction. Anyway, you're trying to optimize re-recovery, which would be slightly lower on the priority list than optimizing recovery itself. General readers should just remember this: if you recover, and you decide you've done it wrong, you can re-recover to a different place. You don't need to understand all of that complexity to use the PITR facilities. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Documentation on PITR still scarce
Simon Riggs [EMAIL PROTECTED] writes: On Sun, 2004-11-07 at 11:15, Joachim Wieland wrote: Ok, that seems to be pretty intuitive. But could one extend the recovery mechanism such that one can go from PIT t_0 to PIT t_1 with t_1 t_0 without re-restoring the original backup? Same question, just restated. When you stop recovery at point in time, t_0 is now in timeline N+1, though it does also still exist in timeline N. In the new timeline there is no such thing (yet) as a time/transaction t_0. The real point is that as soon as you do anything, you are most definitely not in timeline N anymore; you are executing transactions in a new history of the database (new timeline). The reason we need a re-restore is that there's no other way to undo whatever you have done in timeline N+1. I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Documentation on PITR still scarce
Tom Lane [EMAIL PROTECTED] writes: I suppose it might be useful to have some kind of suspended animation behavior where you could bring up a backend and look at the database in a strict read-only fashion, not really executing transactions at all, just to see what you had. Then you could end the recovery and go to normal operations, or allow the recovery to proceed further if you decided this wasn't where you wanted to be yet. However that would require a great deal of mechanism we haven't got (yet). In particular there is no such thing as strict read-only examination of the database. That would be a great thing to have one day for other reasons aside from the ability to test out a recovered database. It makes warm standby databases much more useful. A warm standby is when you keep a second machine constantly up to date by applying the archived PITR logs as soon as they come off your server. You're ready to switch over at the drop of a hat and don't have to go through the whole recovery process, you just switch the database from recovery mode to active mode and make it your primary database. But in the until then the backup hardware languishes, completely useless. Oracle has had a feature for a long time that you can actually open the standby database in a strict read-only mode and run queries. This is great for a data warehouse situation where you want to run long batch jobs against recent data. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Documentation on PITR still scarce
Hi, On Sat, Nov 06, 2004 at 11:13:34AM +, Simon Riggs wrote: The timeline code only comes into effect when you request an archive recovery. If you do not, it has no way of knowing it should have. Ok. However these details should be added to the docs as well. At least a short warning should show up in 22.3.3 7. Once you have brought up a database in timeline N+1, you can't use it as the base to recover to a point in timeline N because the data file contents cannot be trusted to be identical to the way they were in timeline N. You mean in timeline N ... to a point in timeline N+1, don't you? Re-restoring the backup sounds like a thing that needs-optimization, but it is required for transactional correctness. [There is some slight area of improvement, but I don't wish to explain this because it might lure people into error by mentioning it...the code currently requires re-restoring] Ok. Thanks for all your explanations, Joachim ---(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] Documentation on PITR still scarce
On Sat, 2004-11-06 at 15:03, Joachim Wieland wrote: Hi, On Sat, Nov 06, 2004 at 11:13:34AM +, Simon Riggs wrote: The timeline code only comes into effect when you request an archive recovery. If you do not, it has no way of knowing it should have. Ok. However these details should be added to the docs as well. At least a short warning should show up in 22.3.3 7. I agree. I'm thinking of other solutions/options also. Please feel free to suggest one. Once you have brought up a database in timeline N+1, you can't use it as the base to recover to a point in timeline N because the data file contents cannot be trusted to be identical to the way they were in timeline N. You mean in timeline N ... to a point in timeline N+1, don't you? Specifically not. The point is: you can't go back in time. Recovery is a rollforward operation, so you must start at an earlier point and rollforwards from there. -- Best Regards, Simon Riggs ---(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
[HACKERS] Documentation on PITR still scarce
Hi there, I just wanted to try the PITR feature in beta and got it working somehow. However I think the docs on this point are still not sufficient enough. We have to assume that people will have a closer look at the backup/recovery documentation as soon as 8.0 ships, because we're kinda heavily advertizing for 8.0 with the PITR feature. In chapter 22, Backup and Restore, there is not a single example of a recovery.conf, nor is any of these parameters even mentioned: recovery_target_time recovery_target_xid recovery_target_timeline recovery_target_inclusive There's an example file in the source tree in backend/access/transam however. So from looking at the docs only it's quite difficult to figure out how it works in practice. I'd appreciate an example with multiple timelines as well. Joachim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Documentation on PITR still scarce
Joachim Wieland [EMAIL PROTECTED] wrote on 05.11.2004, 16:28:38: Hi there, I just wanted to try the PITR feature in beta and got it working somehow. However I think the docs on this point are still not sufficient enough. We have to assume that people will have a closer look at the backup/recovery documentation as soon as 8.0 ships, because we're kinda heavily advertizing for 8.0 with the PITR feature. In chapter 22, Backup and Restore, there is not a single example of a recovery.conf, nor is any of these parameters even mentioned: recovery_target_time recovery_target_xid recovery_target_timeline recovery_target_inclusive There's an example file in the source tree in backend/access/transam however. So from looking at the docs only it's quite difficult to figure out how it works in practice. I'd appreciate an example with multiple timelines as well. Joachim, Thanks for your interest in PITR and your feedback, which is much appreciated. I'm sorry you've had difficulty. I've read the documentation again, so can vouch for the accuracy of it - it also contains descriptions and advisories that are useful to you. Tom wrote this and my opinion was/is that it is very clear and helpful. Ch22 does specifically point you to the recovery.conf.sample file and describes where to find it. It also tells you the type of information you can specify within it, though you are right in that it doesn't specifically describe each parameter. The sample file gives additional information, just as occurs with pg_hba.conf. I don't see any need to replicate the sample file in the docs, do you? You're not the first person to ask for more docs. It's difficult for me to see how to improve what's there. I'm hampered by understanding it already, if that makes sense. PostgreSQL transactional recovery is very similar to SQL Server, DB2 and Oracle recovery. Obviously, if you're new to the whole subject of transactional recovery then the docs are fairly sparse ... but the docs aren't intended to be a basic course in database transactional recovery. Oracle, for example, cover this in a 3 day course. [My company teaches a short course on PostgreSQL PITR, which is one way that we recoup the cost of developing the software... It's possible for me to arrange courses outside of the UK also, if I get invitations or there is general demand. ...Please excuse OT discussion] I did originally submit some documentation for this to PATCHES, as of mid-August; perhaps that may shed more light. That did contain some descriptive examples, but not worked ones. If you have specific questions, I can answer those. There haven't been any specific questions asked that aren't covered in the docs or the sample file, other than these: recovery_target_time This is the stopping point mentioned in the docs. recovery_target_inclusive This parameter allows you to specify whether you should stop AT/ON (i.e. inclusive [=]) or just before the recovery_target (i.e. exclusive []). I'll add a few more lines to the chapter to include those descriptions. I would encourage you and other users to submit a documentation patch yourself if you find better ways of explaining what it's for, how to make it work etc.. Best Regards, Simon Riggs 2nd Quadrant www.2ndquadrant.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Documentation on PITR still scarce
Simon, thanks for that quick and detailed answer. On Fri, Nov 05, 2004 at 05:42:01PM +0100, [EMAIL PROTECTED] wrote: The sample file gives additional information, just as occurs with pg_hba.conf. I don't see any need to replicate the sample file in the docs, do you? Well, maybe one could add just a few lines. pg_hba.conf and postgresql.conf are also documented in the documentation itself. If you have specific questions, I can answer those. There haven't been any specific questions asked that aren't covered in the docs or the sample file, other than these: My first mistake was that I kept the archive_command setting and thus overwrote my original archive files. Maybe you should add a note that one should set it to another directory when recovering. I would encourage you and other users to submit a documentation patch yourself if you find better ways of explaining what it's for, how to make it work etc.. My question is: When I've restored up to the time t_0, how can I go on to restore up to another point in time, later than t_0 but before the end of my log files. I've set another recovery_target_time but it won't work. When exactly would I have to specify recovery_target_timeline? Thanks for your words of wisdom, Joachim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Documentation on PITR still scarce
On Fri, 2004-11-05 at 18:10, Joachim Wieland wrote: My first mistake was that I kept the archive_command setting and thus overwrote my original archive files. Maybe you should add a note that one should set it to another directory when recovering. That is exactly the situation Timelines are designed to avoid. This should not have happened. What leads you to think it has? My guess is that it has not. If it has, its a bug. Manual (rightly) says: a new timeline does not overwrite the WAL data generated by previous timelines If you did not follow instruction 22.3.3, step 1, then I might understand your comment and grieve with you. I would encourage you and other users to submit a documentation patch yourself if you find better ways of explaining what it's for, how to make it work etc.. My question is: When I've restored up to the time t_0, how can I go on to restore up to another point in time, later than t_0 but before the end of my log files. Thats a good question. Perhaps one for a FAQ entry. You need to re-restore the original backup. Then specify a recovery.conf with t_1, rather than t_0, where t_1 t_0. This should then work correctly. Maybe overkill, but that should work. You shouldn't need to specify recovery_target_timeline, because the default is to use the timeline of the original base backup. If it does not, call us. Tell us exactly what you're doing, which backup you are using etc,, When exactly would I have to specify recovery_target_timeline? Most of the time, never. It is needed mostly to cater for some fairly obscure recovery situations. The default behaviour is good for most things. The timeline concept ensures that you can always re-run a recovery if you think you have done it incorrectly. -- Best Regards, Simon Riggs ---(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] Documentation on PITR still scarce
Hi, On Fri, Nov 05, 2004 at 10:26:55PM +, Simon Riggs wrote: That is exactly the situation Timelines are designed to avoid. This should not have happened. What leads you to think it has? My guess is that it has not. If it has, its a bug. Hmm. I did the following: - I recovered to one PIT. - I verified that everything was fine. - If I shut down postmaster now and try to recover to another PIT, everything will work fine. (by re-restoring the original backup as you pointed out) However if I: - Shut down postmaster and restart it in normal mode (without a new recovery.conf) and then do some database operations, it seems to overwrite a file from my archive: [...recovery...] LOG: archive recovery complete LOG: database system is ready LOG: archived transaction log file 0002.history Now we are at timeline 2 I guess. [...normal startup...] LOG: checkpoint record is at 0/22701F8 LOG: redo record is at 0/22701F8; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 2595; next OID: 231915 LOG: database system is ready [...I do some database action...] LOG: archived transaction log file 00010001 LOG: archived transaction log file 00020002 If I stop postmaster again, wipe out my data/ dir and re-restore the original backup, I can't do any PITRs any more... If I re-install my archive as well, it works again. My question is: When I've restored up to the time t_0, how can I go on to restore up to another point in time, later than t_0 but before the end of my log files. You need to re-restore the original backup. Ah. Ok. I had the impression that the timelines save me from re-restoring the original files and that I could start off directly from there. Ok, that's why it didn't work out that well ;-) Thanks, Joachim ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings