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 <[email protected]> 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 _______________________________________________ Bucardo-general mailing list [email protected] https://bucardo.org/mailman/listinfo/bucardo-general
