I am sorry, I forgot mentioned, that in the second situation I added a
primary key to the table.
Ondrej
On 20/04/2021 18:49, Ondřej Žižka wrote:
Hello Aleksander,
Thank you for the reaction. This was tested on version 13.2.
There are also other possible situations with the same setup and
similar issue:
-----------------
When the background process on server fails....
On postgresql1:
tecmint=# select * from a; --> LAN on sync replica is OK
id
----
1
(1 row)
tecmint=# insert into a values (2); ---> LAN on sync replica is DOWN
and insert is waiting. During this time kill the background process on
the PostgreSQL server for this session
WARNING: canceling the wait for synchronous replication and
terminating connection due to administrator command
DETAIL: The transaction has already committed locally, but might not
have been replicated to the standby.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
tecmint=# select * from a;
id
----
1
2
(2 rows)
tecmint=# ---> LAN on sync replica is still DOWN
The potgres session will restore after the background process failed.
When you run select on master, it still looks OK. But data is still
not replicated on the sync replica. If we lost the master now, we
would lost this data as well.
**************
Another case
**************
Kill the client process.
tecmint=# select * from a;
id
----
1
2
3
(3 rows)
tecmint=# --> Disconnect the sync replica now. LAN on
replica is DOWN
tecmint=# insert into a values (4); --> Kill the client process
Terminated
xzizka@service-vm:~$ psql -U postgres -h 192.168.122.6 -p 5432 -d tecmint
Password for user postgres:
psql (13.2 (Debian 13.2-1.pgdg100+1))
Type "help" for help.
tecmint=# select * from a;
id
----
1
2
3
(3 rows)
tecmint=# --> Number 4 is not there. Now switch the LAN on sync
replica ON.
----------
Result from sync replica after the LAN is again UP:
tecmint=# select * from a;
id
----
1
2
3
4
(4 rows)
In this situation, try to insert the number 4 again to the table.
tecmint=# select * from a;
id
----
1
2
3
(3 rows)
tecmint=# insert into a values (4);
ERROR: duplicate key value violates unique constraint "a_pkey"
DETAIL: Key (id)=(4) already exists.
tecmint=#
This is really strange... Application can be confused, It is not
possible to insert record, which is not there, but some systems which
use the sync node as a read replica maybe already read that record
from the sync replica database and done some steps which can cause
issues and can be hard to track.
If I say, that it would be hard to send the CTRL+C to the database
from the client, I need to say, that the 2 situations I described here
can happen in real.
What do you think?
Thank you and regards
Ondrej
On 20/04/2021 17:23, Aleksander Alekseev wrote:
Hi Ondřej,
Thanks for the report. It seems to be a clear violation of what is
promised in the docs. Although it's unlikely that someone implemented
an application which deals with important data and "pressed Ctr+C" as
it's done in psql. So this might be not such a critical issue after
all. BTW what version of PostgreSQL are you using?
On Mon, Apr 19, 2021 at 10:13 PM Ondřej Žižka
<ondrej.zi...@stratox.cz> wrote:
Hello all,
I would like to know your opinion on the following behaviour I see
for PostgreSQL setup with synchronous replication.
This behaviour happens in a special use case. In this use case,
there are 2 synchronous replicas with the following config (truncated):
- 2 nodes
- synchronous_standby_names='*'
- synchronous_commit=remote_apply
With this setup run the following steps (LAN down - LAN between
master and replica):
-----------------
postgres=# truncate table a;
TRUNCATE TABLE
postgres=# insert into a values (1); -- LAN up, insert has been
applied to replica.
INSERT 0 1
Vypnu LAN na serveru se standby:
postgres=# insert into a values (2); --LAN down, waiting for a
confirmation from sync replica. In this situation cancel it (press
CTRL+C)
^CCancel request sent
WARNING: canceling wait for synchronous replication due to user
request
DETAIL: The transaction has already committed locally, but might
not have been replicated to the standby.
INSERT 0 1
There will be warning that commit was performed only locally:
2021-04-12 19:55:53.063 CEST [26104] WARNING: canceling wait for
synchronous replication due to user request
2021-04-12 19:55:53.063 CEST [26104] DETAIL: The transaction has
already committed locally, but might not have been replicated to the
standby.
postgres=# insert into a values (2); --LAN down, waiting for a
confirmation from sync replica. In this situation cancel it (press
CTRL+C)
^CCancel request sent
WARNING: canceling wait for synchronous replication due to user
request
DETAIL: The transaction has already committed locally, but might
not have been replicated to the standby.
INSERT 0 1
postgres=# insert into a values (2); --LAN down, waiting for sync
replica, second attempt, cancel it as well (CTRL+C)
^CCancel request sent
WARNING: canceling wait for synchronous replication due to user
request
DETAIL: The transaction has already committed locally, but might
not have been replicated to the standby.
INSERT 0 1
postgres=# update a set n=3 where n=2; --LAN down, waiting for sync
replica, cancel it (CTRL+C)
^CCancel request sent
WARNING: canceling wait for synchronous replication due to user
request
DETAIL: The transaction has already committed locally, but might
not have been replicated to the standby.
UPDATE 2
postgres=# update a set n=3 where n=2; -- run the same
update,because data from the previous attempt was commited on
master, it is sucessfull, but no changes
UPDATE 0
postgres=# select * from a;
n
---
1
3
3
(3 rows)
postgres=#
------------------------
Now, there is only value 1 in the sync replica table (no other
values), data is not in sync. This is expected, after the LAN
restore, data will come sync again, but if the main/primary node
will fail and we failover to replica before the LAN is back up or
the storage for this node would be destroyed and data would not sync
to replica before it, we will lose data even if the client received
successful commit (with a warning).
From the synchronous_commit=remote_write level and "higher", I
would expect, that when the remote application (doesn't matter if
flush, write or apply) would not be applied I would not receive a
confirmation about the commit (even with a warning). Something like,
if there is no commit from sync replica, there is no commit on
primary and if someone performs the steps above, the whole
transaction will not send a confirmation.
This can cause issues if the application receives a confirmation
about the success and performs some follow-up steps e.g. create a
user account and sends a request to the mail system to create an
account or create a VPN account. If the scenario above happens,
there can exist a VPN account that does not have any presence in the
central database and can be a security issue.
I hope I explained it sufficiently. :-)
Do you think, that would be possible to implement a process that
would solve this use case?
Thank you
Ondrej
