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

Very nice I hope it works well for you guys and thanks for sharing. We are the same only a few that need a restart. I have heard great things about ansible and would love to check it out sometime.

> On Oct 3, 2014, at 3:07 AM, Strahinja Kustudić <strahinjak(at)nordeus(dot)com> wrote:
>
> On Wed, Oct 1, 2014 at 2:18 PM, Stuart Bishop <stuart(at)stuartbishop(dot)net> wrote:
>> 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.
>
> It's not as easy as it sounds to do that with Ansible, since you don't magically get a list of parameters which are changed from last time. All parameters are in a template which generates postgresql.conf, so you just get the information that that file changed, that is why you need to compare each parameter separately. Since it is a lot faster to compare just a few parameters which we set that require a restart, it is worth keeping a list.
>
> If anyone is interested this is how I detect if a server needs a restart. I define a dict with parameters that require a restart and set the keys to have values of the current variables:
>
> postgres__restart_params:
> listen_addresses: "{{ postgres_listen_addresses }}"
> port: "{{ postgres_port }}"
> max_connections: "{{ postgres_max_connections }}"
> superuser_reserved_connections: "{{ postgres_superuser_reserved_connections }}"
> wal_level: "{{ postgres_wal_level }}"
> fsync: "{{ postgres_fsync }}"
> max_wal_senders: "{{ postgres_max_wal_senders }}"
> hot_standby: "{{ postgres_hot_standby }}"
> shared_buffers: "{{ postgres_shared_buffers }}"
> archive_mode: "{{ postgres_archive_mode }}"
>
> then I check this in the following task:
>
> - name: Check if a postgres restart is required on master
> command: psql -p {{ postgres_port }} -At -U postgres -c "SELECT current_setting('{{ item.key }}') <> '{{ item.value }}';"
> with_dict: postgres__restart_params
> register: restart_params
> always_run: yes
> ignore_errors: yes
> changed_when: restart_params.stdout == 't' or restart_params|failed
> notify: restart postgres
>
> ignore_errors is needed since when changing port, psql cannot connect, so when that happens I also restart.

--

This transmission contains confidential and privileged information intended
solely for the party identified above. If you receive this message in
error, you must not use it or convey it to others. Please destroy it
immediately and contact the sender at (303) 386-3955 or by return e-mail to
the sender.