Thread: allowing wal_level change at run time

In [1], it was discussed to make wal_level changeable at run time
(PGC_SIGHUP), as part of an effort to make replication easier to set up
and/or provide better defaults.  I was wondering what it would take to
do that.

I looks like increasing the setting is doable, as long as we WAL-log the
change using existing facilities.  I don't understand the hot_standby ->
logical transition, so maybe something else is necessary there.

How would we handle decreases at run time?  We can prevent >=archive ->
minimal if archiving is running or there are physical replication slots,
and we can prevent logical -> something less if there are logical
replication slots, but AFAICT, we don't have a way to check whether
anyone currently needs level hot_standby.  (Thread [1] originally
proposed to get rid of the archive/hot_standby distinction, which would
help here.)  Or we could just let users make their own mess if they want to.

Comments?


[1]
http://www.postgresql.org/message-id/CA+TgmoZLji0tAjoVOPFAPJ8d2e8Q=ZM4ZdTQXSKA3J+z-PNi4A@mail.gmail.com

On Tue, Aug 18, 2015 at 7:59 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> How would we handle decreases at run time?  We can prevent >=archive ->
> minimal if archiving is running or there are physical replication slots,
> and we can prevent logical -> something less if there are logical
> replication slots, but AFAICT, we don't have a way to check whether
> anyone currently needs level hot_standby.

What do you mean by "prevent"?  If the user edits postgresql.conf and
reduces the setting, and then reloads the configuration file, they
have a right to expect that the changes got applied.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 8/18/15 8:48 AM, Robert Haas wrote:
> On Tue, Aug 18, 2015 at 7:59 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> How would we handle decreases at run time?  We can prevent >=archive ->
>> minimal if archiving is running or there are physical replication slots,
>> and we can prevent logical -> something less if there are logical
>> replication slots, but AFAICT, we don't have a way to check whether
>> anyone currently needs level hot_standby.
> 
> What do you mean by "prevent"?  If the user edits postgresql.conf and
> reduces the setting, and then reloads the configuration file, they
> have a right to expect that the changes got applied.

We have certain checks in place that require a minimum wal_level before
other things are allowed.  For example, turning on archiving requires
wal_level >= archive.  The issue is then, if you have archiving on and
then turn wal_level to minimal at run time, we need to prevent that to
preserve the integrity of the original check.

Peter Eisentraut <peter_e@gmx.net> writes:
> On 8/18/15 8:48 AM, Robert Haas wrote:
>> What do you mean by "prevent"?  If the user edits postgresql.conf and
>> reduces the setting, and then reloads the configuration file, they
>> have a right to expect that the changes got applied.

> We have certain checks in place that require a minimum wal_level before
> other things are allowed.  For example, turning on archiving requires
> wal_level >= archive.  The issue is then, if you have archiving on and
> then turn wal_level to minimal at run time, we need to prevent that to
> preserve the integrity of the original check.

IIRC, the reason for having a wal_level parameter separate from those
other ones was precisely that only wal_level had to be PGC_POSTMASTER,
and you could change the others if you wanted.  If we are going to fix
the mechanisms to allow dynamic changing in wal log verbosity, maybe
we could simply get rid of wal_level as a user-settable parameter, and
have its effective value be inferred from the active settings of the
other parameters.

IOW: let's simplify, not complicate even further.  Try to get rid of
the interdependencies between settable parameters.
        regards, tom lane

On Tue, Aug 18, 2015 at 9:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> On 8/18/15 8:48 AM, Robert Haas wrote:
>>> What do you mean by "prevent"?  If the user edits postgresql.conf and
>>> reduces the setting, and then reloads the configuration file, they
>>> have a right to expect that the changes got applied.
>
>> We have certain checks in place that require a minimum wal_level before
>> other things are allowed.  For example, turning on archiving requires
>> wal_level >= archive.  The issue is then, if you have archiving on and
>> then turn wal_level to minimal at run time, we need to prevent that to
>> preserve the integrity of the original check.
>
> IIRC, the reason for having a wal_level parameter separate from those
> other ones was precisely that only wal_level had to be PGC_POSTMASTER,
> and you could change the others if you wanted.  If we are going to fix
> the mechanisms to allow dynamic changing in wal log verbosity, maybe
> we could simply get rid of wal_level as a user-settable parameter, and
> have its effective value be inferred from the active settings of the
> other parameters.

