Rotating a Root Database Password Without Orphaning a Replication Slot

by ·Comments Off on Rotating a Root Database Password Without Orphaning a Replication Slot

The password change is the easy part

Rotating a root database password is a one-line SQL statement. The risk sits around it: connections drop, a standby reconnects with the wrong credential, and a replication slot stops advancing. On a lean team with no dedicated SRE, that slot can quietly pin WAL in pg_wal until the disk fills. PostgreSQL’s own documentation is blunt about the mechanism: “A slow or failed standby server that uses a replication slot will have the same effect” as failed WAL archiving — old WAL files accumulate until the situation is resolved (PostgreSQL 18, WAL Configuration).

This is a runbook for treating credential rotation as a replication-slot safety problem. The rotation itself is trivial. The controls that matter are a slot baseline before the change, a slot confirmation after it, and a rollback path you have actually rehearsed.

Why a password change can touch slot state at all

Two separate credentials are usually in play, and conflating them is the first mistake:

  • The application role password, changed with ALTER ROLE ... PASSWORD. Existing authenticated sessions are not terminated by the statement; new connections must present the new password.
  • The replication connection credential, carried in the standby’s primary_conninfo or in a ~/.pgpass file on the standby. This is a different role and a different connection path.

Rotating one does not rotate the other. If you change the replication role’s password on the primary and do not update the standby’s connection string, the WAL receiver cannot authenticate. The standby stops streaming. If a physical replication slot is in use, that slot stops advancing, and WAL retention follows the slot’s restart_lsn.

The PostgreSQL documentation notes that “abrupt disconnection of a streaming client might leave an orphaned connection slot behind until a timeout is reached,” and recommends setting max_wal_senders slightly above the expected client count so disconnected clients can reconnect immediately (PostgreSQL 18, Replication). That is the documented failure shape: a connection that goes away and does not come back promptly.

Pre-flight: record the slot baseline

Before touching any credential, capture the state you will compare against. The pg_replication_slots view is the source of truth for slot identity, activity, and WAL availability. The documentation states that WAL availability of replication slots is visible there (PostgreSQL 18, Replication).

SELECT slot_name,
       slot_type,
       active,
       active_pid,
       restart_lsn,
       confirmed_flush_lsn,
       wal_status,
       inactive_since
FROM pg_replication_slots
ORDER BY slot_name;

Record the output in the change ticket or decision record. Also capture the current WAL position and the size of pg_wal:

SELECT pg_current_wal_lsn();

And on the host:

du -sh $PGDATA/pg_wal

Two checks matter more than the rest. First, is the slot active? An inactive physical slot is not necessarily broken, but it is the state in which WAL retention becomes a liability. Second, what is wal_status? If it is already lost or unreserved, the rotation is not your first problem.

Note the default retention behavior. max_slot_wal_keep_size defaults to -1, which means “replication slots may retain an unlimited amount of WAL files” (PostgreSQL 18, Replication). If you have not set a bound, a stalled slot has no ceiling. That is a configuration decision to revisit separately, but it changes how much urgency the post-change check deserves.

Order of operations

The sequence below keeps the old credential valid until the new one is proven. The window is short and logged; that is a rollback property, not a security compromise.

  1. Create the new credential path first. For the replication role, set the new password on the primary. For the application role, set the new password and update the application’s secret store. Do not retire the old value yet.
  2. Update the standby’s connection string. Edit primary_conninfo (or the .pgpass entry) on the standby. The documentation states that if primary_conninfo is changed while the WAL receiver is running, that process is signaled to shut down and expected to restart with the new setting (PostgreSQL 18, Replication). Plan for a brief interruption in streaming.
  3. Confirm the standby reconnects. Check pg_stat_replication on the primary for a streaming state, and check the standby’s log for authentication errors.
  4. Confirm the slot is advancing. Re-run the pg_replication_slots query. restart_lsn should move forward, and active should be true for the physical slot used by the standby.
  5. Retire the old credential. Only after steps 3 and 4 pass. Record the time of retirement.

For the application role, the equivalent check is a new connection using the new password, followed by a normal query. Existing sessions continue until they disconnect; that is expected and not a signal of failure.

Two checks, not one

“Replication is streaming” and “the slot is advancing” are different observations. A connection can appear healthy in pg_stat_replication while a slot’s restart_lsn is stale — for example, if the standby is connected but not acknowledging WAL, or if a different slot is the one holding retention. The WAL accumulation is the thing that bites a lean team, and it is visible in pg_replication_slots, not in the connection state alone.

After the change, compare the new restart_lsn to the baseline. If it has not moved, treat that as an incident, not a cosmetic warning. The relevant question is not “is the standby connected?” but “is the slot that governs WAL retention still moving forward?”

The orphaned-slot trap after failover or switchover

