On Fri, 2023-06-23 at 15:05 +0530, Postgres all-rounder wrote: > Context: We have faced a network isolation and ended-up with locally committed data on the > old primary database server as one of the tools that is in-place for HA decided to promote > one of the SYNC standby servers. As the PostgreSQL won't provide a HA solution as in-built, > I would like to just confirm on the behaviour of core parameter synchronous_commit= remote_apply. > > As per the documentation the PRIMARY database server will NOT commit unless > the SYNC standby acknowledges that it received the commit record of the transaction > and applied it, so that it has become visible to queries on the standby(s), and also written > to durable storage on the standbys.
That's not true. The primary will commit locally, but wait for the synchronous standby servers before it reports success to the client.
> However, during the network outage or few scenarios where the current primary is waiting > for the SYNC to acknowledge and when the application sends a cancel signal [even control +c > from a PSQL session which inserted data] then we see locally committed data on the primary > database server. > > "The transaction has already committed locally, but might not have been replicated to the standby." > > 1. It appears to be a known behaviour, however wanted to understand, is this considered as an > expected behaviour or limitation with the architecture
This is expected behavior AND a limitation of PostgreSQL.
> 2. Any known future plans in the backlog to change the behaviour in > such a way PRIMARY won't have the LOCALLY commit data which is NOT received and acknowledged > by a SYNC standby when synchronous_commit= remote_apply is used?
There have been efforts to use two-phase commit, but that would require PostgreSQL to have its own distributed transaction manager.
> 3. If the information is available in the document that primary database can have locally > committed data when it is waiting on SYNC and receive the cancel signal from the application, > it can be helpful.
I don't think that's anywhere in the documentation.
