Runbook: Change a Configuration Parameter Safely

1 · Prerequisites

Confirm every item is in place before any state change.

• The parameter name, the value you intend to set, and a written reason for the change that somebody other than you could evaluate
• Shell access to the database host as a user who can read the configuration files and the server log
• A database superuser connection, or a role with the privilege to run ALTER SYSTEM if that is your chosen mechanism
• Knowledge of which configuration mechanism this estate uses as its source of truth: a managed postgresql.conf, an include directory, ALTER SYSTEM, or a configuration-management tool that owns the file
• A change window if the parameter requires a restart, and confirmation that the application can survive one
• The current value of the parameter, recorded before you touch anything

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Read the current value and where it came from. SELECT name, setting, unit, source, sourcefile, sourceline, boot_val, reset_val, pending_restart FROM pg_settings WHERE name = :param; The source column tells you which mechanism is currently winning, and that is the mechanism you must change.
• · Read the context, which decides whether a restart is needed. SELECT context FROM pg_settings WHERE name = :param; A context of postmaster requires a restart. sighup needs only a reload. superuser and user can be set per session or per role without touching the server at all.
• · Confirm nothing is already pending a restart. SELECT name, setting FROM pg_settings WHERE pending_restart; A non-empty result means somebody has already changed a postmaster parameter and the running server is not using it. Find out what and why before adding another.
• · Confirm who owns the configuration file. If a configuration-management tool writes postgresql.conf, an ALTER SYSTEM change will be silently outranked or silently overwritten on the next run. Establish this before choosing a mechanism, not afterwards.
• · Check for an existing conflicting entry. grep -rn "^[[:space:]]*<param>" /etc/postgresql/18/main/ /var/lib/postgresql/18/main/postgresql.auto.conf The last assignment read wins, and a parameter set twice in one file is a common source of "I changed it and nothing happened".
• · Confirm the log destination and that you can read it. You will be reading the server log to confirm the reload succeeded, and finding the log for the first time during a change is a poor use of a window.
• · Record the rollback value now. The reset_val and the current setting both go in the change note before the first edit. A rollback that has to reconstruct the old value from memory is not a rollback.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1State the class of change out loud: session, role, reload, or restart. These have entirely different blast radii and entirely different rollbacks. A change that could have been a SET LOCAL in one query should not become a cluster-wide restart.
2. 2If the change only needs to affect one workload, set it on the role rather than the cluster. ALTER ROLE reporting SET work_mem = '64MB'; This takes effect on that role's next connection, affects nobody else, and is reverted with ALTER ROLE reporting RESET work_mem;.
3. 3If the change is cluster-wide, apply it through the mechanism that owns the file. With ALTER SYSTEM: ALTER SYSTEM SET <param> = '<value>'; writes to postgresql.auto.conf, which is read last and therefore wins over postgresql.conf. With configuration management: edit the source template and let the tool converge, and do not also run ALTER SYSTEM.
4. 4Check the file was written as you expect, before reloading. cat /var/lib/postgresql/18/main/postgresql.auto.conf or the equivalent path. A quoted value that should not be quoted, or a unit you did not intend, is cheaper to find here than after a failed reload.
5. 5Reload, and read the result rather than assuming it. SELECT pg_reload_conf(); returns t when the signal was sent, not when the configuration was accepted. The server log is where acceptance or rejection is recorded.
6. 6Read the log for the reload. grep -E "received SIGHUP|configuration file|could not|parameter" /var/log/postgresql/postgresql-18-main.log | tail -20. A syntax error produces a message and the old configuration is kept, which is a good outcome badly disguised as a successful reload.
7. 7Confirm the new value on a live backend, not on the file. SELECT name, setting, source, sourcefile FROM pg_settings WHERE name = :param; from a new connection. Existing sessions may retain the old value for user-context parameters, so a check from your existing session can report either answer and mean nothing.
8. 8**If pending_restart is now true, stop and decide deliberately.** The parameter is set in the file and not in the running server. Either schedule the restart or revert the file, but do not leave the cluster in a state where the configuration and the behaviour disagree.
9. 9For a restart, drain and stop cleanly. Use pg_ctl -D <datadir> -m fast restart or the service manager's restart, never -m immediate. A fast shutdown disconnects clients and rolls back their open transactions; an immediate shutdown skips the shutdown checkpoint and forces crash recovery on start.
10. 10After a restart, confirm the cluster came back and the value took effect. pg_isready, then SELECT pg_postmaster_start_time(), name, setting FROM pg_settings WHERE name = :param;. A restart that appears to have worked because the command returned is not a restart that has been verified.
11. 11Observe the effect the change was made for. A configuration change is a hypothesis. Measure the thing it was supposed to improve — latency, checkpoint frequency, connection count, whatever it was — and write the measurement next to the change.
12. 12Record the change: parameter, old value, new value, mechanism, reload or restart, and the measurement. The next person to investigate this cluster will find the parameter and want to know why. This note is the only thing that will tell them.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓A new connection reports the intended value in pg_settings.setting, with source and sourcefile naming the mechanism you actually used.
• ✓SELECT count(*) FROM pg_settings WHERE pending_restart; returns zero, or the outstanding restart is scheduled with an owner and a window.
• ✓The server log contains the reload line and contains no configuration error between the reload and the present.
• ✓For a restart, pg_postmaster_start_time() is after the change and the cluster accepts connections from the application, not only from the database host.
• ✓The metric the change was intended to move has been measured after the change and the result recorded, whether or not it moved in the expected direction.
• ✓No other parameter changed. SELECT name, setting FROM pg_settings WHERE source NOT IN ('default', 'override') ORDER BY name; compared against the same query taken before the change.
• ✓The change note contains the old value, so a rollback does not depend on anybody remembering it.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶For an ALTER SYSTEM change, ALTER SYSTEM RESET <param>; followed by SELECT pg_reload_conf(); removes the line from postgresql.auto.conf and returns the parameter to whatever the next-lowest source says. This is preferable to setting the old value explicitly, because it restores the previous structure and not just the previous number.
• ↶For a role-scoped change, ALTER ROLE <role> RESET <param>;. It takes effect on that role's next connection; existing sessions keep the value they started with.
• ↶For a file edited by configuration management, revert the change in the source of truth and let the tool converge. Editing the file directly to roll back leaves the tool ready to re-apply the change at its next run.
• ↶If the parameter required a restart, rolling it back requires another restart. Decide before the first restart whether you are willing to perform a second one, because a postmaster parameter cannot be undone with a reload.
• ↶If the reload was rejected for a syntax error, the running configuration is unchanged and nothing needs rolling back — but the file does. Fix or remove the offending line before the next reload, which may otherwise be somebody else's and may be a restart.
• ↶If the cluster fails to start after a restart, read the log first. postgresql.auto.conf can be edited directly with the server down, and a single bad line can be removed by hand; this is the one situation in which editing that file by hand is correct.
• ↶Record the rollback in the same note as the change, with the reason. A parameter that was set and reverted is information the next person needs as much as one that was set and kept.

