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

On Wed, Oct 1, 2014 at 2:48 PM, 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.
>
> I have a relatively easy solution for this with Ansible. Ansible saves the
> values of all parameters which require a restart in one file and I just
> check if that file changed, but the problem with this is that if I change
> this file for any other reason, it could restart Postgres even if it
> doesn't need a restart. The right way is to parse the postgresql.conf and
> compare the values with pg_settings, but I was hoping to avoid that.
>
>

I am not sure the reason behind for this requirement
which can be accomplished using a shell script that compares the database
parameters which needs a start whenever changes in postgresql.conf happen
result towards restart of the database. I feel this implementation may not
be a good fit for the production implementations as the database parameter
changes which need a restart need to go through various business approvals
to happen these changes only in off peak hours. Implementing auto start of
the shell script for the changes on postgresql.conf may lead towards
loosing control over this as user mistakes in changing this file may result
towards restart of the database automatically at any time.

Regards:

Nitin Magdum

www.shreeyansh.com