Re: Advice for geographically dispersed multi master

On Thu, 2019-10-03 at 19:05 +1000, Nikolai Lusan wrote:
> So I am looking to move my current setup of 3 separate, individual,
> postgres 11 servers into a multi master cluster.
>
> I have read the various replication and clustering documentation for
> postgresql 11, and it looks like what I want is "Synchronous
> Multimaster Replication". The organisation I am doing this for does
> not have the money to throw at a commercial solution like BRD

Don't do it then.

As far as I know, there are only closed source solutions for this.

95% of all requests for multi-master replication come from people
who know little about databases and just want "a couple of databases
that all hold the same data and magically reconcile with each other".

Any multi-master solution will require the application to be
written with special awareness of a multi-master database.

For example, there will have to be special considerations with
auto-generated primary keys, and you will have to avoid certain
operations altogether.

Imagine this situation:

DB 1 DB 2

DELETE FROM tab UPDATE tab
WHERE id = 42 SET id = 1 WHERE id = 42

If these statements are executed at the same time on two
databases and then replicated, DB 2 will end up with one row
more in the table than DB 1.

Try to find a solution with a single master database.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com