Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-sync-replication~45 min

A standby was rebooted for a kernel patch and every write on the primary stopped, on a cluster built for high availability

Reported symptoms

  • ●A routine kernel patch reboot of db-standby-01 at 21:40 causes every write transaction on the primary to stop returning
  • ●The primary is up, accepting connections, and answering read queries normally throughout
  • ●The application appears frozen rather than erroring - requests hang until they hit their own timeouts
  • ●No error appears in the primary server log at any point
  • ●Restarting the primary was considered and rejected, correctly, since the primary is not faulty
  • ●Service returns the moment the standby finishes booting and reconnects, six minutes later
  • ●The cluster was described in its design document as highly available

Evidence

  • · synchronous_standby_names on the primary is db-standby-01, naming exactly one standby
  • · pg_stat_replication on the primary is empty during the outage
  • · pg_stat_activity shows every writing backend with state active, wait_event_type IPC and wait_event SyncRep
  • · Read-only queries during the same period return normally and immediately
  • · A row count taken during the outage showed 2 rows, and the same count after recovery showed 5, so the waiting transactions had already been written locally
  • · Cancelling one of the waiting backends produced WARNING: canceling wait for synchronous replication due to user request with DETAIL: The transaction has already committed locally, but might not have been replicated to the standby
  • · The cancelled statement then reported INSERT 0 1 - a success
  • · Setting synchronous_standby_names to empty and reloading restored commit latency to 0.021 seconds immediately
Diagnosis and resolutionclick to reveal

Root cause

Synchronous replication to a single standby converts that standby into a hard dependency of the primary. This is not a defect; it is the guarantee working exactly as specified, and the guarantee is stronger than the team intended to buy. With `synchronous_standby_names = 'db-standby-01'`, a commit on the primary is not acknowledged to the client until the named standby confirms it. Remove the standby and there is nothing to confirm, so the commit waits — indefinitely, by design. A timeout would silently weaken the durability guarantee, so PostgreSQL does not offer one. Everything observed follows from that. The primary is healthy and answers reads, because reads take no commits. Writes hang with `wait_event_type = IPC` and `wait_event = SyncRep`, which names the wait precisely. No error appears in the log because nothing has failed — the primary is patiently doing what it was told. The most consequential detail is that **the waiting transactions were already durable on the primary.** A count during the outage returned 2 rows; after recovery the same count returned 5. The three waiting commits had been written and flushed locally the whole time; they were waiting only for the standby's acknowledgement. PostgreSQL says so itself when a wait is cancelled: *the transaction has already committed locally, but might not have been replicated to the standby.* That is why cancelling is not a free escape. It returns success to the client for a transaction that is durable on the primary and may be absent from the standby — which is precisely the state synchronous replication was configured to make impossible. It is the right choice in some incidents and it must be made knowingly. The design document called this cluster highly available. Configured this way it is less available than a single server: it has two components, and losing either stops writes. What was bought was a durability guarantee, and durability and availability were traded without anybody recording the trade.

Remediation

Identify the wait before doing anything, because a frozen application with a healthy database has several possible causes and this one has a signature: ```sql SELECT pid, state, wait_event_type, wait_event, left(query, 60) AS query FROM pg_stat_activity WHERE backend_type = 'client backend' AND wait_event = 'SyncRep'; SELECT count(*) AS standbys FROM pg_stat_replication; SHOW synchronous_standby_names; ``` `IPC` / `SyncRep` with an empty `pg_stat_replication` is this incident and nothing else. If the standby will return within an acceptable window, waiting is a legitimate choice. It preserves the guarantee, and it is what the configuration was asking for. If it will not, release the requirement. This is reloadable and takes effect immediately: ```sql ALTER SYSTEM SET synchronous_standby_names = ''; SELECT pg_reload_conf(); ``` Commit latency returns at once — measured at 0.021 seconds on the next commit. Every waiting transaction completes. Understand what you have just done: the cluster is now asynchronous, and a primary failure from this moment can lose recently committed transactions. Record the time, and set a reminder to restore the setting when the standby is back. Cancelling individual backends is the other option, and it is narrower: ```sql 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. Prefer changing the setting once, deliberately, over cancelling transactions one at a time. The durable fix is a quorum. `ANY 1 (a, b)` requires an acknowledgement from either of two standbys, so losing one is survivable: ```sql ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (sync1, sync2)'; SELECT pg_reload_conf(); ``` Measured with two standbys configured this way: stopping one left commits at 0.022 seconds, and stopping the second made the quorum unsatisfiable and commits hung again — as they must. A quorum tolerates the failures you provisioned for and no more, which is the honest property to design around. Note the quoting rule, because it is a common trap: `synchronous_standby_names` parses its value as a name list, so a name containing a hyphen must be double-quoted *inside* the string literal, or you get `syntax error at or near "-"`: ```sql ALTER SYSTEM SET synchronous_standby_names = '"db-standby-01"'; ```

Verification

`pg_stat_replication` shows the expected standbys with the expected `sync_state`. A quorum configuration reports `quorum`; a priority configuration reports `sync` and `potential`: ```sql SELECT application_name, state, sync_state, sync_priority FROM pg_stat_replication ORDER BY application_name; ``` Commit latency is normal with all standbys present. **Stop one standby deliberately and confirm commits continue.** This is the entire point of the change and it takes one command: ```bash pg_ctl -D /var/lib/postgresql/18/main -m fast stop ``` Measured on a quorum of two with one stopped: `INSERT 0 1` in 0.022 seconds, and `pg_stat_replication` showing the single remaining standby still in `quorum`. **Stop the second one and confirm commits hang.** That is not a bug to be fixed; it is the guarantee being honoured, and a team that has seen it will not restart the primary during the next incident. No backend sits in `SyncRep` during ordinary operation: ```sql SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'SyncRep'; ``` The maintenance runbook for standby patching has been exercised end to end, including the step that takes the standby out of the synchronous set before rebooting it.

Prevention

**Never configure synchronous replication to a single standby.** One standby makes the primary depend on it. Use `ANY 1 (a, b)` across at least two, so that a routine reboot is survivable. **Write down which you are buying: durability or availability.** Synchronous replication trades one for the other. A design document that says "highly available" above a single-standby synchronous configuration has not made the trade — it has hidden it. **Alert on `wait_event = 'SyncRep'`.** It is unambiguous, and it is the difference between a six-minute diagnosis and a six-minute outage spent guessing: ```sql SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'SyncRep'; ``` **Alert on the count of connected synchronous standbys falling below the quorum requirement.** That fires *before* the last one goes and gives you a window. **Put the removal step in the standby maintenance runbook.** Taking a standby out of `synchronous_standby_names` before rebooting it costs one reload and removes this incident entirely. **Know that cancelling a `SyncRep` wait returns success for a transaction that may not be on the standby.** That is sometimes the right call. It must never be an accidental one, and the warning text should be in the runbook so nobody meets it for the first time at 21:40. **Rehearse losing a standby.** This cluster had never had one stopped deliberately, which is why a routine kernel patch became an outage. **Restore the setting afterwards.** A cluster left asynchronous after an incident has quietly abandoned the durability guarantee it was built for, and nothing will remind you.

Reported symptoms

A routine kernel patch reboot of db-standby-01 at 21:40 causes every write transaction on the primary to stop returning.

The primary is up, accepting connections, and answering read queries normally throughout. The application appears frozen rather than erroring — requests hang until they hit their own timeouts.

No error appears in the primary server log at any point.

Restarting the primary was considered and rejected, correctly: the primary is not faulty.

Service returns the moment the standby finishes booting and reconnects, six minutes later.

The cluster is described in its design document as highly available.

Evidence provided

Read-only / Safeevery writing backend, waiting on the same thing
$ psql -c "SELECT pid, state, wait_event_type, wait_event, left(query,40) AS query FROM pg_stat_activity WHERE wait_event_type IS NOT NULL AND backend_type='client backend';"
 pid | state  | wait_event_type | wait_event |             query             
-----+--------+-----------------+------------+-------------------------------
179 | active | IPC             | SyncRep    | INSERT INTO t DEFAULT VALUES;
188 | active | IPC             | SyncRep    | INSERT INTO t DEFAULT VALUES;
(2 rows)

pg_stat_replication is empty. Reads return normally and immediately.

