Postgres 15 Multi-Master Replication

Question

For the last few days I've been trying to implement a working multi-master replication using postgresql however, unsuccessfull. I came across multiple tools, none of which as worked.

I think I'm getting close to a solution but I'm hitting a wall.

Right now I'm using pg_logical. It seems that pg_logical alone doesn't allow multi-master but only master-slave. I saw people using the plugin "BDR", but I can't find any way of installing it and from what I read it is no longer maintained and has moved onto a paid on-cloud solution (which I cannot use in my scenario)

If anyone has a solution to replicate, multi-master, multi-servers (without access to the internet, only during installation)

I've also tried bucardo, but, once again, unsuccessful.

Thank you all

Answer 1

You could try logical replication row filtering to replicate table data bidirectionally.

https://www.postgresql.org/docs/15/logical-replication-row-filter.html

Give each node a unique id and create a publication that's limited to its unique id.

That would be ideal for read only tables where you only INSERT, i.e., audit logs. But would have limited uses for other operations.

--On host=192.168.2.2
--Set WAL level to logical
--Execute in # order,
--#1
CREATE TABLE service.message_log
(
    id bigint NOT NULL,
    server_id smallint NOT NULL,
    message bytea,
    CONSTRAINT message_log_pkey PRIMARY KEY(id, server_id)
);

--#2
CREATE PUBLICATION message_log_host2002 FOR TABLE service.message_log WHERE (server_id = 2002);

--#5
CREATE SUBSCRIPTION message_log_host1002 CONNECTION 'host=192.168.1.2 port=5435 user=repuser dbname=testdb' PUBLICATION message_log_host1002;

--#8
INSERT into service.message_log (id, server_id, message) VALUES (1, 2002, 'Hello from 192.168.2.2');
INSERT into service.message_log (id, server_id, message) VALUES (2, 2002, 'Test from 192.168.2.2');

--On host=192.168.1.2
--Set WAL level to logical
--#3
CREATE TABLE service.message_log
(
    id bigint NOT NULL,
    server_id smallint NOT NULL,
    message bytea,
    CONSTRAINT message_log_pkey PRIMARY KEY(id, server_id)
);

--#4
CREATE PUBLICATION message_log_host1002 FOR TABLE service.message_log WHERE (server_id = 1002);

--#6
CREATE SUBSCRIPTION message_log_host2002 CONNECTION 'host=192.168.2.2 port=5435 user=repuser dbname=testdb' PUBLICATION message_log_host2002;

--#7 
INSERT into service.message_log (id, server_id, message) VALUES (1, 1002, 'Hello from 192.168.1.2');
INSERT into service.message_log (id, server_id, message) VALUES (2, 1002, 'Test from 192.168.1.2');

To verify

select id, server_id, convert_from(message, 'UTF8') from service.message_log 

Output from 192.168.1.2: 
1   1002    "Hello from 192.168.1.2"
2   1002    "Test from 192.168.1.2"
1   2002    "Hello from 192.168.2.2"
2   2002    "Test from 192.168.2.2"

Output from 192.168.2.2:
1   1002    "Hello from 192.168.1.2"
2   1002    "Test from 192.168.1.2"
1   2002    "Hello from 192.168.2.2"
2   2002    "Test from 192.168.2.2"

Answer 2

You could try to use the postgresql spock extension. I've done multi-master logical replication with postgresql using custom triggers and UUID primary keys.

If your database relies on sequences for primary keys then the Spock extension above is a better fit.