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

[Michelle Sullivan]

This is a common problem when you point an application at a multimaster database when it wasn’t written for such.  There are a few ways around it..  the easiest is use a front end “load balancer” (can be software or hardware) and set “sticky” ...  this will ensure where a connection is made to a DB it will always use that DB until there is a failure of the connection when it will failover to the other DB.  Another way is to have it (if capable.. eg haproxy etc) only write to one server and read from either...

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.

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

> On 13 Apr 2021, at 05:48, Rodrigo Rodovalho <rodrigo.rodovalho at gmail.com> wrote:
> 
> Hi,
> 
> I have two Postgres instances running in a multi-master environment. It works but my application keeps getting violations of unique constraint in the primary keys when simultaneous changes are made in each of the DB instances at the same time. 
> 
> At this point I just would like to separate the origin of this issue: is it my web framework of choice  (Django) that keeps the last sequential primary key stored in some memory variable, or is it something in Bucardo that I misconfigured?
> 
> Users of Django or others frameworks, do you experience some issues like this? I'm also using Django REST.
> 
> Thanks in advance for any help!
> 
> Ps.: I'm also posting in some Django forums about this and promise to add here if anything helpful comes up. 
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://bucardo.org/mailman/listinfo/bucardo-general