Postgres ALTER SYSTEM: Configuration Changes Without Editing postgresql.conf

Postgres configuration lives in postgresql.conf, traditionally edited with a text editor on the database host. ALTER SYSTEM is the SQL alternative, added in Postgres 9.4 and gradually adopted as the standard way to make configuration changes. It writes to a separate file, postgresql.auto.conf, which Postgres reads after the main config and which takes precedence for any setting it contains. The settings change is durable across restarts and can be made entirely over a SQL connection.

The mechanics are simple enough that the value is in understanding when to use it, when not to, and how to keep the configuration auditable when it lives in a file that nobody edits directly.

What ALTER SYSTEM actually does

The command takes a setting name and a value: ALTER SYSTEM SET work_mem = '64MB';. Postgres validates the value against the parameter's known range and type, writes the assignment to postgresql.auto.conf, and returns success. The new value does not take effect until you reload (pg_reload_conf() or SIGHUP) for parameters that allow runtime reload, or until restart for parameters that do not.

To remove an override and revert to whatever postgresql.conf or the compile-time default specifies, the command is ALTER SYSTEM RESET parameter_name; or ALTER SYSTEM RESET ALL; for everything. The RESET form removes the line from postgresql.auto.conf; it does not set the parameter back to a previous value beyond what the standard config provides.

The file format is the same as postgresql.conf, with one parameter per line and Postgres-managed comments at the top warning humans not to edit manually. The do-not-edit warning is real: if Postgres encounters a syntax error in postgresql.auto.conf during startup, it fails to start, and the recovery is awkward because the obvious instinct (edit the file) is exactly what the file warns against.

Reload vs restart

Whether a configuration change takes effect on reload or only on restart is a property of the parameter, not of ALTER SYSTEM. The pg_settings view exposes this in the context column: parameters with context 'user' or 'superuser' can be changed per-session; parameters with context 'sighup' take effect on reload; parameters with context 'postmaster' require a restart.

The query to find out which parameters in your override file require restart:

SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name IN (
  SELECT split_part(line, ' = ', 1)
  FROM pg_read_file('postgresql.auto.conf') AS line
)
AND context = 'postmaster';

The pending_restart column on pg_settings is the operational signal that matters: it goes true when you ALTER SYSTEM a parameter whose change requires a restart, and stays true until restart happens. Monitoring pending_restart catches the case where a configuration change has been made but not yet applied because nobody restarted.

The auditability problem

postgresql.conf in version control is a common pattern: changes are made via pull request, reviewed, and deployed via configuration management. ALTER SYSTEM bypasses this. The configuration change happens via SQL connection, the file changes on the database host outside the version-controlled config tree, and the change history is whatever is captured in the database's own audit log.

Three patterns handle this. The first is to forbid ALTER SYSTEM entirely and require all configuration changes go through postgresql.conf and configuration management. This works for organizations with mature configuration management and the discipline to use it.

The second is to allow ALTER SYSTEM for emergency-response situations and have a periodic reconciliation job that reads postgresql.auto.conf and either alerts on changes or commits them to the version-controlled config. This is the pragmatic middle ground for most operations.

The third is to lean into ALTER SYSTEM as the primary interface, capture the changes in a database-level audit log (pgAudit can do this), and treat postgresql.auto.conf as the source of truth. This works when the audit log is rigorously monitored and when the team is comfortable with database configuration living in the database rather than the file tree.

What ALTER SYSTEM cannot do

A small set of parameters cannot be set with ALTER SYSTEM. The parameters in this list are typically those that affect Postgres startup before the auto.conf file is read, or that are intended to be set only at compile time. The clearest case is shared_preload_libraries on some versions: ALTER SYSTEM accepts it but the change does not take full effect until restart, and certain extensions require the file-based config.

Per-database and per-role parameter overrides use different commands: ALTER DATABASE and ALTER ROLE rather than ALTER SYSTEM. ALTER SYSTEM affects the cluster default; the per-database and per-role overrides are layered on top.

Three patterns that catch teams out

First, the silent-override pattern: postgresql.conf contains a sensible production value, someone ALTER SYSTEMs an override, the override is forgotten, and the next person who reads postgresql.conf sees one value while the database uses another. The fix is monitoring: postgresql.auto.conf should be empty on most systems, and a configured value in it should be a deliberate documented choice.

Second, the reload-without-restart pattern: ALTER SYSTEM a parameter that requires restart, run pg_reload_conf(), observe that pg_settings still shows the old value, conclude that ALTER SYSTEM is broken. The fix is reading the context column on pg_settings before the change and planning for the restart window if it is required.

Third, the bad-value-locks-out-startup pattern: ALTER SYSTEM a parameter to a value that prevents Postgres from starting (memory parameters above available RAM, port conflicts), restart the database, find it will not start because of the auto.conf entry. The recovery is to edit postgresql.auto.conf with a text editor (despite its warning) or rename the file so Postgres starts without it. Test parameter changes with a reload first when possible.

Our use across the four products

DocuMint, CronPing, FlagBit, and WebhookVault all run on SQLite, where the equivalent is .sqliterc and PRAGMA statements. PRAGMA settings persist for the connection that issued them but not across connection re-establishment, so the equivalent of ALTER SYSTEM does not exist in our SQLite-based products. Our planned Postgres migration brings ALTER SYSTEM into the operational vocabulary, and the decision about how to handle the auditability problem is one we will need to make before the migration is complete.

The deeper observation is that ALTER SYSTEM is a database feature designed to make operational life easier by removing the need to shell into the host to change configuration. The same feature makes auditing harder by removing the natural file-system trail. The trade-off is acceptable, and probably correct, but it does need a deliberate plan rather than the default of using the feature whenever it is convenient.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) keep the lights on.