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
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"