PostgreSQL 16 Bi-Directional Replication Guide

- March 9, 2024

by Girish Phadke, DBA – Data Patrol Technologies

• Prerequisites
• Prepare Databases
• Create Tables
• Create Publications
• Create Subscriptions
• Test Data Insertion

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:

 

Type	Database	User	Address	Method
Host	All	All	192.168.2.48/32	Trust
Host	All	All	192.168.2.49/32	Trust

 

 

 

 

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

 

List of databases  - [ RECORD 1 ]-----+----------------------
Name	Clone
Owner	Postgres
Encoding	UTF8
Locale Provider	Libc
Collate	en_US.UTF-8
Ctype	en_US.UTF-8
ICU Locale
ICU Rules
Access privileges

 

Node2

postgres=# CREATE DATABASE clone;

postgres=# \l

 

List of databases  - [ RECORD 1 ]-----+----------------------
Name	Clone
Owner	Postgres
Encoding	UTF8
Local Provider	Libc
Collate	en_US.UTF-8
Ctype	en_US.UTF-8
ICU Locale
ICU Rules
Access privileges

 

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;

pubname	schemaname	tablename	attnames	rowfilter
pub0	public	t1	{id}
pub0	public	t2	{id}
pub0	public	t3	{id}
(3 rows)

 

-- Node2

clone=# CREATE PUBLICATION pub00 FOR ALL TABLES;

clone=# select * from pg_publication_tables;

pubname	schemaname	tablename	attnames	rowfilter
pub00	public	t1	{id}
pub00	public	t2	{id}
pub00	public	t3	{id}
(3 rows)

 

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);

 

[RECORD 1]
oid	16443
subdbid	16432
subskiplsn	0/0
subname	sub0
subowner	10
subenabled	t
subbinary	f
substream	f
subtwophasestate	d
subdisableonerr	f
subpasswordrequired	t
subrunasowner	f
subconninfo	host=192.168.2.49 dbname=clone port=5432
subslotname	sub0
subsynccommit	off
subpublications	{pub00}
suborigin	none

 

-- Node2

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

 

[RECORD 1]
oid	2458
subdbid	24576
subskiplsn	0/0
subname	sub00
subowner	10
subenabled	t
subbinary	f
substream	f
subtwophasestate	d
subdisableonerr	f
subpasswordrequired	t
subrunasowner	f
subconninfo	host=192.168.2.48 dbname=clone port=5432
subslotname	sub00
subsynccommit	off
subpublications	{pub00}
suborigin	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.