PostgreSQL Configuration – managing scary settings

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:

[postgres@node1 ~]$ psql -h localhost
psql (9.5.1)
Type "help" for help.

Execute this query for a list of only those settings that are not changed from the
default and require restart:

Execute the following query for a list of all settings and a translation of how the
setting is managed:

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.

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.