Re: [HACKERS] pg_config
Tom Lane wrote: > I doubt it. People can always just read the file to see what > settings are in it, and it's not like nonexperts are going to have a > variety of different configurations that we're gonna have to ask them > about. (Even in the Unix world, pg_config is not really needed when > most people are installing one of a small number of RPM-type > packages...) The point of pg_config is not primarily to debug the installation layout for us. pg_config is used in configure scripts to find PostgreSQL libraries and header files. I don't know if Windows users have a similar need. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump bug fixing
On Mon, 2004-07-19 at 21:20, Christopher Kings-Lynne wrote: > > We already are to some extent, since pg_dump will dump its comment and > > privileges, which it would not do for any other predefined object. > > I think this is actually an implementation artifact rather than > > something that was explicitly intended at the time, but since no one > > has complained about it, it's probably a good thing ;-) > > Does that mean your in favour of dumping a DROP SCHEMA public; command > if they have dropped their public schema? It's definitely not worth > doing it for any other "system" object due to upwards compatibility of > the dump files... Please don't. It would be rather surprising to have stuff disappear from a database while doing a restore -- especially if it's a CASCADE operation. Creating the public schema when needed and leaving it out by default is preferable. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PITR COPY Failure (was Point in Time Recovery)
Mark Kirkwood <[EMAIL PROTECTED]> writes: > I have been doing some re-testing with CVS HEAD from about 1 hour ago > using the simplified example posted previously. > It is quite interesting: The problem seems to be that the computation of checkPoint.redo at xlog.c lines 4162-4169 (all line numbers are per CVS tip) is not allowing for the possibility that XLogInsert will decide it doesn't want to split the checkpoint record across XLOG files, and will then insert a WASTED_SPACE record to avoid that (see comment and following code at lines 758-795). This wouldn't really matter except that there is a safety crosscheck at line 4268 that tries to detect unexpected insertions of other records during a shutdown checkpoint. I think the code in CreateCheckPoint was correct when it was written, because we only recently changed XLogInsert to not split records across files. But it's got a boundary-case bug now, which your test scenario is able to exercise by making the recovery run try to write a shutdown checkpoint exactly at the end of a WAL file segment. The quick and dirty solution would be to dike out the safety check at 4268ff. I don't much care for that, but am too tired right now to work out a better answer. I'm not real sure whether it's better to adjust the computation of checkPoint.redo or to smarten the safety check ... but one or the other needs to allow for file-end padding, or maybe we could hack some update of the state in WasteXLInsertBuffer(). (But at some point you have to say "this is more trouble than it's worth", so maybe we'll end up taking out the safety check.) In any case this isn't a fundamental bug, just an insufficiently smart safety check. But thanks for finding it! As is, the code has a nonzero probability of failure in the field :-( and I don't know how we'd have tracked it down without a reproducible test case. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] patch for allowing multiple -t options to pg_dump
Looks like someone else also just submitted the same patch, except with a -T option to exclude tables. I will consider that version instead. --- Andreas Joseph Krogh wrote: [ PGP not available, raw data follows ] > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, I've prepared a patch(against CVS HEAD of today) to pg_dump.c to > make pg_dump understand multiple -t options for dumping multiple tables > in one command. > Eks: > pg_dump -t table1 -t table2 -t table3 > > The patch is here: > http://home.officenet.no/~andreak/pg_dump.patch > > Any comments, flames? > > Is it too late for it to make it into 7.5? I submitted a patch against > 7.4 a while ago, but was then told it had to wait until 7.5, and, well, > now it's 7.5-time:-) > > - -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > Senior Software Developer / Manager > gpg public_key: http://dev.officenet.no/~andreak/public_key.asc > - +-+ > OfficeNet AS| - a tool should do one job, and do it well. | > Hoffsveien 17 | | > PO. Box 425 Skøyen | | > 0213 Oslo | | > NORWAY | | > Phone : +47 22 13 01 00 | | > Direct: +47 22 13 10 03 | | > Mobile: +47 909 56 963 | | > - +-+ > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.2 (GNU/Linux) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFA7ok9UopImDh2gfQRAmZRAKCUC1H0+7IA3xt9fa385VO9lX4g6wCdEkcO > 2htbeud7zilIw6uMYUD35qM= > =C7Cc > -END PGP SIGNATURE- > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > [ End of raw data] -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] Point in Time Recovery
I don't think so, but it seems like a much less robust way to do things. What happens if you have a failure partway through? For instance archive machine dies and loses recent data right after you've rm'd the source file. The recommended COPY procedure at least provides some breathing room between when you install the data on the archive and when the original file is removed. Well, I tried it in 'cross your fingers' mode and it works, at least: archive_command = 'rm %p' :) Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Point in Time Recovery
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >>> Hang on, are you supposed to MOVE or COPY away WAL segments? >> >> COPY. The checkpoint code will then delete or recycle the segment file, >> as appropriate. > So what happens if you just move it? Postgres breaks? I don't think so, but it seems like a much less robust way to do things. What happens if you have a failure partway through? For instance archive machine dies and loses recent data right after you've rm'd the source file. The recommended COPY procedure at least provides some breathing room between when you install the data on the archive and when the original file is removed. It's not like you save any effort by using a MOVE anyway. You're not going to have the archive on the same machine as the database (or if you are, you ain't gonna be *my* DBA ...) regards, tom lane ---(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] Point in Time Recovery
Hang on, are you supposed to MOVE or COPY away WAL segments? COPY. The checkpoint code will then delete or recycle the segment file, as appropriate. So what happens if you just move it? Postgres breaks? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_config
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I know it's not critical, but is it worth rewriting pg_config in C so it > works on Windows (or alternatively writing it as a .bat file for Windows)? I doubt it. People can always just read the file to see what settings are in it, and it's not like nonexperts are going to have a variety of different configurations that we're gonna have to ask them about. (Even in the Unix world, pg_config is not really needed when most people are installing one of a small number of RPM-type packages...) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Point in Time Recovery
Christopher Kings-Lynne wrote: > > If you keep falling further and further behind, eventually your pg_xlog > > directory will fill the space available on its disk, and I think at that > > point PG will panic and shut down because it can't create any more xlog > > segments. > > Hang on, are you supposed to MOVE or COPY away WAL segments? Copy. pg will delete them once they are archived. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_config
Andrew Dunstan wrote: > > I know it's not critical, but is it worth rewriting pg_config in C so it > works on Windows (or alternatively writing it as a .bat file for Windows)? Oh, pg_config is a shell script. Yes, it should be rewritten in C. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Point in Time Recovery
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> If you keep falling further and further behind, eventually your pg_xlog >> directory will fill the space available on its disk, and I think at that >> point PG will panic and shut down because it can't create any more xlog >> segments. > Hang on, are you supposed to MOVE or COPY away WAL segments? COPY. The checkpoint code will then delete or recycle the segment file, as appropriate. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Point in Time Recovery
If you keep falling further and further behind, eventually your pg_xlog directory will fill the space available on its disk, and I think at that point PG will panic and shut down because it can't create any more xlog segments. Hang on, are you supposed to MOVE or COPY away WAL segments? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump bug fixing
Actually, KL, that would solve a lot of these duplicate object problems. What if pg_restore used Template0 and not Template1?It wouldn't fix the "drop public schema" issue but it would solve the others. Not sure what you mean here, but CVS pg_dump dumps like this: CREATE DATABASE phppgadmin WITH TEMPLATE = template0 OWNER = chriskl ENCODING = 'LATIN1'; CREATE DATABASE usa WITH TEMPLATE = template0 OWNER = usadmin ENCODING = 'LATIN1'; Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Point in Time Recovery
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I've got a PITR set up here that's happily scp'ing WAL files across to > another machine. However, the NIC in the machine is currently stuffed, > so it gets like 50k/s :) What happens in general if you are generating > WAL file bytes faster always than they can be copied off? If you keep falling further and further behind, eventually your pg_xlog directory will fill the space available on its disk, and I think at that point PG will panic and shut down because it can't create any more xlog segments. > Also, does the archive dir just basically keep filling up forever? How > do I know when I can prune some files? Anything older than the last > full backup? Anything older than the starting checkpoint of the last full backup that you might want to restore to. We need to adjust the backup procedure so that the starting segment number for a backup is more readily visible; see recent discussions about logging that explicitly in some fashion. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_config
I know it's not critical, but is it worth rewriting pg_config in C so it works on Windows (or alternatively writing it as a .bat file for Windows)? cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] localhost redux
"Gavin M. Roy" <[EMAIL PROTECTED]> writes: > I have no problem resolving localhost anywhere else on the box, do you > have any suggestions on finding out if it's a misconfiguration? In that case maybe we have a bug to fix; but that makes me even less eager to "solve" it by substituting 127.0.0.1 ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Point in Time Recovery
I've got a PITR set up here that's happily scp'ing WAL files across to another machine. However, the NIC in the machine is currently stuffed, so it gets like 50k/s :) What happens in general if you are generating WAL file bytes faster always than they can be copied off? Also, does the archive dir just basically keep filling up forever? How do I know when I can prune some files? Anything older than the last full backup? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump bug fixing
We already are to some extent, since pg_dump will dump its comment and privileges, which it would not do for any other predefined object. I think this is actually an implementation artifact rather than something that was explicitly intended at the time, but since no one has complained about it, it's probably a good thing ;-) Does that mean your in favour of dumping a DROP SCHEMA public; command if they have dropped their public schema? It's definitely not worth doing it for any other "system" object due to upwards compatibility of the dump files... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PITR COPY Failure (was Point in Time Recovery)
I have been doing some re-testing with CVS HEAD from about 1 hour ago using the simplified example posted previously. It is quite interesting: i) create the table as: CREATE TABLE test0 (filler TEXT); and COPY 100 000 rows on length 109, then recovery succeeds. ii) create the table as: CREATE TABLE test0 (fillerVARCHAR(120)); and COPY as above, then recovery *fails* with the the signal 6 error below. LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/A4807C LOG: record with zero length at 0/E0 LOG: redo done at 0/30 LOG: restored log file "" from archive LOG: archive recovery complete PANIC: concurrent transaction log activity while database system is shutting down LOG: startup process (PID 17546) was terminated by signal 6 LOG: aborting startup due to startup process failure (I am pretty sure both TEXT and VARCHAR(120) failed using the original patch) Any suggestions for the best way to dig a bit deeper? regards Mark ---(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] localhost redux
I have no problem resolving localhost anywhere else on the box, do you have any suggestions on finding out if it's a misconfiguration? Gavin Tom Lane wrote: "Gavin M. Roy" <[EMAIL PROTECTED]> writes: I've been having a problem with the pgstat.c localhost change since 7.4, in that statistics gathering stopped working on my gentoo boxes. I've not traced it back beyond getaddrinfo called in getaddrinfo_all, and decided the quick fix was to go back and change pgstat to use 127.0.0.1 instead of localhost. I don't know if the problem is isolated to just me, but I wanted to suggest that we use a parameter for that, which can be configured in the postgresql.conf, with a default value if it's not set, set to localhost. I think you should first trace down what the problem really is --- is your system just misconfigured or is there some fundamental issue that we really ought to answer to? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] localhost redux
"Gavin M. Roy" <[EMAIL PROTECTED]> writes: > I've been having a problem with the pgstat.c localhost change since 7.4, > in that statistics gathering stopped working on my gentoo boxes. I've > not traced it back beyond getaddrinfo called in getaddrinfo_all, and > decided the quick fix was to go back and change pgstat to use 127.0.0.1 > instead of localhost. > I don't know if the problem is isolated to just me, but I wanted to > suggest that we use a parameter for that, which can be configured in the > postgresql.conf, with a default value if it's not set, set to > localhost. I think you should first trace down what the problem really is --- is your system just misconfigured or is there some fundamental issue that we really ought to answer to? regards, tom lane ---(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] Point in Time Recovery
Bruce and I had another phone chat about the problems that can ensue if you restore a tar backup that contains old (incompletely filled) versions of WAL segment files. While the current code will ignore them during the recovery-from-archive run, leaving them laying around seems awfully dangerous. One nasty possibility is that the archiving mechanism will pick up these files and overwrite good copies in the archive area with the obsolete ones from the backup :-(. Bruce earlier proposed that we simply "rm pg_xlog/*" at the start of a recovery-from-archive run, but as I said I'm scared to death of code that does such a thing automatically. In particular this would make it impossible to handle scenarios where you want to do a PITR recovery but you need to use some recent WAL segments that didn't make it into your archive yet. (Maybe you could get around this by forcibly transferring such segments into the archive, but that seems like a bad idea for incomplete segments.) It would really be best for the DBA to make sure that the starting condition for the recovery run does not have any obsolete segment files in pg_xlog. He could do this either by setting up his backup policy so that pg_xlog isn't included in the tar backup in the first place, or by manually removing the included files just after restoring the backup, before he tries to start the recovery run. Of course the objection to that is "what if the DBA forgets to do it?" The idea that we came to on the phone was for the postmaster, when it enters recovery mode because a recovery.conf file exists, to look in pg_xlog for existing segment files and refuse to start if any are there --- *unless* the user has put a special, non-default overriding flag into recovery.conf. Call it "use_unarchived_files" or something like that. We'd have to provide good documentation and an extensive HINT of course, but basically the DBA would have two choices when he gets this refusal to start: 1. Remove all the segment files in pg_xlog. (This would be the right thing to do if he knows they all came off the backup.) 2. Verify that pg_xlog contains only segment files that are newer than what's stored in the WAL archive, and then set the override flag in recovery.conf. In this case the DBA is taking responsibility for leaving only segment files that are good to use. One interesting point is that with such a policy, we could use locally available WAL segments in preference to pulling the same segments from archive, which would be at least marginally more efficient, and seems logically cleaner anyway. In particular it seems that this would be a useful arrangement in cases where you have questionable WAL segments --- you're not sure if they're good or not. Rather than having to push questionable data into your WAL archive, you can leave it local, try a recovery run, and see if you like the resulting state. If not, it's a lot easier to do-over when you have not corrupted your archive area. Comments? Better ideas? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Why we really need timelines *now* in PITR
On Mon, 2004-07-19 at 23:15, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Mon, 2004-07-19 at 19:33, Tom Lane wrote: > >> * When we need to do recovery, we first identify the source timeline > >> (either by reading the current timeline ID from pg_control, or the DBA > >> can tell us with a parameter in recovery.conf). > > > ** Surely it is the backup itself that determines the source timeline? > > The backup determines the starting point, but there may be several > timelines you could follow after that (especially in the scenario where > you're redoing a recovery starting from the same backup). The point > here is that there could be timeline branches after the backup > occurred. So yes the backup has to be in an ancestral timeline, but not > necessarily exactly the recovery-target timeline. > Agreed. > > ...thinkingrecovery.conf would need to specify: > > recovery_target (if there is one, either a time or txnid) > > recovery_target_timeline (if there is one, otherwise end of last one) > > recovery_target_history_file (which specifies how the timeline ids are > > sequenced) > > No, the source timeline is not necessarily associated with a > recovery_target --- for instance you might want it to run to the end of > a particular timeline. I suspect it might be more confusing than > helpful to use the term "target timeline". > I think we're heatedly agreeing again. A summary: we don't specify the start timeline, but we do specify the timeline which contains our chosen endpoint. [But when we reach it, we may create a new timeline id if we didn't go to end of logs on that timeline.] The history file specifies how to get from start to end, through however many branchpoints there areand the history file we use for recovery is the one pointed to by (target_in_timeline). Or even shorter: - backup specifies starting timeline (and so user specifies indirectly) - user specifies end point (explicitly in recovery.conf) - history file shows how to get from start to end more thoughts...if you specify: target = X target_in_timeline where the default is and if you specify a target, the default target_in_timeline is . I don't like the name target_in_timeline, I'm just trying to clarify what we mean so we can think of a better name for it. ...we definitely need an offline-log inspection tool, don't we? Next month... > We will need to recommend to DBAs that they not delete Y.history from > the archive unless they've already deleted all Y.whatever log segments. > Once they have done this, the past existence of timeline Y is no longer > of interest and so there'd be no real problem in recycling the ID. > I would say the above is just as true if you use random IDs as if you > use sequential ones. I distrust systems that assume there will never be > a collision of "randomly-chosen" IDs. > Yes, I argued myself in a circle, but it seemed worth recording just to avoid repeating the thought next time. 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] Why we really need timelines *now* in PITR
Simon Riggs <[EMAIL PROTECTED]> writes: > I think we're heatedly agreeing again. Yeah, I think so. I'll get started on this tomorrow. > where the default is and if you specify a target, the default > target_in_timeline is . I think actually the default target has to be the timeline ID found in pg_control --- otherwise you get weird behavior in the plain crash recovery, non-PITR case. > I don't like the name target_in_timeline, Agreed, but I don't have a better name offhand for it. The point I was making is that we seem to be using "target" to mean a point-in-time stopping target. But you might be interested in going to the end of timeline N and so there's not a "target" in that sense. That's why I was wanting to avoid using the term "target" for the desired timeline. But maybe there's not a better word. > ...we definitely need an offline-log inspection tool, don't we? > Next month... Yeah. When you get started, I have a toy tool I've been using for awhile that might serve as a starting point. (I'm going to have to whack it around for timelines so there's little point in attaching it right now...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] localhost redux
I've been having a problem with the pgstat.c localhost change since 7.4, in that statistics gathering stopped working on my gentoo boxes. I've not traced it back beyond getaddrinfo called in getaddrinfo_all, and decided the quick fix was to go back and change pgstat to use 127.0.0.1 instead of localhost. I don't know if the problem is isolated to just me, but I wanted to suggest that we use a parameter for that, which can be configured in the postgresql.conf, with a default value if it's not set, set to localhost. I'd be happy to write a patch that follows this behavior but wanted to see if everyone agrees with that line of thinking before doing so. Gavin ---(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] Point in Time Recovery
On Sat, 2004-07-17 at 00:57, Bruce Momjian wrote: > OK, I think I have some solid ideas and reasons for them. > Sorry for taking so long to reply... > First, I think we need server-side functions to call when we start/stop > the backup. The advantage of these server-side functions is that they > will do the required work of recording the pg_control values and > creating needed files with little chance for user error. It also allows > us to change the internal operations in later releases without requiring > admins to change their procedures. We are even able to adjust the > internal operation in minor releases without forcing a new procedure on > users. Yes, I think we should go down this route. there's a "but" and that is we don't absolutely need it for correctnessand so I must decline adding it to THIS release. I don't imagine I'll stop be associated with this code for a while yet Can we recommend that users should expect to have to call a start and end backup routine in later releases? Don't expect you'll agree to that.. > > Second, I think once we start a restore, we should rename recovery.conf > to recovery.in_progress, and when complete rename that to > recovery.done. If the postmaster starts and sees recovery.in_progress, > it will fail to start knowing its recovery was interrupted. This allows > the admin to take appropriate action. (I am not sure what that action > would be. Does he bring back the backup files or just keep going?) > Superceded by Tom's actions. Two states are required: start and stop. Recovery isn't going to be checkpoint-restartable anytime soon, IMHO. 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] Why we really need timelines *now* in PITR
Simon Riggs <[EMAIL PROTECTED]> writes: > On Mon, 2004-07-19 at 19:33, Tom Lane wrote: >> When doing WAL archiving a history file can be copied >> off to the archive area by the existing archiver mechanism (ie, we'll >> make a .ready file for it as soon as it's written). > Need to check the archive code which relies on file shape and length Yeah. I made some adjustments already with the expectation that we'd want to do this, but it'll take a little bit more weakening of the code's tests. >> * When we need to do recovery, we first identify the source timeline >> (either by reading the current timeline ID from pg_control, or the DBA >> can tell us with a parameter in recovery.conf). > ** Surely it is the backup itself that determines the source timeline? The backup determines the starting point, but there may be several timelines you could follow after that (especially in the scenario where you're redoing a recovery starting from the same backup). The point here is that there could be timeline branches after the backup occurred. So yes the backup has to be in an ancestral timeline, but not necessarily exactly the recovery-target timeline. > ...thinkingrecovery.conf would need to specify: > recovery_target (if there is one, either a time or txnid) > recovery_target_timeline (if there is one, otherwise end of last one) > recovery_target_history_file (which specifies how the timeline ids are > sequenced) No, the source timeline is not necessarily associated with a recovery_target --- for instance you might want it to run to the end of a particular timeline. I suspect it might be more confusing than helpful to use the term "target timeline". >> try to open it with the source timeline ID; if that doesn't exist, try >> the immediate parent timeline ID; then the grandparent, etc. > This jigging around is OK, because most people will be using only one > timeline anyhow, so its not likely to cause too much fuss for the user. It might confuse someone who's watching the sequence of archive retrieval requests, but as long as that's all mechanized it doesn't seem like there's any real potential for trouble. > ** I would prefer to add a random number to the timeline as a way of > identifying the next one. This will produce fewer probes, so less wasted > tape mounts, How do you figure that? Seems like the same number of probes either way. > but most importantly it gets round this issue: > You're on timeline X, then you recover and run for a while on timeline > Y. You then realise recovering to that target was a really bad idea for > some reason (some VIPs record wasn't in the recovered data etc). We then > need to re-recover from the backup on X to a new timeline, Z. But how > does X know that Y existed when it creates Z? Because there is a Y.history file laying about (either in the archive or pg_xlog). We will need to recommend to DBAs that they not delete Y.history from the archive unless they've already deleted all Y.whatever log segments. Once they have done this, the past existence of timeline Y is no longer of interest and so there'd be no real problem in recycling the ID. I would say the above is just as true if you use random IDs as if you use sequential ones. I distrust systems that assume there will never be a collision of "randomly-chosen" IDs. > If Y = f(x) in a deterministic way, then Y will always == Z. Of course, > we could provide an id, but what would you pick? The best way is to get > out of trouble by picking a new timeline id that's very unlikely to have > been picked before. I do not see the advantage. 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] Why we really need timelines *now* in PITR
Simon Riggs <[EMAIL PROTECTED]> writes: > The crucial time is when re-running recoveries repeatedly and if we > write the manual with sufficient red ink then we'll avoid this. But > heck, not having your history file is only as bad as not having added > timelines in the first place. Not great, just more care required. Yeah, you only really need them when you are hip-deep in repeated recovery retries. If you haven't gotten to my later proposal yet, the history files will be plain text and it'd be at least theoretically possible for someone to reconstruct one by hand if needed. All you need to have is the sequence of parent timeline IDs, which you could reconstruct in most cases by looking at the archived WAL files. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why we really need timelines *now* in PITR
On Mon, 2004-07-19 at 19:33, Tom Lane wrote: > I wrote: > > I think there's really no way around the issue: somehow we've got to > > keep some meta-history outside the $PGDATA area, if we want to do this > > in a clean fashion. > > After further thought I think we can fix this stuff by creating a > "history file" for each timeline. This will make recovery slightly more > complicated but I don't think it would be any material performance > problem. Here's how it goes: Yes...I came to the conclusion that trying to avoid doing something like DB2 does was just stubornness on my part. We may as well use analogies with other systems when they are available. All of this is good. Two main areas of comments/questions, noted (**) Timelines should be easy to understand for anybody that can follow a HACKERS conversation anyhow :) > > * Timeline IDs are 32-bit ints with no particular semantic significance > (that is, we do not assume timeline 3 is a child of 2, or anything like > that). The actual parentage of a timeline has to be found by inspecting > its history file. > OK...thats better. The nested idea doesn't read well second time through. > * History files will be named by their timeline ID, say "0042.history". > They will be created in /pg_xlog whenever a new timeline is created > by the act of doing a recovery to a point in time earlier than the end > of existing WAL. When doing WAL archiving a history file can be copied > off to the archive area by the existing archiver mechanism (ie, we'll > make a .ready file for it as soon as it's written). > Need to check the archive code which relies on file shape and length > * History files will be plain text (for human consumption) and will > essentially consist of a list of parent timeline IDs in sequence. > I envision adding the timeline split timestamp and starting WAL segment > number too, but these are for documentation purposes --- the system > doesn't need them. We may as well allow comments in there as well, > so that the DBA can annotate the reasons for a PITR split to have been > done. So the contents might look like > > # Recover from unintentional TRUNCATE > 0001000A001425682005-05-16 12:34:15 EDT > # Ex-assistant DBA dropped wrong table > 0007002254342005-11-17 18:44:44 EST > Or should there be a recovery_comment parameter in the recovery.conf? That would be better than suggesting that admins can edit such an important file. (Even if they can, its best not to encourage it). > When we split off a new timeline, we just have to copy the parent's > history file (which we can do verbatim including comments) and then > add a new line at the end showing the immediate parent's timeline ID > and the other details of the split. Initdb can create 0001.history > with empty contents (since that timeline has no parents). Yes. Will you then delete the previous timeline's history file or just leave it there? (OK, you say that later) > * When we need to do recovery, we first identify the source timeline > (either by reading the current timeline ID from pg_control, or the DBA > can tell us with a parameter in recovery.conf). We then read the > history file for that timeline, and remember its sequence of parent > timeline IDs. We can crosscheck that pg_control's timeline ID is > one of this set of timeline IDs, too --- if it's not then the wrong > backup file was restored. ** Surely it is the backup itself that determines the source timeline? Backups are always taken in one particular timeline. The rollforward must start at a checkpoint before the begin backup and roll past the end of backup marker onwards. The starting checkpoint should be the last checkpoint prior to backup - why would you pick another? That checkpoint will always be in the current timeline, since we always come out of startup with a checkpoint (either because we shutdown earlier, or we recovered and just wrote another shutdown checkpoint). So the backup's timeline will determine the source timeline, but not necessarily the target timeline. ...thinkingrecovery.conf would need to specify: recovery_target (if there is one, either a time or txnid) recovery_target_timeline (if there is one, otherwise end of last one) recovery_target_history_file (which specifies how the timeline ids are sequenced) I take it that your understanding is that the recovery_target timeline needs to be specified also? > * During recovery, whenever we need to open a WAL segment file, we first > try to open it with the source timeline ID; if that doesn't exist, try > the immediate parent timeline ID; then the grandparent, etc. Whenever > we find a WAL file with a particular timeline ID, we forget about all > parents further up in the history, and won't try to open their segments > anymore (this is the generalization of my previous rule that you never > drop down in timeline number as you scan forward). > This j
Re: [HACKERS] Why we really need timelines *now* in PITR
On Mon, 2004-07-19 at 16:58, Tom Lane wrote: > I think there's really no way around the issue: somehow we've got to > keep some meta-history outside the $PGDATA area, if we want to do this > in a clean fashion. We could perhaps expect the archive area to store > it, but I'm a bit worried about the idea of overwriting the meta-history > file in archive from time to time; it's mighty critical data and you'd > not be happy if a crash corrupted your only copy. We could archive > meta-history files with successively higher versioned names ... but then > we need an API extension to get back the latest one. > Yes, you've convinced me. It is critical data, but never for that long. If we only split timelines when we recover, then we just make not to take about ~100 copies of it immediately. If we really did recover OK, then it'll only be a few days/weeks before we can forget it ever happened. The crucial time is when re-running recoveries repeatedly and if we write the manual with sufficient red ink then we'll avoid this. But heck, not having your history file is only as bad as not having added timelines in the first place. Not great, just more care required. 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] Why we really need timelines *now* in PITR
I wrote: > I think there's really no way around the issue: somehow we've got to > keep some meta-history outside the $PGDATA area, if we want to do this > in a clean fashion. After further thought I think we can fix this stuff by creating a "history file" for each timeline. This will make recovery slightly more complicated but I don't think it would be any material performance problem. Here's how it goes: * Timeline IDs are 32-bit ints with no particular semantic significance (that is, we do not assume timeline 3 is a child of 2, or anything like that). The actual parentage of a timeline has to be found by inspecting its history file. * History files will be named by their timeline ID, say "0042.history". They will be created in /pg_xlog whenever a new timeline is created by the act of doing a recovery to a point in time earlier than the end of existing WAL. When doing WAL archiving a history file can be copied off to the archive area by the existing archiver mechanism (ie, we'll make a .ready file for it as soon as it's written). * History files will be plain text (for human consumption) and will essentially consist of a list of parent timeline IDs in sequence. I envision adding the timeline split timestamp and starting WAL segment number too, but these are for documentation purposes --- the system doesn't need them. We may as well allow comments in there as well, so that the DBA can annotate the reasons for a PITR split to have been done. So the contents might look like # Recover from unintentional TRUNCATE 0001000A001425682005-05-16 12:34:15 EDT # Ex-assistant DBA dropped wrong table 0007002254342005-11-17 18:44:44 EST When we split off a new timeline, we just have to copy the parent's history file (which we can do verbatim including comments) and then add a new line at the end showing the immediate parent's timeline ID and the other details of the split. Initdb can create 0001.history with empty contents (since that timeline has no parents). * When we need to do recovery, we first identify the source timeline (either by reading the current timeline ID from pg_control, or the DBA can tell us with a parameter in recovery.conf). We then read the history file for that timeline, and remember its sequence of parent timeline IDs. We can crosscheck that pg_control's timeline ID is one of this set of timeline IDs, too --- if it's not then the wrong backup file was restored. * During recovery, whenever we need to open a WAL segment file, we first try to open it with the source timeline ID; if that doesn't exist, try the immediate parent timeline ID; then the grandparent, etc. Whenever we find a WAL file with a particular timeline ID, we forget about all parents further up in the history, and won't try to open their segments anymore (this is the generalization of my previous rule that you never drop down in timeline number as you scan forward). * If we end recovery because we have rolled forward off the end of WAL, we can just continue using the source timeline ID --- we are extending that timeline. (Thus, an ordinary crash and restart doesn't require generating a new timeline ID; nor do we generate a new line during normal postmaster stop/start.) But if we stop recovery at a requested point-in-time earlier than end of WAL, we have to branch off a new timeline. We do this by: * Selecting a previously unused timeline ID (see below). * Writing a history file for this ID, by copying the parent timeline's history file and adding a new line at the end. * Copying the last-used WAL segment of the parent timeline, giving it the same segment number but the new timeline's ID. This becomes the active WAL segment when we start operating. * We can identify the highest timeline ID ever used by simply starting with the source timeline ID and probing pg_xlog and the archive area for history files N+1.history, N+2.history, etc until we find an ID for which there is no history file. Under reasonable scenarios this will not take very many probes, so it doesn't seem that we need any addition to the archiver API to make it more efficient. * Since history files will be small and made infrequently (one hopes you do not need to do a PITR recovery very often...) I see no particular reason not to leave them in /pg_xlog indefinitely. The DBA can clean out old ones if she is a neatnik, but I don't think the system needs to or should delete them. Similarly the archive area could be expected to retain history files indefinitely. * However, you *can* throw away a history file once you are no longer interested in rolling back to times predating the splitoff point of the timeline. If we don't find a history file we can just act as though the timeline has no parents (extends indefinitely far in the past). (Hm, so we don't actually have to bother creating 0001.history...
Re: [HACKERS] pg_dump bug fixing
On Mon, 2004-07-19 at 13:30, Josh Berkus wrote: > Rod, > > > Remove the public schema from template0, but leave it in template1. Have > > pg_dump treat the public schema the same as all of the other ones. > > Hmmm. No good; it wipes out the primary purpose of Template0, which is to > restore a corrupted Template1. Yeah.. This is why my first suggestion was to create a new template for this purpose -- of which most responses told me about template0. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_dump bug fixing
Rod, > Remove the public schema from template0, but leave it in template1. Have > pg_dump treat the public schema the same as all of the other ones. Hmmm. No good; it wipes out the primary purpose of Template0, which is to restore a corrupted Template1. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] NT tab complete patch
Hi all,
I'm trying the savepoint-5.patch
Using the psql the TAB autocomplete doesn't work for savepoint and for release
commands,
also the ROLLBACK doesn't know yet the ROLLBACK TO sintax.
Attached the patch that solve these issues.
BTW, there is a way to know which savepoint are active ?
Regards
Gaetano Mendola
--- pgsql_patched/src/bin/psql/tab-complete.c 2004-07-19 17:21:50.0 +0200
+++ pgsql/src/bin/psql/tab-complete.c 2004-05-26 15:56:55.0 +0200
@@ -463,8 +463,8 @@
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE",
"CLUSTER", "COMMENT",
"COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE", "DROP",
"EXECUTE",
"EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK",
"MOVE", "NOTIFY",
- "PREPARE", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK",
"SAVEPOINT",
-"SELECT", "SET", "SHOW", "START", "TRUNCATE", "UNLISTEN", "UPDATE",
"VACUUM", NULL
+ "PREPARE", "REINDEX", "RESET", "REVOKE", "ROLLBACK", "SELECT", "SET",
"SHOW", "START",
+ "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", NULL
};
static const char * const pgsql_variables[] = {
@@ -726,6 +726,7 @@
else if (pg_strcasecmp(prev_wd, "BEGIN") == 0 ||
pg_strcasecmp(prev_wd, "END") == 0 ||
pg_strcasecmp(prev_wd, "COMMIT") == 0 ||
+pg_strcasecmp(prev_wd, "ROLLBACK") == 0 ||
pg_strcasecmp(prev_wd, "ABORT") == 0)
{
static const char * const list_TRANS[] =
@@ -733,14 +734,6 @@
COMPLETE_WITH_LIST(list_TRANS);
}
-/* ROLLBACK*/
- else if ( pg_strcasecmp(prev_wd, "ROLLBACK") == 0 )
- {
- static const char * const list_TRANS[] =
- {"WORK", "TRANSACTION", "TO", NULL};
-
- COMPLETE_WITH_LIST(list_TRANS);
- }
/* CLUSTER */
/* If the previous word is CLUSTER, produce list of indexes. */
else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump bug fixing
Rod Taylor <[EMAIL PROTECTED]> writes: > Remove the public schema from template0, but leave it in template1. Does not sound very workable. One of the functions of template0 is to be a backup for the virgin state of template1, and you'd lose that. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump bug fixing
On Mon, 2004-07-19 at 12:36, Josh Berkus wrote: > Rod, > > > I think what we want is a clean template without all of the extras that > > template1 has. > > We have this, it's called Template0. Doesn't work for me. I remove a number of things that are included by default in template0, but yes, it's close. > Actually, KL, that would solve a lot of these duplicate object problems. What > if pg_restore used Template0 and not Template1?It wouldn't fix the "drop > public schema" issue but it would solve the others. Remove the public schema from template0, but leave it in template1. Have pg_dump treat the public schema the same as all of the other ones. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_dump bug fixing
Rod, > I think what we want is a clean template without all of the extras that > template1 has. We have this, it's called Template0. Actually, KL, that would solve a lot of these duplicate object problems. What if pg_restore used Template0 and not Template1?It wouldn't fix the "drop public schema" issue but it would solve the others. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Why we really need timelines *now* in PITR
Simon Riggs <[EMAIL PROTECTED]> writes: > Some further thinking from that base... > Perhaps timelines should be nest-numbered: (using 0 as a counter also) > 0 etc is the original branch > 0.1 is the first recovery off the original branch > 0.2 is the second recovery off the original branch > 0.1.1 is the first recovery off the first recovery (so to speak) > 0.1.2 is the second etc > That way you don't have the problem of "which is 3?" in the examples > above. [Would we number a recovery of 1 as 3 or would then next recovery > off 2 be numbered 3?] Hmm. This would have some usefulness as far as documenting "how did we get here", but unless you knew where/when the timeline splits had occurred, I don't think it would be super useful. It'd make more sense to record the parentage and split time of each timeline in some human-readable "meta-history" reference file (but where exactly?) I don't think it does anything to solve our immediate problem, anyhow. You may know that you are recovering off of branch 0.1, but how do you know if this is the first, second, or Nth time you have done that? > Not necessarily the way we would show that as a timeline number. It > could still be shown as a single hex number representing each nesting > level as 4 bits...(restricting us to 7 recoveries per timeline...) Sounds too tight to me :-( I do see a hole in my original concept now that you mention it. It'd be quite possible for timeline 2 *not* to be an ancestor of timeline 3, that is you might have tried a restore, not liked the result, and decided to re-restore from someplace else on timeline 1. That is, instead of 0001.0014 - 0001.0015 - 0001.0016 - 0001.0017 - ... | + 0002.0016 - 0002.0017 - ... | + 0003.0017 - ... maybe the history is 0001.0014 - 0001.0015 - 0001.0016 - 0001.0017 - ... | | | + 0003.0017 - ... | + 0002.0016 - 0002.0017 - ... where I've had to draw 3 above 2 to avoid having unrelated lines crossing each other in my diagram. The problem here is that a crash recovery in timeline 3 would not realize that it should not use WAL segment 0002.0016. So we need a more sophisticated rule than just numerical comparison of timeline numbers. I think your idea of nested numbers might fix this, but I'm concerned about the restrictions of fitting it into 32 bits as you suggest. Can we think of a less restrictive representation? > If we go with the renaming recovery.conf when it completes, why not make > that the record of previous recoveries? Move it to archive_status and > name it according to the timeline it just created, e.g. > recovery.done.. There's still the problem of how can you be sure that all the files created in the past are still in there. It'd be way too likely for someone to decide they ought to do a recovery restore by first doing "rm -rf $PGDATA". Or they lost the disk entirely and are restoring their last full backup onto virgin media. I think there's really no way around the issue: somehow we've got to keep some meta-history outside the $PGDATA area, if we want to do this in a clean fashion. We could perhaps expect the archive area to store it, but I'm a bit worried about the idea of overwriting the meta-history file in archive from time to time; it's mighty critical data and you'd not be happy if a crash corrupted your only copy. We could archive meta-history files with successively higher versioned names ... but then we need an API extension to get back the latest one. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump bug fixing
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I think that we should treat the public schema specially :) We already are to some extent, since pg_dump will dump its comment and privileges, which it would not do for any other predefined object. I think this is actually an implementation artifact rather than something that was explicitly intended at the time, but since no one has complained about it, it's probably a good thing ;-) Also, if we're going to apply Fabien's proposed patch to alter the ownership of the public schema, that's still another way in which the public schema becomes less like a system-defined object ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] function return type
Andrew Dunstan <[EMAIL PROTECTED]> writes: > How can I get a TupleDesc for the return type of a non-SR function that > returns a tuple? I'm sure it's there somewhere but I just can't see it. What do you have available to identify the type? If you know the type OID, then (in CVS tip) I'd recommend the typcache.c function lookup_rowtype_tupdesc(). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CVS compile failure
Bruce Momjian <[EMAIL PROTECTED]> writes: > I am seeing a compile failure in current CVS from strptime(): Should be fixed now. I wasn't very happy with strptime() there in the first place, since it is inflexible about input format and doesn't support a timezone specification. Using our own datetime code works out much better. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CVS compile failure
On Mon, 19 Jul 2004, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Bruce Momjian <[EMAIL PROTECTED]> writes: I am seeing a compile failure in current CVS from strptime(): ./backend/access/transam/xlog.c:if (strptime(tok2, "%Y-%m-%d %H:%M:%S", &tm) == NULL) BSD/OS does not have that function. Under FreeBSD: " The strptime() function does not correctly handle multibyte characters in the format argument" Not sure how critical that is for what you are doing, mind you ... Not at all, since in this call the format is the fixed constant "%Y-%m-%d %H:%M:%S". But it's odd that your BSD variant has strptime() where Bruce's does not. Bruce, how old is your system? :) If I'm reading the man page right, it was introduced on FreeBSD in Jan of '03 ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump bug fixing
On Sun, 2004-07-18 at 23:55, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > I think what we want is a clean template without all of the extras that > > template1 has. > > Sounds like a job for ... template0 ! It doesn't quite work in my case as I've removed items included in template0 (public schema). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump bug fixing
Sounds like a job for ... template0 ! Seriously, this thread would be more convincing if anyone in it betrayed any knowledge that pg_dump wants you to start from template0 rather than template1. What if we made it so that template1 is always restored last? Won't that be an improvement? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why we really need timelines *now* in PITR
On Mon, 2004-07-19 at 04:31, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > The way you write this makes me think you might mean you would allow: we > > can start recovering in one timelines, then rollforward takes us through > > all the timeline nexus points required to get us to the target > > timeline. > > Sure. Let's draw a diagram: > > 0001.0014 - 0001.0015 - 0001.0016 - 0001.0017 - ... > | > + 0002.0016 - 0002.0017 - ... > | > + 0003.0017 - ... > > If you decide you would like to recover to someplace in timeline 0002, > you need to take the 0002 log files where they exist, and the 0001 > log files where there is no 0002, except you do not revert to 0001 > once you have used an 0002 file (this restriction is needed in case > the 0001 timeline goes to higher segment numbers than 0002 has reached). > In no case do you use an 0003 file. > > > I had imagined that recovery would only ever be allowed to start and end > > on the same timeline. I think you probably mean that? > > Logically it's all one timeline, I suppose, but to implement it > physically that way would mean duplicating all past 0001 segments when > we want to create the 0002 timeline. That's not practical and not > necessary. > > > Another of the issues I was thinking through was what happens at the end > > of your scenario abobe > > - You're on timeline 1 and you need to perform recovery. > > - You perform recovery and timeline 2 is created. > > - You discover another error and decide to recover again. > > - You recover timeline 1 again: what do you name the new timeline > > created? 2 or 3? > > You really want to call it 3. To enforce this mechanically would > require having a counter that sits outside the $PGDATA directory and > is not subject to being reverted by a restore-from-backup. I don't > see any very clean way to do that at the moment --- any thoughts? > > In the absence of such a counter we could ask the DBA to specify a new > timeline number in recovery.conf, but this strikes me as one of those > easy-to-get-wrong things ... > > One possibility is to extend the archiving API so that we can inquire > about the largest timeline number that exists anywhere in the archive. > If we take new timeline number = 1 + max(any in archive, any in pg_xlog) > then we are safe. But I'm not really convinced that such a thing would > be any less error-prone than the manual way :-(, because for any > archival method that's more complicated than "cp them all into one > directory", it'd be hard to extract the max stored filename. > Think the same as you do on all of that. Excellent. Some further thinking from that base... Perhaps timelines should be nest-numbered: (using 0 as a counter also) 0 etc is the original branch 0.1 is the first recovery off the original branch 0.2 is the second recovery off the original branch 0.1.1 is the first recovery off the first recovery (so to speak) 0.1.2 is the second etc That way you don't have the problem of "which is 3?" in the examples above. [Would we number a recovery of 1 as 3 or would then next recovery off 2 be numbered 3?] Not necessarily the way we would show that as a timeline number. It could still be shown as a single hex number representing each nesting level as 4 bits...(restricting us to 7 recoveries per timeline...) Just as a thought, DB2 uses the concept of a history file also If we go with the renaming recovery.conf when it completes, why not make that the record of previous recoveries? Move it to archive_status and name it according to the timeline it just created, e.g. recovery.done.. If you're re-restoring into the same directory you wouldn't then overwrite the history of previous recoveries. It would be an extremely bad thing to have to specify the timeline number, and I agree we can't ask the archive. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
