skip to content

On a PostgreSQL 18 ticketing site, ALTER SYSTEM plus pg_reload_conf() returned true, yet sessions still show the old value; how do you find out why?

level: seniorimportance: should knowfreq 52%

answer

  1. true only means signal sent
  2. compare the file with what is live
  3. applied and error columns
  4. a higher source outranks files
  5. which sessions, which node

basics

~10 s

pg_reload_conf() returning true only proves the signal was sent. Check pg_settings (source, sourcefile, pending_restart), then pg_file_settings (applied, error), then role/database overrides, the parameter's context, and whether you changed the right node.

solid answer

~40 s

`true` from `pg_reload_conf()` only means SIGHUP was sent. First query `pg_settings` for the parameter: `pending_restart = true` means it is `postmaster`-context and needs a restart, and `source` tells you who set the value you see: `command line`, `database`, `user` or `session` all outrank the files. Then query `pg_file_settings`: `applied = false` with an `error` shows a rejected entry, and a syntax error anywhere means no change applied at all. Check `\drds` or `pg_db_role_setting` for an `ALTER ROLE ... SET` on the application role. Remember that `backend`-context values only reach new sessions, so pooled connections keep the old one. Finally, `ALTER SYSTEM` writes no WAL: if it ran on a standby or the wrong node, the primary never saw it. The server log confirms most of these.

code

sql · 14 lines
sql
SELECT name, setting, source, sourcefile, sourceline, pending_restart
FROM pg_settings
WHERE name IN ('max_connections', 'log_min_duration_statement');

SELECT sourcefile, sourceline, seqno, name, setting, applied, error
FROM pg_file_settings
WHERE name IN ('max_connections', 'log_min_duration_statement')
   OR error IS NOT NULL
ORDER BY seqno;

SELECT r.rolname, d.datname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

go deeper

for a junior

Know that a reload request can succeed while the setting still does not change, and that pg_settings shows the live value.

for a middle

Explain pending_restart, the source column and the applied and error columns of pg_file_settings, and what each one tells you.

for a senior

Run the investigation in order: log, pg_settings, pg_file_settings, role and database defaults, session context, then the node, and pick the remedy the evidence supports.

for a principal

Turn the incident into prevention: one owner per parameter, alerting on non-empty pending_restart, and audits of stale role-level overrides across environments.

## The scenario On a ticketing site, an engineer runs `ALTER SYSTEM SET log_min_duration_statement = '250ms';` and `ALTER SYSTEM SET max_connections = 300;`, then `SELECT pg_reload_conf();`, which returns `true`. An hour later the checkout service's slow queries are still not logged and `SHOW max_connections` still says 100. The fix is a short, ordered investigation rather than a restart on reflex. ## What `true` proved `pg_reload_conf()` sends SIGHUP to the postmaster and returns `true` if sending worked. Parsing the files, applying values and propagating them to sessions all happen afterwards, and their outcome is reported in the **server log** and the views below, never in that return value. In the log, look for `received SIGHUP, reloading configuration files`, then either `parameter "log_min_duration_statement" changed to "250ms"` for each value that really changed, or the error lines described below. A reload with no `changed to` line for your parameter did not change it at the server level. ## Step 1: what is live, and who set it (`pg_settings`) `pg_settings` shows the value in force for your session and where it came from: - `pending_restart = true`: the files hold a new value for a `postmaster`-context parameter (here `max_connections`). The reload could not apply it; the log says `parameter "max_connections" cannot be changed without restarting the server`. - `source` and `sourcefile`/`sourceline`: if the value comes from somewhere that outranks the files, no reload will ever change it. | `source` | Set by | Beats the files? | |---|---|---| | `configuration file` | `postgresql.conf`, includes, `postgresql.auto.conf` | — | | `command line` | `postgres -c` in the service definition | Yes, until restart with new options | | `database` | `ALTER DATABASE ... SET` | Yes, for new sessions | | `user` / `database user` | `ALTER ROLE ... [IN DATABASE ...] SET` | Yes, for new sessions | | `client` | connection options such as `PGOPTIONS` | Yes, for that session | | `session` | `SET` in the session | Yes, for that session | Remember that `pg_settings` reflects **your** session. To see what the checkout role gets, connect as that role or look at its stored defaults. ## Step 2: did the files apply (`pg_file_settings`) `pg_file_settings` (superuser-only by default) parses the files as they are **now** and shows one row per entry with `sourcefile`, `sourceline`, `seqno`, `applied` and `error`: - `applied = false` and `error = 'setting could not be applied'` means the server rejected that entry: most often a restart-only parameter whose value changed, otherwise a value the parameter does not accept. - `applied = false` with a null `error` means a later entry for the same name overrides this one. - Rows with an `error` but no parameter, such as a syntax error, mean the server applied **nothing** from the files on that reload; the log says the file `contains errors; no changes were applied`. ## Step 3: role and database overrides Application roles often carry their own defaults. `\drds` in psql, or a query on `pg_db_role_setting` (`setdatabase`, `setrole`, `setconfig`), lists them. An `ALTER ROLE checkout_app SET log_min_duration_statement = -1` left over from an old incident would explain the missing slow-query log lines exactly, because a role default outranks every file. ## Step 4: which sessions, which node 1. **Context.** `sighup` and `user`-level values reach open sessions after their current command; `backend` and `superuser-backend` values (such as `log_connections`) reach only new sessions, so a pool of long-lived connections keeps the old value until it recycles them. 2. **Session state.** An application that runs `SET` at connect time keeps that value whatever the files say. 3. **The node.** `ALTER SYSTEM` writes the local `postgresql.auto.conf` and no WAL. If it ran on a standby, or on the old primary before a failover, the node serving traffic never got it. ## Order of work 1. Read the server log around the reload. 2. Query `pg_settings` for `setting`, `source`, `sourcefile` and `pending_restart`. 3. Query `pg_file_settings` for `applied = false`. 4. List role and database defaults. 5. Check the connection's node and age before deciding whether a restart, a role fix or a reconnect is the real remedy.

  • pg_file_settings shows applied = false with a null error for your line. What does that mean?
    Another entry for the same parameter appears later in the read order, for example in a `conf.d` file or `postgresql.auto.conf`, so this one is overridden. That is not an error, so `error` stays null; sort by `seqno` to find the entry that wins.
  • pg_settings shows source = 'command line' for the parameter. What do you do?
    The postmaster was started with `-c name=value`, which outranks `postgresql.conf` and `ALTER SYSTEM`. Change it where the service starts the server, such as the unit file or wrapper script, and restart; editing the files will never win.

  • Treats pg_reload_conf() returning true as proof the new value is in force.
  • Restarts the server first without checking whether a restart is even needed.
  • Believes a reload overrides values set with ALTER ROLE ... SET.
  • Reads pg_settings.setting as the value in the file rather than the value in force.
  • Assumes ALTER SYSTEM on any node changes the whole replicated cluster.