On 21 June 2013 05:47, Gurjeet Singh <gurj...@singh.im> wrote: > On Thu, Jun 20, 2013 at 9:33 AM, Magnus Hagander <mag...@hagander.net>wrote: > >> On Thu, Jun 20, 2013 at 2:54 PM, Dimitri Fontaine >> <dimi...@2ndquadrant.fr> wrote: >> > Magnus Hagander <mag...@hagander.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
