Configure Logical Replication in PostgreSQL

July 28, 2021

Configure Logical Replication in PostgreSQL

In the recent three blogs, we discuss briefly on Replication available options in PostgreSql Click here to read more. And How To Configure File-Based Log Shipping in PostgreSQL Click here to read more. How To Configure Streaming Replication in PostgreSQL Click here to read more. And In this blog, we will implement Logical Replication in PostgreSQL.

Logical replication is a method of replicating data objects and their changes, based upon their replication identity usually a primary key or unique key.

Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe.

PostgreSQL Logical Replication Process

Step 1: A snapshot of the publishing database is copied to the subscriber. This step is also called the table synchronization phase.

Step 2: After the copy is completed, subsequent changes made in the publisher node will be sent to the subscriber node. The changes will be sent as they happen in real-time. The changes will be applied in a commit order to ensure there is transactional consistency.

Below are the high-level steps we will follow to set up our replication.

1. Configure Publisher Server.
2. Create Publication in Publisher Server.
3. Configure Subscriber Server.
4. Create Subscription in Subscriber Server.
5. Test Logical Replication.
6. Monitor Logical Replication.

Below are setup details and the same will be used in this demonstration.

Sr No.	Hostname	IP	Role
1	test-machine01	192.168.114.177	Publisher
2	test-machine02	192.168.114.176	Subscriber

Step 1.1. Configure Publisher Server: For logical Replication wal_level parameter should be set to logical this change will require PostgreSQL Cluster restart. For demo purposes, we will create replication for Table: table1 from database: sample1.

[root@test-machine01 ~]# su - postgres
Last login: Wed Jun 16 17:44:29 +03 2021 on pts/2
-bash-4.2$
-bash-4.2$ psql
psql (13.1)
Type "help" for help.
postgres=# alter system set wal_level=logical;
ALTER SYSTEM
postgres=#


[root@test-machine01 ~]#
[root@test-machine01 ~]#systemctl start postgresql-13
[root@test-machine01 ~]#

postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('wal_level');
   name    | setting | unit
-----------+---------+------
 wal_level | logical |
(1 row)
postgres=# 

postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#

sample1=#
sample1=# CREATE TABLE table1(x int primary key);
CREATE TABLE
sample1=#
sample1=# INSERT INTO table1(x) SELECT y FROM generate_series(1, 100) a(y);
INSERT 0 100
sample1=#
sample1=#

Step 1.2. Edit pg_hba.conf authentication config file: We will edit file /var/lib/pgsql/13/data/pg_hba.conf and tell to PostgreSQL that we want to accept connections from a specific IP address or range. Please note we have added line host all all 192.168.114.0/24 scram-sha-256 in section: # IPv4 local connections. If you are not sure about your IP subnet you can use 0.0.0.0/0 which mean all IP in Test Env. Once changes are done in pg_hba.conf reload the changes with pg_ctl reload or SELECT pg_reload_conf().

[root@test-machine01 data]# pwd
/var/lib/pgsql/13/data
[root@test-machine01 data]# vi pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.114.0/24        scram-sha-256
 :wq!
[root@test-machine01 data]#
[root@test-machine01 ~]# su - postgres
Last login: Wed Jun 28 17:44:29 +03 2021 on pts/2
-bash-4.2$
-bash-4.2$ /usr/pgsql-13/bin/pg_ctl reload
server signaled
-bash-4.2$
 
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
 
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
 
postgres=#

Step 2. Create Publication in Publisher Server: Use command create publication to create publication for table table1. You can also use CREATE PUBLICATION publication_name FOR ALL TABLES to add all tables in publication. A published table must have a “replica identity” i.e. Primary or Uniqe key configured in order to be able to replicate UPDATE and DELETE operations, so that appropriate rows to update or delete can be identified on the subscriber side.

sample1=#
sample1=#
sample1=# create publication my_publication for table table1;
CREATE PUBLICATION
sample1=#
sample1=#

Step 3.1. Configure Subscriber Server: Connect to database sample1 and create only table1 structure.

postgres=#
postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#
sample1=#
sample1=# CREATE TABLE table1(x int primary key);
CREATE TABLE
sample1=#
sample1=#

Step 3.2. Create pgpass file in Subscriber Server: We are using pgpass file to avoid adding passwords in the subscriber command.
Format : hostname : port : database : username : password.

-bash-4.2$ hostname
test-machine02
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ vi .pgpass
test-machine01:5432:sample1:postgres:Root@1234
:wq!
-bash-4.2$ chmod 0600 .pgpass
-bash-4.2$
-bash-4.2$ export PGPASSFILE=/var/lib/pgsql/.pgpass
-bash-4.2$
-bash-4.2$ echo $PGPASSFILE
/var/lib/pgsql/.pgpass

