Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42929=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Xa2UI-0003F5-Od
 for pgsql-admin@arkaria.postgresql.org; Fri, 03 Oct 2014 13:01:34 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42929=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Xa2UI-0001T5-7X
 for pgsql-admin@arkaria.postgresql.org; Fri, 03 Oct 2014 13:01:34 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jmathis@redzonesoftware.com>)
 id 1Xa2UG-0001QF-IF
 for pgsql-admin@postgresql.org; Fri, 03 Oct 2014 13:01:32 +0000
Received: from mail-ob0-f181.google.com ([209.85.214.181])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jmathis@redzonesoftware.com>)
 id 1Xa2UD-0003ih-GT
 for pgsql-admin@postgresql.org; Fri, 03 Oct 2014 13:01:30 +0000
Received: by mail-ob0-f181.google.com with SMTP id wm4so800029obc.12
 for <pgsql-admin@postgresql.org>; Fri, 03 Oct 2014 06:01:28 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc
 :message-id:references:to:content-type:content-transfer-encoding;
 bh=pE8S98zmOo6EP3g+ShEoPvzKZ5J1XkgT/CDkZdZZ7PM=;
 b=CyizTZqt1DRbpjfLDUoeP3NBaS89Tmwbwi5o99cWcTC1d7EzQ5CpjSvnSvqTFHljft
 XV15xVBY22uTdOL+oNj8E7lRHYhbg+45EYtDRvOqqw06/HeLmeXbP8KoDO00Y1A0uu2q
 QZSxgUXEAj0uR8UIcMHmKD1KGt391ST6Z7yqnXsN5M8xNmnesp3XFGMfBx7UGf01s66j
 G7pMWVhnl1B0j7Pz0C0jjdXId+I52yAcK0Ac9HAYHm/v1o6JvuC9kOsTsd/9+6vuar7w
 6Dq6yaMAMqksoWry6FfE8HohpgkiqXqcn2I6RwYnkk2dNdAIiyZ/7fvah142V9wqFEFS
 CZbA==
X-Gm-Message-State: 
 ALoCoQm1rR5xri6TDdJRGKQGAlrSTbBnO+pfelTOQztb0xFmiDlv6C5v3V1Woc9KHHz1uU1dGOcmq+R94EhOJBHJneYNl+WqbEuZlPhCXfvHU3ShcpwUcjXZQDbH1zmEkScp4UpMl47t
X-Received: by 10.182.226.161 with SMTP id rt1mr6756088obc.18.1412341288506;
 Fri, 03 Oct 2014 06:01:28 -0700 (PDT)
Received: from [192.168.99.67] (c-50-152-13-243.hsd1.co.comcast.net.
 [50.152.13.243])
 by mx.google.com with ESMTPSA id pf15sm4122161oeb.1.2014.10.03.06.01.27
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Fri, 03 Oct 2014 06:01:28 -0700 (PDT)
Mime-Version: 1.0 (1.0)
Subject: Re: How to detect if Postgres needs a restart when configuration
 changes
From: Jason Mathis <jmathis@redzonesoftware.com>
X-Mailer: iPhone Mail (12A405)
In-Reply-To: 
 <CADKbJJXP-eKGf=ntcHRJgTqiN-XhogmD2RJsRdCt4TcW7Mp8=w@mail.gmail.com>
Date: Fri, 3 Oct 2014 07:01:27 -0600
Cc: Stuart Bishop <stuart@stuartbishop.net>,
 Peter Eisentraut <peter_e@gmx.net>, Ian Barwick <ian@2ndquadrant.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Message-Id: <80920AC4-56A3-4E4F-9AF2-7E7A8635F0D2@redzonesoftware.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>
To: =?utf-8?Q?Strahinja_Kustudi=C4=87?= <strahinjak@nordeus.com>
Content-Type: multipart/alternative;
 boundary=Apple-Mail-42279507-0588-4CA9-86ED-E1A55D76FDF1
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

--Apple-Mail-42279507-0588-4CA9-86ED-E1A55D76FDF1
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 an=
sible and would love to check it out sometime.=20



> On Oct 3, 2014, at 3:07 AM, Strahinja Kustudi=C4=87 <strahinjak@nordeus.c=
om> wrote:
>=20
> On Wed, Oct 1, 2014 at 2:18 PM, Stuart Bishop <stuart@stuartbishop.net> w=
rote:
>> It is unnecessary maintaining a list of parameters which require a
>> restart, as you can tell by looking at pg_settings.
>>=20
>> 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.
>=20
> It's not as easy as it sounds to do that with Ansible, since you don't ma=
gically get a list of parameters which are changed from last time. All para=
meters are in a template which generates postgresql.conf, so you just get t=
he 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 param=
eters which we set that require a restart, it is worth keeping a list.
>=20
> 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:
>=20
> postgres__restart_params:
>   listen_addresses: "{{ postgres_listen_addresses }}"
>   port: "{{ postgres_port }}"
>   max_connections: "{{ postgres_max_connections }}"
>   superuser_reserved_connections: "{{ postgres_superuser_reserved_connect=
ions }}"
>   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 }}"
>=20
> then I check this in the following task:
>=20
> - 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 =3D=3D 't' or restart_params|failed
>   notify: restart postgres
>=20
> ignore_errors is needed since when changing port, psql cannot connect, so=
 when that happens I also restart.

