Updating PostgreSQL server config

If you are running a PostgreSQL (pg) server in production for a fair amount of time, chances are that you’d have had to change its configuration at least once by now. Overtime any database server needs regular tuning to make it operationally suitable to the evolving needs, particularly if it powers a successful/growing product.

In case of pg, the simplest way to update server configuration is to make changes in the config file, postgresql.conf and restart the server process. But depending on your setup, doing this without downtime could be non-trivial at best (if your setup is highly available) or impossible at worst (if there’s just one server running).

The good news is that in pg not all config parameter changes require a server restart. But how do we know whether or not the parameter we want to change requires a restart?

The pg_settings view

The builtin view pg_settings can give us this information. This view provides access to run-time config parameters of the server through SQL queries. If you are connected to the db using psql, try running select * from pg_settings limit 10; to quickly check the schema.

The column we’re interested in is context which can have a bunch of values. For example - a query such as,

SELECT
    name,
    context
FROM
    pg_settings
WHERE
    name IN ('min_wal_size', 'max_wal_size', 'wal_level');

would return the result,

     name     |  context
--------------+------------
 max_wal_size | sighup
 min_wal_size | sighup
 wal_level    | postmaster
(3 rows)

In the above output we can see 2 different values for context:

1. sighup: This means changes to these settings can be made in postgresql.conf without restarting the server. We can simply send a SIGHUP signal to the postmaster process and it will re-read postgresql.conf and apply the changes.
2. postmaster: These settings can only be applied when the server starts, so any change requires restarting the server.

Besides the above two, there are other types of contexts too. See docs — https://www.postgresql.org/docs/current/view-pg-settings.html.

In the above case, max_wal_size and min_wal_size can be modified by reloading the config, whereas to modify wal_level we will need to restart the server.

Sending SIGHUP using ‘pg_reload_conf’

One easy way to send SIGHUP to the postmaster from within a running psql session is by calling the admin function pg_reload_conf

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Note that the use of this function is restricted to superusers only, for obvious reasons.

Verifying that the new config is in effect

After reloading, you might want to confirm that the updated config has taken effect. To do that, you may either run the SHOWcommand as follows,

postgres=# show max_wal_size;
 max_wal_size
--------------
 1536MB
(1 row)

or query the same pg_settings view, which is more convenient for multiple config params.

SELECT
    name,
    setting,
    unit
FROM
    pg_settings
WHERE
    name IN ('min_wal_size', 'max_wal_size');

Making temporary config changes using ‘alter system’

It’s even possible to update a “reloadable” config parameter by setting it directly from the psql session (again, restricted to superuser).

postgres=# alter system set max_wal_size = 144;
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres=# SELECT name, setting, unit from pg_settings where name = 'max_wal_size';
     name     | setting | unit
--------------+---------+------
 max_wal_size | 144     | 16MB
(1 row)

This way, there’s no need to edit the config file thereby making it extremely useful for,

1. changing the config as quickly as possible during an ongoing incident or,
2. trying out various config values while experimenting on a local/staging server.

But in most other cases, it’s recommended to update the config file in order to avoid any confusion. A config change done using alter system instead of updating the config file is ephemeral, i.e. if the server restarts for whatever reason, the change would get unknowingly reverted.

Having a provision to update the config without changing the config file also means that the config file cannot be treated as the source of truth. So if you want to check the current value of a config parameter, the best way to do so is to just query the pg_settings view.

Config parameters with implicit units

In the previous example, notice that the max_wal_size value was set to 144. However, the entry in pg_settings view for this parameter shows 16MB in the “unit” column.

Config parameters that store memory or time values have implicit units. In such cases, the actual value is then obtained by multiplying the explicitly set value (144) with the unit (16MB). This can be confirmed using the SHOW command which implicitly does the calculation.

postgres=# SELECT name, setting, unit from pg_settings where name = 'max_wal_size';
     name     | setting | unit
--------------+---------+------
 max_wal_size | 144     | 16MB
(1 row)
postgres=# show max_wal_size;
 max_wal_size
--------------
 2304MB
(1 row)

The SHOW command correctly shows the value of max_wal_size as 144 * 16 = 2304MB .

So when inspecting any config parameter value from the pg_settings view, make sure to include the unit column as well to get the full picture.

In summary

If you find yourself in a situation that requires pg config changes, first find out whether any of the params require a restart of the postmaster. If all are “reloadable”, then the config change is fairly trivial. On the other hand, if any of the params have ‘postmaster’ as the context, then a server restart is required. Depending on the above and your setup, you will need to plan it accordingly (planned maintenance window, customer communication etc.)

alter system is quite handy for experimentation and quickly making changes to recover from production incidents (provided you know what you’re doing).

The server config file postgresql.conf shouldn’t be treated as a source of truth. Instead check the values by querying the pg_settings view. In any case, it’s always recommended to update the config file for permanent changes.

When inspecting config values, don’t forget to consider the implicit units.

These are just a few useful things about postgresql configuration. For more comprehensive info on the subject, the official documentation is your friend — https://www.postgresql.org/docs/current/runtime-config.html.

Thanks to

Narendra Pal

and Somya Maithani for reviewing the drafts.