Mmm, I like that idea.  If we can do it, it seems much cleaner that way.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 8/18/15 9:50 AM, Tom Lane wrote:
> IIRC, the reason for having a wal_level parameter separate from those
> other ones was precisely that only wal_level had to be PGC_POSTMASTER,
> and you could change the others if you wanted.

I think you are thinking of having split archive_mode/archive_command,
so you can change archive_command at run time.

> If we are going to fix
> the mechanisms to allow dynamic changing in wal log verbosity, maybe
> we could simply get rid of wal_level as a user-settable parameter, and
> have its effective value be inferred from the active settings of the
> other parameters.

That would be nice, but those "other parameters" aren't really things
that we can easily look at.  In the old days, you could say that
archive_mode = on was a pretty sure sign that you'd need wal_level =
archive, but nowadays you can run replication without archiving.  We
could dial wal_level up and down every time someone connects to stream
WAL, but that would surely lead to complications.  Also, we have no way
of knowing whether someone needs wal_level hot_standby until the WAL is
on the standby and the standby decides to set hot_standby = on.  The
permutations of what could possibly influence this setting are quite
enormous, if you consider archiving, streaming, hot standby, hot standby
feedback, replication slots, etc., and synchronizing all of that sounds
like a mess.

On Tue, Aug 18, 2015 at 12:27 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On 8/18/15 9:50 AM, Tom Lane wrote:
>> IIRC, the reason for having a wal_level parameter separate from those
>> other ones was precisely that only wal_level had to be PGC_POSTMASTER,
>> and you could change the others if you wanted.
>
> I think you are thinking of having split archive_mode/archive_command,
> so you can change archive_command at run time.
>
>> If we are going to fix
>> the mechanisms to allow dynamic changing in wal log verbosity, maybe
>> we could simply get rid of wal_level as a user-settable parameter, and
>> have its effective value be inferred from the active settings of the
>> other parameters.
>
> That would be nice, but those "other parameters" aren't really things
> that we can easily look at.  In the old days, you could say that
> archive_mode = on was a pretty sure sign that you'd need wal_level =
> archive, but nowadays you can run replication without archiving.  We
> could dial wal_level up and down every time someone connects to stream
> WAL, but that would surely lead to complications.  Also, we have no way
> of knowing whether someone needs wal_level hot_standby until the WAL is
> on the standby and the standby decides to set hot_standby = on.  The
> permutations of what could possibly influence this setting are quite
> enormous, if you consider archiving, streaming, hot standby, hot standby
> feedback, replication slots, etc., and synchronizing all of that sounds
> like a mess.

If archive_mode=on or max_wal_senders>0, then we need at least
wal_level=archive.  Otherwise wal_level=minimal is enough.

If we eliminate the distinction between wal_level=archive and
wal_level=hot_standby, then we just need a way to distinguish between
hot_standby and logical.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 8/18/15 12:35 PM, Robert Haas wrote:
> If archive_mode=on or max_wal_senders>0, then we need at least
> wal_level=archive.  Otherwise wal_level=minimal is enough.

Totally forgot about max_wal_senders.

However, the thread I linked to earlier aimed for a different master
plan (or if not, I'm aiming for it now).  There is camp 1, which wants
to keep all the defaults the same, for "performance" or something like
that.  And there is camp 2, which wants to have a replication-friendly
setup by default.  Instead of fighting over this, your idea was to be
able to switch between 1 and 2 easily (which means in particular without
restarts).

But if we tie the effective wal_level to archive_mode or
max_wal_senders, both of which are restart-only, then we haven't gained
anything.  (We would have removed half a GUC parameter, effectively.
Not bad, but not very exciting.)

On 2015-08-18 13:24:54 -0400, Peter Eisentraut wrote:
> On 8/18/15 12:35 PM, Robert Haas wrote:
> > If archive_mode=on or max_wal_senders>0, then we need at least
> > wal_level=archive.  Otherwise wal_level=minimal is enough.
>
> Totally forgot about max_wal_senders.
>
> However, the thread I linked to earlier aimed for a different master
> plan (or if not, I'm aiming for it now).  There is camp 1, which wants
> to keep all the defaults the same, for "performance" or something like
> that.  And there is camp 2, which wants to have a replication-friendly
> setup by default.  Instead of fighting over this, your idea was to be
> able to switch between 1 and 2 easily (which means in particular without
> restarts).

I don't think not requiring restarts is sufficient, having to twiddle a
bunch of parameters manually still is a lot more effort than people see
as necessary.

The only reason I think changing the default is a good approach is that
it's doable within a relatively short amount of time.

> But if we tie the effective wal_level to archive_mode or
> max_wal_senders, both of which are restart-only, then we haven't gained
> anything.  (We would have removed half a GUC parameter, effectively.
> Not bad, but not very exciting.)

ISTM that it's not too hard to
a) make archive_mode PGC_SIGHUP
b) make wal_level PGC_SIGHUP
c) automatically increase wal_level to logical whenever a logical  replication slot is defined

