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

[Michelle Sullivan]

Personally I hate that method.. very hacky and an absolute nightmare if you need to expand the cluster in the future..  I did it here slightly different ..  added a “serverid” column (that is defined in the postgresql.conf) and the primary key is sequence+serverid...  but again.. all good as I wrote the software :)

Michelle Sullivan
http://www.mhix.org/
Hallowed are those that walk in unison.

> On 13 Apr 2021, at 09:40, Joshua Tolley <josh at endpoint.com> wrote:
> 
>> 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