PostgreSQL logical replication enables sending a stream of data modifications from one server to another server. logical replication creates a line of the data modifications stream from the WAL and allows the data changes from individual tables to be replicated. The replication processes only one database at a time by sending row changes and committed transactions without allowing to send vacuum data, index changes, etc. We can selectively choose some tables within a database to put into streaming which makes logical replication more efficient on bandwidth. The downside of logical streaming is that DDL isn't handled automatically. We have to update the table definitions in sync between source and destination target ourselves, which might be complicated to get this right.

In this article, we are going to do an experiment of logical streaming replication between two databases within the same PostgreSQL 14 server. However, we still can follow the steps for the experiment in different servers scenario.

Prerequisite: You need to have the PostgreSQL database server up and running, we will provide a few articles which are related to this article
- How to Install PostgreSQL 14 on Ubuntu 20.04 from Source
- Installing PgBouncer as Connection Pool for PostgreSQL 14
- Setting Up PostgreSQL 14 Database Cluster Using Google Cloud Engine

STEP 1: Checking WAL_LEVEL

In order to configure logical streaming in the database server, we have to change the PostgreSQL configuration file postgresql.conf with parameter wal_level to logical Using the below command to check wal_level status, if it's not set to logical we have to change and then restart the PostgreSQL service.

sethathay@instance-1:~$ sudo su - postgres
postgres@instance-1:~$ psql
psql (14.0)
Type "help" for help.
postgres=# show wal_level;
 wal_level 
-----------
 logical
(1 row)
postgres=# 

STEP 2: Create Source Database and Tables

In this step, you have to create a database schema and tables that you want to stream data to the destination. In this experiment, we are going to create the database  sourcedb schema core and two tables tb_students and tb_subjects. Follow the command below or you can create by yourself

postgres@instance-1:~$ psql
psql (14.0)
Type "help" for help.
postgres=# create database sourcedb;
CREATE DATABASE
postgres=# \c sourcedb;
You are now connected to database "sourcedb" as user "postgres".
sourcedb=# create schema core;
CREATE SCHEMA