it seems considerably harder to

d) make max_wal_senders PGC_SIGHUP
e) make max_replication_slots PGC_SIGHUP

because they need shmem, locks, and everything.


Therefore I propose something slightly different:

We change the default of max_wal_senders, max_replication_slots, to some
reasonably high setting and make wal_level an internal automagically
determined variable. archive_mode = on automatically increases the level
to what's now hot-standby.

To deal with streaming replication, we automatically create slots for
replicas, but, by default, *without* having them reserve WAL. The slot
name would be determined in some automatic fashion (uuid or something)
and stored in a new file in the data directory.  That allows us to
increase the internal wal_level to hot_standby/archive whenever a
replica has connected (and thus a physical slot exists), and to logical
whenever a logical slot exists.

Now, that'll mean that the wal_level doesn't decrease automatically
until a slot has been dropped. But that seems relatively harmless if
it's not reserving WAL.

Too crazy?

Greetings,

Andres Freund

On Wed, Aug 19, 2015 at 2:46 AM, Andres Freund <andres@anarazel.de> wrote:
> On 2015-08-18 13:24:54 -0400, Peter Eisentraut wrote:
>> But if we tie the effective wal_level to archive_mode or
>> max_wal_senders, both of which are restart-only, then we haven't gained
>> anything.  (We would have removed half a GUC parameter, effectively.
>> Not bad, but not very exciting.)
>
> ISTM that it's not too hard to
> a) make archive_mode PGC_SIGHUP
> b) make wal_level PGC_SIGHUP
> c) automatically increase wal_level to logical whenever a logical
>    replication slot is defined

Switching archive_mode and wal_level to PGC_SIGHUP would be nice. We
can already faking that by setting archive_command to '/usr/bin/true'
for the first one or similar but that's not really the same as
switching it completely to off.

> it seems considerably harder to
>
> d) make max_wal_senders PGC_SIGHUP
> e) make max_replication_slots PGC_SIGHUP
>
> because they need shmem, locks, and everything.

Yes. Those have effects on the shared memory size allocated at
postmaster startup.

> Therefore I propose something slightly different:
> We change the default of max_wal_senders, max_replication_slots, to some
> reasonably high setting and make wal_level an internal automagically
> determined variable. archive_mode = on automatically increases the level
> to what's now hot-standby.

Or surely max_wal_senders > 0.

> To deal with streaming replication, we automatically create slots for
> replicas, but, by default, *without* having them reserve WAL. The slot
> name would be determined in some automatic fashion (uuid or something)
> and stored in a new file in the data directory.  That allows us to
> increase the internal wal_level to hot_standby/archive whenever a
> replica has connected (and thus a physical slot exists), and to logical
> whenever a logical slot exists.

So, wal_level is automatically bumped to hot_standby when the physical
slot is created (or logical for a logical slot) even if WAL is not
reserved, right? When would those slots be created?

> Now, that'll mean that the wal_level doesn't decrease automatically
> until a slot has been dropped. But that seems relatively harmless if
> it's not reserving WAL.
>
> Too crazy?

Perhaps, craziest ideas are usually worth it :)

At least, we have a couple of things we can consider:
- Make archive_mode SIGHUP
- Remove wal_level and set it as follows:
-- archive/hot_standby if max_wal_sender > 0 (depends surely on
restart) or archive_mode = on (gets more complicated if archive_mode
switches to SIGHUP) at startup.
-- logical should a logical slot be created.
-- If max_wal_senders = 0 and archive_mode = off, switch wal_level to
hot_standby once a physical slot is created.
In short switching archive_mode to SIGHUP has as requirement to get
rid of wal_level first.
Regards,
-- 
Michael

