Thread: How to detect if Postgres needs a restart when configuration changes
I am automating Postgres deployment on Linux and I need to check if Postgres needs to be restarted when a configuration parameter that requires a restart changes, e.g. when you change wal_level. Is there a way to detect a parameter which requires a restart was changed?
Reading the log file after sending SIGHUP is not possible to automate. I know I can query pg_settings and then compare relevant parameters from the conf file, but that means I will need to check all parameters which I set and which require a restart. I only need to know if postgres requires a restart.
Strahinja Kustudić | Lead System Engineer | Nordeus
Strahinja Kustudić | Lead System Engineer | Nordeus
you can query the context value of the particular setting in pg_settings table. http://www.postgresql.org/docs/9.1/static/view-pg-settings.html
On Tue, Sep 30, 2014 at 7:51 PM, Strahinja Kustudić <strahinjak@nordeus.com> wrote:
I am automating Postgres deployment on Linux and I need to check if Postgres needs to be restarted when a configuration parameter that requires a restart changes, e.g. when you change wal_level. Is there a way to detect a parameter which requires a restart was changed?Reading the log file after sending SIGHUP is not possible to automate. I know I can query pg_settings and then compare relevant parameters from the conf file, but that means I will need to check all parameters which I set and which require a restart. I only need to know if postgres requires a restart.
Strahinja Kustudić | Lead System Engineer | Nordeus
On 14/10/01 8:51, Strahinja Kustudić wrote: > I am automating Postgres deployment on Linux and I need to check if Postgres > needs to be restarted when a configuration parameter that requires a restart > changes, e.g. when you change wal_level. Is there a way to detect a parameter > which requires a restart was changed? > > Reading the log file after sending SIGHUP is not possible to automate. I know > I can query pg_settings and then compare relevant parameters from the conf file, > but that means I will need to check all parameters which I set and which require a > restart. I only need to know if postgres requires a restart. I created an experimental background worker which records configuration parameter changes after a SIGHUP is received: https://github.com/ibarwick/config_log It was originally intended to log configuration changes, however it might be possible to create a query based off the recorded values to detect changes which require a restart. No guarantee of fitness for purpose. Requires 9.3 or later. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
How are you automating? Have you looked at puppet or anything? Puppet labs maintains a postgresql module that has all thatbaked in. Check it out at least it can give you some ideas. https://forge.puppetlabs.com/puppetlabs/postgresql > On Sep 30, 2014, at 6:20 PM, Ian Barwick <ian@2ndquadrant.com> wrote: > >> On 14/10/01 8:51, Strahinja Kustudić wrote: >> I am automating Postgres deployment on Linux and I need to check if Postgres >> needs to be restarted when a configuration parameter that requires a restart >> changes, e.g. when you change wal_level. Is there a way to detect a parameter >> which requires a restart was changed? >> >> Reading the log file after sending SIGHUP is not possible to automate. I know >> I can query pg_settings and then compare relevant parameters from the conf file, >> but that means I will need to check all parameters which I set and which require a >> restart. I only need to know if postgres requires a restart. > > I created an experimental background worker which records configuration parameter > changes after a SIGHUP is received: > > https://github.com/ibarwick/config_log > > It was originally intended to log configuration changes, however it > might be possible to create a query based off the recorded values to detect > changes which require a restart. > > No guarantee of fitness for purpose. Requires 9.3 or later. > > > Regards > > Ian Barwick > > -- > Ian Barwick http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin -- 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.
Re: How to detect if Postgres needs a restart when configuration changes
From
Peter Eisentraut
Date:
On Wed, 2014-10-01 at 01:51 +0200, Strahinja Kustudić wrote: > I am automating Postgres deployment on Linux and I need to check if > Postgres needs to be restarted when a configuration parameter that > requires a restart changes, e.g. when you change wal_level. Is there a > way to detect a parameter which requires a restart was changed? As you can gather, there isn't really a straightforward way. But it would be a great feature addition.
Re: How to detect if Postgres needs a restart when configuration changes
From
Peter Eisentraut
Date:
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.
Re: How to detect if Postgres needs a restart when configuration changes
From
Strahinja Kustudić
Date:
On Wed, Oct 1, 2014 at 6:56 AM, Peter Eisentraut <peter_e@gmx.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.
On Wed, Oct 1, 2014 at 2:48 PM, Strahinja Kustudić <strahinjak@nordeus.com> wrote:
On Wed, Oct 1, 2014 at 6:56 AM, Peter Eisentraut <peter_e@gmx.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
On 1 October 2014 16:18, Strahinja Kustudić <strahinjak@nordeus.com> wrote: > On Wed, Oct 1, 2014 at 6:56 AM, Peter Eisentraut <peter_e@gmx.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. 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. -- Stuart Bishop <stuart@stuartbishop.net> http://www.stuartbishop.net/
Re: How to detect if Postgres needs a restart when configuration changes
From
Strahinja Kustudić
Date:
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 }}"
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
- 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.
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 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 postgresignore_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.