Thread: Re: Documentation on PITR still scarce
Joachim Wieland <joe@mcknight.de> 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
Simon, thanks for that quick and detailed answer. On Fri, Nov 05, 2004 at 05:42:01PM +0100, simon@2ndquadrant.com 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
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
Hi, On Fri, Nov 05, 2004 at 10:26:55PM +0000, 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 originalbackup 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 "00000002.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 "000000010000000000000001" LOG: archived transaction log file "000000020000000000000002" 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
On Sat, 2004-11-06 at 00:54, Joachim Wieland wrote: > Hi, > > On Fri, Nov 05, 2004 at 10:26:55PM +0000, 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: > Right. You have not done a correct archive recovery and so, yes, you will get that failure. The database can't know about your activities - you do, and you know they are wrong, so you should expect error. 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". This error is possible because of two things: i) when PostgreSQL starts up, the only things it knows about are in the files in the data directory... it has no other "memory" likes humans do...if you put an incorrect set of files there for it, then it will be...incorrect ii) PostgreSQL hands-off responsibility for management of the archive to you. Using a simple copy command is not the best way to protect your important data archives - its just an example for understanding and testing. It doesn't and can't know what you have done, so cannot itself avoid *requesting* the overwrite. You are the only one that determine that the *request* to archive would cause an error. I can see that this exposes a window for user error, and we should document this. The correct way to get around this potential error is to: i) follow the instructions ii) or, for safety, write a script that checks for the existence of the file in the archive before it does the copy. so then set archive_command = "copy2myarchive ...." where copy2myrchive does - checks for file existence in archive, abort if file exists - does the copy Timelines are brilliant, but they don't protect you from everything. > [...recovery...] > LOG: archive recovery complete > LOG: database system is ready > LOG: archived transaction log file "00000002.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 "000000010000000000000001" > LOG: archived transaction log file "000000020000000000000002" > > > 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 ;-) > 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. 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] -- Best Regards, Simon Riggs
Hi, On Sat, Nov 06, 2004 at 11:13:34AM +0000, 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
On Sat, 2004-11-06 at 15:03, Joachim Wieland wrote: > Hi, > > On Sat, Nov 06, 2004 at 11:13:34AM +0000, 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
On Sat, Nov 06, 2004 at 07:17:29PM +0000, 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
On Sun, 2004-11-07 at 11:15, Joachim Wieland wrote: > On Sat, Nov 06, 2004 at 07:17:29PM +0000, 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
Simon Riggs <simon@2ndquadrant.com> 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
Tom Lane <tgl@sss.pgh.pa.us> 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
On Sun, 2004-11-07 at 20:26, Greg Stark wrote: > Tom Lane <tgl@sss.pgh.pa.us> 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. > Agreed, its all possible, just more code. > 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. "for a long time" is somewhat subjective... I still remember the time before clearly enough, so will many potential PostgreSQL users. There's a huge range of features we can implement eventually and they certainly aren't limited to ones Oracle implemented before us. There are some PostgreSQL unique features to exploit yet. -- Best Regards, Simon Riggs
Is this a TODO? --------------------------------------------------------------------------- Greg Stark wrote: > > Tom Lane <tgl@sss.pgh.pa.us> 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 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
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 <tgl@sss.pgh.pa.us> 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
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 <tgl@sss.pgh.pa.us> 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 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
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 <tgl@sss.pgh.pa.us> 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 majordomo@postgresql.org) > -- 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
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 <tgl@sss.pgh.pa.us> 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
Simon Riggs wrote:> On Mon, 2004-11-29 at 13:10, Bruce Momjian wrote:>>>Or TODO maybe worded as:>>>> * Allow the PITRprocess 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
Simon Riggs <simon@2ndquadrant.com> 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 orfor inspecting a database without disturbing PITR recovery. -- greg
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 <tgl@sss.pgh.pa.us> 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 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