skip to content

In PostgreSQL, after changing a parameter in postgresql.conf, how do you tell whether a reload applies it or a restart is needed?

level: middleimportance: must knowfreq 74%

answer

  1. every parameter has a fixed class
  2. pg_settings.context column
  3. postmaster means restart
  4. reload, then check pending_restart

basics

~10 s

Check pg_settings.context: postmaster parameters need a restart, while sighup and session-level ones apply on a reload. After pg_reload_conf(), pending_restart = true in pg_settings flags any value still waiting for a restart.

solid answer

~40 s

Every parameter has a fixed `context` in `pg_settings`. `postmaster` ones, such as `max_connections` or `shared_buffers`, are read only at server start, so they need a restart. `sighup` ones apply on a reload (`SELECT pg_reload_conf()` or `pg_ctl reload`), and existing sessions adopt them once their current command finishes. `backend` and `superuser-backend` ones also reload, but only sessions started afterwards see them. `user` and `superuser` ones reach existing sessions too, unless a higher-priority source such as a session `SET` has set them. To confirm, reload and query `pg_settings`: `setting` is the value in force, and `pending_restart = true` marks a file change that still waits for a restart; the server log also says `parameter ... cannot be changed without restarting the server`.

code

sql · 11 lines
sql
-- which class is each parameter in?
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('max_connections', 'log_line_prefix', 'log_connections', 'work_mem');

SELECT pg_reload_conf();

-- anything edited in the files that still waits for a restart
SELECT name, setting, sourcefile, sourceline
FROM pg_settings
WHERE pending_restart;

go deeper

for a junior

Remember that some settings apply on a reload and some only at restart, and that pg_settings tells you which.

for a middle

Walk through the context classes, what a reload does to open sessions, and how pending_restart and the server log confirm the outcome.

for a senior

Show the operational routine: pre-check with pg_file_settings, reload freely, batch restart-only changes into a window, and verify pending_restart is empty afterwards.

for a principal

Discuss how restart-only parameters shape change management: sizing them with headroom up front so routine tuning never needs a maintenance window.

## Every parameter has a context Whether a change can be applied without a restart is not a judgement call: each PostgreSQL parameter has a fixed **context**, visible in `pg_settings.context`. In order of decreasing difficulty: | `context` | How a file change applies | Example | |---|---|---| | `internal` | Cannot be changed; reflects build or `initdb` choices | `block_size` | | `postmaster` | Only at server start: needs a **restart** | `max_connections`, `shared_buffers` | | `sighup` | **Reload**; existing sessions adopt it too | `log_line_prefix` | | `superuser-backend` | Reload, but only **new sessions**; a privileged user can also set it at connection start | `log_connections` | | `backend` | Reload, only new sessions; any user can set it at connection start | `ignore_system_indexes` (a developer option) | | `superuser` | Reload, or `SET` by a superuser or a role with `SET` privilege | `log_min_duration_statement` | | `user` | Reload, or `SET` by any user | `work_mem` | The same context explains errors from `SET`. `SET max_connections = 200` fails with `parameter "max_connections" cannot be changed without restarting the server`, and `SET log_line_prefix = ...` fails with `parameter "log_line_prefix" cannot be changed now`; both carry SQLSTATE `55P02` (`cant_change_runtime_param`). Only `user` and `superuser` parameters can be changed by `SET` inside a running session. ## What a reload does A **reload** makes the server re-read `postgresql.conf`, its includes and `postgresql.auto.conf` without dropping connections. You can trigger it three ways: - `SELECT pg_reload_conf();` from any SQL session; it is restricted to superusers by default, but `EXECUTE` on it can be granted. - `pg_ctl reload -D <datadir>` on the database host, as the operating-system user that owns the cluster. - Signalling the postmaster process directly. The postmaster forwards the request to every child process, and each session picks up the new values **after it finishes its current command**, so a long-running query keeps the old value until it ends. Three details trip people up: - `pg_reload_conf()` returning `true` only means the signal was sent. It does not mean the files parsed or that any value changed. - For `user` and `superuser` parameters, a file change reaches an open session only if nothing with higher priority set it there: a session `SET`, an `ALTER ROLE ... SET` default or connection options all keep winning. - For `backend` and `superuser-backend` parameters, open sessions never change; long-lived pooled connections keep the old value until they reconnect. ## Restart-only parameters and `pending_restart` When a reload meets a changed `postmaster`-context value, the server cannot apply it. It keeps the old value, writes `parameter "max_connections" cannot be changed without restarting the server` to the log, followed by a note that the file contains errors and unaffected changes were applied, and sets **`pending_restart = true`** on that row of `pg_settings`. Removing such a line from the file has the same effect: the default cannot come back without a restart either. `pending_restart` is only set by a reload that has read the change. Before reloading, use `pg_file_settings`, which parses the files as they are now: an edited restart-only entry shows `applied = false` with the error `setting could not be applied`. ## Confirming a change, step by step 1. Look up the context: `SELECT name, context FROM pg_settings WHERE name = '...';`. 2. Optionally pre-check the files with `pg_file_settings` and look for non-null `error` values. 3. Reload with `pg_reload_conf()`. 4. Check `setting`, `source` and `pending_restart` in `pg_settings`, and read the server log. 5. If anything is pending, plan a restart; otherwise open a new session if the context is `backend` or `superuser-backend`. ```sql SELECT name, setting, unit, context, pending_restart FROM pg_settings WHERE name IN ('max_connections', 'log_line_prefix', 'log_min_duration_statement'); SELECT pg_reload_conf(); SELECT name, setting FROM pg_settings WHERE pending_restart; ``` ## Practical habits - Batch restart-only changes and apply them in a planned window; a reload is cheap and safe to run at any time. - After every restart window, `pending_restart` should list nothing; a leftover row means the file and the running server disagree. - Do not restart "just to be sure": a restart drops every connection, while most parameters need only a reload.

  • What happens if you delete a postmaster-context line from postgresql.conf and reload?
    The value cannot revert to the default without a restart either. The reload logs `parameter ... cannot be changed without restarting the server`, marks the row `pending_restart = true`, and the old value stays in force until the next restart.
  • How can you see a restart-only edit before you reload?
    Query `pg_file_settings`, which parses the files as they are now. The edited entry shows `applied = false` with the error `setting could not be applied`. `pg_settings.pending_restart` turns true only once a reload has actually read the change.
  • Why might a session still use the old value of a sighup parameter right after the reload?
    Each process applies the reload only after it finishes the client command it is running, so a session in the middle of a long query keeps the old value until that statement ends.

  • Says every postgresql.conf change needs a full server restart.
  • Believes pg_reload_conf() applies postmaster-context settings such as max_connections.
  • Claims open sessions never see any reloaded value.
  • Thinks pending_restart turns true the moment the file is edited, before any reload.
  • Treats pg_reload_conf() returning true as proof the new value is live.