Logical Decoding by SQL Function Interfaces

In MogDB, you can call SQL functions to create, delete, and push logical replication slots, as well as obtain decoded transaction logs.

Prerequisites

• Currently, logical logs are extracted from host nodes. Since SSL connections are disabled by default, to perform logical replication, set the GUC parameter ssl to on on host nodes.

  NOTE: For security purposes, ensure that SSL connections are enabled.

• The GUC parameter wal_level is set to logical.

• The GUC parameter max_replication_slots is set to a value greater than the number of physical replication slots and logical replication slots required by each node.

  Physical replication slots provide an automatic method to ensure that Xlogs are not removed from a primary node before they are received by all the standby nodes and secondary nodes. That is, physical replication slots are used to support HA clusters. The number of physical replication slots required by a cluster is equal to the ratio of standby and secondary nodes to the primary node. For example, if an HA cluster has 1 primary node, 1 standby node, and 1 secondary node, the number of required physical replication slots will be 2. If an HA cluster has 1 primary node and 3 standby nodes, the number of required physical replication slots will be 3.

  Plan the number of logical replication slots as follows:

  • A logical replication slot can carry changes of only one database for decoding. If multiple databases are involved, create multiple logical replication slots.
  • If logical replication is needed by multiple target databases, create multiple logical replication slots in the source database. Each logical replication slot corresponds to one logical replication link.

• Only database administrators and users with the REPLICATION permission can perform operations in this scenario.

Procedure

1. Log in to the primary node of the MogDB cluster as the cluster installation user.

2. Run the following command to connect to the default database postgres:

   gsql -d postgres -p 16000 -r

   In this command, 16000 is the database port number. It can be replaced by an actual port number.

3. Create a logical replication slot named slot1.

   mogdb=# SELECT * FROM pg_create_logical_replication_slot('slot1', 'mppdb_decoding');
   slotname | xlog_position
   ----------+---------------
   slot1    | 0/601C150
   (1 row)

4. Create a table t in the database and insert data into it.

   mogdb=# CREATE TABLE t(a int PRIMARY KEY, b int);
   mogdb=# INSERT INTO t VALUES(3,3);

5. Read the decoding result of slot1. The number of decoded records is 4096.

   mogdb=# SELECT * FROM pg_logical_slot_peek_changes('slot1', NULL, 4096);
   location  |  xid  | data
   -----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------
   -------------------------------------------
    0/601C188 | 1010023 | BEGIN 1010023
    0/601ED60 | 1010023 | COMMIT 1010023 CSN 1010022
    0/601ED60 | 1010024 | BEGIN 1010024
    0/601ED60 | 1010024 | {"table_name":"public.t","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","3"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
    0/601EED8 | 1010024 | COMMIT 1010024 CSN 1010023
   (5 rows)

6. Delete the logical replication slot slot1.

   mogdb=#  SELECT * FROM pg_drop_replication_slot('slot1');
    pg_drop_replication_slot
   --------------------------
   
   (1 row)