[Bucardo-general] Primary Key Collisions in Multi-Master Environment

[Joshua Tolley]

On Tue, Apr 13, 2021 at 08:30:29AM +1000, Michelle Sullivan wrote:
> The hard way is fix the software...  don’t rely on sequences for primary key
> generation unless you can ensure they are globally unique...  easy if you
> wrote the software yourself, not so easy if it’s someone else’s.

You can see if your primary keys are based on a sequence like this, in psql:

mydb=# \d mydb
                                           Table "public.mytable"
       Column  |           Type  | Collation | Nullable | Default
---------------+-----------------+-----------+----------+------------------------------------
 id            | integer         |           | not null | nextval('my_id_seq'::regclass)
...
 
Here the id column gets its default value from a sequence. It's possible to
define (or redefine) that sequence differently on each of your master servers,
so that each server generates primary keys taken from a unique set of possible
values. The easiest way I've seen to do this is with the INCREMENT BY option.
If I had two servers in a multi-master configuration, I'd define the sequence
like this on the first server:

CREATE SEQUENCE my_id_seq INCREMENT BY 2 START WITH 1;

This means it will generate ID values with only odd numbers: 1, 3, 5, etc. On
the other server, I'd do this:

CREATE SEQUENCE my_id_seq INCREMENT BY 2 START WITH 2;

This server will use only even numbers. Whereas before, the servers might
create records with the same ID under certain conditions, now they can't.

Some people might quite reasonably consider this a hacky solution. For
instance, it creates differences between the schema on one server compared to
the other. It may make maintenance more difficult. Another option might be to
use UUID values for primary keys. But as has already been noted, you will
probably need to do more than just depend on Bucardo to handle it for you.

-- 
Joshua Tolley
End Point Corporation
801 987 0252