When it comes to highly available database servers and configuration, a very important aspect is whether or not a changed setting requires a database restart before taking effect. While it is true that many of these are important enough and they should be set correctly before starting the server, our requirements evolve sometimes.
If or when this happens, there is no alternative but to restart the PostgreSQL service. There are, of course, steps we can take to avoid this fate. Perhaps, an existing server didn't need the WAL output to be compatible with hot standby servers. Maybe, we need to move the logfile, enable WAL archival, or increase the amount of connections.
These are all scenarios that require us to restart PostgreSQL. We can avoid this by identifying these settings early and paying special attention to them.
PostgreSQL has a lot of useful views for DBAs to get information about the database and its current state. For this section, we will concentrate on the pg_settings view, which supplies a wealth of data regarding the current server settings, defaults, and usage context. We recommend that you peruse the PostgreSQL documentation for this view.
Follow these steps to learn more about PostgreSQL settings:
- Execute the following query to obtain a list of settings that require a server restart and their current value:
SELECT name, setting FROM pg_settings WHERE context = 'postmaster';
- Execute this query for a list of only those settings that are not changed from the default and require restart:
SELECT name, setting, boot_val FROM pg_settings WHERE context = 'postmaster' AND boot_val = setting;
- Execute the following query for a list of all settings and a translation of how the setting is managed:
SELECT name, CASE context WHEN 'postmaster' THEN 'REQUIRES RESTART' WHEN 'sighup' THEN 'Reload Config' WHEN 'backend' THEN 'Reload Config' WHEN 'superuser' THEN 'Reload Config / Superuser' WHEN 'user' THEN 'Reload Config / User SET' END AS when_changed FROM pg_settings WHERE context != 'internal' ORDER BY when_changed;
The first query, and the simplest one, merely identifies the name and value for each setting that can only be modified by restarting PostgreSQL. In relation to all the available settings, this list is relatively short. However, there are a few notable settings that could affect us.
We already mentioned wal_level, shared_buffers, max_connections, and max_wal_senders in another recipe. However, this list also includes parameters related to SSL and WAL archival. We will eventually discuss WAL archival separately, so that leaves SSL. When setting up a secure PostgreSQL server that encrypts connection traffic, we require a host SSL certificate. If this certificate is ever compromised, we need to regenerate it. Unfortunately, we can't simply tell PostgreSQL to re-read the existing certificate; if we overwrite it, the entire database must be restarted.
The second query only shows the settings that we have not already changed but would require server restart. This list is potentially more interesting and concise, as we are presumably seeking further parameters to modify. Of course, the opposite can also be argued; we have only modified the settings we care about.
The final query is a bit more complicated as it uses a CASE statement, yet it also simplifies the contents of the view. First, consider the WHERE clause, which purges internal settings. We don't care about these specifically because they can only be set when compiling PostgreSQL itself. While such an action may be necessary to apply an emergency patch from the PostgreSQL developers, we cannot modify several of these parameters without rebuilding the entire contents of every affected database. These settings are for experts only, and these experts rarely even consider changing them.
Within SELECT, we fetch the setting name as well as how it is modified. Note that all settings that require a server reload to take effect are found in postgresql.conf. Subsequent changes applied at the session level can also be overridden using SET syntax, so we included that as well.
Of course, the pg_settings view can provide more than just an insight into the parameters that require a server restart.
A common request on the PostgreSQL mailing lists is for users to provide a list of settings they've changed. This helps everyone diagnose where a problem could originate or give us an idea of a database's usage pattern. Now that we know about this view, we can easily provide that data with the following query:
SELECT name, setting FROM pg_settings WHERE boot_val IS DISTINCT FROM setting;
The IS DISTINCT FROM clause isn't as well known as it should be. It can be easy to forget that != or <> evaluates to NULL when either side of the equation is NULL. Thus, if the default boot_val value is NULL, we would fail to obtain the entire list of modified settings.
The IS DISTINCT FROM clause considers NULL as a distinct value instead of an unknown one, permitting direct comparisons.
- The
pg_settingsview has a lot more information than what we have presented here. Check the documentation at http://www.postgresql.org/docs/9.3/static/view-pg-settings.html for more details.