pg_settings — Query PostgreSQL Configuration Parameters

calendar Aug 26, 2025 / · 10 min read · postgresql database sql pg_file_settings pg_settings administration configuration tuning performance  ·
Share on: linkedin copy

pg_settings — Query PostgreSQL Configuration Parameters

The live configuration PostgreSQL is running with and the values written in postgresql.conf are not always the same. A parameter that requires a server restart has already been updated in the file but the old value is still in effect. A reload that produced a parse error left one directive silently inactive. A setting changed at the session level masks whatever the config file says. None of these discrepancies are visible in the config file alone, and none raise an obvious error in the application log. pg_settings shows the runtime state of every GUC parameter — what value the server is using right now, what file set it, and whether a restart is pending to apply a queued change. pg_file_settings shows what the configuration files say and, critically, whether each directive was successfully applied or blocked by an error. Querying both together is the complete picture any configuration investigation needs.

Purpose and Overview

PostgreSQL manages configuration through a set of Grand Unified Configuration (GUC) parameters, each controlling one aspect of server behavior — memory allocation, connection handling, replication settings, query planner behavior, autovacuum thresholds, and more. Every parameter has a compiled-in default and can be overridden through postgresql.conf, postgresql.auto.conf, ALTER SYSTEM, environment variables, ALTER ROLE, ALTER DATABASE, or a session-level SET command. The runtime value is the result of that entire precedence chain, and the chain is not always obvious from inspecting the config file alone.

pg_settings is the authoritative view of the runtime state. Each row represents one GUC parameter with its current effective setting, the unit of that value, the context that determines how a change can take effect, the source that explains where the current value came from, and the sourcefile and sourceline that pinpoint the specific configuration file line responsible. The pending_restart boolean flags parameters that have been changed in the configuration files but cannot take effect until the server restarts — a common source of confusion when a tuning change appears to have no effect after a reload.

pg_file_settings reads the configuration files directly, one row per directive found. Its applied column answers the question that pg_settings alone cannot: is this file directive actually reflected in what the server is running? When applied is false, something blocked the directive — either an error in the setting itself, or a later directive in the same or an included file that overrides it. The error column carries the parse or validation message when the directive failed outright. This is the first place to look after a reload that did not change behavior as expected.

Sample Code

The queries below expand the original snippet with the columns that carry the most operational value in production environments:

 1-- Runtime configuration: current values and their origin
 2SELECT
 3    name,
 4    setting,
 5    unit,
 6    category,
 7    context,
 8    source,
 9    sourcefile,
10    sourceline,
11    pending_restart
12FROM
13    pg_settings
14WHERE
15    source <> 'default'
16ORDER BY
17    category, name;

 1-- File configuration: what the files say and whether each directive is active
 2SELECT
 3    sourcefile,
 4    sourceline,
 5    name,
 6    setting,
 7    applied,
 8    error
 9FROM
10    pg_file_settings
11ORDER BY
12    sourcefile, sourceline;

Notes: Both views are readable by superusers. On PostgreSQL 14 and later, the pg_read_all_settings built-in role allows non-superuser monitoring accounts to read these views without a full superuser grant. pg_file_settings requires that configuration files be accessible to the server process; it is available on all supported PostgreSQL releases from 9.5 onward.

Code Breakdown

setting, unit, and vartype

The setting column is always a text string regardless of the parameter's underlying data type. Numeric values such as shared_buffers = 131072 or work_mem = 4096 appear as their normalized string equivalent. The unit column carries the implicit unit — kB for memory parameters, ms for time parameters — that the setting number should be multiplied by to get an absolute byte or millisecond count. The vartype column (bool, integer, real, string, enum) tells you how to interpret the setting text. For enum parameters, the companion enumvals column lists the complete set of valid string options.

context: The Change Mechanism

The context column determines what is required to make a change take effect. postmaster means the server must be fully restarted — parameters like shared_buffers, max_connections, and wal_level fall here, and are the most common reason pending_restart becomes true. sighup parameters take effect on the next reload, triggered by SELECT pg_reload_conf() or a SIGHUP signal to the postmaster — no downtime required. superuser and user parameters can be changed at session or transaction level: superuser requires a superuser to issue the SET command, while user permits any authorized role to set it for their own session.

source and sourcefile

The source column traces the current value back to its origin in the precedence chain. Common values include default (the compiled-in default), configuration file (a directive in postgresql.conf or an included file), database (set via ALTER DATABASE), user (set via ALTER ROLE), and session (changed in the current session via SET). The sourcefile and sourceline columns narrow the origin down to the exact file path and line number when the source is a configuration file, making it possible to jump directly to the responsible directive when investigating an unexpected value. When source is session, sourcefile is null — the value came from an in-session command, not a file.

pg_file_settings: applied and error

In pg_file_settings, the applied column is true when the file directive is currently reflected in pg_settings, and false when it is not. A directive can fail to apply for two reasons: it contains an error that prevented parsing or validation, in which case the error column carries a message describing the problem; or a later directive in the same configuration hierarchy sets the same parameter to a different value, in which case the earlier directive is silently shadowed. Distinguishing these two cases requires checking both columns together — applied = false AND error IS NULL means a later entry overrides this one; applied = false AND error IS NOT NULL means the directive itself is invalid and was skipped on reload.

Key Configuration Contexts

postmaster: Server Restart Required

