skip to content

When a DBA changes a PostgreSQL setting on temBoard's configuration page, what does the agent execute, and when does that clash with how the fleet is configured?

level: seniorimportance: should knowfreq 16%

answer

  1. SQL, not a file edit
  2. auto.conf wins over postgresql.conf
  3. reload, not restart
  4. pending restart stays pending
  5. allow_alter_system off since 17

basics

~10 s

The agent runs ALTER SYSTEM SET for each changed setting, then pg_reload_conf(); the value lands in postgresql.auto.conf, overriding postgresql.conf, which clashes with configuration management and fails outright when allow_alter_system is off.

solid answer

~40 s

For every changed value the temBoard agent runs `ALTER SYSTEM SET <name> TO '<value>'` as its superuser, then `SELECT pg_reload_conf()`; resetting a value runs `ALTER SYSTEM RESET` (or `SET ... TO DEFAULT`) and reloads. The change is written to `postgresql.auto.conf`, which overrides `postgresql.conf`. Reload-context settings take effect at once; restart-only ones such as `shared_buffers` show `pending_restart`, and since 10.0 temBoard cannot restart PostgreSQL for you because the old administration plugin is ignored. The clashes: a configuration-management tool that templates `postgresql.conf` is silently overridden; on PostgreSQL 17 and 18 `allow_alter_system = off` makes the edit fail; an HA manager that owns parameters may undo it. Each edit touches one instance only.

code

sql · 8 lines
sql
-- what the agent sends when a DBA sets work_mem to 32MB on a branch instance
ALTER SYSTEM SET "work_mem" TO '32MB';
SELECT pg_reload_conf();

-- afterwards: which stored values still wait for a restart?
SELECT name, setting, unit, context
FROM pg_settings
WHERE pending_restart;

go deeper

for a junior

Recall that the configuration page changes the server with ALTER SYSTEM and then reloads it.

for a middle

Explain where the value lands (postgresql.auto.conf), why reload-context settings apply at once and postmaster ones show pending restart, and that temBoard 10 does not restart.

for a senior

Show how to keep a fleet coherent: detect drift from templates, use allow_alter_system on 17 and later, and handle restart-only changes explicitly.

for a principal

Decide the configuration source of truth for the fleet and what role, if any, a click-to-change console should have beside it.

## What the agent runs temBoard's configuration page lists every setting by category, with its current value, unit, context and description. When a user saves changes, the UI sends a mapping of setting to value to the agent on that instance, which does the following with its superuser connection: 1. Skips any setting whose value did not change, and refuses a missing value or one longer than 1,024 characters. 2. Records an audit notification, `Setting <name> to <value>.`, with the UI user's name. 3. Runs `ALTER SYSTEM SET <name> TO <value>`, with the name quoted as an identifier and the value passed as a parameter. 4. After all settings, runs `SELECT pg_reload_conf()`. Restoring a setting to its default runs `ALTER SYSTEM SET <name> TO DEFAULT` or `ALTER SYSTEM RESET <name>`, followed by the same reload. If PostgreSQL rejects a statement (a bad value, a permission problem, a disabled command), the agent returns HTTP 406 with PostgreSQL's error text and the UI shows it. ## Reading the page before you edit The agent fills the page from `pg_settings`, so each row already tells you most of what you need: - **context**: whether a reload is enough or a restart is required; - **unit**: whether a bare number means kilobytes, 8 kB pages or milliseconds, the classic source of a value off by a factor of 1,024; - **boot and reset values**: what the server would use without your override; - **pending restart**: whether an earlier change, by anyone, is still waiting. The button is labelled **Save and reload configuration**: there is no way to store a value for a later reload, so any other unapplied change sitting in `postgresql.conf` is picked up at the same moment. ## Where the change lands `ALTER SYSTEM` writes to **`postgresql.auto.conf`** in the data directory. PostgreSQL reads that file whenever it reads `postgresql.conf`, and its entries **override** the main file. The detailed rules for files, contexts and reloads belong to the PostgreSQL configuration topic; what matters here is what temBoard's single button can and cannot achieve: | Setting's context | After temBoard's reload | Example | |---|---|---| | `sighup`, `superuser` or `user` | in effect, except in sessions that set their own value | `work_mem`, `log_min_duration_statement` | | `postmaster` | stored, not in effect; `pg_settings.pending_restart` is true | `shared_buffers`, `max_connections` | For the `postmaster` row the UI warns that a restart is pending. Up to 9.x an agent could carry an **administration** plugin that drove `pg_ctl`; **temBoard 10 ignores that plugin** and the upgrade guide says to remove its section. So the restart is yours to do, through systemd or whatever manages the cluster, in a window you choose. ## When it clashes - **Configuration management.** If a playbook or template owns `postgresql.conf`, a temBoard edit wins silently from `postgresql.auto.conf`. The repository of record now disagrees with the server, and the next person reading the template is misled. Re-running the template does not remove the override either. - **`allow_alter_system = off`.** PostgreSQL 17 added this setting (default `on`, reloadable) precisely for externally managed configuration. With it off, `ALTER SYSTEM` fails with `ALTER SYSTEM is not allowed in this environment`, so every temBoard save fails and shows that error. It is a guard against mistakes, not a security boundary: a superuser, which the agent is, can still change files by other means. - **An HA manager.** When a cluster manager owns PostgreSQL parameters, a local `ALTER SYSTEM` may be overwritten or may diverge between primary and standbys; how that manager treats parameters is its own topic. - **Fleet scale.** Each save touches **one instance**. temBoard has no push of one setting to all instances of an Environment, so forty instances mean forty edits, and they drift unless someone checks. ## A policy for a small team For a two-person team running forty instances across a library network, decide the source of truth once: 1. If configuration is templated, set `allow_alter_system = off` on PostgreSQL 17 and 18 instances and disable the configuration feature (the `pgconf` plugin) for them in temBoard, so the console stays a viewer. 2. If temBoard is allowed to edit, limit it to reloadable, per-incident tweaks, and fold every change back into the templates the same day. 3. Watch `pending_restart` after any edit and schedule the restart explicitly; never leave a fleet in a half-applied state. 4. Read temBoard's audit notifications when you need to know who changed what.

  • A teammate raised max_connections in temBoard yesterday, but connections still fail at the old limit. Why?
    max_connections has postmaster context. temBoard wrote it to postgresql.auto.conf and reloaded, so pg_settings shows pending_restart = true, but the running server still uses the old value. temBoard 10 no longer restarts PostgreSQL, so the change applies only after someone restarts the instance.
  • How do you stop temBoard edits on instances whose configuration is templated, without removing temBoard?
    On PostgreSQL 17 and later set allow_alter_system = off in postgresql.conf and reload; temBoard's saves then fail with a clear error. Also disable the pgconf feature for those instances so the page is not offered, and keep temBoard for monitoring, activity and maintenance.

  • temBoard edits postgresql.conf directly, so the template and the server stay in sync.
  • After saving shared_buffers in temBoard 10 the agent restarts PostgreSQL to apply it.
  • A value set through temBoard is lost the next time the server restarts.
  • allow_alter_system = off is a security control that stops a superuser changing settings.
  • One temBoard save applies a setting to every instance in the Environment.

Assumes temBoard 10 (administration plugin ignored, so no restart from the UI) on PostgreSQL 18; allow_alter_system exists since PostgreSQL 17.