ALTER SYSTEM, and how it fails

ALTER SYSTEM is the supported way to change server configuration from a SQL connection, without shell access to the host. It is genuinely useful — a managed service or a locked-down host may offer no other route — and it has one failure mode serious enough to justify a lesson of its own.

What it does

It writes a single key to postgresql.auto.conf in the data directory, rewriting that file wholesale each time. The file is read last, after postgresql.conf and everything it includes, so an ALTER SYSTEM entry overrides them.

ALTER SYSTEM SET max_wal_size = '4GB';   -- write it
ALTER SYSTEM RESET max_wal_size;         -- remove that one key
ALTER SYSTEM RESET ALL;                  -- empty the file

It does not apply the change. Applying still requires a reload for a sighup parameter or a restart for a postmaster one, exactly as if you had edited the file by hand.

The errors it does raise

These are all rejections at statement time, which is the helpful case:

$ psql -U postgres  # four separate ALTER SYSTEM attempts

-- inside a transaction block
ERROR:  ALTER SYSTEM cannot run inside a transaction block

-- a value that does not parse
ALTER SYSTEM SET work_mem = 'banana';
ERROR:  invalid value for parameter "work_mem": "banana"

-- a read-only parameter
ALTER SYSTEM SET block_size = 16384;
ERROR:  parameter "block_size" cannot be changed

-- a parameter that does not exist
ALTER SYSTEM SET not_a_real_parameter = 1;
ERROR:  unrecognized configuration parameter "not_a_real_parameter"

The transaction-block rejection is worth remembering practically: it means several ALTER SYSTEM statements cannot be passed in one psql -c argument, because psql wraps multiple statements in a single transaction. Use a separate -c for each.

The error it does not raise

ALTER SYSTEM validates that a value is syntactically legal for the parameter. It does not validate that the machine can satisfy it.

The parameters where this bites are the postmaster-context ones that size shared memory: shared_buffers, max_connections, max_locks_per_transaction, wal_buffers, max_worker_processes. A sighup parameter set to an unreasonable value is far less dangerous, because the reload applies it immediately and you find out at once.

Disabling ALTER SYSTEM

PostgreSQL provides a parameter to turn the command off entirely.

$ psql -U postgres -c "SELECT name, setting, context, short_desc FROM pg_settings WHERE name = 'allow_alter_system'"

        name        | setting | context |                short_desc
--------------------+---------+---------+------------------------------------------
allow_alter_system | on      | sighup  | Allows running the ALTER SYSTEM command.
(1 row)

Setting it to off — in postgresql.conf, not via ALTER SYSTEM — makes the command raise an error. This is worth doing in one specific situation: an estate where configuration is owned by Ansible, Terraform or a GitOps pipeline, and where an ALTER SYSTEM made during an incident would silently diverge the running server from the repository that everyone believes describes it.

It is explicitly not a security control. The documentation is direct about this: a superuser has many other ways to change server behaviour, so this prevents accident and drift rather than a determined actor. Treat it as a guard rail, in the same category as a protected branch.

Production discipline

1. Check pending_restart immediately after every ALTER SYSTEM. It is the only signal distinguishing a change that is live from one that is merely written.
2. Never leave a restart-only change pending indefinitely. Restart in a window you chose rather than discovering the outcome during an unplanned one.
3. Treat ALTER SYSTEM on shared-memory parameters as the highest-risk configuration action available, because it is the one whose failure is deferred and total.
4. Use one -c per ALTER SYSTEM. Several statements in a single -c become one transaction, which the command refuses.
5. Consider allow_alter_system = off where configuration is managed as code, as a drift guard rail rather than a security control.
6. Editing postgresql.auto.conf by hand is legitimate exactly once: when the server will not start because of what is in it.

Cross-course references

• Git, CI/CD & GitOps — Part LXXV (Drift) covers the divergence between a repository and a running system that allow_alter_system exists to prevent, and Part CVI (Change management) covers stating a rollback for a change whose effect is deferred.
• Ansible for Production Sysadmins — Part XXXVI (Drift) covers detecting and correcting exactly this class of out-of-band change.
• Linux for Production Sysadmins — Part XXXVII (Resources) and Part XL (Memory performance) cover the shared-memory limits that the failure in this lesson runs into.

Quiz