0

I've configured two RHEL machines with redundancy configuration (master- master setup) with below software versions.

  • Database: PostgreSQL 9.5
  • Bucardo: Bucardo 5.4

Database replication goes smoothly till some point of time, After that replication stops all of sudden. I could see below kind of statements in bucardo log.

*KID (database_sync) Conflicts for public.interface: 4
KID (database_sync) Conflicts have been resolved
KID (database_sync) Warning! Aborting due to exception for public.interface:? Error was 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.
KID (database_sync) Kid has died, error is: 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
DBI::db=HASH(0x2e1a708)->disconnect invalidates 9 active statement handles (either destroy statement handles or call finish on them before disconnecting) at /usr/share/perl5/vendor_perl/Bucardo.pm line 2756.
DBI::db=HASH(0x2e1a828)->disconnect invalidates 9 active statement handles (either destroy statement handles or call finish on them before disconnecting) at /usr/share/perl5/vendor_perl/Bucardo.pm line 2756.
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 MCP Starting check_sync_health*

Below are the steps I followed for creation of syncs.

bucardo add database bucardo add table all --db="$database"_node1    --herd="$database"_herd 
bucardo add sequence all --db="$database"_node1 --herd="$database"_herd 
bucardo add dbgoup "$database"_group 
bucardo add dbgroup "$database"_group    "$database"_node1:source 
bucardo add dbgroup "$database"_group    "$database"_node2:source 
bucardo add sync "$database"_sync    herd="$database_herd"_herd dbs="$database"_group    conflict_strategy=bucardo_latest onetimecopy=2 stayalive=1    kidsalive=1 autokick=1 systemctl enable bucardo.service

Could you please help me with your findings for the issue. Can my sequences in my db responsible for creation of duplicate entries in database?

ogirginc
  • 4,948
  • 3
  • 31
  • 45
Prasad
  • 11
  • 4
  • Did the master experience crashes at any point? I've had similar issues (duplicate entries in a table with primary key) after the database crashed due to running out of disk space. – Julia Leder Feb 14 '20 at 16:18
  • I need to check on crashes then, How did you resolve it ? – Prasad Feb 20 '20 at 07:08
  • I had to do a dump and recreate the database from the dump, because of multiple propblems througout the DB. May be worth attempting to recreate the table in question to see if it helps. – Julia Leder Feb 20 '20 at 14:18

1 Answers1

0

When user a multi source setup with Bucardo sequences must not be replicated, or conflicts will arise as you already experimented.

  • Remove the sequences from the sync. I am writing from memory and not testing it, so take care
bucardo remove sequence all
bucardo validate all
bucardo reload
  • Alter the sequence in each source to avoid create the same primary key in two databases. Check this answer for a couple of options, like generate even, odd numbers; generate numbers like 1, 11, 21 in db1, 2, 12, 22 in db2, ...; or using UUID.
Francisco Puga
  • 23,869
  • 5
  • 48
  • 64