Postgres - BDR replication - problem with auto_increment primary key

Question

I have a cluster of Postgres BDR that has 2 nodes (multi master replication). I've created a table with auto increment primary key as follow:

create table t1 (id serial primary key, name text);

I've added some values on that table from node1:

insert into t1(name) values('foo');

and now when I try to add another value into this table from node2, I receive that error:

ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (id)=(1) already exists.

Answer 1

Your problem is that both databases have their own sequences, which are local to the database and not replicated. That causes the replication conflicts.

You can do the following:

• Use a BDR-proprietary global sequence.
• Use a sequence with START 1 INCREMENT2 in one database and START 2 INCREMENT 2 on the other one, so that sequence value cannot collide.
• Use UUIDs that are globally unique.