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?
PostgreSQL – Can Synchronous Replication Stall Cluster if Standby Crashes?
postgresqlpostgresql-9.3replication
Related Question
- PostgreSQL – How to Perform Live Backup
- MySQL Database replication for high availability
- PostgreSQL 9.4 High Availability – Best Practices
- Postgres 9.4 Replication Slots – Failover Master Issues
- PostgreSQL Failover – Using pg_rewind to Show Lost Data
- PostgreSQL streaming replication lag for synchronous standbys
Best Answer
Yes, by design. See the manual:
synchronous_standby_namesIt'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 = offin those transactions they'll commit without blocking.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.