Setup Logical Replication using pglogical in PostgreSQL

August 25, 2021
()

Setup Logical replication using pglogical in PostgreSQL

In the recent two blogs, we discuss built-in options like Streaming replication Click here to read more and Logical replication Click here to read more. In this blog, we will use pglogical extension to setup logical streaming replication for PostgreSQL. 

The pglogical extension provides logical streaming replication for PostgreSQL, using a publish/subscribe module. It is based on technology developed as part of the BDR (Bi-Directional Replication) Project. pglogical is fully integrated with PostgreSQL. This alternative to physical replication is a highly efficient method of replicating data using a publish/subscribe model for selective replication. pglogical forms the foundation for BDR – which runs as an extension on top of it – providing the essential logical replication and other technologies for its features.


Use cases supported by pglogical are:

  • Upgrades between major versions
  • Full database replication
  • Selective replication of sets of tables using replication sets
  • Selective replication of table rows at either publisher or subscriber side (row_filter)
  • Selective replication of table columns at publisher side
  • Data gather/merge from multiple upstream servers


Below are the high-level steps we will follow to set up our replication.
1. Install pglogical rpm on both nodes
2. Edit postgresql.conf & pg_hba.conf on both nodes
3. Create pglogical extension on both nodes
4. Configure Publisher Node
5. Configure Subscriber Node
6. Test replication
7. Monitor replication


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

Sr No.HostnameIPRole
1test-machine01192.168.114.177Publisher/Provider Node
2test-machine02192.168.114.176Subscriber Node


Step 1. Install pglogical rpm on both nodes: The pglogical rpm must be installed on both publisher and subscriber.


Publisher Node 

[root@test-machine01 ~]# yum whatprovides pglogical_13
Loaded plugins: langpacks, ulninfo
pglogical_13-2.3.4-1.rhel7.x86_64 : Logical Replication extension for PostgreSQ
Repo        : pgdg13

pglogical_13-2.4.0-1.rhel7.x86_64 : Logical Replication extension for PostgreSQL
Repo        : pgdg13


[root@test-machine01 ~]# yum install pglogical_13-2.4.0-1.rhel7.x86_64
--> Running transaction check
---> Package pglogical_13.x86_64 0:2.4.0-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                         Arch                                      Version                                            Repository                                 Size
==============================================================================================================================================================================================
Installing:
 pglogical_13                                    x86_64                                    2.4.0-1.rhel7                                      pgdg13                                    141 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package

Total download size: 141 k
Installed size: 493 k
Is this ok [y/d/N]: y
Downloading packages:
pglogical_13-2.4.0-1.rhel7.x86_64.rpm                                                                                                                                  | 141 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pglogical_13-2.4.0-1.rhel7.x86_64                                                                                                                                          1/1
  Verifying  : pglogical_13-2.4.0-1.rhel7.x86_64                                                                                                                                          1/1

Installed:
  pglogical_13.x86_64 0:2.4.0-1.rhel7

Complete!
[root@test-machine01 ~]#


Subscriber Node 

[root@test-machine02 ~]# yum whatprovides pglogical_13
Loaded plugins: langpacks, ulninfo
pglogical_13-2.3.4-1.rhel7.x86_64 : Logical Replication extension for PostgreSQ
Repo        : pgdg13
pglogical_13-2.4.0-1.rhel7.x86_64 : Logical Replication extension for PostgreSQL
Repo        : pgdg13


[root@test-machine02 ~]# yum install pglogical_13-2.4.0-1.rhel7.x86_64
Loaded plugins: langpacks, ulninfo
--> Running transaction check
---> Package pglogical_13.x86_64 0:2.4.0-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                         Arch                                      Version                                            Repository                                 Size
==============================================================================================================================================================================================
Installing:
 pglogical_13                                    x86_64                                    2.4.0-1.rhel7                                      pgdg13                                    141 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package

Total download size: 141 k
Installed size: 493 k
Is this ok [y/d/N]: y
Downloading packages:
pglogical_13-2.4.0-1.rhel7.x86_64.rpm                                                                                                                                  | 141 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pglogical_13-2.4.0-1.rhel7.x86_64                                                                                                                                          1/1
  Verifying  : pglogical_13-2.4.0-1.rhel7.x86_64                                                                                                                                          1/1

Installed:
  pglogical_13.x86_64 0:2.4.0-1.rhel7

Complete!
[root@test-machine02 ~]#


Step 2. Edit postgresql.conf & pg_hba.conf on both nodes: PostgreSQL server has to be properly configured to support logical decoding. Open postgresql.conf file and edit below parameters in both publisher and subscriber node. Open pg_hba.conf and add lines “host all all 192.168.114.0/24 scram-sha-256” & “host replication all 192.168.114.0/24 scram-sha-256” to allow normal and replication connection to PostgreSql cluster.


Publisher Node 

[postgres@test-machine01]psql
psql (13.1)
Type "help" for help.
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');
           name           | setting | unit
