Config drift between primary and standby

A replica is a copy of the data, not of the server

Streaming replication copies write-ahead log records. It does not copy postgresql.conf, it does not copy pg_hba.conf, and it has no opinion about whether the two machines have the same amount of memory. Everything outside the data directory’s contents is somebody’s job, and on most teams that job was done once, by hand, on the day the replica was built.

Drift then accumulates quietly, because nothing exercises it. The standby serves read queries perfectly well with half the work_mem and a stale authentication file, right up until the moment it is promoted and becomes the thing production depends on. That is the worst possible time to discover that a setting is wrong, and it is when most teams do.

There are two categories here and they deserve different treatment. A few settings will stop the standby from starting or from serving reads at all, and PostgreSQL tells you about those loudly. Everything else is silent, and is only a problem after promotion.

The five that are not optional

The size of several shared memory structures is fixed at startup from configuration, and recovery replays records that assume the primary’s sizes. If the standby’s structures are smaller, recovery can reach a record it has nowhere to put. PostgreSQL therefore requires that these are no lower on the standby than on the primary:

max_connections, max_prepared_transactions, max_locks_per_transaction, max_wal_senders and max_worker_processes.

When one of them is too low, the standby does not fail quietly. It logs that hot standby is not possible because of insufficient parameter settings, names the parameter, gives both values, and pauses recovery with a line saying that if recovery is unpaused the server will shut down. The fix is to raise the setting and restart, and there is no way around it.

The trap here is directional. Raising max_connections on a primary is a routine change that nobody thinks of as a replication change, and it silently puts every standby into a state where the next restart refuses to come up. Change the standbys first, then the primary, every time.

Everything else, which fails later

The silent category is larger and is where the real risk lives. A short list of what actually bites:

Memory settings sized for a smaller machine. shared_buffers, work_mem, maintenance_work_mem and effective_cache_size on a replica that was built on last year’s instance type will make the promoted node slower than the one it replaced, and the plans will change with them, which is a plan regression arriving at the same moment as a failover.

Authentication. A pg_hba.conf that was never updated with the application’s new subnet means the promoted primary rejects the connections it is supposed to accept. This is the one that turns a thirty-second failover into an outage.

Durability and retention. synchronous_commit, wal_level, max_wal_size and archive_command differing across nodes mean the recovery guarantees after promotion are not the ones the runbook claims.

Extensions. shared_preload_libraries must list the same modules, or a promoted node comes up without pg_stat_statements and without whatever else the monitoring assumes, losing the history at the exact moment somebody needs it.

Diffing two nodes properly

The catalog makes this straightforward. pg_settings reports the effective value on the node you are connected to, along with where it came from:

SELECT name,
       setting,
       coalesce(unit, '')  AS unit,
       source,
       sourcefile,
       sourceline,
       pending_restart
FROM pg_settings
WHERE context IN ('postmaster', 'sighup')
ORDER BY name;

Restricting to those two contexts is what makes the output comparable: they are the settings that belong to the server rather than to a session, so a difference between two nodes is a real difference rather than an artefact of who ran the query. Capture it on each node and compare the files:

for host in pg-primary pg-standby-a pg-standby-b; do
  psql -h "$host" -Atc "SELECT name || E'\t' || setting || E'\t' || coalesce(unit,'')
                        FROM pg_settings
                        WHERE context IN ('postmaster','sighup')
                        ORDER BY name;" > "settings-$host.tsv"
done

diff -u settings-pg-primary.tsv settings-pg-standby-a.tsv

Two columns in that query earn their place. pending_restart is true when somebody edited the file and the running server is still using the old value, which means the node is configured one way and behaving another; a failover is exactly the event that makes the edited value take effect. And sourcefile with sourceline tells you which file to change, which matters more than it sounds once ALTER SYSTEM is in play, because postgresql.auto.conf overrides the file people are editing and is invisible to anyone reading the main config in a repository.

For the files themselves rather than the running values, pg_file_settings shows every entry the server parsed, in order, with applied false on any that were overridden later and error populated on any that could not be applied at all. It is superuser-readable by default. pg_hba_file_rules does the same for authentication, which is the cheapest possible check against the failure mode above.

The drift that is not a setting

Two things outside postgresql.conf deserve a place on the checklist, because both are silent and both are worse than a wrong parameter.

The first is the operating system’s collation library. A standby built on a newer base image can have a different version of the C library or of ICU, which means text comparison can order strings differently. Indexes built under one version and read under another can return wrong answers, and after promotion that becomes your primary’s problem. PostgreSQL records the version each collation was created with and warns when the system provides a different one:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS collation,
       pg_describe_object(classid, objid, objsubid)          AS object
FROM pg_depend d
JOIN pg_collation c
  ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid)
ORDER BY 1, 2;

Anything returned by that has to be rebuilt with REINDEX before the version is refreshed with ALTER COLLATION ... REFRESH VERSION or ALTER DATABASE ... REFRESH COLLATION VERSION. Refreshing the version without reindexing only silences the warning.

The second is the minor version of PostgreSQL itself. A standby may run a newer minor release than its primary, and that is supported, but the reverse is not something to rely on, and a fleet where the patch levels have drifted apart is a fleet where the next security update is a much longer conversation. Keeping that straight is part of the same discipline as pg_upgrade and extensions. Across majors the risk inverts, because a setting one release accepts the next one refuses outright and the server will not start: the statistics collector process is gone is the clearest example of a configuration line that has to be removed rather than merely reviewed.