-bash-4.2$  ls -l /var/lib/pgsql/.pgpass
-rw-------. 1 postgres postgres 47 Jul 26 23:14 /var/lib/pgsql/.pgpass
-bash-4.2$

Step 4. Create Subscription in Subscriber Server: Use command CREATE SUBSCRIPTION to add subscription, If you skip Step 3.2 you will receive the error “ERROR: could not connect to the publisher: fe_sendauth: no password supplied” Once the snapshot phase is done records will be added.

sample1=#
sample1=# CREATE SUBSCRIPTION my_subscription CONNECTION 'host=test-machine01 port=5432 dbname=sample1' PUBLICATION my_publication;
ERROR:  could not connect to the publisher: fe_sendauth: no password supplied
sample1=#


sample1=#
sample1=# CREATE SUBSCRIPTION my_subscription CONNECTION 'host=test-machine01 port=5432 dbname=sample1' PUBLICATION my_publication;
NOTICE:  created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION
sample1=#

sample1=#
sample1=# select count(*) from table1;
 count
-------
   100
(1 row)

sample1=#

-bash-4.2$ pwd
/var/lib/pgsql/13/data/log
-bash-4.2$ vi postgresql-Wed.log
2021-07-26 23:17:00.091 +03 [112747] LOG:  logical replication apply worker for subscription "my_subscription" has started
2021-07-26 23:17:00.108 +03 [112753] LOG:  logical replication table synchronization worker for subscription "my_subscription", table "table1" has started

Step 5. Test Logical Replication: Will test for all DML operations Insert, Update, and Delete.

Publisher DB

sample1=#
sample1=# INSERT INTO table1(x) SELECT y FROM generate_series(101, 200) a(y);
INSERT 0 100
sample1=#

Subscriber DB

sample1=# select count(*) from table1;
 count
-------
   200
(1 row)

Publisher DB

sample1=#
sample1=# delete from table1 where x between 1 and 100;
DELETE 100
sample1=#

Subscriber DB

sample1=#  select count(*) from table1;
 count
-------
   100
(1 row)

sample1=#

Publisher DB

sample1=# update table1 set x=303 where x=101;
UPDATE 1
sample1=#

Subscriber DB

sample1=# select * from table1 where x=303;
  x
-----
 303
(1 row)

sample1=# select * from table1 where x=101;
 x
---
(0 rows)

sample1=#

Step 6. Monitor Logical Replication: The monitoring on a publication node is similar to the monitoring of a physical replication master. The monitoring information about the subscription is visible in pg_stat_subscription.

Publisher DB

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 50859
usesysid         | 10
usename          | postgres
application_name | my_subscription
client_addr      | 192.168.114.176
client_hostname  |
client_port      | 35730
backend_start    | 2021-07-26 23:17:08.399956+03
backend_xmin     |
state            | streaming
sent_lsn         | 0/3D012488
write_lsn        | 0/3D012488
flush_lsn        | 0/3D012488
replay_lsn       | 0/3D012488
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-07-26 23:24:57.439699+03

postgres=#

postgres=#
postgres=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+----------------
slot_name           | my_subscription
plugin              | pgoutput
slot_type           | logical
datoid              | 16715
database            | sample1
temporary           | f
active              | t
active_pid          | 50859
xmin                |
catalog_xmin        | 1096
restart_lsn         | 0/3D012450
confirmed_flush_lsn | 0/3D012488
wal_status          | reserved
safe_wal_size       | 1090444152

postgres=#

Subscriber DB

postgres=# \x
Expanded display is on.
postgres=#
postgres=#
postgres=# select * from pg_subscription;
-[ RECORD 1 ]---+---------------------------------------------
oid             | 41963
subdbid         | 16715
subname         | my_subscription
subowner        | 10
subenabled      | t
subconninfo     | host=test-machine01 port=5432 dbname=sample1
subslotname     | my_subscription
subsynccommit   | off
subpublications | {my_publication}

postgres=#
postgres=# select * from pg_replication_origin_status;
-[ RECORD 1 ]-----------
local_id    | 1
external_id | pg_41963
remote_lsn  | 0/3D012368
local_lsn   | 0/3D017B40

postgres=#
postgres=# select * from pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid                 | 41963
subname               | my_subscription
pid                   | 112747
relid                 |
received_lsn          | 0/3D012488
last_msg_send_time    | 2021-07-26 23:27:26.052933+03
last_msg_receipt_time | 2021-07-26 23:27:17.743859+03
latest_end_lsn        | 0/3D012488
latest_end_time       | 2021-07-26 23:27:26.052933+03

postgres=#

Reference: PostgreSql Doc – Logical Replication: https://www.postgresql.org/docs/current/logical-replication.html

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Recent articles

POSTGRESQL, REPLICATION