Taking over a Postgres cluster someone else built is mostly archaeology. The postgresql.conf you inherit is never the file that shipped with the release. It has three years of incident-driven additions, half of them commented out, two of them contradicting each other, and at least one line a previous DBA added at 2 a.m. during an outage and never removed.

You do not fix that by pasting a tuning article into the file. You fix it by finding out what is actually different from the defaults, where each difference comes from, and which of them need a restart to change. This is the audit I run on a handover cluster before touching a single value. It is the same on PostgreSQL 16, 17 and 18.

Why the file on disk is not the whole story

A PostgreSQL 18 install exposes 399 settings, up from 348 in version 14 and 271 in version 10 — that count comes from Ilya Kosmodemiansky’s walk through the default postgresql.conf, published this week and worth reading alongside this audit. Almost none of those settings live in one file. The server reads postgresql.conf, any include or include_dir targets, and postgresql.auto.conf (which ALTER SYSTEM writes and which is read last, so it wins). On top of that, values can arrive from the command line, from ALTER DATABASE ... SET, from ALTER ROLE ... SET, or from the environment of the postmaster.

If you audit by reading the file, you will miss all of those. Query the running server instead.

Step 1 — Dump every setting that is not the default

pg_settings is the server’s own view of its runtime parameters, and its source column tells you where each current value came from. Filter out default and you have the real configuration of the cluster, however it was assembled.

psql -X -d appdb -c "
SELECT name, setting, unit, source, sourcefile, sourceline
FROM pg_settings
WHERE source <> 'default'
ORDER BY source, name;"

Two practical notes. First, sourcefile and sourceline are null unless you are a superuser or a member of pg_read_all_settings — run the audit as a role that has one of those, or you will see names and values but not where they came from. Second, when a value shows a file ending in postgresql.auto.conf, it was set with ALTER SYSTEM, not by a human editing postgresql.conf. That distinction matters when you go to change it later.

Step 2 — Split reload-safe settings from restart-required ones

The context column tells you what it costs to change a setting. Anything with context postmaster needs a full restart. sighup settings only need pg_reload_conf(). Grouping by context turns a 400-line config into a short list of decisions:

SELECT context, count(*) AS settings,
       string_agg(name, ', ' ORDER BY name) AS names
FROM pg_settings
WHERE context IN ('postmaster', 'sighup')
GROUP BY context
ORDER BY context;

-- anything edited but not yet applied?
SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart;

If nothing is waiting on a restart, that second query returns no rows — useful before you open a maintenance window, and useful afterwards to confirm the restart did what you thought. A row here on a production primary is also a red flag: someone edited the file and never applied it, so the running server is not the server the config file describes. That gap between written config and running config is exactly how parameter surprises turn into incidents, including ones as ordinary as an oversized prefetch setting on a standby.

Step 3 — Diff the old cluster against the new one

Before a major-version migration, dump the same query from both sides and compare the files. This is the cheapest way to catch settings that pg_upgrade will happily carry forward and that no longer mean what they used to.

for c in prod18 prod19; do
  psql -X -d appdb -c "\copy (SELECT name, setting, unit FROM pg_settings WHERE source <> 'default' ORDER BY name) TO '/tmp/settings_$c.csv' CSV"
done
diff -u /tmp/settings_prod18.csv /tmp/settings_prod19.csv

Every line starting with - is a setting the old cluster had and the new one does not; every + is a name that exists only on the target, usually a renamed or newly added parameter. Read the removals first: a parameter that silently disappeared is a knob your team thinks is working and is not. I treat this diff as step one of any pg_upgrade rehearsal, and I re-run it after the cutover, not just before.

Step 4 — Converge with ALTER SYSTEM, then reload

Once you know what is set, change it in a way that leaves a trail. ALTER SYSTEM writes to postgresql.auto.conf, keeps the original postgresql.conf untouched, and is trivially greppable and reversible:

-- change a reload-safe setting
ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

-- remove an override the previous owner left behind
ALTER SYSTEM RESET statement_timeout;

-- verify nothing is queued for the next restart
SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;

For postmaster-context parameters (memory sizing, WAL and connection limits), the reload does nothing and pending_restart stays true until the next restart. Do not batch those changes into a surprise restart during business hours — schedule them, and confirm afterwards that the restart cleared the pending flag.

Step 5 — Write the baseline down

An audit that lives only in a psql history is worth nothing in six months. Export the filtered list to a file in version control, with a one-line comment on every change explaining the incident or workload that justified it. That file is what the next DBA will read instead of guessing, and it is what makes the next audit a five-minute diff rather than an afternoon of forensics.

When the audit turns up something you cannot explain

The uncomfortable outcome is finding a setting with no known owner that is load-bearing for a workload nobody documented — a huge work_mem hiding a bad plan, a low autovacuum_max_workers propping up an overloaded box. Those you do not change during the audit. You note them, test the change on a copy, and only then touch production. If you are inheriting a cluster in Houston and would rather have a second set of eyes on the parameter list before you start changing it, we do Postgres configuration reviews and handover audits and will tell you which settings are actually doing work and which are just history.