Help Center/ GaussDB/ Feature Guide(Centralized_8.x)/ Logical Replication/ Logical Decoding/ Logical Decoding by SQL Functions
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Logical Decoding by SQL Functions

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

Procedure

  1. Log in to the primary DN of the GaussDB database as a user who has the REPLICATION permission.
  2. Run the following command to connect to the database: 

    gsql -U user1 -W password -d db1 -p 16000 -r

    In the preceding command, user1 indicates the username, password indicates the user password, db1 indicates the name of the database to be connected, and 16000 indicates the database port number. You can replace them as required.

  3. Create a logical replication slot named slot1. 

    1
2
3
4
5
    		 
    db1=> 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. 

    1
2
    		 
    db1=> CREATE TABLE t(a int PRIMARY KEY, b int);
db1=> INSERT INTO t VALUES(3,3);

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

    For details about the logical decoding options, see Logical Decoding Options.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    db1=> SELECT * FROM pg_logical_slot_peek_changes('slot1', NULL, 4096);
location  |  xid  | data                                                                                         
-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
 0/601C188 | 1010023 | BEGIN 1010023
 0/601ED60 | 1010023 | COMMIT 1010023 (at 2023-09-14 16:03:51.394287+08) 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 (at 2023-09-14 16:03:57.239821+08) CSN 1010023
(5 rows)

  6. Delete the logical replication slot slot1 and delete the service table t. 

    1
2
3
4
5
6
7
8
    		 
    db1=> SELECT * FROM pg_drop_replication_slot('slot1');
 pg_drop_replication_slot
--------------------------

(1 row)

db1=> DROP TABLE t;
DROP TABLE

Parent topic: Logical Decoding