[Bucardo-general] Replication failure because of duplicate key violation

[David Christensen]

On Feb 14, 2020, at 5:24 AM, Sai Prasad <spchinna25 at gmail.com> wrote:

> (22825) [Wed Feb 12 12:10:05 2020] KID (database_sync) Kid 22825 exiting at cleanup_kid. Sync "database_sync" public.interface Reason: DBD::Pg::db pg_putcopyend failed: ERROR:  duplicate key value violates unique constraint "interface_nodeid_ifindex_unique_idx"\nDETAIL:  Key (nodeid, ifindex)=(81, 1) already exists.\nCONTEXT:  COPY interface, line 2 at /usr/share/perl5/vendor_perl/Bucardo.pm line 9879. Line: 5041 Main DB state: ? Error: none DB database_node1 state: ? Error: none DB database_node2 state: 23505 Error: 7

Hi Sai,

I would suspect this is due to having another unique index defined on this table in addition to the primary key; there can definitely be issues with this and fairly obscure corner cases when using/writing a conflict handler.

That said, you should definitely adjust sequences on both sides of a multi-master sequence so new ids will be staggered.  The basic idea being to start one sides’ sequences on evens and the other on odds with INCREMENT2.  This is fairly easy to script and there are likely already scripts out there to do this for your entire database.  (Don’t have one in front of me, sorry.)

Best,

David
--
David Christensen
Senior Software and Database Engineer
End Point Corporation
david at endpoint.com
785-727-1171


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20200214/bec58d38/attachment.sig>