In this guide, we will walk through the steps to set up bi-directional replication between two PostgreSQL nodes. Bi-directional replication allows data changes on both nodes to be synchronised, providing redundancy and high availability.

Prerequisites:

Node1 (prod):

• IP: 192.168.2.48

Node2 (replica):

• IP: 192.168.2.49

1. Below are the parameters we need to set in the 'PostgreSQL. conf' file for Bi-Directional replication replication on both nodes. 

   wal_level=logical

   listen_addresses=’*’

2. Below are the entries we need to add on both sides in pg_hba.conf for the communication for between nodes:

Step 1: Prepare Databases

For bi-directional replication to work, we must create a separate database 'clone' on both nodes. All tables will be created inside this database.

--  Node1

postgres=# CREATE DATABASE clone;

postgres=# \l

Node2

postgres=# CREATE DATABASE clone;

postgres=# \l

Step 2: Create Tables

Create identical tables on both nodes within the 'clone' database.

-- Node1

postgres=# \c clone

You are now connected to database "clone" as user "postgres".

clone=# CREATE TABLE t1 (id int);

clone=# CREATE TABLE t2 (id int);

clone=# CREATE TABLE t3 (id int);

-- Node2

postgres=# \c clone

You are now connected to database "clone" as user "postgres".

clone=# CREATE TABLE t1 (id int);

clone=# CREATE TABLE t2 (id int);

clone=# CREATE TABLE t3 (id int);

Step 3: Create Publications

Create publications for all tables in the current database on both nodes.

-- Node1

clone=# CREATE PUBLICATION pub0 FOR ALL TABLES;

clone=# select * from pg_publication_tables;

-- Node2

clone=# CREATE PUBLICATION pub00 FOR ALL TABLES;

clone=# select * from pg_publication_tables;

Step 4: Create Subscriptions

Create subscriptions for the target publications on both nodes.

-- Node1

clone=# CREATE SUBSCRIPTION sub0 CONNECTION 'host=192.168.2.49 dbname=clone port=5432' PUBLICATION pub00 WITH (copy_data = false, origin = none);

-- Node2

clone=# CREATE SUBSCRIPTION sub00 CONNECTION 'host=192.168.2.48 dbname=clone port=5432' PUBLICATION pub0 WITH (copy_data = false, origin = none);

Step 5: Test Data Insertion

Now, let's insert data into the same table on both nodes and ensure synchronization.

-- Node1

postgres=# \c clone

clone=# INSERT INTO t1 VALUES (1001);

INSERT 0 1

-- Node2

postgres=# \c clone

clone=# INSERT INTO t1 VALUES (1002);

INSERT 0 1

CONCLUSION:

Bi-directional replication in PostgreSQL 16 proves to be a robust solution for maintaining synchronized data changes across multiple nodes, ensuring redundancy and high availability in a real-time scenario.