Hi Laurenz, Thank you for the quick response.
Could you please point me to the link where the "two-phase commit" approach is being discussed. I can track it for my reference. On Fri, Jun 23, 2023 at 3:26 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > 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. > > Yours, > Laurenz Albe >
