Skip to content

PostgreSQL configuration is never collected: no pg_settings read, and no way to see what changed #2658

Closed

Description

@erikdarlingdata

Part of the PostgreSQL parity effort. pg_settings is never collected. The only references to it in the
codebase read one or two individual values in passing (PgPlanCaptureReadinessCollector checks whether
auto_explain is loaded, PgBlockingCollector reads a unit); nothing stores the server's configuration and
nothing serves it.

For SQL Server we answer this three ways — get_server_config, get_server_config_changes,
get_database_scoped_config. For PostgreSQL there is no answer at all, and "what is work_mem set to on
this server" is a first-hour question for anyone evaluating the tool.

What the source actually offers

Measured on the verification rig (17.11):

415 settings total, 28 non-default, 0 pending_restart

pg_settings carries more than the value, and the extra columns are the interesting part:

name, setting, unit, category, short_desc, extra_desc, context, vartype, source,
min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline, pending_restart

Three of those change what we can answer:

  • source separates a real server setting from session noise. On the rig, application_name = psql
    with source = client sits in the same view as auto_explain.log_min_duration = 50 from
    command line — a collector that stores them undifferentiated records the collector's own session as
    server configuration.
  • boot_val / reset_val give "changed from the default" without a hardcoded table of defaults, which
    is what makes a useful config read possible: 28 rows worth reading instead of 415.
  • pending_restart is a production trap nothing surfaces today: someone edited postgresql.conf and
    reloaded, the file says one thing and the running server another, and there is no symptom until the next
    restart silently changes behaviour. SQL Server has no equivalent of this column and PostgreSQL hands it
    to us for free.

context (postmaster / sighup / superuser / user) is the other half of that: it says whether a
change needs a restart, a reload, or nothing.

The work

  1. A pg_server_config collector over pg_settings, cluster-wide, no extension required, readable by
    any login — the same cheap tier as pg_wraparound_risk. Store name, setting, unit, source,
    boot_val, reset_val, context, vartype, pending_restart, sourcefile, sourceline.
  2. get_pg_server_config — non-default first, because 415 rows sorted alphabetically is not an answer.
    Say pending_restart loudly when any setting has it.
  3. get_pg_server_config_changes — differenced across snapshots, the same shape as the SQL Server read.
    This is the one nothing else in the stack can reconstruct after the fact.
  4. Viewer tab and web panel, per the parity pattern.

Two things to decide while building rather than after:

  • Filter or record source? Recording it and filtering at the read is better — a client-source row is
    evidence about the collector's own session, and dropping it at collection time makes that
    unrecoverable — but the read must then never show session-scoped rows as server configuration.
  • sourcefile is a path on the target's filesystem. Useful for "which include file set this", but it is
    also the kind of value that should never end up in a public issue or a shared export.

Activity

  1. erikdarlingdata commented on Aug 26, 2026

    @erikdarlingdata
    OwnerAuthor

    Shipped in #2660 (merged to dev).

    get_pg_server_config and get_pg_server_config_changes, a Configuration tab in the web dashboard, and a settings panel on the WPF viewer's Overview tab under the extension axis. V102 stores the snapshot hourly for 365 days.

    Both of the issue's open questions got answered by running it, and one of them the other way from how I wrote it.

    Filter or record source? Record, and filter at the read — as proposed. But defaultness must ALSO come from source, not from comparing setting to boot_val, which was the first implementation. The string comparison invents non-defaults on a server nobody configured:

    setting setting boot_val source
    data_directory_mode 0700 448 default
    archive_command (disabled) (empty) default
    commit_timestamp_buffers 32 0 default

    Octal against decimal, a display convention, and an auto-tuned value the server resolved at startup. boot_val is still stored and shown — seeing the default is useful — it just does not get to decide.

    sourcefile is a path on the target. Stored, and deliberately not surfaced in either read's projection.

    A setting appearing is not a change, which turned out to be the load-bearing guard: LAG returns NULL for the first snapshot of every setting, so without it the first collection reports several hundred changes nobody made, and again for every extension whose GUCs appear when its library loads. Verified on the rig — first snapshot 415 settings, and after a real ALTER SYSTEM the read reported exactly one change (work_mem 4096 → 8192) while shared_buffers correctly showed pending_restart with its value unchanged.

    Filed and fixed along the way: #2659, ten PostgreSQL MCP tools that were never registered with the host.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions