Postgres pg_settings: How to Audit Configuration Without Surprises

Most Postgres teams have a postgresql.conf file checked into a repo somewhere, a couple of ALTER SYSTEM commands run during an incident two years ago that nobody remembers, environment-specific overrides applied by the cloud provider, and a per-database SET that has been quietly active since a migration in 2023. The pg_settings view exposes all of this. Reading it is the closest thing to a configuration audit a Postgres team can do without leaving the database.

What pg_settings contains

One row per parameter, with the columns that matter being name (the parameter), setting (the current value), unit (the unit if any), category (the documentation grouping), boot_val (the compiled-in default), reset_val (the value RESET would restore), source (where the current value came from), sourcefile and sourceline (which file and line if applicable), pending_restart (whether the value has been changed but requires a restart to take effect), and context (when the parameter can be changed).

The source column is the most informative. Possible values include default (compiled-in), configuration file (postgresql.conf), command line (postgres process arguments), environment variable, global (ALTER SYSTEM via postgresql.auto.conf), database (ALTER DATABASE SET), user (ALTER ROLE SET), session (SET command in current session), client (parameter passed at connect time), and override (set by Postgres internals).

The everyday audit query

The query that catches most surprises:

SELECT name, setting, unit, source, sourcefile, sourceline, pending_restart
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY source, name;

This returns every parameter that has been explicitly changed from its compiled default, along with where the change came from. On a freshly installed Postgres instance this returns a handful of rows. On a five-year-old production database it commonly returns 50-100 rows, and roughly half of them tend to surprise the team running the database.

The pending_restart trap

Some parameters require a server restart to take effect. ALTER SYSTEM commands write to postgresql.auto.conf immediately, but the runtime value does not change until the server restarts. The diagnostic:

SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;

If this query returns rows, the configuration on disk does not match the configuration in memory. We have seen this go wrong when a team ran ALTER SYSTEM SET shared_buffers, restarted the wrong instance in a replication pair, and then spent a week troubleshooting unrelated performance issues without realizing the configuration change had not actually applied to the primary.

The context column and what it tells you about change semantics

The context column tells you when a parameter can be changed. Values include internal (compile-time only), postmaster (requires server restart), sighup (changes apply on SIGHUP / config reload), superuser-backend (changeable per session by superuser only), backend (changeable per session at connect time), superuser (changeable per session by superuser via SET), and user (changeable per session by any user).

The audit pattern that surfaces hidden flexibility:

SELECT category, name, setting, context
FROM pg_settings
WHERE context IN ('user', 'superuser')
  AND source = 'default'
ORDER BY category, name;

This shows parameters that no one has touched but that any session could set with SET LOCAL. The candidates for per-query tuning live here.

The configuration source hierarchy

When multiple sources set the same parameter, the precedence is roughly: session (SET) overrides user (ALTER ROLE SET) overrides database (ALTER DATABASE SET) overrides global (ALTER SYSTEM / postgresql.conf) overrides default. The exact ordering depends on the parameter and context, but the practical implication is that pg_settings shows the effective value, not the layered values that might be in play.

To see the layered overrides, query pg_db_role_setting:

SELECT
  COALESCE(d.datname, 'ALL DATABASES') AS database,
  COALESCE(r.rolname, 'ALL ROLES') AS role,
  s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole;

This is the only way to surface ALTER DATABASE SET and ALTER ROLE SET overrides. They do not appear in any configuration file and are easy to lose track of.

The configuration drift workflow

A repeatable audit takes a snapshot of pg_settings, stores it somewhere version-controlled, and diffs the next snapshot against it. The query that produces an auditable snapshot:

SELECT name, setting, source
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY name;

Saved as a CSV per environment per quarter, this becomes the closest thing to a configuration history a Postgres database has. We have used this several times to answer questions like "when did work_mem get raised on the analytics replica" that no other source could answer.

What pg_settings does not show

It does not show what the configuration should be. There is no recommended value and no validation that the configured value is appropriate. It does not show parameters set by foreign data wrappers or extensions installed in specific schemas. It does not show OS-level parameters (kernel.shmmax, ulimit, page cache size) that materially affect Postgres performance. It does not show parameters set by the cloud provider that may not be ALTER-SYSTEM-changeable.

Across DocuMint, CronPing, FlagBit, and WebhookVault we currently run on SQLite, whose configuration surface is dramatically smaller (a few PRAGMA settings, set at connection time). When we migrate to Postgres, the pg_settings audit will be part of the deployment runbook from day one. The lesson from teams we have observed is that configuration drift accumulates silently and surfaces during incidents, which is exactly when nobody has time to audit it. The cheap insurance is to audit during calm times.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) keep the lights on.