CALL

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

WhatsAppImage20240318at09.11.5532d36850.jpg

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

On Node1:

clone=# select * from t1;                                                                                            

  id

------

 1001

(1 row)

 

On Node2: 

clone=# select * from t1;                                                                                      

  id

------

 1001

(1 row)

 

-- Node2

postgres=# c clone

clone=# INSERT INTO t1 VALUES (1002);

INSERT 0 1

 

On Node2:

clone=# select * from t1;                                                                            

  id

------

 1001

 1002

(2 rows)

 

 On Node1: 

clone=# select * from t1;  

  id

------

 1001

 1002

(2 rows)

 

 

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.

 

 

Leave a comment


Comments...

No Comment Found
Call
Enquiry