Parameters in the postmaster context control fundamental server structures — shared memory allocation, worker process counts, WAL settings, and network binding. These cannot be changed without stopping and restarting the server because the underlying operating system resources they manage are allocated at startup. When a postmaster parameter is edited in postgresql.conf and the server is reloaded rather than restarted, the new value appears in pg_file_settings.setting but pg_settings.pending_restart flips to true and the old value remains in pg_settings.setting. The server has correctly read the change but cannot act on it until the next restart.

sighup: Hot Reload with pg_reload_conf()

Most parameters that govern autovacuum thresholds, connection timeouts, log settings, and planner cost parameters carry sighup context. Calling SELECT pg_reload_conf() from a superuser session causes PostgreSQL to re-read its configuration files and apply all sighup-context changes without interrupting existing connections. The reload is the mechanism behind zero-downtime tuning adjustments. After a reload, pg_file_settings.applied is how you confirm the change actually landed — if a sighup parameter shows applied = false after a reload, the error column explains why it was skipped.

superuser and user: Session-Level Overrides

Parameters with superuser or user context can be overridden for an individual session or transaction, bypassing whatever postgresql.conf specifies. A DBA running a maintenance operation might SET work_mem = '256MB' for the duration of a sort-heavy query without touching the server-wide configuration. These session values appear in pg_settings.setting with source = 'session', while the file value remains visible in pg_file_settings.setting. A monitoring query that reads pg_settings without inspecting the source column can therefore report a session-level value and mistake it for the server-wide configuration — particularly relevant in environments where application frameworks routinely issue SET commands at connection time.

Practical Applications

Finding Settings That Need a Restart

After editing postgresql.conf and reloading, run:

1SELECT name, setting, context, sourcefile, sourceline
2FROM pg_settings
3WHERE pending_restart = true
4ORDER BY name;

Any row returned is a parameter whose new file value is waiting for a server restart. This query makes it immediately clear whether the tuning change is live or deferred, without needing to read and interpret the reload log output.

Diagnosing Configuration Drift

Configuration drift occurs when what the server is running no longer matches what postgresql.conf specifies — due to an unplanned ALTER SYSTEM command, a missed reload, or an environment variable override at the OS level. A join across the two views surfaces the gap:

 1SELECT
 2    f.sourcefile,
 3    f.sourceline,
 4    f.name,
 5    f.setting       AS file_value,
 6    s.setting       AS runtime_value,
 7    s.source,
 8    s.pending_restart,
 9    f.error
10FROM
11    pg_file_settings f
12    JOIN pg_settings s ON s.name = f.name
13WHERE
14    f.setting <> s.setting
15    OR s.pending_restart = true
16    OR f.error IS NOT NULL
17ORDER BY
18    f.sourcefile, f.sourceline;

Rows returned from this join indicate places where the file and runtime disagree. The source column on each row shows why — a session override, a database-level default, or a higher-priority command-line option.

Validating Configuration Files Before a Reload

Before issuing SELECT pg_reload_conf() after editing postgresql.conf, scan pg_file_settings for errors:

1SELECT sourcefile, sourceline, name, setting, error
2FROM pg_file_settings
3WHERE error IS NOT NULL;

Any row returned means the directive will be skipped on reload. The reload will appear to succeed at the OS level, but that parameter will remain at its old value without a visible warning in the server log by default. Catching validation errors before reloading is faster than diagnosing a failed tuning change after the fact.

Profiling Non-Default Tuning Parameters

The WHERE source <> 'default' predicate in the first sample query limits results to parameters that have been deliberately changed from their compiled-in defaults. On a freshly initialized cluster this list is short. On a tuned production cluster it can include dozens of parameters set across postgresql.conf, ALTER ROLE, and ALTER DATABASE. This filtered list is the right starting point for a configuration audit or for documenting the server's tuning baseline before a major-version upgrade.

Version Compatibility

The pg_settings view has been available across PostgreSQL's entire documented release history. Its core columns — name, setting, unit, context, vartype, min_val, max_val, boot_val, reset_val, source, sourcefile, and sourceline — have been present since PostgreSQL 8.0, making the full column set available on every release in active use today.

The pg_file_settings view and the pending_restart column in pg_settings were both introduced in PostgreSQL 9.5, released as part of the same configuration-transparency feature set. Before 9.5, there was no in-database way to inspect whether a configuration file directive had been successfully applied or whether a file change was waiting for a restart. DBAs relied on parsing the server log after a reload and manually comparing the file text against pg_settings output. On PostgreSQL 9.4 and earlier, neither pg_file_settings nor pending_restart is available.

From PostgreSQL 14 onward, the pg_read_all_settings predefined role grants read access to both views without a full superuser grant, which is appropriate for read-only monitoring and alerting accounts. The query behavior and column semantics are otherwise consistent across PostgreSQL 9.5 through 17.

Best Practices

  • Query both views after every configuration change — pg_settings confirms the current runtime state; pg_file_settings confirms the file parsed correctly and was applied.
  • Check pending_restart after any reload — a non-empty list means the tuning change is not yet live and downtime must be scheduled before it takes effect.
  • Filter by source <> 'default' for configuration audits — this scopes the view to deliberate changes rather than the full 300+ parameter catalog.
  • Treat applied = false AND error IS NOT NULL as a blocking error — the reload appeared to succeed but that specific parameter was skipped; the intended change did not take effect.
  • Validate pg_file_settings before reloading in automation — a pre-reload error check prevents deploying a broken configuration to a production server.
  • Grant pg_read_all_settings to monitoring roles on PG 14+ — avoids superuser for routine configuration observation without exposing write capabilities.

References

Posts in this series