<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<font face="Carlito">Dear Bucardo team,<br>
<br>
Few weeks ago I started working on building a postgresql cluster
(in master / master mode) composed of 3 VM with Debian 11
installed.<br>
</font>
<ul>
<li><font face="Carlito">1 server is running an HA proxy server
(version 2.2.9). It is used to provide load balancing features<br>
</font></li>
<li><font face="Carlito">2 Postgresql server (version 13.8) and
Bucardo (version 5.6.0). Bucardo is used here to provide high
availability with a master / master mode.<br>
</font></li>
</ul>
<font face="Carlito">I have tested inserting data in each database
server locally and every data is available on each server. So the
master / master looks working fine.<br>
Then I have tried to insert data from a distant server through the
HA Proxy server and I have noticed that only the half of the
insert are available in postgresql.<br>
<br>
I have analyzed logs of the 3 softwares to find where something
goes wrong and think it comes from bucardo. </font><br>
<font face="Carlito"><font face="Carlito">In this case I tried to
insert 4 rows in a table named "files"</font><font
face="Carlito">. In postgresql only 2 entries are available. The
2 entries are the same on both postgresql server.</font></font>
<ul>
<li><font face="Carlito">First I checked HA proxy logs and find
that it was well redirecting 50% of insert on server 1 and 50%
on server 2. No error in its logs so I presume that it really
works fine.</font></li>
<li><font face="Carlito">Then I checked Postgresql log. In
Postgresql logs I cannot see any error and I can't see any
attempt to insert the missing values. I have changed log level
to ensure I'm not missing something but without success.</font></li>
<li><font face="Carlito">At last I have checked in Bucardo and
find below logs : <br>
</font></li>
</ul>
<blockquote><font face="Carlito"><font face="monospace">(292750)
[Mon Nov 21 10:46:30 2022] KID (sync_oneToTwo) Got NOTICE
run_sync_oneToTwo from 292748 (Bucardo DB) (line 3113)</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Adding entry to syncrun
table</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Populating the dbrun table</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Adding note to the syncrun
table</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Doing final maindbh commit</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Set database "serv1" to
repeatable read read write</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Set database "serv2" to
repeatable read read write</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Checking truncate_trigger
table on database "serv1"</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Sequence
public.files_code_seq from db serv1 is the highest</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Tables with deltas on
serv1: 1 Without: 0</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Delta count for
serv1.public.files : 2</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Total delta count: 2</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Setting
session_replication_role to replica for database serv2</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Rows to push from
serv1.public.files: 2</font></font><br>
<b><font face="Carlito"><font face="monospace">(292750) [Mon Nov
21 10:46:30 2022] KID (sync_oneToTwo) Deleting from target
serv2.public.files (round 1 of 1)</font></font></b><b><br>
</b><b><font face="Carlito"><font face="monospace">(292750) [Mon
Nov 21 10:46:30 2022] KID (sync_oneToTwo) Rows deleted from
serv2.public.files: 2</font></font></b><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Copying from
serv1.public.files</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Rows copied to (postgres)
serv2.public.files: 2</font></font><br>
<font face="Carlito"><font face="monospace"><b>(292750) [Mon Nov
21 10:46:30 2022] KID (sync_oneToTwo) Totals: deletes=2
inserts=2 conflicts=0</b></font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Rows inserted to
bucardo_track for serv1.public.files : 2</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Setting
session_replication_role to default for database serv2</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Issuing final commit for
all databases</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) All databases committed</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Total time for sync
"sync_oneToTwo" (2 rows, 1 table): 0.05 secondes</font></font><br>
<font face="Carlito"><font face="monospace">(292750) [Mon Nov 21
10:46:30 2022] KID (sync_oneToTwo) Sending NOTIFY
"ctl_syncdone_sync_oneToTwo" (line 4937) skip_commit=0</font></font><br>
</blockquote>
<blockquote><font face="Carlito">I'm not sure I correctly understand
bucardo logs as I'm not experimented using it, but it looks like
it deletes 2 raws in a server 2 and has pushed 2 raws from
server 1.</font><br>
</blockquote>
<font face="Carlito">My first question is, is my analysis correct or
am I wrong?<br>
My second question if I'm right is how should I set up Bucardo to
avoid this issue. I'm sure I'm doing something wrong but I can't
find what's wrong.<br>
</font><font face="Carlito"><font face="Carlito">My second question
if I'm wrong, do you have any idea of the cause of the issue?<br>
<br>
On server1, my bucardo set up is : <br>
<br>
<font face="monospace">-- dbgroups:<br>
dbgroup: sync_oneToTwo Members: serv1:source serv2:target<br>
-- databases:<br>
Database: serv1 Status: active Conn: psql -p 5432 -U bucardo
-d hubmaster -h ***.**.**.24<br>
Database: serv2 Status: active Conn: psql -p 5432 -U bucardo
-d hubmaster -h ***.**.**.25<br>
-- relgroup:<br>
Relgroup: one_two DB: serv1 Members: public.files,
public.files_code_seq<br>
Used in syncs: sync_oneToTwo<br>
-- syncs:<br>
Sync "sync_oneToTwo" Relgroup "one_two" DB group
"sync_oneToTwo" serv1:source serv2:target [Active]<br>
-- tables:<br>
2. Table: public.files DB: serv2 PK: code (integer)<br>
1. Table: public.files DB: serv1 PK: code (integer)<br>
-- sequences:<br>
Sequence: public.files_code_seq DB: serv1<br>
Sequence: public.files_code_seq DB: serv2</font><br>
</font></font><br>
<font face="Carlito"><font face="Carlito"><font face="Carlito"><font
face="Carlito">On server1, my bucardo set up is : </font></font><br>
<br>
<font face="monospace">-- dbgroups:<br>
dbgroup: sync_oneToTwo Members: serv1:source serv2:target<br>
-- databases:<br>
Database: serv1 Status: active Conn: psql -U bucardo -d
hubmaster -h ***.**.**.25<br>
Database: serv2 Status: active Conn: psql -U bucardo -d
hubmaster -h ***.**.**.24<br>
-- relgroup:<br>
Relgroup: one_two DB: serv1 Members: public.files,
public.files_code_seq<br>
Used in syncs: sync_oneToTwo<br>
-- syncs:<br>
Sync "sync_oneToTwo" Relgroup "one_two" DB group
"sync_oneToTwo" serv1:source serv2:target [Active]<br>
-- tables:<br>
1. Table: public.files DB: serv1 PK: code (integer)<br>
2. Table: public.files DB: serv2 PK: code (integer)<br>
-- sequences:<br>
Sequence: public.files_code_seq DB: serv1<br>
Sequence: public.files_code_seq DB: serv2<br>
</font><br>
Thanks in advance for any help provided.<br>
<br>
</font>Best regards</font><br>
<div class="moz-signature">-- <br>
<!-- logo et background --> <br>
<table style="border-top: 1px dashed #45BED7;" width="450"
cellspacing="0" cellpadding="0" border="0">
<tbody>
<tr>
<td style="vertical-align: top" vertical-align="top"
align="left"> <img style="vertical-align: top"
vertical-align="top"
src="cid:part1.V4aWMEE4.H7AKoxzo@numlog.fr" width="210"
height="60"> <br>
<br>
<!-- nom/prenom --> <strong> <span style="font-family:
Rubik; font-size: 16px; color: #1A084B;" id="sigName">
Quentin BIENFAIT </span> </strong> <br>
<!-- fonction --> <span style="color: #45BED7; font-size:
11px; font-weight: 600" id="sigName"> Equipe EDI / EDI
Team </span> </td>
<td align="right"> <img style="vertical-align: top;"
src="cid:part2.JBJfkf5J.LefAPraV@numlog.fr" width="100"
height="100"> </td>
</tr>
<!-- mail --> <tr>
<td> <br>
<strong> <span style="font-family: Rubik; font-size:
12px; color: #1A084B;"> <a
href="mailto:support.edi@numlog.fr" id="sigEmail"
style="text-decoration: none; color: #1A084B;"><u>Mail
:</u> support.edi@numlog.fr</a> </span> </strong>
<br>
<!-- telephone --> <strong> <span style="font-family:
Rubik; font-size: 12px; color: #1A084B; margin: 0px
0;"> <a href="tel:+33130791616"
style="text-decoration: none; color: #1A084B;"><u>Tél
:</u> +33 1 30 79 16 16 (choix 2)</a> </span> </strong>
<br>
<br>
<!-- adresse --> <span id="sigAddress"
style="font-family: Rubik; font-size: 12px; color:
#45BED7;"> 9 ter route de Saint Germain<br>
78640 Villiers Saint Frédéric - France </span> <br>
<br>
</td>
</tr>
</tbody>
</table>
<!-- bloc Linkedin -->
<table style="border-top: 1px dashed #45BED7; border-bottom: 1px
dashed #45BED7; display: flex;justify-content: center;"
width="450" cellspacing="0" cellpadding="0" border="0">
<tbody>
<tr>
<td width="40" align="center"> <a
href="https://www.linkedin.com/company/numlog/"
target="_blank"> <img
src="cid:part3.goX0xPYC.NOAq8w1O@numlog.fr"
alt="linkedin numlog" title="linkedin numlog"
width="35" height="35"></a> </td>
<td width="75" align="left"> <a style="font-family: Rubik;
color: #1A084B; font-size: 14px; vertical-align: 50%;
text-decoration: none;" text-decoration="none"
href="https://www.linkedin.com/company/numlog/"
target="_blank">linkedin</a> </td>
<!-- bloc newsletter --> <td width="40" align="center"> <a
href="https://cce6ca04.sibforms.com/serve/MUIEAA5rng8bgYTlAybtSwmIjNm02i1D75jtRGGJKYmaDWA0p1F7pFksNkRD2XGHdmuAWCVoAkNLyYLSK0dJAV2FbkodU2DNptDAf4ITyjy4qK2nteGxkYQkUOM1dB65fTbAvmk7mAQkokRC02MAYzFPkVWDN_cbdU5dcNeHwpkaa2PAVFkeiSe8OQ05-QMsu4CUpRzSswD6zk_z"
target="_blank"> <img
src="cid:part4.7DFd4Xbe.bbKhovQO@numlog.fr"
alt="formulaire newsletter numlog" title="formulaire
newsletter numlog" width="35" height="35"></a> </td>
<td width="75" align="left"> <a style="font-family: Rubik;
color: #1A084B; font-size: 14px; vertical-align: 50%;
text-decoration: none;"
href="https://cce6ca04.sibforms.com/serve/MUIEAA5rng8bgYTlAybtSwmIjNm02i1D75jtRGGJKYmaDWA0p1F7pFksNkRD2XGHdmuAWCVoAkNLyYLSK0dJAV2FbkodU2DNptDAf4ITyjy4qK2nteGxkYQkUOM1dB65fTbAvmk7mAQkokRC02MAYzFPkVWDN_cbdU5dcNeHwpkaa2PAVFkeiSe8OQ05-QMsu4CUpRzSswD6zk_z"
target="_blank">newsletter</a> </td>
<!-- bloc site web --> <td width="40" align="center"> <a
href="https://www.numlog.fr" target="_blank"> <img
src="cid:part5.Y6SaNa6P.Akk8xgMx@numlog.fr" alt="site
web numlog" title="site web numlog" width="35"
height="35"></a> </td>
<td width="75" align="left"> <a style="font-family: Rubik;
color: #1A084B; font-size: 14px; vertical-align:
50%;text-decoration: none;" href="https://www.numlog.fr"
target="_blank">numlog.<font color="#45BED7">fr</font></a>
</td>
</tr>
</tbody>
</table>
</div>
<br>
<span style="font-family: Rubik; font-size: 10px; color: #1A084B;
margin: 0px 0;"> Ce message électronique contient des informations
confidentielles, couvertes par le secret professionnel ou
réservées exclusivement à leur destinataire. Toute lecture,
utilisation, diffusion ou divulgation sans autorisation expresse
est strictement interdite.<br>
Si vous n'en êtes pas le destinataire, merci de prendre contact
avec l'expéditeur et de détruire ce message. <br>
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you
have received it in error, please notify the sender immediately
and delete the original. Any other use of the email by you is
prohibited. <br>
Avant d'imprimer, pensez à l'environnement - Before printing,
think about the environment </span> <br>
<br>
<!-- RGPD -->
</body>
</html>