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
, setting
, CASE
WHEN unit IN ('kB', 'MB')
AND setting <> '-1'
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting) * pg_catalog.pg_size_bytes(format('1%1$s', unit)))
WHEN unit = '8kB'
AND setting <> '-1'
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting) * pg_catalog.pg_size_bytes(unit))
WHEN unit = 'B'
AND setting <> '-1'
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting))
WHEN unit IN ('ms', 's', 'min')
AND setting <> '-1'
THEN format('%1$s %2$s', setting, unit)
WHEN setting = '-1'
THEN setting
ELSE setting
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
-- When autovacuum_work_mem is greater than -1 but less than the min_val of maintenance_work_mem which is 1024 kB,
-- PostgreSQL sets autovacuum_work_mem to 1024 kB
, CASE
WHEN pf.name = 'autovacuum_work_mem'
AND pf.setting <> '-1'
AND (
pg_catalog.pg_size_bytes(pf.setting)
*
CASE
WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
THEN 1
ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
END
) < pg_catalog.pg_size_bytes('1024 kB')
THEN '1024 kB'
WHEN cs.unit IN ('kB', 'MB')
AND pf.setting <> '-1'
THEN pg_catalog.pg_size_pretty(
pg_catalog.pg_size_bytes(pf.setting)
*
CASE
WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
THEN 1
ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
END
)
WHEN unit = '8kB'
AND pf.setting <> '-1'
THEN pg_catalog.pg_size_pretty(
pg_catalog.pg_size_bytes(pf.setting)
*
CASE
WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
THEN 1
ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
END
)
WHEN cs.unit = 'B'
AND pf.setting <> '-1'
THEN pg_catalog.pg_size_pretty(
pg_catalog.pg_size_bytes(pf.setting)
*
CASE
WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
THEN 1
ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
END
)
WHEN cs.unit = 'ms'
AND pf.setting <> '-1'
THEN format(
'%1$s %2$s'
, CAST(
floor(
EXTRACT(
EPOCH FROM CAST(
format(
'%1$s%2$s'
, pf.setting
, CASE
WHEN pf.setting ~ '\d+\s*([a-zA-Z]+)'
THEN ''
ELSE cs.unit
END
) AS interval
)
) * 1000
) AS text
)
, cs.unit
)
WHEN cs.unit = 's'
AND pf.setting <> '-1'
THEN format(
'%1$s %2$s'
, CAST(
floor(
EXTRACT(
EPOCH FROM CAST(
format(
'%1$s%2$s'
, pf.setting
, CASE
WHEN pf.setting ~ '\d+\s*([a-zA-Z]+)'
THEN ''
ELSE cs.unit
END
) AS interval
)
)
) AS text
)
, cs.unit
)
WHEN cs.unit = 'min'
AND pf.setting <> '-1'
THEN format(
'%1$s %2$s'
, CAST(
floor(
EXTRACT(
EPOCH FROM CAST(
format(
'%1$s%2$s'
, pf.setting
, CASE
WHEN pf.setting ~ '\d+\s*([a-zA-Z]+)'
THEN ''
ELSE cs.unit
END
) AS interval
)
) / 60
) AS text
)
, cs.unit
)
WHEN pf.setting = '-1'
THEN pf.setting
ELSE pf.setting
END AS file_setting
, pf.sourcefile
, pf.sourceline
, pf.error
FROM current_settings AS cs
INNER 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 = 'sighup'
THEN TRUE
ELSE FALSE
END AS pending_reload
, CASE
WHEN cs.current_setting <> fs.file_setting
AND cs.context = 'postmaster'
THEN TRUE
ELSE FALSE
END AS pending_restart
FROM current_settings AS cs
INNER 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, pending_restart 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 | false
autovacuum_work_mem | 10 MB | 1024 kB | sighup | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf | 20 | <null> | true | false
max_wal_size | 1024 MB | 10 GB | sighup | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf | 26 | <null> | true | false
archive_mode | off | on | postmaster | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf | 27 | <null> | false | true
shared_buffers | 160 MB | 1024 MB | postmaster | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf | 25 | <null> | false | true
(5 rows)
Time: 8.198 ms
To avoid surprises, this can also be implemented into a configuration management system.
Top comments (1)
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