--------------------------+---------+------
 max_replication_slots    | 10      |
 max_wal_senders          | 10      |
 max_worker_processes     | 8       |
 shared_preload_libraries |         |
 track_commit_timestamp   | off     |
 wal_level                | replica |
(6 rows)
postgres=# 

[postgres@test-machine01]pwd
/var/lib/pgsql/13/data
[postgres@test-machine01]vi postgresql.conf
max_worker_processes = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = 'on'
wal_level = 'logical'
:wq!
[postgres@test-machine01]

[postgres@test-machine01]pwd
/var/lib/pgsql/13/data
[postgres@test-machine01]cat 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
# replication privilege.
host    replication     all             192.168.114.0/24        scram-sha-256
[postgres@test-machine01]


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

postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');
           name           |  setting  | unit
--------------------------+-----------+------
 max_replication_slots    | 10        |
 max_wal_senders          | 10        |
 max_worker_processes     | 10        |
 shared_preload_libraries | pglogical |
 track_commit_timestamp   | on        |
 wal_level                | logical   |
(6 rows)
postgres=#


Subscriber Node 

[postgres@test-machine02]psql
psql (13.2)
Type "help" for help.

postgres=# select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');
           name           | setting | unit
--------------------------+---------+------
 max_replication_slots    | 10      |
 max_wal_senders          | 10      |
 max_worker_processes     | 8       |
 shared_preload_libraries |         |
 track_commit_timestamp   | off     |
 wal_level                | replica |
(6 rows)
postgres=#

[postgres@test-machine02]pwd
/var/lib/pgsql/13/data
[postgres@test-machine02]vi postgresql.conf
max_worker_processes = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = 'on'
wal_level = 'logical'
:wq!
[postgres@test-machine02]

[postgres@test-machine02]pwd
/var/lib/pgsql/13/data
[postgres@test-machine02]cat 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
# replication privilege.
host    replication     all             192.168.114.0/24        scram-sha-256
[postgres@test-machine02]

[root@test-machine02 ~]#
[root@test-machine02 ~]# systemctl restart postgresql-13
[root@test-machine02 ~]#

postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');
           name           |  setting  | unit
--------------------------+-----------+------
 max_replication_slots    | 10        |
 max_wal_senders          | 10        |
 max_worker_processes     | 10        |
 shared_preload_libraries | pglogical |
 track_commit_timestamp   | on        |
 wal_level                | logical   |
(6 rows)
postgres=#


Step 3. Create pglogical extension on both nodes: pglogical works on a per-database level, not whole server level like physical streaming replication. So if you are planning to configure Logical replication using pglogical you need to install pglogical extension on all individual databases separately. We will create separate user appuser and db appdb for your demo. Please note after creating appdb we need to connect to appdb database and execute all configuration steps on appdb database.


Publisher Node 

postgres=# create role appuser with LOGIN SUPERUSER PASSWORD 'Root@1234';
CREATE ROLE
postgres=# CREATE DATABASE appdb WITH OWNER appuser;
CREATE DATABASE
postgres=#
postgres=# \connect appdb
You are now connected to database "appdb" as user "postgres".
appdb=#
appdb=# CREATE EXTENSION pglogical;
CREATE EXTENSION
appdb=#
appdb=# \dx
                   List of installed extensions
   Name    | Version |   Schema   |          Description
-----------+---------+------------+--------------------------------
 pglogical | 2.4.0   | pglogical  | PostgreSQL Logical Replication
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

appdb=#


Subscriber Node 

postgres=# create role appuser with LOGIN SUPERUSER PASSWORD 'Root@1234';
CREATE ROLE
postgres=#  CREATE DATABASE appdb WITH OWNER appuser;
CREATE DATABASE
postgres=#
postgres=#  \connect appdb
You are now connected to database "appdb" as user "postgres".
appdb=#
appdb=#
appdb=#
appdb=# CREATE EXTENSION pglogical;
CREATE EXTENSION
appdb=# \dx
                   List of installed extensions
   Name    | Version |   Schema   |          Description
-----------+---------+------------+--------------------------------
 pglogical | 2.4.0   | pglogical  | PostgreSQL Logical Replication
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

appdb=#


Step 4. Configure Publisher Node: Create the provider node, Add my_table_test1 table in the public schema to the replication_set replication set.

appdb=#
appdb=# CREATE TABLE my_table_test1 (col1 serial primary key, col2 varchar);
CREATE TABLE

appdb=# INSERT INTO my_table_test1 VALUES (1, 'this is row 1'), (2, 'this is row 2'), (3, 'this is row 3'), (4, 'this is row 4'), (5, 'this is row 5');
INSERT 0 5
appdb=#

appdb=# SELECT pglogical.create_node(
appdb=# node_name := 'provider1',
appdb=# dsn := 'host=test-machine01 port=5432 dbname=appdb user=appuser');
 create_node
-------------
  2976894835
(1 row)

appdb=# select pglogical.create_replication_set('replication_set');
 create_replication_set
------------------------
              726052966
(1 row)

appdb=#  select pglogical.replication_set_add_table(
appdb=#  set_name := 'replication_set',
appdb=#  relation := 'my_table_test1',
appdb=#  synchronize_data := true);
 replication_set_add_table
