Runbook: Add or Remove a Synchronous Standby

1 · Prerequisites

Confirm every item is in place before any state change.

• At least two healthy standbys if synchronous replication is being enabled, because a single synchronous standby makes the primary depend on it
• The application_name of each standby as it appears in pg_stat_replication, since synchronous_standby_names matches on that
• A written statement of which property is being bought — durability — and an acknowledgement that it is being traded against availability
• Agreement from the service owner, because this changes the cluster availability characteristics and is not a database-team decision alone
• A measured baseline of commit latency and throughput before the change
• A tested procedure for removing the requirement quickly, because that is the emergency action when a standby is lost

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Confirm how many standbys are streaming, and their names. SELECT application_name, state, sync_state, sync_priority, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind FROM pg_stat_replication ORDER BY application_name;
• · **Confirm each standby's application_name is meaningful.** The default is walreceiver, identical for every standby, and synchronous_standby_names cannot distinguish them. Fix the names before configuring anything.
• · Count the standbys against the quorum you intend. ANY 1 (a, b) needs two standbys to survive losing one. With one standby, every commit hangs when it goes away, indefinitely, by design.
• · Measure the baseline. Commit throughput and latency, under representative load, before the change. Measured on one cluster: 824 tps at synchronous_commit = local against 552 at on — a cost of roughly 25 to 33 percent for waiting on the standby.
• · Confirm the standbys can keep up. A synchronous standby that lags makes every commit wait for it. bytes_behind under load, not at rest, is the number that matters.
• · Check the network path between primary and standbys. Synchronous commit adds a round trip to every transaction, so latency between the hosts becomes latency in the application.
• · Write down the emergency action and where it is documented: ALTER SYSTEM SET synchronous_standby_names = ''; SELECT pg_reload_conf();. Somebody will need it at 21:40.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Choose the mode explicitly. ANY N (list) is a quorum: any N of the listed standbys must acknowledge. FIRST N (list) is priority-ordered. ANY 1 (a, b) is the usual right answer, because it survives losing either standby.
2. 2Quote names containing a hyphen inside the string literal. synchronous_standby_names parses its value as a name list. 'db-standby-01' produces ERROR: invalid value for parameter "synchronous_standby_names" ... syntax error at or near "-". The correct form is '"db-standby-01"'.
3. 3Set the value and reload. ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (sync1, sync2)'; SELECT pg_reload_conf(); This is reloadable and needs no restart.
4. 4Confirm the standbys took the expected role. SELECT application_name, state, sync_state, sync_priority FROM pg_stat_replication ORDER BY application_name; A quorum configuration reports sync_state = 'quorum'; a priority configuration reports sync and potential.
5. 5**Choose synchronous_commit deliberately, and know what each level waits for.** remote_write waits for the standby to write; on waits for it to flush to disk; remote_apply waits for it to apply, which is what makes a read on the standby immediately consistent. Each level costs more than the last.
6. 6**Set synchronous_commit per role or per transaction where the workload allows it.** A bulk-loading job that can tolerate loss can use SET LOCAL synchronous_commit = 'local' without weakening the guarantee for everybody else.
7. 7Measure again under the same load and compare against the baseline. Report the cost honestly; a 25 percent throughput reduction is a real price and the service owner agreed to pay it for a reason.
8. 8Test the failure mode deliberately, in a window. Stop one standby and confirm commits continue. Measured on a quorum of two with one stopped: INSERT 0 1 in 0.022 seconds, and pg_stat_replication showing the remaining standby still in quorum.
9. 9Test the unsatisfiable case too. Stop the second standby and confirm commits hang. This is the guarantee being honoured, not a bug — and a team that has seen it will not restart the primary during the next incident.
10. 10Learn to recognise the hang. SELECT pid, state, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event = 'SyncRep'; returns the waiting backends. Reads continue working throughout, and no error appears in the server log, because nothing has failed.
11. 11Add the standby-maintenance step. Removing a standby from synchronous_standby_names before rebooting it costs one reload and removes an entire class of incident.
12. 12**Alert on wait_event = 'SyncRep' and on connected synchronous standbys falling below the quorum requirement.** The second fires before the last one goes, which is the alert that gives you a window.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓pg_stat_replication shows the intended standbys with the intended sync_state — quorum for a quorum configuration, sync and potential for a priority one.
• ✓Commit latency under representative load is measured and recorded against the baseline.
• ✓Stopping one standby leaves commits working, confirmed by an actual write and its timing rather than by reasoning about the configuration.
• ✓Stopping every synchronous standby makes commits hang at IPC / SyncRep, confirmed once in a window so the team recognises it.
• ✓Reads continue to work during that hang, which is the observation that distinguishes this from a database failure.
• ✓SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'SyncRep'; is zero during ordinary operation.
• ✓The emergency action — clearing synchronous_standby_names and reloading — has been exercised and its effect measured. On one cluster the next commit completed in 0.021 seconds.
• ✓The standby-maintenance runbook includes the step that removes a standby from the synchronous set before it is rebooted.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶Clear the requirement: ALTER SYSTEM SET synchronous_standby_names = ''; SELECT pg_reload_conf();. It is reloadable and takes effect immediately; every waiting transaction completes at once.
• ↶Understand what that does. The cluster is now asynchronous, and a primary failure from that moment can lose recently committed transactions. Record the time and set a reminder to restore the setting.
• ↶Cancelling individual waiting backends is the narrower option: SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE wait_event = 'SyncRep';. Each cancelled transaction returns success to its client while being potentially absent from the standby, and PostgreSQL says so: the transaction has already committed locally, but might not have been replicated to the standby.
• ↶Prefer changing the setting once, deliberately, over cancelling transactions one at a time. The setting is a decision; the cancellations are a series of small silent ones.
• ↶If the change is being reverted because the latency cost was unacceptable, record the measured cost. That number is what the next proposal will be evaluated against.
• ↶A cluster left asynchronous after an incident has quietly abandoned the durability guarantee it was built for. Nothing will remind you; put it in the incident's follow-up actions.

6 · Escalation

When the runbook isn't enough, contact:

• · Only one standby exists and synchronous replication has been requested: escalate to the service owner. A single synchronous standby makes the primary depend on it, and a routine reboot stops every write.
• · The latency cost is unacceptable to the application: escalate with the measured numbers. The choice between throughput and durability belongs to the service owner, and it is better made against evidence.
• · A standby cannot keep up under load and is holding commits: escalate to the platform owner. A synchronous standby on slower storage or a longer network path makes every transaction wait for the slowest component in the estate.
• · Commits are hanging and nobody can reach a standby: escalate to the incident owner and present both options — wait, or clear the requirement and accept asynchronous durability. That is a business decision under time pressure and it should be made by the person accountable for it.
• · Somebody proposes disabling synchronous_commit globally to relieve a performance problem: escalate to the data owner. That trades a durability guarantee for a symptom, and the trade must be explicit.
• · The estate has a compliance requirement for zero data loss: escalate to whoever owns it before changing anything. Synchronous replication is one part of meeting such a requirement and the configuration details matter to the auditor.

References

1. PostgreSQL 18 documentation, Synchronous Replication
2. PostgreSQL 18 documentation, Replication settings
3. PostgreSQL 18 documentation, Asynchronous Commit
4. PostgreSQL 18 documentation, Wait Events