Re: 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.com
PostgreSQL Training, Services and Support

>>> Simon Riggs <simon(at)2ndQuadrant(dot)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(at)2ndQuadrant(dot)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(at)2ndQuadrant(dot)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(at)2ndQuadrant(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)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(dot)linnakangas(at)enterprisedb(dot)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(at)2ndQuadrant(dot)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(at)ca(dot)afilias(dot)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(dot)linnakangas(at)enterprisedb(dot)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.com
PostgreSQL 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.com
PostgreSQL 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(at)gregsmith(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +