debugsqlMinor
Can Postgres synchronous replication stall a cluster if secondary/standby crashes?
Viewed 0 times
canpostgressynchronouscrashesreplicationstallsecondarystandbycluster
Problem
In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master? That is, since the Postgres master always waits for confirmation from the secondary/standby before it commits the write transaction, then will the master simply stop functioning if the secondary/standby is inaccessible? How does Postgres deal with this case?
Solution
In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master?
Yes, by design. See the manual:
will the master simply stop functioning if the secondary/standby is inaccessible?
It'll still serve read-only queries, but commits will block and not return until the replica comes back up. If you can accept some transactions not being synchronously replicated and have
How does Postgres deal with this case?
As documented above you can configure multiple synchronous replicas to mitigate potential production impact. PostgreSQL does what you tell it to, and doesn't confirm commits until they're replicated.
If you don't want that, use asynchronous replication. The idea of "synchronous replication" where the master can keep on working while the replica is offline is nonsensical.
Yes, by design. See the manual:
synchronous_standby_names
- synchronous replication
will the master simply stop functioning if the secondary/standby is inaccessible?
It'll still serve read-only queries, but commits will block and not return until the replica comes back up. If you can accept some transactions not being synchronously replicated and have
SET LOCAL synchronous_commit = off in those transactions they'll commit without blocking.How does Postgres deal with this case?
As documented above you can configure multiple synchronous replicas to mitigate potential production impact. PostgreSQL does what you tell it to, and doesn't confirm commits until they're replicated.
If you don't want that, use asynchronous replication. The idea of "synchronous replication" where the master can keep on working while the replica is offline is nonsensical.
Context
StackExchange Database Administrators Q#65956, answer score: 6
Revisions (0)
No revisions yet.