Proving a change took effect

Almost every configuration incident in this part of the course has the same shape: somebody made a change, the change appeared to succeed, and it was not in effect. The reload was rejected atomically. The parameter needed a restart. An ALTER SYSTEM override was winning. A per-role setting was more specific. The sessions that mattered had connected before the change.

Each of those is invisible from the shell and obvious from one query.

The four states

A change can be in any of the first three states while failing the next one. The last state is the one nobody checks and the one the application experiences.

The verification sequence

# 1. Did the files parse, and is every line I wrote actually applied?
psql -U postgres -c \
  "SELECT sourcefile, sourceline, name, setting, applied, error
     FROM pg_file_settings
    WHERE error IS NOT NULL OR NOT applied"

# 2. What is the server running, and where did it come from?
psql -U postgres -c \
  "SELECT name, setting, unit, source, sourcefile, sourceline, pending_restart
     FROM pg_settings WHERE name = 'max_wal_size'"

# 3. Is anything written but not yet in effect, anywhere?
psql -U postgres -c \
  "SELECT name, setting, sourcefile FROM pg_settings WHERE pending_restart"

# 4. Is any more-specific level overriding it?
psql -U postgres -c \
  "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"

Step 1 is the one most often skipped and the one that catches the atomic-reload failure. An empty result means every line in every file parsed and won; any row is a line having no effect.

Verifying from where the application sits

The three server-side checks all run in your session, which connected just now, as a superuser, probably to the postgres database. The application’s sessions connected earlier, as a different role, to a different database, and may carry per-role or per-database overrides.

# Sample the value as the application's role actually sees it
PGPASSWORD="$APP_PASSWORD" psql -h "$PGHOST" -U app_user -d app_db -c \
  "SELECT name, setting, source FROM pg_settings
    WHERE name IN ('work_mem','statement_timeout','max_wal_size')"

The source column is what makes this worth doing. A value showing configuration file means the change reached this role and database. A value showing database or user means something more specific is overriding it, and your server-wide change is not what this application is running.

For a parameter that backends fix at connection time — the superuser-backend and backend contexts from earlier in this part — add one more check: whether the sessions you care about predate the change.

psql -U postgres -c \
  "SELECT count(*) FILTER (WHERE backend_start < now() - interval '10 minutes')
            AS sessions_older_than_the_change,
          count(*) AS total
     FROM pg_stat_activity WHERE backend_type = 'client backend'"

A change procedure that cannot pass on a failure

Put together, the procedure looks like this. Each step’s output is recorded, so the change record contains evidence rather than assertion.

1. Before. Record the current value, its source, and its context: SELECT name, setting, source, context FROM pg_settings WHERE name = ...
2. Decide from the context whether this needs a reload, a restart, or neither.
3. Change it, by editing the managed file or with ALTER SYSTEM.
4. Apply it with pg_reload_conf() or a restart, matching step 2.
5. Read the log for the parameter-changed lines, or for “no changes were applied”.
6. Check pg_file_settings for any row with an error or with applied = false.
7. Check pg_settings for the new value and for pending_restart.
8. Sample from an application session and confirm source is what you expect.
9. Record the rollback: the previous value from step 1, and whether reverting needs a reload or a restart.

Steps 5 to 8 are the ones that distinguish this from “I edited it and reloaded”. They take under a minute together.

Production discipline

1. Never verify a change by reading the file you edited. The file is the input; six distinct failure modes are invisible in it.
2. Read the log after every reload and look for the parameter-changed lines, or for “no changes were applied”.
3. Check pg_file_settings for errors and unapplied lines after every change. An empty result is the pass condition.
4. Sample the value from an application session, not your own. The source column reveals a more-specific override that a server-side check cannot see.
5. Record the previous value and the rollback method before changing anything. The context column already told you which method it is.
6. Treat pg_reload_conf() returning t as “the signal was sent”, never as “the configuration was accepted”.

Cross-course references

• Git, CI/CD & GitOps — Part CVI (Change management) and Part LXIV (Auditability) cover recording evidence rather than assertion in a change record, which is what this procedure produces.
• Observability for Production Sysadmins — Part LXXXV (CI validation) covers validating configuration before it reaches a server, which shortens this procedure by catching the parse errors earlier.
• Ansible for Production Sysadmins — Part XXV (Check and diff) covers previewing a configuration change, and Part XVI (Handlers) covers triggering the correct apply action.

Quiz