Re: How to detect if Postgres needs a restart when configuration changes

On 1 October 2014 16:18, Strahinja Kustudić <strahinjak(at)nordeus(dot)com> wrote:
> On Wed, Oct 1, 2014 at 6:56 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>
>> On Tue, 2014-09-30 at 18:51 -0600, Jason Mathis wrote:
>> > How are you automating? Have you looked at puppet or anything? Puppet
>> > labs maintains a postgresql module that has all that baked in. Check
>> > it out at least it can give you some ideas.
>>
>> Their "idea" is to maintain a hard-coded list about which parameter
>> changes need a restart, and their list is wrong.
>
>
> I'm deploying Postgres with Ansible, so Puppet doesn't help.
>
> The idea of maintaining a list of parameters which require a restarts is ok
> for automation, since you usually only change a few of them. I also have a
> list of those parameters which can be changed by Ansible and I would like to
> detect if any of them changed, so I know I need to restart. Or it would be
> even better if Postgres can tell me if it requires a restart to apply all
> parameter changes, something like when you send SIGHUP and in the log it
> tells you that a parameter can only be changed after restart. But I can see
> that doesn't exist.

It is unnecessary maintaining a list of parameters which require a
restart, as you can tell by looking at pg_settings.

The way I do it is by comparing the contents of pg_settings with a
snapshot I made just after (or just before) the server was last
restarted. If any settings have been changed that have a context of
'postmaster', the server needs a restart.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/