And this is the detail that changes how you think about the incident:

Read-only / Safea count during the hang, and the same count after recovery
$ psql -c "SELECT count(*) FROM t;"   # during, then after
 rows_readable 
---------------
           2

rows_now 
----------
      5

Cancelling a waiting backend says it in words:

Service impact possiblewhat PostgreSQL tells you when you cancel a synchronous commit wait
$ psql -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE wait_event = 'SyncRep';"
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1

Setting synchronous_standby_names to empty and reloading restored commit latency to 0.021 seconds immediately.

Work the evidence before reading on

  1. The primary is healthy and reads work. Why do only writes hang?
  2. Two rows visible during the hang and five afterwards. Where were the other three?
  3. The cancelled INSERT reported success. Is that safe?
  4. What does the design document mean by “highly available” here?

Root cause

One synchronous standby is a hard dependency

The waiting transactions were already durable

Two rows visible during the hang, five afterwards. The three waiting commits had been written and flushed on the primary the whole time. They were waiting only for the standby’s acknowledgement, and PostgreSQL says so when you cancel one: already committed locally, but might not have been replicated to the standby.

The design document was wrong

Resolution

Identify the wait first. A frozen application with a healthy database has several possible causes, and this one has a signature:

SELECT pid, state, wait_event_type, wait_event, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND wait_event = 'SyncRep';

SELECT count(*) AS standbys FROM pg_stat_replication;
SHOW synchronous_standby_names;

IPC / SyncRep with an empty pg_stat_replication is this incident and nothing else.

If the standby will return within an acceptable window, waiting is a legitimate choice. It preserves the guarantee, which is what the configuration was asking for.

If it will not, release the requirement:

ALTER SYSTEM SET synchronous_standby_names = '';
SELECT pg_reload_conf();

Commit latency returns at once. Understand what you have just done: the cluster is asynchronous, and a primary failure from this moment can lose recently committed transactions. Record the time and set a reminder to restore it.

The durable fix is a quorum:

ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (sync1, sync2)';
SELECT pg_reload_conf();
Configuration changea quorum of two, with one standby stopped
$ pg_ctl -D /tmp/sb -m fast stop && time psql -c 'INSERT INTO t DEFAULT VALUES;'
 application_name | sync_state 
------------------+------------
sync2            | quorum
(1 row)

INSERT 0 1

real	0m0.022s

Stopping the second standby made the quorum unsatisfiable and commits hung again — as they must. A quorum tolerates the failures you provisioned for and no more, which is the honest property to design around.

Verification

pg_stat_replication shows the expected standbys with the expected sync_state — quorum for a quorum configuration, sync and potential for a priority one:

SELECT application_name, state, sync_state, sync_priority
FROM pg_stat_replication ORDER BY application_name;

Commit latency is normal with all standbys present.

Stop one standby deliberately and confirm commits continue. That is the entire point of the change and it takes one command.

Stop the second and confirm commits hang. Not a bug to be fixed — the guarantee being honoured. A team that has seen it will not restart the primary during the next incident.

No backend sits in SyncRep during ordinary operation:

SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'SyncRep';

The standby-patching runbook has been exercised end to end, including the step that removes the standby from the synchronous set before rebooting it.

Prevention

Never configure synchronous replication to a single standby. Use ANY 1 (a, b) across at least two, so a routine reboot is survivable.

Write down which you are buying: durability or availability. A design document that says “highly available” above a single-standby synchronous configuration has not made the trade — it has hidden it.

Alert on wait_event = 'SyncRep'. It is unambiguous, and it is the difference between a six-minute diagnosis and six minutes of guessing.

Alert on connected synchronous standbys falling below the quorum requirement. That fires before the last one goes.

Put the removal step in the standby maintenance runbook. One reload, and this incident does not happen.

Know that cancelling a SyncRep wait returns success for a transaction that may not be on the standby, and put the warning text in the runbook so nobody meets it for the first time at 21:40.

Rehearse losing a standby. This cluster had never had one stopped deliberately, which is why a kernel patch became an outage.

Restore the setting afterwards. A cluster left asynchronous after an incident has quietly abandoned the guarantee it was built for, and nothing will remind you.