--=20
=20

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

--Apple-Mail-42279507-0588-4CA9-86ED-E1A55D76FDF1
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=
=3Dutf-8"></head><body dir=3D"auto"><div>Very nice I hope it works well for=
 you guys and thanks for sharing. We are the same only a few that need a re=
start. I have heard great things about ansible and would love to check it o=
ut sometime.&nbsp;<br><br><br></div><div><br>On Oct 3, 2014, at 3:07 AM, St=
rahinja Kustudi=C4=87 &lt;<a href=3D"mailto:strahinjak@nordeus.com">strahin=
jak@nordeus.com</a>&gt; wrote:<br><br></div><blockquote type=3D"cite"><div>=
<div dir=3D"ltr"><div class=3D"gmail_extra"><div><div dir=3D"ltr"><span><fo=
nt color=3D"#888888"><div><div style=3D"border-collapse:collapse;font-size:=
13px"><font face=3D"tahoma, sans-serif"><font color=3D"#3366ff"></font></fo=
nt></div>
</div></font></span></div></div>
On Wed, Oct 1, 2014 at 2:18 PM, Stuart Bishop <span dir=3D"ltr">&lt;<a href=
=3D"mailto:stuart@stuartbishop.net" target=3D"_blank">stuart@stuartbishop.n=
et</a>&gt;</span> wrote:<br><div class=3D"gmail_quote"><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><span>
</span>It is unnecessary maintaining a list of parameters which require a<b=
r>
restart, as you can tell by looking at pg_settings.<br>
<br>
The way I do it is by comparing the contents of pg_settings with a<br>
snapshot I made just after (or just before) the server was last<br>
restarted. If any settings have been changed that have a context of<br>
'postmaster', the server needs a restart.<span></span><br></blockquote><div=
><br></div><div>It's not as easy as it sounds to do that with Ansible, sinc=
e 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 t=
o compare each parameter separately. Since it is a lot faster to compare ju=
st a few parameters which we set that require a restart, it is worth keepin=
g a list.<br><br></div><div>If anyone is interested this is how I detect if=
 a server needs a restart. I define a dict with parameters that require a r=
estart and set the keys to have values of the current variables:<br><br><sp=
an style=3D"font-family:courier new,monospace">postgres__restart_params:<br=
>&nbsp; listen_addresses: "{{ postgres_listen_addresses }}"<br>&nbsp; port:=
 "{{ postgres_port }}"<br>&nbsp; max_connections: "{{ postgres_max_connecti=
ons }}"<br>&nbsp; superuser_reserved_connections: "{{ postgres_superuser_re=
served_connections }}"<br>&nbsp; wal_level: "{{ postgres_wal_level }}"<br>&=
nbsp; fsync: "{{ postgres_fsync }}"<br>&nbsp; max_wal_senders: "{{ postgres=
_max_wal_senders }}"<br>&nbsp; hot_standby: "{{ postgres_hot_standby }}"<br=
>&nbsp; shared_buffers: "{{ postgres_shared_buffers }}"<br>&nbsp; archive_m=
ode: "{{ postgres_archive_mode }}"</span><br><br></div><div>then I check th=
is in the following task:<br><br><span style=3D"font-family:courier new,mon=
ospace">- name: Check if a postgres restart is required on master<br>&nbsp;=
 command: psql -p {{ postgres_port }} -At -U postgres -c "SELECT current_se=
tting('{{ item.key }}') &lt;&gt; '{{ item.value }}';"<br>&nbsp; with_dict: =
postgres__restart_params<br>&nbsp; register: restart_params<br>&nbsp; alway=
s_run: yes<br>&nbsp; ignore_errors: yes<br>&nbsp; changed_when: restart_par=
ams.stdout =3D=3D 't' or restart_params|failed<br>&nbsp; notify: restart po=
stgres</span><br><br></div><div>ignore_errors is needed since when changing=
 port, psql cannot connect, so when that happens I also restart.<br></div><=
/div></div></div>
</div></blockquote></body></html>
<br>


<p><font face=3D"Arial" size=3D"2">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 se=
nder at
(303) 386-3955 or by return e-mail to the sender.</font></p>


--Apple-Mail-42279507-0588-4CA9-86ED-E1A55D76FDF1--