Cleanly modifying PostgreSQL configurations

PostgreSQL has numerous configurations. Making changes to these configurations could occasionally result in issues or even stop the server from starting.
Additionally, a config might have been modified but yet to be applied. This may easily result in a scenario in which we modify a config, reload or restart the server, and then encounter a problem unrelated to our modification.

But the good news is that PostgreSQL provided an infrastructure to check if a config has an error so that it may be fixed before we reload or restart the server.

Ways of adjusting PostgreSQL configs

• Editing the config files
• Using ALTER SYSTEM

Checking for errors in the config

The SQL query below can be used to check for configuration errors

 SELECT name, sourcefile, sourceline, setting, error FROM pg_catalog.pg_file_settings WHERE error IS NOT NULL;
 name |                    sourcefile                     | sourceline | setting |                error
------+---------------------------------------------------+------------+---------+--------------------------------------
 test | /usr/local/etc/postgresql/14/main/postgresql.conf |          2 | test    | unrecognized configuration parameter
(1 row)

Time: 2.354 ms

Let us try reloading the server. We get this info in the LOG:

2022-07-22 16:00:53.190 CEST [4317] LOG:  received SIGHUP, reloading configuration files
2022-07-22 16:00:53.190 CEST [4317] LOG:  unrecognized configuration parameter "test" in file "/usr/local/etc/postgresql/14/main/postgresql.conf" line 2
2022-07-22 16:00:53.191 CEST [4317] LOG:  configuration file "/usr/local/etc/postgresql/14/main/postgresql.conf" contains errors; no changes were applied

Let us try restarting the server. We got the below error and the server could not be started.

2022-07-22 14:03:45.281 GMT [4940] LOG:  unrecognized configuration parameter "test" in file "/usr/local/etc/postgresql/14/main/postgresql.conf" line 2
2022-07-22 14:03:45.282 GMT [4940] FATAL:  configuration file "/usr/local/etc/postgresql/14/main/postgresql.conf" contains errors
pg_ctl: could not start server
Examine the log output.

The issues can be quickly fixed because we can check the state of the configuration before reloading or restarting.

To avoid surprises, this can also be implemented into a configuration management system.

Checking for pending reload and pending restart

It is wise to confirm the current state of the server before making any configuration changes. This guarantees that we begin in a clean condition and helps prevent cascade problems.

Consider a scenario in which one of the DBAs on your team changed random page cost but the change is not yet effective. You make your modification and reload or restart the server. After a few days, your queries began to perform poorly. You were questioned about the adjustment you made and even requested to reverse it, but there was still no relief.
Investigating such issues can be tiresome and time-consuming; it is better to be safe than sorry.

We can easily check for pending reload and restart with this SQL query:

WITH current_settings AS (
    SELECT
        name,
        CASE
            WHEN unit IN ('kB', '8kB', 'MB') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting || unit))
            WHEN unit = 'B' THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting))
            -- extract seconds from config with units s and min
            WHEN unit IN ('s', 'min') THEN floor(extract(epoch from (setting || unit)::interval))::text
            -- extract milliseconds from config with unit ms
            WHEN unit = 'ms' THEN floor(extract(epoch from (setting || unit)::interval) * 1000)::text
            ELSE setting || ' ' || coalesce(unit, '')
        END AS current_setting,
        unit,
        context
    FROM pg_catalog.pg_settings
),
file_settings AS (
    SELECT
        row_number() OVER (PARTITION BY pf.name ORDER BY pf.seqno DESC) AS rn,
        pf.name,
        CASE
            WHEN cs.unit IN ('kB', '8kB', 'MB') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(case when not pf.setting ~ '^\-?\d*\.?\d+[a-zA-Z]+$' then pf.setting || cs.unit else pf.setting end))
            WHEN cs.unit = 'B' THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(case when not pf.setting ~ '^\-?\d*\.?\d+B$' and not pf.setting ~ '^\-?\d*\.?\d+[a-zA-Z]+$' then pf.setting || 'bytes' when pf.setting ~ '^\-?\d+B$' then replace(pf.setting, 'B', 'bytes') else pf.setting end))
            -- extract seconds from config with units s and min
            WHEN cs.unit IN ('s', 'min') THEN floor(extract(epoch from (case when not pf.setting ~ '^\-?\d*\.?\d+[a-zA-Z]+$' then pf.setting || cs.unit else pf.setting end)::interval))::text
            -- extract milliseconds from config with unit ms
            WHEN unit = 'ms' THEN floor(extract(epoch from (case when not pf.setting ~ '^\-?\d*\.?\d+[a-zA-Z]+$' then pf.setting || cs.unit else pf.setting end)::interval) * 1000)::text
            ELSE setting || ' ' || coalesce(unit, '')
        END AS file_setting,
        pf.sourcefile,
        pf.sourceline,
        pf.error
    FROM current_settings AS cs
    JOIN pg_catalog.pg_file_settings AS pf ON pf.name = cs.name
)
SELECT
    cs.name,
    cs.current_setting,
    fs.file_setting AS pending_setting,
    cs.context,
    fs.sourcefile,
    fs.sourceline,
    fs.error,
    CASE
        WHEN cs.current_setting != fs.file_setting AND cs.context != 'postmaster' THEN true::text
        ELSE 'N/A'
    END AS pending_reload,
    CASE
        WHEN cs.current_setting != fs.file_setting AND cs.context = 'postmaster' THEN true::text
        ELSE 'N/A'
    END AS pending_restart
FROM current_settings AS cs
JOIN file_settings AS fs ON cs.name = fs.name
WHERE fs.rn = 1
AND cs.current_setting != fs.file_setting
ORDER BY pending_reload DESC;

Sample output:

        name         | current_setting | pending_setting |  context   |                         sourcefile                         | sourceline | error  | pending_reload | pending_restart
---------------------+-----------------+-----------------+------------+------------------------------------------------------------+------------+--------+----------------+-----------------
 archive_command     | (disabled)      | /bin/true       | sighup     | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         28 | <null> | true           | N/A
 autovacuum_work_mem | 10 MB           | 1024 kB         | sighup     | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         20 | <null> | true           | N/A
 max_wal_size        | 1024 MB         | 10 GB           | sighup     | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         26 | <null> | true           | N/A
 archive_mode        | off             | on              | postmaster | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         27 | <null> | N/A            | true
 shared_buffers      | 160 MB          | 1024 MB         | postmaster | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         25 | <null> | N/A            | true
(5 rows)

Time: 8.198 ms

To avoid surprises, this can also be implemented into a configuration management system.

Comments

[Bolaji Wahab]

Please note the above query does not work when setting a config to a default value or resetting a config. The reason according to the documentation:
Setting a parameter to DEFAULT, or using the RESET variant, removes that configuration entry from the postgresql.auto.conf file