On 8/18/15 1:46 PM, Andres Freund wrote:
> I don't think not requiring restarts is sufficient, having to twiddle a
> bunch of parameters manually still is a lot more effort than people see
> as necessary.

I agree that we want both.  But requiring a restart is a hard stop,
whereas making configuration easier is a soft feature.

> ISTM that it's not too hard to
> a) make archive_mode PGC_SIGHUP

That has been contentious in the past, but I would agree that it seems
that it should be doable.  (I consider archiving a legacy feature at
this point, so for this purpose I don't really care whether it's possible.)

> b) make wal_level PGC_SIGHUP
> c) automatically increase wal_level to logical whenever a logical
>    replication slot is defined
> 
> it seems considerably harder to
> 
> d) make max_wal_senders PGC_SIGHUP
> e) make max_replication_slots PGC_SIGHUP
> 
> because they need shmem, locks, and everything.

check

> Therefore I propose something slightly different:
> 
> We change the default of max_wal_senders, max_replication_slots, to some
> reasonably high setting and make wal_level an internal automagically
> determined variable. archive_mode = on automatically increases the level
> to what's now hot-standby.

check

> To deal with streaming replication, we automatically create slots for
> replicas, but, by default, *without* having them reserve WAL. The slot
> name would be determined in some automatic fashion (uuid or something)
> and stored in a new file in the data directory.  That allows us to
> increase the internal wal_level to hot_standby/archive whenever a
> replica has connected (and thus a physical slot exists), and to logical
> whenever a logical slot exists.

That seems kind of weird.  So every time a replication client connects,
we create a new replication slot?  Won't they accumulate?  Do we need
the replication slot, or could we just trigger this on the existence of
a replication client?

Also note that this scheme and any similar one requires merging the
archive and hot_standby levels, which was the core of your original
proposal [1], which was then objected to, which subsequently lead to
Robert's proposal to make wal_level SIGHUP, which lead to my message,
which lead to your message, which is similar to your initial one.
Somehow we have to find a way break out of this circle. ;-)


[1]
http://www.postgresql.org/message-id/20150203124317.GA24767@awork2.anarazel.de

On 2015-08-18 21:47:51 -0400, Peter Eisentraut wrote:
> On 8/18/15 1:46 PM, Andres Freund wrote:
> > I don't think not requiring restarts is sufficient, having to twiddle a
> > bunch of parameters manually still is a lot more effort than people see
> > as necessary.
> 
> I agree that we want both.  But requiring a restart is a hard stop,
> whereas making configuration easier is a soft feature.

I don't think it makes that much of a difference for people new to
postgres.

> > To deal with streaming replication, we automatically create slots for
> > replicas, but, by default, *without* having them reserve WAL. The slot
> > name would be determined in some automatic fashion (uuid or something)
> > and stored in a new file in the data directory.  That allows us to
> > increase the internal wal_level to hot_standby/archive whenever a
> > replica has connected (and thus a physical slot exists), and to logical
> > whenever a logical slot exists.
> 
> That seems kind of weird.  So every time a replication client connects,
> we create a new replication slot?  Won't they accumulate?

No, that's not what I was thinking of. The name of the slot would be
stored somewhere in the data directory, and then be reused henceforth.


> Do we need the replication slot, or could we just trigger this on the
> existence of a replication client?

I don't think that can work, because a replication connection obviously
can go away temporarily. If we'd then fall back to wal_level minimal the
standby would be cut off.


> Also note that this scheme and any similar one requires merging the
> archive and hot_standby levels, which was the core of your original
> proposal [1]

It doesn't really, we could continue to keep them separate. I'm
proposing to maintain wal_level automatically, so it'd not be
configurable anymore, so it'd not matter much, as long as we're not less
efficient.


> , which was then objected to, which subsequently lead to
> Robert's proposal to make wal_level SIGHUP, which lead to my message,
> which lead to your message, which is similar to your initial one.
> Somehow we have to find a way break out of this circle. ;-)

My reading of the objections was that it was primarily about increasing
the amount of WAL in the default configuration, and this proposal
wouldn't anymore. An out-of-the-box configuration wouldn't emit more WAL
than today, until it's been used with streaming rep.