6 · Escalation

When the runbook isn't enough, contact:

• · The cluster does not start after a restart and the log names a parameter you did not set: escalate to whoever owns configuration management, because the file contains changes that were not part of this work.
• · The value reported by pg_settings does not match any file you can find: escalate rather than guessing. A value coming from a command-line argument or an environment the service manager supplies will not appear in any configuration file, and hunting it under time pressure is how the wrong file gets edited.
• · pending_restart was already true when you started, for a parameter nobody can account for: escalate before restarting. The restart will apply that change too, and you will own the result.
• · The change was intended to relieve a production incident and has not: escalate to the incident owner and stop tuning. A configuration change that does not move the symptom is evidence about the cause and should be treated as such, not followed by another guess.
• · The parameter affects durability or replication — fsync, synchronous_commit, wal_level, full_page_writes, synchronous_standby_names — and the change was proposed to make a problem go away: escalate to the data owner. These parameters trade a guarantee for a symptom, and that trade is not a database-team decision to make alone.
• · Configuration management reverts the change on its next run and the estate has no agreed mechanism: escalate to the platform owner. Two systems writing the same file will keep undoing each other until somebody decides which one wins.

References

1. PostgreSQL 18 documentation, Setting Parameters
2. PostgreSQL 18 documentation, ALTER SYSTEM
3. PostgreSQL 18 documentation, pg_settings
4. PostgreSQL 18 documentation, Server Shutdown