synchronous_commit= remote_apply | "The transaction has already committed locally..."
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.
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
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.albe@cybertec.at>
wrote:
Show quoted text
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 NOTreceived 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
On Fri, 2023-06-23 at 16:23 +0530, Postgres all-rounder wrote:
Could you please point me to the link where the "two-phase commit" approach is being discussed.
I can track it for my reference.
I looked, and didn't find it. I must have mis-remembered.
There is this proposal:
/messages/by-id/CALj2ACUrOB59QaE6=jF2cFAyv1MR7fzD8tr4YM5+OwEYG1SNzA@mail.gmail.com
Yours,
Laurenz Albe