Greetings,

Andres Freund

On 2015-08-19 10:49:46 +0200, Magnus Hagander wrote:
> What happens "the first time"? Meaning I'm on wal_level=minimal and take a
> base backup. Then when the replica first connects 10 minutes later, it
> needs WAL back in time, which was logged at wal_level=minimal.

> So you'd need to bump it up whenever a base backup is done -- but then you
> can't drop it back down again or your base backup will be useless.

> Or am I missing something?

Nope. Requiring pg_basebackup to automatically create such a
'non-reserving' slot doesn't seem to be too bad to me.

Greetings,

Andres Freund

On 2015-08-19 17:51:47 +0200, Magnus Hagander wrote:
> That's doable - but what about manual base backups? And if they don't go
> away, what about the ones that are generated by the
> nightly/weekly/hourly/whatever "pg_basebackup -x" ones?

Good questions. I guess we could just make do_pg_start_backup() elevate
the WAL level (using a fixed slot name or some other mechanism) he first
time they're run, until there's explicit admin action.

On 8/19/15 9:32 AM, Andres Freund wrote:
>> I agree that we want both.  But requiring a restart is a hard stop,
>> whereas making configuration easier is a soft feature.
> 
> I don't think it makes that much of a difference for people new to
> postgres.

People new to postgres are not the only audience for this change.

>>> To deal with streaming replication, we automatically create slots for
>>> replicas, but, by default, *without* having them reserve WAL. The slot
>>> name would be determined in some automatic fashion (uuid or something)
>>> and stored in a new file in the data directory.  That allows us to
>>> increase the internal wal_level to hot_standby/archive whenever a
>>> replica has connected (and thus a physical slot exists), and to logical
>>> whenever a logical slot exists.
>>
>> That seems kind of weird.  So every time a replication client connects,
>> we create a new replication slot?  Won't they accumulate?
> 
> No, that's not what I was thinking of. The name of the slot would be
> stored somewhere in the data directory, and then be reused henceforth.

It seems to me that this would effectively replace the wal_level
parameter with the presence or absence of a magic replication slot.
That doesn't seem like a net improvement.  It just replaces one
well-known configuration mechanism with a new ad hoc one.

>> Also note that this scheme and any similar one requires merging the
>> archive and hot_standby levels, which was the core of your original
>> proposal [1]
> 
> It doesn't really, we could continue to keep them separate. I'm
> proposing to maintain wal_level automatically, so it'd not be
> configurable anymore, so it'd not matter much, as long as we're not less
> efficient.

But, under any scheme to set wal_level automatically, how will the
primary know whether it needs to use level archive or hot_standby?
There is no way to know.  So if we are going to keep these levels
separate, there would need to be a manual way to switch between them.

On 2015-08-20 15:11:02 -0400, Peter Eisentraut wrote:
> It seems to me that this would effectively replace the wal_level
> parameter with the presence or absence of a magic replication slot.
> That doesn't seem like a net improvement.  It just replaces one
> well-known configuration mechanism with a new ad hoc one.

Well, with the difference that it can be changed automatically. We could
alternatively automagically use ALTER SYSTEM, but that's not really
guaranteed to work and isn't available via the replication protocol
currently. But I could live with that as well.

> >> Also note that this scheme and any similar one requires merging the
> >> archive and hot_standby levels, which was the core of your original
> >> proposal [1]
> > 
> > It doesn't really, we could continue to keep them separate. I'm
> > proposing to maintain wal_level automatically, so it'd not be
> > configurable anymore, so it'd not matter much, as long as we're not less
> > efficient.
> 
> But, under any scheme to set wal_level automatically, how will the
> primary know whether it needs to use level archive or hot_standby?

I'd have said archive_mode triggered archive and everything else
hot_standby.  I am still of the opinion though that the difference
between those two levels is meaningless and that we should remove
archive.

Andres

On 8/20/15 3:50 PM, Andres Freund wrote:
>> But, under any scheme to set wal_level automatically, how will the
>> primary know whether it needs to use level archive or hot_standby?
> 
> I'd have said archive_mode triggered archive and everything else
> hot_standby.

That might be a decent heuristic, but it's not correct if there is no
way to override it.  People are using archive-only replication with hot
standby (for delayed replication, for example).