sourcedb=# CREATE TABLE "core"."tb_students" (
sourcedb(#   "id" serial8 NOT NULL,
sourcedb(#   "card_no" varchar(255),
sourcedb(#   "full_name" varchar(500),
sourcedb(#   "gender" varchar(1),
sourcedb(#   "address" text,
sourcedb(#   CONSTRAINT "tb_students_pkey" PRIMARY KEY ("id")
sourcedb(# );
CREATE TABLE

sourcedb=# CREATE TABLE "core"."tb_subjects" (
sourcedb(#   "id" serial8 NOT NULL,
sourcedb(#   "name" varchar(500),
sourcedb(#   "credit" int8,
sourcedb(#   "outline" text,
sourcedb(#   CONSTRAINT "tb_subjects_pkey" PRIMARY KEY ("id")
sourcedb(# );
CREATE TABLE

STEP 3: Create Replication Role

We have to create a role with replication permission in order to transfer data between two regions (source and destination) of the logical replication technique. Let's name it as rep_role with password rep$$169#123

sourcedb=# CREATE ROLE rep_role WITH REPLICATION LOGIN PASSWORD 'rep$$169#123';
CREATE ROLE

Next, grant permission of replication role (rep_role) to the database sourcedb and schema core

sourcedb=# GRANT ALL PRIVILEGES ON DATABASE sourcedb TO rep_role; 
GRANT
sourcedb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA core TO rep_role;
GRANT
sourcedb=# GRANT USAGE ON SCHEMA core TO rep_role;
GRANT

Next, allow replication role rep_role in pg_hba. This help to enable opening streaming slot of the sourcedb

#ip address of sourcedb
host    sourcedb        rep_role        xx.xx.xx.xx/32           md5
host    replication     rep_role        xx.xx.xx.xx/32           md5

Restart the service of PostgreSQL or do the reload pg_hba command below

postgres@instance-1:/data01$ vi pg_hba.conf 
postgres@instance-1:/data01$ psql
psql (14.0)
Type "help" for help.
postgres=# select pg_reload_conf(); 
 pg_reload_conf 
----------------
 t
(1 row)

Test connection from the destination server to source server using replication role that we just created (But in our case it's the same server)

postgres@instance-1:/data01$ psql -h xx.xx.xx.xx -U rep_role -d sourcedb
Password for user rep_role: 
psql (14.0)
Type "help" for help.
sourcedb=> 

STEP 4: Create Destination Database and Tables

Now let create destination database schema and tables as respectively targetdb, core, tb_students and tb_subjects

postgres@instance-1:~$ psql
psql (14.0)
Type "help" for help.
postgres=# create database targetdb;
CREATE DATABASE
postgres=# \c targetdb 
You are now connected to database "targetdb" as user "postgres".
targetdb=# create schema core;
CREATE SCHEMA

targetdb=# CREATE TABLE "core"."tb_students" (
targetdb(# "id" serial8 NOT NULL,
targetdb(# "card_no" varchar(255),
targetdb(# "full_name" varchar(500),
targetdb(# "gender" varchar(1),
targetdb(# "address" text,
targetdb(# CONSTRAINT "tb_students_pkey" PRIMARY KEY ("id")
targetdb(# );
CREATE TABLE

targetdb=# CREATE TABLE "core"."tb_subjects" (
targetdb(# "id" serial8 NOT NULL,
targetdb(# "name" varchar(500),
targetdb(# "credit" int8,
targetdb(# "outline" text,
targetdb(# CONSTRAINT "tb_subjects_pkey" PRIMARY KEY ("id")
targetdb(# );
CREATE TABLE

STEP 5: Create Publication

Now let create a new publication sourcedb and add the two tables into the publication. Let's call the publication as logical_pub

sethathay@instance-1:~$ sudo su - postgres
postgres@instance-1:~$ psql
psql (14.0)
Type "help" for help.
postgres=# \c sourcedb 
You are now connected to database "sourcedb" as user "postgres".
sourcedb=# CREATE PUBLICATION logical_pub;
CREATE PUBLICATION
sourcedb=# ALTER PUBLICATION logical_pub ADD TABLE core.tb_students;
ALTER PUBLICATION
sourcedb=# ALTER PUBLICATION logical_pub ADD TABLE core.tb_subjects;
ALTER PUBLICATION

Since we use replication on the same server, there is an additional task to configure. We have to manually create a replication slot using pg_create_logical_replication_slot that also mentioned in PostgreSQL documentation here (Section Note).

postgres@instance-1:/data01$ psql
psql (14.0)
Type "help" for help.
postgres=# \c sourcedb 
You are now connected to database "sourcedb" as user "postgres".
sourcedb=# SELECT * FROM pg_create_logical_replication_slot('logical_sub', 'pgoutput');
  slot_name  |    lsn    
-------------+-----------
 logical_sub | 0/70B17B8
(1 row)

STEP 6: Create Subscription

We will create a new subscription called logical_sub which will replicate the data from the publication we just created above step. Please note that we use the option create_slot = false since the replication is happening on the same server. If you configure in a different server there is no need to include this option.

postgres@instance-1:/data01$ psql
psql (14.0)
Type "help" for help.
postgres=# \c targetdb 
You are now connected to database "targetdb" as user "postgres".
targetdb=# CREATE SUBSCRIPTION logical_sub CONNECTION 'host=10.148.0.2 port=5432 password=rep$$169#123 user=rep_role d
bname=sourcedb' PUBLICATION logical_pub WITH (create_slot = false);
CREATE SUBSCRIPTION

STEP 7: Testing with INSERT and UPDATE Operation

Now let do the experiment by using insert and update operations on sourcedb and check the result in targetdb

- INSERT OPERATION

postgres@instance-1:/data01$ psql
psql (14.0)
Type "help" for help.
postgres=# \c sourcedb 
You are now connected to database "sourcedb" as user "postgres".
sourcedb=# insert into core.tb_students(card_no,full_name,gender,address) values('001','Setha','M','Phnom Penh');
INSERT 0 1
sourcedb=# insert into core.tb_students(card_no,full_name,gender,address) values('002','Sekada','F','Phnom Penh');
INSERT 0 1

Checking the result in taregetdb

postgres@instance-1:/data01$ psql
psql (14.0)
Type "help" for help.
postgres=# \c targetdb 
You are now connected to database "targetdb" as user "postgres".
targetdb=# select * from core.tb_students;
 id | card_no | full_name | gender |  address   
----+---------+-----------+--------+------------
  1 | 001     | Setha     | M      | Phnom Penh
  2 | 002     | Sekada    | F      | Phnom Penh
(2 rows)
targetdb=# 

- UPDATE OPERATION

postgres@instance-1:/data01$ psql
psql (14.0)
Type "help" for help.
postgres=# \c sourcedb 
You are now connected to database "sourcedb" as user "postgres".
sourcedb=# update core.tb_students SET full_name='Setha Thay' WHERE card_no='001';
UPDATE 1
sourcedb=#

Checking the result in taregetdb

postgres@instance-1:/data01$ psql
psql (14.0)
Type "help" for help.
postgres=# \c targetdb 
You are now connected to database "targetdb" as user "postgres".
targetdb=# select * from core.tb_students where card_no='001';
 id | card_no | full_name  | gender |  address   
----+---------+------------+--------+------------
  1 | 001     | Setha Thay | M      | Phnom Penh
(1 row)
targetdb=#

THANK YOU, Find Us @
Facebook
Telegram
Twitter
LinkedIn