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.

Please provide the insert query that you try – Zahid Hassan Shaikot Feb 12 '19 at 09:27 — Zahid Hassan Shaikot, Feb 12 '19 at 09:27

score 3 · Answer 1 · answered Feb 12 '19 at 11:29

3

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.

answered Feb 12 '19 at 11:29

Laurenz Albe

209,280
17
206
263

Thanks :) I will use global sequence. Is it possible to set node1 to start from 1 and node2 from 1000000 ? – Feb 13 '19 at 09:47
I don't know much about BDR global sequences. But with normal sequences that would work fine. Better is to use even and odd numbers, though. – Laurenz Albe Feb 13 '19 at 09:52

Postgres - BDR replication - problem with auto_increment primary key

1 Answers1