Re: Config reload/restart preview

Hi,

I've noticed that there's no easy way of checking which settings will
change if the config is reloaded, and I think not being able to do this can
cause some unfortunate problems.

For example, a customer went to change their configuration, just setting
log_autovacuum_min_duration to about 20 seconds, and reloaded the server.
However, the log file swelled to over 5GB in size before they realised
something was wrong, and then reverted the change. It transpired that the
reload also pulled in a log_statements change from 'ddl' to 'all' that
someone must have changed at some point without applying it.

Should we have a way of previewing changes that would be applied if we
reloaded/restarted the server?

For example:

pg_ctl previewconfig

SIGHUP: log_statements will change from 'ddl' to 'all'
SIGHUP: log_vacuum_min_duration will change from -1 to 20000
POSTMASTER: fsync will change from 'on' to 'off'

I'm not proposing this specifically, but something that would provide such
information.

--
Thom

On Thu, Jun 20, 2013 at 1:04 PM, Thom Brown <thom(at)linux(dot)com> wrote:
> Hi,
>
> I've noticed that there's no easy way of checking which settings will change
> if the config is reloaded, and I think not being able to do this can cause
> some unfortunate problems.
>
> For example, a customer went to change their configuration, just setting
> log_autovacuum_min_duration to about 20 seconds, and reloaded the server.
> However, the log file swelled to over 5GB in size before they realised
> something was wrong, and then reverted the change. It transpired that the
> reload also pulled in a log_statements change from 'ddl' to 'all' that
> someone must have changed at some point without applying it.
>
> Should we have a way of previewing changes that would be applied if we
> reloaded/restarted the server?
>
> For example:
>
> pg_ctl previewconfig
>
> SIGHUP: log_statements will change from 'ddl' to 'all'
> SIGHUP: log_vacuum_min_duration will change from -1 to 20000
> POSTMASTER: fsync will change from 'on' to 'off'
>
> I'm not proposing this specifically, but something that would provide such
> information.

Yes, we should.

