Thread: Enabling archive_mode without restart
Currently we enable archive_mode only at server start. The reason for this was to protect against people enabling archive_mode half way through somebody else running a bulk load without WAL and then having an incomplete backup. All we need to do is this: * When we change archive_mode to "on" get the next xid and place it in xlogctl. When we turn archive_mode "off" clear the value. * When we run pg_start_backup() check the xid and then wait for all transactions to end that can still see that xid before returning. This means there may be a delay after enabling archive_mode before we can run a backup, but normal running will not be interrupted. It's a fairly small change, touching just guc.c and xlog.c, using same code as DefineIndex() to wait. Objections? -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
>>> Simon Riggs <simon@2ndQuadrant.com> wrote: > Currently we enable archive_mode only at server start. The reason for > this was to protect against people enabling archive_mode half way > through somebody else running a bulk load without WAL and then having an > incomplete backup. > > All we need to do is this: > > * When we change archive_mode to "on" get the next xid and place it in > xlogctl. When we turn archive_mode "off" clear the value. > > * When we run pg_start_backup() check the xid and then wait for all > transactions to end that can still see that xid before returning. > > This means there may be a delay after enabling archive_mode before we > can run a backup, but normal running will not be interrupted. > > It's a fairly small change, touching just guc.c and xlog.c, using same > code as DefineIndex() to wait. This would be a welcome change. While we do continuous archiving for most of our databases, there are some big ones where we just turn on WAL archiving to capture a PITR "snapshot". Having to restart PostgreSQL on both sides of the backup complicates things operationally. -Kevin
Simon Riggs <simon@2ndQuadrant.com> writes:
> Objections?
Why is this worth spending effort on? I doubt that your sketch is
correct/complete anyway (you at least neglected to describe starting
and stopping the archiver, as well as how it knows where to start
archiving).
regards, tom lane
Tom Lane wrote: > Simon Riggs <simon@2ndQuadrant.com> writes: >> Objections? > > Why is this worth spending effort on? The addition of archive_mode changeable with restart only was a significant loss of usability going from 8.2 to 8.3, in the minds of many people.
Peter Eisentraut wrote: > Tom Lane wrote: >> Simon Riggs <simon@2ndQuadrant.com> writes: >>> Objections? >> >> Why is this worth spending effort on? > > The addition of archive_mode changeable with restart only was a > significant loss of usability going from 8.2 to 8.3, in the minds of > many people. It certainly makes more sense to do this: archive_mode = on pg_ctl reload archive_mode = off pg_ctl reload versus archive_command = '/path/to/really/long/archive/string' pg_ctl reload archive_command = '/bin/true' pg_ctl reload So the short question is, "Do we care about sane behavior for our users?" If the answer is yes then that is *exactly* why it is worth spending effort on. If the answer is no, well it's open source. Joshua D. Drake
>>> "Joshua D. Drake" <jd@commandprompt.com> wrote: > It certainly makes more sense to do this: > > archive_mode = on > pg_ctl reload > > archive_mode = off > pg_ctl reload > > versus > > archive_command = '/path/to/really/long/archive/string' > pg_ctl reload > > archive_command = '/bin/true' > pg_ctl reload Hmmm... If there's no significant performance difference between archive_mode = on archive_command = '/bin/true' and archive_mode = off archive_command = '/archive/command/used/during/snapshot/backups' I could live with diddling the command to control archiving. It doesn't log anything extra when archive_mode is on? Why is it safer to change archive_command to a no-op on the fly than to turn off archive mode? -Kevin
Kevin Grittner wrote: >>>> "Joshua D. Drake" <jd@commandprompt.com> wrote: >> It certainly makes more sense to do this: > archive_mode = off > archive_command = '/archive/command/used/during/snapshot/backups' > > I could live with diddling the command to control archiving. > > It doesn't log anything extra when archive_mode is on? Depends on your logging level. I don't know that there is noticeable difference between the two in terms of performance. That said, it is still silly that we have to handle it in such a hacky way. Frankly, it should be a catalog reloption of some sort... "ALTER CATALOG (which doesn't exist) archive_mode TO off" but I digress. > > Why is it safer to change archive_command to a no-op on the fly than > to turn off archive mode? I think it is because one launches a process that controls another process. Just like autovacuum or the logger. You can change what the children processes do but not the parent. Joshua D. Drake > > -Kevin >
Joshua D. Drake wrote: > Kevin Grittner wrote: >>>>> "Joshua D. Drake" <jd@commandprompt.com> wrote: >>> It certainly makes more sense to do this: > >> archive_mode = off >> archive_command = '/archive/command/used/during/snapshot/backups' >> >> I could live with diddling the command to control archiving. >> >> It doesn't log anything extra when archive_mode is on? > > Depends on your logging level. I don't know that there is noticeable > difference between the two in terms of performance. That said, it is > still silly that we have to handle it in such a hacky way. Frankly, it > should be a catalog reloption of some sort... "ALTER CATALOG (which > doesn't exist) archive_mode TO off" but I digress. archive_mode = on disables the optimization to skip WAL-logging when loading into a new table that was created in the same transaction. That's the case that Simon described in the mail that started this thread. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
>>> "Joshua D. Drake" <jd@commandprompt.com> wrote: > Kevin Grittner wrote: >> It doesn't log anything extra when archive_mode is on? > > Depends on your logging level. I guess my question wasn't clear -- I meant into the Write-Ahead Log (WAL) files. -Kevin
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> archive_mode = on disables the optimization to skip WAL-logging when
> loading into a new table that was created in the same transaction.
... and affects a whole bunch of other behaviors too, in processes all
across the database that could not possibly adopt a new setting
synchronously. That's exactly why it was made a postmaster-start option
to begin with. Simon's given no argument at all why it would be safe to
flip it on-the-fly.
regards, tom lane
On Fri, 2008-10-31 at 17:38 +0200, Peter Eisentraut wrote: > Tom Lane wrote: > > Simon Riggs <simon@2ndQuadrant.com> writes: > >> Objections? > > > > Why is this worth spending effort on? > > The addition of archive_mode changeable with restart only was a > significant loss of usability going from 8.2 to 8.3, in the minds of > many people. I put my hand up as one of those people. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
>>> Brad Nicholson <bnichols@ca.afilias.info> wrote: > On Fri, 2008-10-31 at 17:38 +0200, Peter Eisentraut wrote: >> Tom Lane wrote: >> > Why is this worth spending effort on? >> >> The addition of archive_mode changeable with restart only was a >> significant loss of usability going from 8.2 to 8.3, in the minds of >> many people. > > I put my hand up as one of those people. Now that I better understand the issue, I don't think there was any loss of functionality. There was one change that broke compatibility (possibly unnecessarily) and one enhancement which you may or may not be able to use, depending on your work-flow. The compatibility change was that if you want to turn off WAL file archiving without restarting the database, you now have to set your archive_command to 'exit 1' or '/bin/true' (or similar) instead of setting it to an empty string. The new feature is that if you don't want to archive WAL files without a restart, you can use the archive_mode setting and get the benefit of several optimizations. If you need to toggle, you get to choose between restarts to enable the optimizations, or legacy techniques to keep the database running. Seems reasonable enough to me. We might have an opportunity to expand the documentation here.... -Kevin
On Fri, 2008-10-31 at 13:14 -0400, Tom Lane wrote: > Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes: > > archive_mode = on disables the optimization to skip WAL-logging when > > loading into a new table that was created in the same transaction. > > ... and affects a whole bunch of other behaviors too, in processes all > across the database that could not possibly adopt a new setting > synchronously. That's exactly why it was made a postmaster-start option > to begin with. Simon's given no argument at all why it would be safe to > flip it on-the-fly. First, you are right my initial sketch missed a couple of obvious mechanisms. But those aren't correctness issues. Flipping in mid-execution wasn't really what I was proposing. That would clearly be a big problem. Even between statements could be a problem. The number of places that test XLogArchivingActive() is fairly small and infrequently executed, so it would be OK to check a shared memory value at those points and retain the setting for the rest of the transaction. Anyway, I think this is worth fixing before release but it clearly isn't worth attempting to rush a patch in the next few hours. I don't think we'll find anyone who is happy with making it a restart-required option. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
> Anyway, I think this is worth fixing before release but it clearly isn't > worth attempting to rush a patch in the next few hours. I don't think > we'll find anyone who is happy with making it a restart-required option. I couldn't find a patch/commit for this and was just wondering whether someone is addressing it for 8.4? In a large-scale OLTP environment, uptime is paramount, and having to restart the database to enable PITR is a big PITA. -- Jonah H. Harris, Senior DBA myYearbook.com
On Wed, 2008-11-12 at 10:48 -0500, Jonah H. Harris wrote: > > Anyway, I think this is worth fixing before release but it clearly isn't > > worth attempting to rush a patch in the next few hours. I don't think > > we'll find anyone who is happy with making it a restart-required option. > > I couldn't find a patch/commit for this and was just wondering whether > someone is addressing it for 8.4? In a large-scale OLTP environment, > uptime is paramount, and having to restart the database to enable PITR > is a big PITA. Agreed, though I think we should let the dust settle on other features before working on this. We need to look at the full set of options we'll need for replication and hot standby. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
On Wed, 12 Nov 2008, Jonah H. Harris wrote: > In a large-scale OLTP environment, uptime is paramount, and having to > restart the database to enable PITR is a big PITA. What I did last time I was stuck with this problem was make the archive_command point to a script I could toggle on and off outside of the database itself via a settings file. Then you can leave archive_mode on all the time, instead adjusting that settings file to determine whether it ships the logs somewhere or just ignores them when it gets called. A large benefit to that approach is that you can do other upgrades to said script without touching the database configuration. The whole idea of implementing the archive_command as a one-line bit is horrifying to me anyway. Put it in a script instead, then you can do error checking and basic sanity checks (disk space and number of active WAL segments are two good things to monitor) and e-mail or otherwise alert if there's a problem. The primary downside to always having archiving on is you lose the fresh table WAL optimization path, but in real systems I haven't found that to be so compelling. When doing initial bulk loading, absolutely a help, but if your database doesn't actually create/truncate tables in normal use it doesn't buy you anything once you're in production. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Added to TODO:
Allow archive_mode to be changed without server restart?
* http://archives.postgresql.org/pgsql-hackers/2008-10/msg01655.php
---------------------------------------------------------------------------
Simon Riggs wrote:
>
> Currently we enable archive_mode only at server start. The reason for
> this was to protect against people enabling archive_mode half way
> through somebody else running a bulk load without WAL and then having an
> incomplete backup.
>
> All we need to do is this:
>
> * When we change archive_mode to "on" get the next xid and place it in
> xlogctl. When we turn archive_mode "off" clear the value.
>
> * When we run pg_start_backup() check the xid and then wait for all
> transactions to end that can still see that xid before returning.
>
> This means there may be a delay after enabling archive_mode before we
> can run a backup, but normal running will not be interrupted.
>
> It's a fairly small change, touching just guc.c and xlog.c, using same
> code as DefineIndex() to wait.
>
> Objections?
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +