tencent cloud

Using pglogical for Logical Replication
Download PDF
Last updated: 2025-06-09 10:25:49
Using pglogical for Logical Replication
Last updated: 2025-06-09 10:25:49
Download PDF
pglogical is a logical replication extension plugin for PostgreSQL. It uses the "publish/subscribe" data model for data replication. This model includes a "publisher" and a "subscriber". The publisher is used to define and publish designated data, and the subscriber can choose to subscribe, thereby receiving and using data changes. pglogical allows users to replicate only specific data content, reducing unnecessary data transmission and processing, making selective replication more efficient.
The replication of pglogical is applicable to various scenarios, including:
database major version upgrade
Complete database replication.
Leverage the replica set to selectively filter tables, rows, and columns.
Can be obtained from multiple upstream servers for data aggregation and merging.

Prerequisites

Note:
If you need to use the pglogical plugin, please submit a ticket and contact us to add the shared_preload_libraries parameter. Modifying the shared_preload_libraries parameter will restart the instance. Ensure the business has a reconnection mechanism.
TencentDB for PostgreSQL supports configuring pglogical logical replication between the same instance or different instances in the same region.
Note: Currently, only TencentDB for PostgreSQL instances with Linux kernel versions v11.22_r1.21, v12.20_r1.24, v13.16_r1.19, v14.13_r1.26, v15.6_r1.13, v16.4_r1.7 or higher and major versions from 11 to 16 support the pglogical plugin.
Please ensure the shared_preload_libraries parameter includes pglogical, as shown below:
postgres=> show shared_preload_libraries;
                                                       shared_preload_libraries                                          
             
-------------------------------------------------------------------------------------------------------------------------
-------------
 pg_stat_statements,pg_stat_log,wal2json,decoderbufs,decoder_raw,pg_hint_plan,rds_server_handler,tencentdb_pwdcheck,pgaud
it,pglogical
(1 row)
The value of the wal_level parameter is logical, as shown below.
postgres=> show wal_level;
 wal_level 
-----------
 logical
(1 row)
If you need to modify the wal_level parameter, please enter the Parameter Settings page of the TencentDB for PostgreSQL console to modify the parameter.


Directions

Check whether the pglogical plugin can be installed in instances at the publishing end and subscription end.
postgres=> select * from pg_available_extensions where name='pglogical';
   name    | default_version | installed_version |            comment             
-----------+-----------------+-------------------+--------------------------------
 pglogical | 2.4.4           |                   | PostgreSQL Logical Replication
(1 row)

Installing Plugin

Create a database named am and switch to am.
postgres=> create database am;
CREATE DATABASE
postgres=> \\c am
You are now connected to database "am" as user "dbadmin".
am=>
Create table t and insert data
am=> create table t(a int primary key, b int);
CREATE TABLE
am=> insert into t(a,b)values(1,1),(2,2),(3,3);
INSERT 0 3
am=> select * from t;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)
Execute the following commands on both the publisher and subscriber instances to create the pglogical plugin.
am=> create extension pglogical;
CREATE EXTENSION
Use the following command to check whether it is installed successfully.
am=>  select * from pg_extension where extname='pglogical';
  oid  |  extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+-----------+----------+--------------+----------------+------------+-----------+--------------
 16424 | pglogical |    16387 |        16423 | f              | 2.4.4      |           | 
(1 row)

Create a Publishing Node on the Publisher

Create a publishing node. For parameters in dns, refer to Cross-database Access. The parameters here should be filled with the instance information where the Publisher is located.
am=> SELECT pglogical.create_node(node_name := 'provider1',dsn := 'host=10.*.*.* port=5432 dbname=am user=**** password=**** instanceid=postgres-****** uin=********');
 create_node 
-------------
  2976894835
(1 row)

Configure a Replica Set

The following example shows one way to configure a Replica Set. Use the pglogical_republication_set_add_all_tables function to add all tables under the specified schema to the designated Replica Set. 'default' refers to the name of the Replica Set, which can be customized; here, the default Replica Set name is used. 'public' refers to the schema name where the tables to be published are located, which is 'public' in this case.
For more parameters, see the pglogical official documentation.
am=> SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
 replication_set_add_all_tables 
--------------------------------
 t
(1 row)

Create a Subscription Node on the Subscriber

Create a table in the corresponding database on the subscriber side.
postgres=> \\c am
psql (14.11, server 15.6)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
You are now connected to database "am" as user "dbadmin".
am=> create table t(a int primary key, b int);
CREATE TABLE
Create a node on the subscriber side. For parameters in dns, refer to Cross-database Access. The parameters here should be filled with the instance information where the subscriber is located.
am=> SELECT pglogical.create_node(node_name := 'subscriber1',dsn := 'host=10.*.*.* port=5432 dbname=am user=**** password=**** instanceid=postgres-***** uin=*******');
 create_node 
-------------
   330520249
(1 row)

Create a Subscription on the Subscriber Side

Create a node on the subscriber side. For parameters in dns, refer to Cross-database Access. The parameters here should be filled with the instance information where the publisher is located.
am=> SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=10.*.*.* port=5432 dbname=am user=**** password=**** instanceid=postgres-**** uin=*******');
 create_subscription 
---------------------
          1763399739
(1 row)
Query the table at the subscription end. Verify the target data. If the query result is consistent with the publisher's data, logical replication is successful.
am=> select * from t;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback