Using pglogical for Logical Replication

Recent Pages

Using pglogical for Logical Replication

Last updated: 2024-12-09 11:58:34

pglogical is a PostgreSQL plugin that uses a publish/subscribe model to replicate data, making selective replication more efficient. It supports multi-scenario replication, specifically including:
Upgrades of major database versions.
Complete database replication.
Utilization of replica sets to selectively filter tables, rows, and columns.
Data aggregation and merging from multiple upstream servers.
Prerequisites
Note:
If you need to use the pglogical plugin, submit a ticket to contact us to add the shared_preload_libraries parameter. Modifying the shared_preload_libraries parameter will restart the instance, so make sure your business has a reconnection mechanism.
TencentDB for PostgreSQL supports configuring pglogical logical replication within the same instance or between different instances in the same region.
Currently, only TencentDB for PostgreSQL instances with 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 and above support the pglogical plugin.
Confirm that the shared_preload_libraries parameter contains pglogical, as shown in the figure 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 parameter wal_level is logical, as shown in the figure below.
postgres=> show wal_level;
 wal_level 
-----------
 logical
(1 row)
If you need to modify the wal_level parameter, go to the TencentDB for PostgreSQL console and modify the parameter:
﻿
Directions
Check whether the pglogical plugin is available for installation on both the publisher and subscriber instances:
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 the Plugin
Install the pglogical plugin on both the publisher and subscriber instances:
postgres=> create extension pglogical;
CREATE EXTENSION
Check whether the installation is successful:
postgres=>  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)
Creating a Publisher Node at the Publisher
Create a new database named am and switch to am:
postgres=> create database am;
CREATE DATABASE
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 a 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)
﻿
Create a publisher node. For parameters in the DNS, refer to the documentation. The parameters here should be filled in with the information of the instance where the publisher is located.
am=> SELECT pglogical.create_node(node_name := 'provider1',dsn := 'host=10.216.0.106 port=5432 dbname=am user=dbadmin password=Lxh202405! instanceid=postgres-gip4cpjw');
 create_node 
-------------
  2976894835
(1 row)
Configuring the Replica Set
The following example is one way to configure the replica set. For more parameters, refer to the pglogical official documentation.
am=> SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
 replication_set_add_all_tables 
--------------------------------
 t
(1 row)
Creating a Subscriber Node at the Subscriber
Create a table in the corresponding database at the subscriber:
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 at the subscriber. For parameters in the DNS, refer to the documentation. The parameters here should be filled in with the information of the instance where the subscriber is located.
am=> SELECT pglogical.create_node(node_name := 'subscriber1',dsn := 'host=10.216.1.162 port=5432 dbname=am user=dbadmin password=Lxh202405! instanceid=postgres-70beu0ou');
 create_node 
-------------
   330520249
(1 row)
Creating a Subscription at the Subscriber
Create a node at the subscriber. For parameters in the DNS, refer to the documentation. The parameters here should be filled in with the information of the instance where the publisher is located.
am=> SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=10.216.0.106 port=5432 dbname=am user=dbadmin password=Lxh202405! instanceid=postgres-gip4cpjw');
 create_subscription 
---------------------
          1763399739
(1 row)
Verify target data:
am=> select * from t;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)

Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support

tencent cloud

Recent Pages

Using pglogical for Logical Replication

Prerequisites

Directions

Installing the Plugin

Creating a Publisher Node at the Publisher

Configuring the Replica Set

Creating a Subscriber Node at the Subscriber

Creating a Subscription at the Subscriber

Was this page helpful?

Was this page helpful?

tencent cloud

Sign Up

Log in

Recent Pages

Using pglogical for Logical Replication

Prerequisites

Directions

Installing the Plugin

Creating a Publisher Node at the Publisher

Configuring the Replica Set

Creating a Subscriber Node at the Subscriber

Creating a Subscription at the Subscriber

Was this page helpful?

Was this page helpful?