How to logical sync postgresql database mutually

Postgresql has a sync way called logical duplication. I you make multiple publisher as both publisher and subscriber of each other, you can sync them mutually. Though it did not work well in a full mesh network, maybe due to network factor and schema of database. In this post, I will use two nodes as example. You should make sure all node run postgresql with same version. Here I use Postgresql 17.

Environment

Both Node A and Node B
Ubuntu 22.04.4 LTS
psql (PostgreSQL) 17.5 (Ubuntu 17.5-1.pgdg22.04+1)

How to sync mutually using logical replication

Update Postgresql configure

Setting	Recommended value	Why
wal_level	logical	Enables logical decoding
max_replication_slots	≥ number of remote peers	Each subscription uses one slot
max_logical_replication_workers, max_wal_senders	tune ≥ number of slots	Provide workers for apply/send
track_commit_timestamp	on (optional)	Helps conflict-resolution tools
shared_preload_libraries	include pglogical only if you want its extra CDR features

Make sure same schema in database

Before we start to sync, it is recommended to make both database have same schema. You can do this by creating two empty database or migrate your exsited database.

Create publisher in both nodes

su - postgres
psql -U postgres -d test_db

ALTER ROLE test WITH REPLICATION;
CREATE PUBLICATION test_pub FOR ALL TABLES;

Some helpful sql command

#see current publisher
\dRp[+]
#delete publisher
DROP PUBLICATION test_pub;

Create subscriber in both nodes

su - postgres
psql -U postgres -d test_db

CREATE SUBSCRIPTION sub_A2B
  CONNECTION 'host=node.a port=5432 dbname=test_db
              user=test password=your_password sslmode=require'
  PUBLICATION test_pub
  WITH (origin = none, copy_data = false);

Some helpful sql command

#see current subscriber
\dRs
#delete subscriber
ALTER SUBSCRIPTION sub_A2B DISABLE;
ALTER SUBSCRIPTION sub_A2B SET (slot_name = NONE);
DROP SUBSCRIPTION sub_A2B;