This would go well with something I started working on some time ago
(but haven't actually gotten far on at all), which is the ability for
pg_ctl to be able to give feedback at all. Meaning a "pg_ctl reload"
should also be able to tell you which parameters were changed, without
having to go to the log. Obviously that's almost exactly the same
feature.

The problem today is that pg_ctl just sends off a SIGHUP when it does
a reload. We'd have to give it an actual interface that could return
data back as well, such as a socket of some kind. So it does take some
work to come up with. But I definitely think we should have something
like this.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Thu, Jun 20, 2013 at 4:34 PM, Thom Brown <thom(at)linux(dot)com> wrote:

> Hi,
>
> I've noticed that there's no easy way of checking which settings will
> change if the config is reloaded, and I think not being able to do this can
> cause some unfortunate problems.
>
> For example, a customer went to change their configuration, just setting
> log_autovacuum_min_duration to about 20 seconds, and reloaded the server.
> However, the log file swelled to over 5GB in size before they realised
> something was wrong, and then reverted the change. It transpired that the
> reload also pulled in a log_statements change from 'ddl' to 'all' that
> someone must have changed at some point without applying it.
>
> Should we have a way of previewing changes that would be applied if we
> reloaded/restarted the server?
>
> For example:
>
> pg_ctl previewconfig
>
> SIGHUP: log_statements will change from 'ddl' to 'all'
> SIGHUP: log_vacuum_min_duration will change from -1 to 20000
> POSTMASTER: fsync will change from 'on' to 'off'
>
> I'm not proposing this specifically, but something that would provide such
> information.
>
>
May be we can have a nice little utility which can show configuration diff
between two running servers, or a running server and its modified conf file
?

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On 20 June 2013 13:16, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:

>
>
>
> On Thu, Jun 20, 2013 at 4:34 PM, Thom Brown <thom(at)linux(dot)com> wrote:
>
>> Hi,
>>
>> I've noticed that there's no easy way of checking which settings will
>> change if the config is reloaded, and I think not being able to do this can
>> cause some unfortunate problems.
>>
>> For example, a customer went to change their configuration, just setting
>> log_autovacuum_min_duration to about 20 seconds, and reloaded the server.
>> However, the log file swelled to over 5GB in size before they realised
>> something was wrong, and then reverted the change. It transpired that the
>> reload also pulled in a log_statements change from 'ddl' to 'all' that
>> someone must have changed at some point without applying it.
>>
>> Should we have a way of previewing changes that would be applied if we
>> reloaded/restarted the server?
>>
>> For example:
>>
>> pg_ctl previewconfig
>>
>> SIGHUP: log_statements will change from 'ddl' to 'all'
>> SIGHUP: log_vacuum_min_duration will change from -1 to 20000
>> POSTMASTER: fsync will change from 'on' to 'off'
>>
>> I'm not proposing this specifically, but something that would provide
>> such information.
>>
>>
> May be we can have a nice little utility which can show configuration diff
> between two running servers, or a running server and its modified conf file
> ?
>

Well checking for configuration differences between 2 running servers is
kind of a separate feature, and I'm not sure it's going to be needed by
that many DBAs. If they wanted to see differences, they could just create
a foreign table to the other and do an anti join.

However, comparing the difference between "a running server and its
modified conf file" is the case I'm describing. I'd personally prefer not
to have a whole new utility if it can be avoided. Magnus mentioned making
the necessary changes to pg_ctl so that it can provide unlogged feedback,
and pg_ctl does feel more like the go-to tool for such a feature. Apache
has apache2ctl which is responsible for starting, stopping and restarting
the server, but also provides a configtest option to check that a restart
won't bring the service down. This seems to be a feature in the same kind
of area. In fact that's pretty much the only action apache2ctl does that
pg_ctl doesn't do.

--
Thom

Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Should we have a way of previewing changes that would be applied if we
>> reloaded/restarted the server?
>
> Yes, we should.

+1

> This would go well with something I started working on some time ago
> (but haven't actually gotten far on at all), which is the ability for
> pg_ctl to be able to give feedback at all. Meaning a "pg_ctl reload"
> should also be able to tell you which parameters were changed, without
> having to go to the log. Obviously that's almost exactly the same
> feature.

It could probably connect to the server and issue the SQL command to
reload, and that one could probably get enhanced to return modified
variable as NOTICE, or be run with the right client_min_message:

SELECT pg_reload_conf();

The pg_ctl client would then have to know to display the messages sent
back by the server.

Then back to what Thom actually is asking, I guess that implementing a
SRF that parses the configuration and return something very much like
what we get from pg_settings should be possible, and then it's a matter
of doing an anti-join, as already proposed.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Thu, Jun 20, 2013 at 2:54 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>> Should we have a way of previewing changes that would be applied if we
>>> reloaded/restarted the server?
>>
>> Yes, we should.
>
> +1
>
>> This would go well with something I started working on some time ago
>> (but haven't actually gotten far on at all), which is the ability for
>> pg_ctl to be able to give feedback at all. Meaning a "pg_ctl reload"
>> should also be able to tell you which parameters were changed, without
>> having to go to the log. Obviously that's almost exactly the same
>> feature.
>
> It could probably connect to the server and issue the SQL command to
> reload, and that one could probably get enhanced to return modified
> variable as NOTICE, or be run with the right client_min_message:
>
> SELECT pg_reload_conf();
>
> The pg_ctl client would then have to know to display the messages sent
> back by the server.

The problem with that is that now you must *always* have your system
set up to allow the postgres user to log in in pg_hba.conf or it
fails.

But yes, one option would be to use SQL instead of opening a socket.
Maybe that's a better idea - have pg_ctl try to use that if available,
and if not send a regular signal and not try to collect the output.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Thu, Jun 20, 2013 at 9:33 AM, Magnus Hagander <magnus(at)hagander(dot)net>wrote:

> On Thu, Jun 20, 2013 at 2:54 PM, Dimitri Fontaine
> <dimitri(at)2ndquadrant(dot)fr> wrote:
> > Magnus Hagander <magnus(at)hagander(dot)net> writes:
> >>> Should we have a way of previewing changes that would be applied if we
> >>> reloaded/restarted the server?
> >>
> >> Yes, we should.
> >
> > +1
> >
> >> This would go well with something I started working on some time ago
> >> (but haven't actually gotten far on at all), which is the ability for
> >> pg_ctl to be able to give feedback at all. Meaning a "pg_ctl reload"
> >> should also be able to tell you which parameters were changed, without
> >> having to go to the log. Obviously that's almost exactly the same
> >> feature.
> >
> > It could probably connect to the server and issue the SQL command to
> > reload, and that one could probably get enhanced to return modified
> > variable as NOTICE, or be run with the right client_min_message:
> >
> > SELECT pg_reload_conf();
> >
> > The pg_ctl client would then have to know to display the messages sent
> > back by the server.
>
> The problem with that is that now you must *always* have your system
> set up to allow the postgres user to log in in pg_hba.conf or it
> fails.
>
> But yes, one option would be to use SQL instead of opening a socket.
> Maybe that's a better idea - have pg_ctl try to use that if available,
> and if not send a regular signal and not try to collect the output.
>

I started working on it yesterday after Thom proposed this idea internally
at EDB. The discussion until now doesn't seem to be hostile to a SQL
interface, so attached is a hack attempt, which hopefully will serve at
least as a POC. A sample session is shown below, while changing a few
values in postgresql.conf files.

The central idea is to use the SIGHUP processing function to do the work
for us and report potential changes via DEBUG2, instead of having to write
a new parsing engine. The (GUC-nesting + PGC_S_TEST) is nice to have since
it avoids the current session from adopting the values that are different
in conf file. This approach is susceptible to the fact that the connected
superuser may have its GUC values picked up from user/database/session
level settings (ALTER USER/DATABASE .. SET ; or SET param TO val;).

$ pgsql
Expanded display is used automatically.
psql (9.4devel)
Type "help" for help.

postgres=# show work_mem;
work_mem
----------
1MB
(1 row)

postgres=# set client_min_messages = debug2;
SET
postgres=# select pg_test_reload_conf();
DEBUG: parameter "work_mem" changed to "70MB"
pg_test_reload_conf
---------------------
t
(1 row)

postgres=# show work_mem;
work_mem
----------
1MB
(1 row)

postgres=# select pg_test_reload_conf();
DEBUG: parameter "shared_buffers" cannot be changed without restarting the
server
DEBUG: configuration file
"/home/gurjeet/dev/pgdbuilds/report_guc_chanege_pre_reload/db/data/postgresql.conf"
contains errors; unaffected changes were applied
pg_test_reload_conf
---------------------
t
(1 row)

postgres=# select pg_test_reload_conf();
DEBUG: parameter "log_min_messages" removed from configuration file, reset
to default
pg_test_reload_conf
---------------------
t
(1 row)

Best regards,
--
Gurjeet Singh

http://gurjeet.singh.im/

EnterpriseDB Inc.

On 21 June 2013 05:47, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:

> On Thu, Jun 20, 2013 at 9:33 AM, Magnus Hagander <magnus(at)hagander(dot)net>wrote:
>
>> On Thu, Jun 20, 2013 at 2:54 PM, Dimitri Fontaine
>> <dimitri(at)2ndquadrant(dot)fr> wrote:
>> > Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> >>> Should we have a way of previewing changes that would be applied if we
>> >>> reloaded/restarted the server?
>> >>
>> >> Yes, we should.
>> >
>> > +1
>> >
>> >> This would go well with something I started working on some time ago
>> >> (but haven't actually gotten far on at all), which is the ability for
>> >> pg_ctl to be able to give feedback at all. Meaning a "pg_ctl reload"
>> >> should also be able to tell you which parameters were changed, without
>> >> having to go to the log. Obviously that's almost exactly the same
>> >> feature.
>> >
>> > It could probably connect to the server and issue the SQL command to
>> > reload, and that one could probably get enhanced to return modified
>> > variable as NOTICE, or be run with the right client_min_message:
>> >
>> > SELECT pg_reload_conf();
>> >
>> > The pg_ctl client would then have to know to display the messages sent
>> > back by the server.
>>
>> The problem with that is that now you must *always* have your system
>> set up to allow the postgres user to log in in pg_hba.conf or it
>> fails.
>>
>> But yes, one option would be to use SQL instead of opening a socket.
>> Maybe that's a better idea - have pg_ctl try to use that if available,
>> and if not send a regular signal and not try to collect the output.
>>
>
> I started working on it yesterday after Thom proposed this idea internally
> at EDB. The discussion until now doesn't seem to be hostile to a SQL
> interface, so attached is a hack attempt, which hopefully will serve at
> least as a POC. A sample session is shown below, while changing a few
> values in postgresql.conf files.
>
> The central idea is to use the SIGHUP processing function to do the work
> for us and report potential changes via DEBUG2, instead of having to write
> a new parsing engine. The (GUC-nesting + PGC_S_TEST) is nice to have since
> it avoids the current session from adopting the values that are different
> in conf file. This approach is susceptible to the fact that the connected
> superuser may have its GUC values picked up from user/database/session
> level settings (ALTER USER/DATABASE .. SET ; or SET param TO val;).
>
> $ pgsql
> Expanded display is used automatically.
> psql (9.4devel)
> Type "help" for help.
>
> postgres=# show work_mem;
> work_mem
> ----------
> 1MB
> (1 row)
>
> postgres=# set client_min_messages = debug2;
> SET
> postgres=# select pg_test_reload_conf();
> DEBUG: parameter "work_mem" changed to "70MB"
> pg_test_reload_conf
> ---------------------
> t
> (1 row)
>
> postgres=# show work_mem;
> work_mem
> ----------
> 1MB
> (1 row)
>
> postgres=# select pg_test_reload_conf();
> DEBUG: parameter "shared_buffers" cannot be changed without restarting
> the server
> DEBUG: configuration file
> "/home/gurjeet/dev/pgdbuilds/report_guc_chanege_pre_reload/db/data/postgresql.conf"
> contains errors; unaffected changes were applied
> pg_test_reload_conf
> ---------------------
> t
> (1 row)
>
> postgres=# select pg_test_reload_conf();
> DEBUG: parameter "log_min_messages" removed from configuration file,
> reset to default
> pg_test_reload_conf
> ---------------------
> t
> (1 row)
>

Thanks for taking a look at this Gurjeet. This seems to be a step towards
it, but there are a few issues:

1) As you say, if the superuser has role-level settings, it will provide
incorrect feedback.

2) Settings that require a restart don't tell you what there are currently
set to and what they would be set to. I'm not sure the currently-logged
text provided by a SIGHUP is necessarily appropriate for such a feature.

3) I'd expect that a DBA that goes editing postgresql.conf wouldn't then go
logging in to the database to run this command, if they even knew it
existed. Whereas they would typically be familiar with
"/etc/init.d/postgresql <action>" or "pg_ctl <action>". Now if the SQL
function was a medium to achieve this, that would be fine, but I'm not
clear on what would be required technically to achieve the kind of
intuitive admin-friendly interface. Or maybe there's a better method of
checking the config that I haven't considered.

--
Thom