Hi Team,
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.
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
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?
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.