Password rotation is not the only way to end up with a slot that does not advance. A slot exists solely on the instance that created it; PostgreSQL does not replicate it to standbys, so after a failover or switchover the new primary does not contain the old primary’s slot (CloudNativePG 1.18, Replication). Operators such as CloudNativePG manage physical HA slots to fill that gap, but logical slots and slot synchronization have their own failure modes.

A reported CloudNativePG issue documents one: with PostgreSQL 17’s slot synchronization, a demoted primary can retain a locally created slot marked synced=false, and “PostgreSQL’s slot sync worker refuses to overwrite slots that aren’t marked as synced=true” (CloudNativePG issue #9969). The same report notes that the worker aborts the whole cycle on the first name clash, so one leftover slot can block synchronization of all slots. The observed effect in that report was WAL accumulation on the replica.

This matters for rotation because the same verification query catches both problems. If you rotate a credential and then discover that a slot is inactive or that wal_status has degraded, you need to know whether the cause is the credential change or a pre-existing slot-identity problem. The baseline from the pre-flight step is what lets you tell the difference.

Handling the password itself

The PostgreSQL documentation cautions that an unencrypted password passed to ALTER ROLE “will be transmitted to the server in cleartext, and it might also be logged in the client’s command history or the server log,” and points to psql’s \password command as a way to change a role’s password without exposing the cleartext password (PostgreSQL 18, ALTER ROLE). For a root or replication role, use the interactive path or a secret-management tool that does not put the value in shell history.

This is a hygiene point, not a slot point. It belongs in the runbook because the rotation is the moment the credential is most likely to leak into a terminal log or a CI transcript.

Rollback drill

The rollback is: restore the previous credential on the primary, restore the previous primary_conninfo on the standby, and confirm the slot resumes advancing. Rehearse this on a standby or a staging cluster before you need it in production. The rehearsal is where you find out whether your deployment tolerates the connection drop, and whether your slot synchronization configuration recovers cleanly.

Write the exact commands into the runbook, including the baseline query and the confirmation query. The next on-call engineer should not have to reconstruct the reasoning from a chat thread. If your team keeps a recovery checklist, the rotation runbook should reference it rather than duplicate it; see Write the Recovery Checklist Before You Need It for the structure.

What to log and alert on

Three signals cover the failure modes described above:

  • Slot lag growth. Track the difference between pg_current_wal_lsn() and each slot’s restart_lsn. A slot that stops advancing while the primary keeps writing is the leading indicator of WAL accumulation.
  • Inactive slots. Alert on active = false for physical slots that should be streaming. The inactive_since column gives you a timestamp to reason about.
  • pg_wal directory growth. A host-level check on the directory size catches the case where slot metrics are missing or misconfigured.

PostgreSQL 18 adds idle_replication_slot_timeout, which invalidates slots that have remained inactive longer than the configured duration; the default of zero disables the mechanism (PostgreSQL 18, Replication). If you run 18 and choose to enable it, understand that invalidation is a deliberate loss of the slot’s ability to resume — it is a bound on WAL retention, not a repair. For most lean teams, alerting on inactivity is the safer first step.

What goes in the decision record

The rotation runbook is a decision record as much as a procedure. Record the baseline query output, the exact commands used, the time the old credential was retired, and the post-change slot state. If the rotation is routine, the record is short. If it goes wrong, the record is the difference between a blameless postmortem and a guessing game.

The argument in one sentence: the password change is not the risk; the connection churn and the slot-state verification around it are. Sequence the runbook so the credential change sits between a slot baseline and a slot confirmation, and keep the old credential valid until the new one is proven.

FAQ

Does ALTER ROLE ... PASSWORD terminate existing sessions?

The documentation describes the statement as changing the role’s attributes; it does not state that existing authenticated sessions are terminated. New connections must present the new password. Plan for existing sessions to continue until they disconnect naturally.

Do I need to restart PostgreSQL after changing primary_conninfo?

The documentation states that if primary_conninfo is changed while the WAL receiver is running, that process is signaled to shut down and expected to restart with the new setting (PostgreSQL 18, Replication). A reload is the documented path; a full server restart is not required for this parameter.

What is the default for max_slot_wal_keep_size?

-1, meaning replication slots may retain an unlimited amount of WAL files (PostgreSQL 18, Replication). If you have not set a bound, a stalled slot has no ceiling.

Can a password rotation itself orphan a slot?

The retrieved documentation describes slot orphaning in terms of abrupt disconnection, failover, and slot synchronization — not password rotation as a direct cause. The practical risk is indirect: a credential mismatch prevents the standby from reconnecting, and the slot stops advancing. Treat the slot check as the control, not the password statement.

Does pgBackRest need reconfiguration during a password rotation?

The retrieved pgBackRest material does not establish that. pgBackRest’s archive-push is invoked by PostgreSQL’s archive_command, and its authentication is a separate concern from the replication role’s password. Verify your own configuration rather than assuming a change is required.