Re: [HACKERS] Interaction of PITR backups and Bulk operationsavoiding WAL
On Fri, 2007-03-09 at 11:15 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Say you issue COPY, CREATE INDEX etc.. pg_start_backup() pg_stop_backup() ...then bulk operation ends. This will result in a base backup that does not contain the data written during the bulk operation and the changes aren't in WAL either. Uh, no. The state of XLogArchivingActive() isn't affected by that. Sorry, error case should have been Say you issue COPY, CREATE INDEX etc.. set archive_command pg_ctl reload pg_start_backup() pg_stop_backup() ...then bulk operation ends. It strikes me that allowing archive_command to be changed on the fly might not be such a good idea though, or at least it shouldn't be possible to flip it from empty to nonempty during live operation. As long as we allow it to be turned on/off during normal operation then there is a current window of error. I'd rather fix it the proposed way than force a restart. ISTM wrong to have an availability feature cause downtime. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Interaction of PITR backups and Bulk operationsavoiding WAL
Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2007-03-09 at 11:15 -0500, Tom Lane wrote: It strikes me that allowing archive_command to be changed on the fly might not be such a good idea though, or at least it shouldn't be possible to flip it from empty to nonempty during live operation. I'd rather fix it the proposed way than force a restart. ISTM wrong to have an availability feature cause downtime. I don't think that people are very likely to need to turn archiving on and off on-the-fly. Your proposed solution introduces a great deal of complexity (and risk of future bugs-of-omission, to say nothing of race conditions) to solve a non-problem. We have better things to be doing with our development time. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interaction of PITR backups and Bulk operationsavoiding WAL
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2007-03-09 at 11:15 -0500, Tom Lane wrote: It strikes me that allowing archive_command to be changed on the fly might not be such a good idea though, or at least it shouldn't be possible to flip it from empty to nonempty during live operation. I'd rather fix it the proposed way than force a restart. ISTM wrong to have an availability feature cause downtime. I don't think that people are very likely to need to turn archiving on and off on-the-fly. Your proposed solution introduces a great deal of complexity (and risk of future bugs-of-omission, to say nothing of race conditions) to solve a non-problem. We have better things to be doing with our development time. So how to do a file based backup without permanent archiving? If pg_start_backup would turn on archiving temporarily with forcing archiving all WAL files that contain open transactions, this would be possible. This is what's requested for sites where PITR isn't needed, just filesystem level backup. Currently, this can be mimicked somehow by turning on archiving on-the-fly, hoping that all xactions are in the WAL archive when pg_start_backup is issued (Simons mail shows how this will fail). Regards, Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Interaction of PITR backups and Bulk operationsavoiding WAL
On Fri, 2007-03-09 at 17:47, Tom Lane wrote: I don't think that people are very likely to need to turn archiving on and off on-the-fly. We did need occasionally to turn archiving on on-the-fly. It did happen that I started up a new DB machine and I did not have yet the log archive available, so I had to wait with configuring that, but the machine went on-line before the archive machine was ready... and then later I had to switch on archiving. It was very convenient that I could do it without a restart. It's true that has been rare occasion, more often you just need to change the archive command (e.g. to archive to a different location if the archive repository goes down). It's somewhat moot for us as we changed to use Slony (which is a heavy beast but once it works it's great). Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend