Re: How to detect if Postgres needs a restart when configuration changes - Mailing list pgsql-admin

On Wed, Oct 1, 2014 at 2:18 PM, Stuart Bishop <stuart@stuartbishop.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.