postgresqlmulti-master-replicationpglogicalbucardopostgresql-15

Postgres 15 Multi-Master Replication


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


Solution

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