---------------------------
 t
(1 row)

appdb=#


Step 5. Configure Subscriber Node: Create .pgpass and add the below entry in it. We need .pgpass file to avoid passing passwords in the below subscription command. Create an only structure of table my_table_test1. Once the provider node is setup, subscribers can be subscribed to it. First, the subscriber node must be created. And finally, on the subscriber node, you can create the subscription which will start the synchronization and replication process in the background. Use the below command to verify replication is running and once sync is complete you will notice all 5 records created in the provider and copied to subscriber tables as well.

[postgres@test-machine02]pwd
/var/lib/pgsql
[postgres@test-machine02]vi .pgpass
test-machine01:5432:appdb:appuser:Root@1234
test-machine01:5432:replication:appuser:Root@1234
test-machine02:5432:appdb:appuser:Root@1234
test-machine02:5432:replication:appuser:Root@1234
:wq!
[postgres@test-machine02]

appdb=#
appdb=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

appdb=# CREATE TABLE my_table_test1 (col1 serial primary key, col2 varchar);
CREATE TABLE
appdb=#

appdb=#  select pglogical.create_node(
appdb=#  node_name := 'subscriber1',
appdb=#  dsn := 'host=test-machine02 port=5432 dbname=appdb user=appuser');
 create_node
-------------
   330520249
(1 row)
appdb=#

appdb=#  select pglogical.create_subscription(
appdb=#  subscription_name := 'subscription1',
appdb=#  replication_sets := array['replication_set'],
appdb=#  provider_dsn := 'host=test-machine01 port=5432 dbname=appdb user=appuser' );
 create_subscription
---------------------
          1763399739
(1 row)
appdb=#

appdb=#
appdb=# select *  FROM pglogical.show_subscription_status();
 subscription_name |   status    | provider_node |                      provider_dsn                       |             slot_name             | replication_sets  | forward_origins
-------------------+-------------+---------------+---------------------------------------------------------+-----------------------------------+-------------------+-----------------
 subscription1     | replicating | provider1     | host=test-machine01 port=5432 dbname=appdb user=appuser | pgl_appdb_provider1_subscription1 | {replication_set} | {all}
(1 row)

appdb=#

appdb=# SELECT pglogical.wait_for_subscription_sync_complete('subscription1');
 wait_for_subscription_sync_complete
-------------------------------------

(1 row)

appdb=#
appdb=# select count(*) from my_table_test1 ;
 count
-------
     5
(1 row)
appdb=#


Step 6. Test replication: Add and Delete few rows from the publisher node and verify the same reflected in the subscriber node.


Publisher Node – Insert

appdb=#
appdb=# INSERT INTO my_table_test1 VALUES (6, 'this is row 6'), (7, 'this is row 7'), (8, 'this is row 8'), (9, 'this is row 9'), (10, 'this is row 10');
INSERT 0 5
appdb=#


Subscriber Node 

appdb=# select count(*) from my_table_test1 ;
 count
-------
    10
(1 row)

appdb=#


Publisher Node – Delete

appdb=#
appdb=# delete from my_table_test1 where col1 in (1,2,3);
DELETE 3
appdb=#


Subscriber Node 

appdb=# select count(*) from my_table_test1 ;
 count
-------
     7
(1 row)

appdb=#


Step 7. Monitor replication: You can monitor using the below queries in Publisher and Subscriber node to monitor replication.


Publisher Node

appdb=#
appdb=# \x
Expanded display is on.
appdb=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 70237
usesysid         | 18225
usename          | appuser
application_name | subscription1
client_addr      | 192.168.114.176
client_hostname  |
client_port      | 60254
backend_start    | 2021-08-24 14:10:39.667237+03
backend_xmin     |
state            | streaming
sent_lsn         | 0/1A156D80
write_lsn        | 0/1A156D80
flush_lsn        | 0/1A156D80
replay_lsn       | 0/1A156D80
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-08-24 15:04:18.868166+03

appdb=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+----------------------------------
slot_name           | pgl_appdb_provider1_subscription1
plugin              | pglogical_output
slot_type           | logical
datoid              | 18226
database            | appdb
temporary           | f
active              | t
active_pid          | 70237
xmin                |
catalog_xmin        | 16495
restart_lsn         | 0/1A156D48
confirmed_flush_lsn | 0/1A156D80
wal_status          | reserved
safe_wal_size       | 1089114752

appdb=#


Subscriber Node 

appdb=#
appdb=# select *  FROM pglogical.show_subscription_status();
 subscription_name |   status    | provider_node |                      provider_dsn                       |             slot_name             | replication_sets  | forward_origins
-------------------+-------------+---------------+---------------------------------------------------------+-----------------------------------+-------------------+-----------------
 subscription1     | replicating | provider1     | host=test-machine01 port=5432 dbname=appdb user=appuser | pgl_appdb_provider1_subscription1 | {replication_set} | {all}
(1 row)

appdb=#


Reference: pglogical Docs: https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/

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.


Related Articles

, ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *