Thread: Interaction of PITR backups and Bulk operations avoiding WAL
Reviewing earlier threads, I realised that there was a potential bug/loophole in PITR backups in conjunction with avoiding WAL for bulk operations. This would be rare, but should be fixed. http://archives.postgresql.org/pgsql-hackers/2006-05/msg01113.php 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. I propose to fix this by making two new callsbool RequestBulkCommandUseNoWAL(void)void ResetBulkCommandUseNoWAL(void) so we would use it like thisuse_wal = RequestBulkCommandUseNoWAL() and then at end of operationif (!use_wal) ResetBulkCommandUseNoWAL(); The routine would record a flag on the shmem ControlFile data that would prevent pg_start backup functions from executing while a bulk operation was in progress. It would also prevent a bulk operation from using no WAL while a backup was in progress, as is already the case, since the backup can only take place while archiving is enabled. A new entry point pg_start_backup(text, bool) would allow the user to specify whether to wait for bulk ops to finish, or not. The old entry point would always wait, to ensure safety in all cases. Thoughts? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
"Simon Riggs" <simon@2ndquadrant.com> 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.
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.
regards, tom lane
On Fri, 2007-03-09 at 11:15 -0500, Tom Lane wrote: > "Simon Riggs" <simon@2ndquadrant.com> 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
"Simon Riggs" <simon@2ndquadrant.com> 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
On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > "Simon Riggs" <simon@2ndquadrant.com> 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. It's certainly a quicker fix. Unless others object, I'll set archive_command to only be changeable at server startup. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Tom Lane wrote: > "Simon Riggs" <simon@2ndquadrant.com> 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
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.
On Fri, Mar 09, 2007 at 04:57:18PM +0000, Simon Riggs wrote: > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > It's certainly a quicker fix. Unless others object, I'll set > archive_command to only be changeable at server startup. I think the docs should also explain why it's server-start only, since if someone wanted to they could circumvent the behavior by having archive_command call a shell script that changes it's behavior. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
"Jim C. Nasby" <jim@nasby.net> writes:
> On Fri, Mar 09, 2007 at 04:57:18PM +0000, Simon Riggs wrote:
>> It's certainly a quicker fix. Unless others object, I'll set
>> archive_command to only be changeable at server startup.
> I think the docs should also explain why it's server-start only, since
> if someone wanted to they could circumvent the behavior by having
> archive_command call a shell script that changes it's behavior.
Um, what's the problem with that? The concern was about whether PG
would produce consistent WAL output, not whether the archive_command
actually needed to do anything.
regards, tom lane
Where is this patch? --------------------------------------------------------------------------- Simon Riggs wrote: > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > It's certainly a quicker fix. Unless others object, I'll set > archive_command to only be changeable at server startup. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Mon, 2007-04-02 at 19:09 -0400, Bruce Momjian wrote: > Where is this patch? see Hackers thread: "Minor changes to Recovery related code", Mar 30 -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > It's certainly a quicker fix. Unless others object, I'll set > archive_command to only be changeable at server startup. I _still_ have no patch for this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Wed, 2007-04-04 at 22:05 -0400, Bruce Momjian wrote: > I _still_ have no patch for this. Bruce, As I've mentioned, there is another thread where the discussion continued, which you should refer to. The subject of this thread is a potential bug that has existed since 8.1 and that I recently picked up on. Knowing that fixing bugs was OK after freeze, I thought it best to finish the features I was working on first. Even so, before freeze I requested some additional time to work on some related minor items. So there's nothing overdue now and in any case will be finished shortly. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Wed, 2007-04-04 at 22:05 -0400, Bruce Momjian wrote: > > > I _still_ have no patch for this. > > Bruce, > > As I've mentioned, there is another thread where the discussion > continued, which you should refer to. > > The subject of this thread is a potential bug that has existed since 8.1 > and that I recently picked up on. Knowing that fixing bugs was OK after > freeze, I thought it best to finish the features I was working on first. > Even so, before freeze I requested some additional time to work on some > related minor items. > > So there's nothing overdue now and in any case will be finished shortly. OK, I was unaware you were still working on this item. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Simon Riggs wrote: > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > It's certainly a quicker fix. Unless others object, I'll set > archive_command to only be changeable at server startup. Simon, has this patch been submitted? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Thu, 2007-04-26 at 18:51 -0400, Bruce Momjian wrote: > Simon Riggs wrote: > > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > > > It's certainly a quicker fix. Unless others object, I'll set > > archive_command to only be changeable at server startup. > > Simon, has this patch been submitted? Yes -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Thu, 2007-04-26 at 18:51 -0400, Bruce Momjian wrote: > > Simon Riggs wrote: > > > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > > > > > It's certainly a quicker fix. Unless others object, I'll set > > > archive_command to only be changeable at server startup. > > > > Simon, has this patch been submitted? > > Yes Uh, do you see it in the patch queue now? If not, what was the subject line. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --------------------------------------------------------------------------- Simon Riggs wrote: > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > It's certainly a quicker fix. Unless others object, I'll set > archive_command to only be changeable at server startup. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Simon intended to commit this per http://archives.postgresql.org/pgsql-hackers/2007-03/msg01761.php (actually, there was a change in what was being done). I suspect this item isn't valid any longer. On Tue, May 15, 2007 at 07:30:58PM -0400, Bruce Momjian wrote: > > This has been saved for the 8.4 release: > > http://momjian.postgresql.org/cgi-bin/pgpatches_hold > > --------------------------------------------------------------------------- > > Simon Riggs wrote: > > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > > > It's certainly a quicker fix. Unless others object, I'll set > > archive_command to only be changeable at server startup. > > > > -- > > Simon Riggs > > EnterpriseDB http://www.enterprisedb.com > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
OK, removed. --------------------------------------------------------------------------- Jim C. Nasby wrote: > Simon intended to commit this per > http://archives.postgresql.org/pgsql-hackers/2007-03/msg01761.php > (actually, there was a change in what was being done). I suspect this > item isn't valid any longer. > > On Tue, May 15, 2007 at 07:30:58PM -0400, Bruce Momjian wrote: > > > > This has been saved for the 8.4 release: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches_hold > > > > --------------------------------------------------------------------------- > > > > Simon Riggs wrote: > > > On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: > > > > "Simon Riggs" <simon@2ndquadrant.com> 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. > > > > > > It's certainly a quicker fix. Unless others object, I'll set > > > archive_command to only be changeable at server startup. > > > > > > -- > > > Simon Riggs > > > EnterpriseDB http://www.enterprisedb.com > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: explain analyze is your friend > > > > -- > > Bruce Momjian <bruce@momjian.us> http://momjian.us > > EnterpriseDB http://www.enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. + > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > -- > Jim Nasby decibel@decibel.org > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +