Show parameters pending restart and configuration file errors

Command

-- Parameters changed but not effective until a restart
SELECT name, setting, unit, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE pending_restart;

-- Lines in configuration files that were not applied or have errors
SELECT sourcefile, sourceline, seqno, name, setting, applied, error
FROM pg_file_settings
WHERE NOT applied OR error IS NOT NULL
ORDER BY sourcefile, sourceline;

-- Where does a parameter's effective value come from?
SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 'work_mem';

What it does

Shows parameters changed in the files but waiting for a restart, configuration file lines that were not applied or have errors, and which file/line a parameter's effective value comes from.

When to use it

To verify after an ALTER SYSTEM or postgresql.conf change that the reload had the expected effect, and to list pending changes before a planned restart.

Caution

pg_file_settings reads the files as they are now; if a parameter is defined in several places, only the last one has applied = true (postgresql.auto.conf is read last, so ALTER SYSTEM always wins). A line with applied = false and an empty error is shadowed by another definition; it is not an error. A parameter that needs a restart shows error 'setting could not be applied'. A faulty file may be applied partially on reload; after a change, run pg_reload_conf() and then check these queries and the server log. Do not edit postgresql.auto.conf by hand; use ALTER SYSTEM RESET.