From: Jason Mathis <jmathis@redzonesoftware.com>
To: Strahinja Kustudić <strahinjak@nordeus.com>
Cc: Stuart Bishop <stuart@stuartbishop.net>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: Ian Barwick <ian@2ndquadrant.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: How to detect if Postgres needs a restart when configuration changes
Date: Fri, 3 Oct 2014 07:01:27 -0600
Message-ID: <80920AC4-56A3-4E4F-9AF2-7E7A8635F0D2@redzonesoftware.com> (raw)
In-Reply-To: <CADKbJJXP-eKGf=ntcHRJgTqiN-XhogmD2RJsRdCt4TcW7Mp8=w@mail.gmail.com>
References: <CADKbJJVwK9Gx=UEAZQthz-_6kOOcd36_qnyX0+R9StD4bmcH6w@mail.gmail.com>
<542B48C5.6070507@2ndquadrant.com>
<7090DAD0-F2CA-4574-9CA0-BBDF6DD85A04@redzonesoftware.com>
<1412139392.13055.2.camel@vanquo.pezone.net>
<CADKbJJUN_W+LJMZ6eoMUuqDtVNCMv1hB3RQQLGgmV79kC8bPOg@mail.gmail.com>
<CADmi=6Ma_sfW9vWja=wLx2oYmRGa9JmdS2OtKS6MBGn=8NVF4g@mail.gmail.com>
<CADKbJJXP-eKGf=ntcHRJgTqiN-XhogmD2RJsRdCt4TcW7Mp8=w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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@nordeus.com> wrote:
>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jmathis@redzonesoftware.com, strahinjak@nordeus.com, stuart@stuartbishop.net, peter_e@gmx.net, ian@2ndquadrant.com
Subject: Re: How to detect if Postgres needs a restart when configuration changes
In-Reply-To: <80920AC4-56A3-4E4F-9AF2-7E7A8635F0D2@redzonesoftware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox