tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for PostgreSQL
    Documentation
    Download PDF

    Using pglogical for Logical Replication

    Last updated: 2024-10-28 15:05:15
    Download PDF
      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 v15.6_r1.12 and above and Major Version 15 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.

      Contact Us
      Technical Support

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

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy