Re: Config reload/restart preview

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