Updated on 2025-05-29 GMT+08:00

Logical Replication Functions

When using the logical replication functions, set the GUC parameter wal_level to logical. For details about the configuration, see "Logical Decoding > Logical Decoding by SQL Functions" in Feature Guide.

pg_create_logical_replication_slot('slot_name', 'plugin_name', 'output_order')

Description: Creates a logical replication slot.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • plugin_name

    Indicates the name of the plug-in.

    Value range: a string. Currently, mppdb_decoding, sql_decoding, parallel_binary_decoding, parallel_json_decoding, and parallel_text_decoding are supported.

  • output_order

    Indicates the output sequence of the replication slot decoding results. This parameter is optional.

    Valid value: 0 or 1. The default value is 0 on DNs and 1 on CNs.

    • 0: The replication slot decoding results are sorted by transaction COMMIT LSN. In this case, the value of confirmed_csn of the replication slot is 0. This replication slot is called an LSN-based replication slot.
    • 1: The replication slot decoding results are sorted by transaction CSN. In this case, the value of confirmed_csn of the replication slot is not 0. This replication slot is called a CSN-based replication slot, and the confirmed_flush value of such replication slot is meaningless.

Return type: name, text

Example:
gaussdb=# SELECT * FROM pg_create_logical_replication_slot('slot_lsn','mppdb_decoding',0);
 slotname | xlog_position
----------+---------------
 slot_lsn | 0/6D08B58
(1 row)

gaussdb=# SELECT * FROM pg_create_logical_replication_slot('slot_csn','mppdb_decoding',1);
 slotname | xlog_position
----------+---------------
 slot_csn | 0/59AD800
(1 row)

Note: The first return value is the slot name, and the second one has different meanings in LSN-based replication slots and CSN-based replication slots. For an LSN-based replication slot, the value is confirmed_flush of the replication slot, indicating that transactions whose commit LSN is less than or equal to the value will not be decoded and output. For a CSN-based replication slot, the value is confirmed_csn of the replication slot, indicating that transactions whose CSN is less than or equal to the value will not be decoded and output. Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication. Currently, this function can be called only on the CN or primary DNs.

When this function is executed on a CN, if output_order is set to 1, a replication slot with the same name will be created on all CNs and primary DNs. If a replication slot with the same name already exists on some DNs, a CSN-based logical replication slot will be created on other CNs and primary DNs that do not have a replication slot with the same name. In addition, an error message is displayed, indicating that the replication slot already exists. In this case, you need to delete the existing replication slot with the same name on other CNs and primary DN (if the replication slot is an LSN-based logical replication slot, you need to manually delete it on the corresponding node) and create a replication slot on the CN. If you set output_order to 0 when creating a replication slot on a CN, no replication slot will be created on DNs. Replication slots created on CNs are only used to identify whether related replication slots exist on DNs.

pg_create_physical_replication_slot('slot_name', 'isDummyStandby')

Description: Creates a physical replication slot.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • isDummyStandby

    Reserved parameter.

    Type: Boolean

Return type: name, text

  • Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.
  • The physical replication slot created by this function does not have restart_lsn. Therefore, the slot is considered invalid and will be automatically deleted when the checkpoint is performed.

pg_drop_replication_slot('slot_name')

Description: Deletes a streaming replication slot.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

Return type: void

  • Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.
  • When a database is deleted, the logical replication slots on the database are not deleted. You need to manually delete the logical replication slots on the database. Otherwise, historical versions of Xlogs and system catalogs cannot be reclaimed.
  • When a logical replication slot is deleted from a CN, if the logical replication slot is an LSN-based logical replication slot, only the replication slot of the current node is deleted. Replication slots with the same name on other nodes are not affected. When a CSN-based logical replication slot with the same name exists on other nodes, no error is reported because some nodes do not have replication slots. In addition, replication slots with the same name on all nodes are successfully deleted. If no replication slot exists on any node, an error is reported.
  • If an LSN-based logical replication slot remains on the current CN and a CSN-based logical replication slot with the same name remains on other nodes, deleting the replication slot on the current CN will delete only the local LSN-based logical replication slot. After the deletion is complete, perform the deletion operation again to delete the CSN-based logical replication slots with the same name on other nodes.

pg_logical_slot_peek_changes('slot_name', 'upto_lsn', upto_nchanges, 'options_name', 'options_value')

Description: Performs decoding but does not go to the next streaming replication slot. (The decoded result will be returned again during the next decoding.)

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • upto_lsn

    For the CSN-based logical replication slot, the decoding is complete until the transaction whose CSN is less than or equal to the value is decoded (a transaction whose CSN is greater than the specified CSN may be decoded). For the LSN-based replication slot, the decoding is complete until the first transaction whose COMMIT LSN is greater than or equal to the value is decoded.

    Value range: a string, for example, '1/2AAFC60', '0/A060', or '3A/0' (a hexadecimal uint64 value containing two uint32 values separated by a slash (/); if any uint32 value is 0, 0 is displayed.) NULL indicates that the end position of decoding is not specified.

  • upto_nchanges

    Indicates the number of decoded records (including the begin and commit timestamps). Assume that there are three transactions, which involve 3, 5, and 7 records, respectively. If upto_nchanges is set to 4, 8 records of the first two transactions will be decoded. Specifically, decoding is stopped when the number of decoded records exceeds the value of upto_nchanges after decoding in the first two transactions is finished.

    Value range: a non-negative integer

    If any of the upto_lsn and upto_nchanges values is reached, decoding ends.

  • options: Specifies optional parameters, consisting of options_name and options_value.
    • include-xids

      Specifies whether the decoded data column contains XID information.

      Value range: Boolean. The default value is true.

      • false: The decoded data column does not contain XID information.
      • true: The decoded data column contains XID information.
    • skip-empty-xacts

      Specifies whether to ignore empty transaction information during decoding.

      Value range: Boolean. The default value is false.

      • false: The empty transaction information is not ignored during decoding.
      • true: The empty transaction information is ignored during decoding.
    • include-timestamp

      Specifies whether decoded information contains the commit timestamp.

      Value range: Boolean. The default value is true.

      • false: The decoded information does not contain the commit timestamp.
      • true: The decoded information contains the commit timestamp.
    • only-local

      Specifies whether to decode only local logs.

      Value range: Boolean. The default value is true.

      • false: Non-local logs and local logs are decoded.
      • true: Only local logs are decoded.
    • force-binary

      Specifies whether to output the decoding result in binary format.

      Value range: Boolean. The default value is false.

      • false: The decoding result is output in text format.
      • The value cannot be set to true currently.
    • white-table-list

      Whitelist parameter, including the schema and table name to be decoded.

      Value range: a string that contains table names in the whitelist. Different tables are separated by commas (,). An asterisk (*) is used to fuzzily match all tables. Schema names and table names are separated by periods (.). No space character is allowed. The following is an example:

      SELECT * FROM pg_logical_slot_peek_changes('slot1', NULL, 4096, 'white-table-list', 'public.t1,public.t2');
    • max-txn-in-memory

      Memory control parameter. The unit is MB. If the memory occupied by a single transaction is greater than the value of this parameter, data is flushed to disks.

      Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

    • max-reorderbuffer-in-memory

      Memory control parameter. The unit is GB. If the total memory (including the cache) of transactions being concatenated in the sender thread is greater than the value of this parameter, the current decoding transaction is flushed to disks.

      Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

    • desc-memory-limit

      A memory control parameter, in MB. When the total memory of table metadata maintained by a logical decoding task is greater than the value of this parameter, some table metadata will be evicted.

      Value range: an integer ranging from 10 to 1024. The default value is 100.

    • include-user

      Specifies whether the BEGIN logical log of a transaction records the username of the transaction.

      Value range: Boolean. The default value is false.

      • false: The BEGIN logical log of a transaction does not record the username of the transaction.
      • true: The BEGIN logical log of a transaction records the username of the transaction.
    • exclude-userids

      Specifies the OID of a blacklisted user.

      Value range: OIDs of blacklisted users. Multiple OIDs are separated by commas (,). The system does not check whether the OIDs exist.

    • exclude-users

      Specifies the name of a blacklisted user.

      Value range: names of blacklisted users. Multiple names are separated by commas (,). dynamic-resolution specifies whether to dynamically parse and identify usernames. If the decoding is interrupted because the user does not exist and the corresponding blacklisted user does not exist at the time when logs are generated, you can set dynamic-resolution to true or delete the username from the blacklist to start decoding and continue to obtain logical logs.

    • enable-ddl-decoding

      Specifies whether to enable logical decoding for DDL statements.

      Value range: Boolean. The default value is false.
      • false: Logical decoding of DDL statements is disabled.
      • true: Logical decoding of DDL statements is enabled.
    • enable-ddl-json-format

      Specifies the DDL statement reverse parsing process and output format for logical decoding.

      Value range: Boolean. The default value is false.
      • false: The DDL statement reverse parsing result is output in text format.
      • true: The DDL statement reverse parsing result is output in JSON format.
    • skip-generated-columns

      Specifies whether to skip stored generated columns in the logical decoding result. This parameter is invalid for UPDATE and DELETE on old tuples, and the corresponding tuples always output the stored generated columns. Generated columns are not supported in a distributed system and therefore, this parameter has no actual impact.

      Value range: Boolean. The default value is false or off.

      • true/on: The decoding result of stored generated columns is not output.
      • false/off: The decoding result of stored generated columns is output.

      Virtual generated columns are not controlled by this parameter. The DML decoding result does not output virtual generated columns.

    • dynamic-resolution

      Specifies whether to dynamically parse the names of blacklisted users.

      Value range: Boolean. The default value is true.

      • false: An error is reported and the logical decoding exits when the decoding detects that a user does not exist in blacklist exclude-users.
      • true: Decoding continues when it detects that a user does not exist in blacklist exclude-users.
    • decode-sequence

      Specifies whether to output the decoding result of the change log of the sequence value, which is a logical decoding control parameter.

      Value range: Boolean. The default value is false.

      • The value cannot be set to true currently.
      • false: The decoding result of the change log of the sequence value is not output.

      Currently, decode-sequence can only be set to false. If decode-sequence is set to true, an error is reported when decoding is started and the decoding process exits.

For details about other configuration options, see "Logical Replication > Logical Decoding > Logical Decoding Options" in the Feature Guide.

Return type: text, xid, text

Example:
gaussdb=# EXECUTE DIRECT ON (datanode1) 'SELECT * FROM pg_logical_slot_peek_changes(''slot_lsn'',NULL,4096,''skip-empty-xacts'',''on'')';
 location  |  xid  |                                                                                            data
-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0/6D0B500 | 46914 | BEGIN 46914
 0/6D0B530 | 46914 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","1"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/6D0B8B8 | 46914 | COMMIT 46914 (at 2023-02-22 17:29:31.090018+08) CSN 94034528
 0/6D0BB58 | 46915 | BEGIN 46915
 0/6D0BB88 | 46915 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","2"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/6D0BF08 | 46915 | COMMIT 46915 (at 2023-02-22 17:31:30.672093+08) CSN 94034568
 0/6D0BF08 | 46916 | BEGIN 46916
 0/6D0BF38 | 46916 | {"table_name":"public.t1","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/6D0C218 | 46916 | COMMIT 46916 (at 2023-02-22 17:31:34.438319+08) CSN 94034570
(9 rows)

gaussdb=# EXECUTE DIRECT ON (datanode1) 'SELECT * FROM pg_logical_slot_peek_changes(''slot_csn'',NULL,4096,''skip-empty-xacts'',''on'')';
 location  |  xid  |                                                                                            data
-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0/0       | 46914 | BEGIN CSN: 94034528
 0/0       | 46914 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","1"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/59ADA60 | 46914 | COMMIT 46914 (at 2023-02-22 17:29:31.090018+08) CSN 94034528
 0/59ADA60 | 46915 | BEGIN CSN: 94034568
 0/59ADA60 | 46915 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","2"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/59ADA88 | 46915 | COMMIT 46915 (at 2023-02-22 17:31:30.672093+08) CSN 94034568
 0/59ADA88 | 46916 | BEGIN CSN: 94034570
 0/59ADA88 | 46916 | {"table_name":"public.t1","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/59ADA8A | 46916 | COMMIT 46916 (at 2023-02-22 17:31:34.438319+08) CSN 94034570
(9 rows)

Note: In the preceding function example, datanode1 is the name of the DN to be decoded, and slot_lsn and slot_csn are the names of the logical replication slots.

The decoding result returned by the function contains three columns, corresponding to the preceding return value types, which are the LSN (for an LSN-based replication slot) or CSN (for a CSN-based replication slot), XID, and decoded content, respectively. If the location column indicates the CSN, the value of the location column is updated only when the commit logs are decoded. Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0).

pg_logical_slot_get_changes('slot_name', 'upto_lsn', upto_nchanges, 'options_name', 'options_value')

Description: Performs decoding and goes to the next streaming replication slot.

Parameter: This function has the same parameters as pg_logical_slot_peek_changes. For details, see pg_logical_slot_peek_changes.

Note: Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0). When this function is called on the standby DN, the corresponding logical replication slot on the primary DN is updated synchronously. If this function is executed on the standby DN, a WAL sender of the primary DN is occupied when the replication slot number on the primary DN is updated. The logical decoding function reserves a WAL sender for each logical replication slot. Therefore, if this function is executed in normal scenarios, the logical replication slot number on the primary DN is updated normally. If this function is executed continuously in a short period of time, the primary DN fails to update the slot number and no error is reported.

pg_logical_slot_peek_binary_changes('slot_name', 'upto_lsn', upto_nchanges, 'options_name', 'options_value')

Description: Performs decoding in binary mode and does not go to the next streaming replication slot. (The decoded data can be obtained again during the next decoding.)

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • upto_lsn

    For the CSN-based logical replication slot, the decoding is complete until the transaction whose CSN is less than or equal to the value is decoded (a transaction whose CSN is greater than the specified CSN may be decoded). For the LSN-based replication slot, the decoding is complete until the first transaction whose COMMIT LSN is greater than or equal to the value is decoded.

    Value range: a string, for example, '1/2AAFC60', '0/A060', or '3A/0' (a hexadecimal uint64 value containing two uint32 values separated by a slash (/); if any uint32 value is 0, 0 is displayed.) NULL indicates that the end position of decoding is not specified.

  • upto_nchanges

    Indicates the number of decoded records (including the begin and commit timestamps). Assume that there are three transactions, which involve 3, 5, and 7 records, respectively. If upto_nchanges is set to 4, 8 records of the first two transactions will be decoded. Specifically, decoding is stopped when the number of decoded records exceeds the value of upto_nchanges after decoding in the first two transactions is finished.

    Value range: a non-negative integer

    If any of the upto_lsn and upto_nchanges values is reached, decoding ends.

  • options: Specifies optional parameters, consisting of options_name and options_value.
    • include-xids

      Specifies whether the decoded data column contains XID information.

      Value range: Boolean. The default value is true.

      • false: The decoded data column does not contain XID information.
      • true: The decoded data column contains XID information.
    • skip-empty-xacts

      Specifies whether to ignore empty transaction information during decoding.

      Value range: Boolean. The default value is false.

      • false: The empty transaction information is not ignored during decoding.
      • true: The empty transaction information is ignored during decoding.
    • include-timestamp

      Specifies whether decoded information contains the commit timestamp.

      Value range: Boolean. The default value is true.

      • false: The decoded information does not contain the commit timestamp.
      • true: The decoded information contains the commit timestamp.
    • only-local

      Specifies whether to decode only local logs.

      Value range: Boolean. The default value is true.

      • false: Non-local logs and local logs are decoded.
      • true: Only local logs are decoded.
    • force-binary

      This parameter is useless for the function.

      Value range: Boolean. The result is output in binary format.

    • white-table-list

      Whitelist parameter, including the schema and table name to be decoded.

      Value range: a string that contains table names in the whitelist. Different tables are separated by commas (,). An asterisk (*) is used to fuzzily match all tables. Schema names and table names are separated by periods (.). No space character is allowed. Example: select * from pg_logical_slot_peek_binary_changes('slot1', NULL, 4096, 'white-table-list', 'public.t1,public.t2');

    • max-txn-in-memory

      Memory control parameter. The unit is MB. If the memory occupied by a single transaction is greater than the value of this parameter, data is flushed to disks.

      Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

    • max-reorderbuffer-in-memory

      Memory control parameter. The unit is GB. If the total memory (including the cache) of transactions being concatenated in the sender thread is greater than the value of this parameter, the current decoding transaction is flushed to disks.

      Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

    • desc-memory-limit

      A memory control parameter, in MB. When the total memory of table metadata maintained by a logical decoding task is greater than the value of this parameter, some table metadata will be evicted.

      Value range: an integer ranging from 10 to 1024. The default value is 100.

    • include-user

      Specifies whether the BEGIN logical log of a transaction records the username of the transaction.

      Value range: Boolean. The default value is false.

      • false: The BEGIN logical log of a transaction does not record the username of the transaction.
      • true: The BEGIN logical log of a transaction records the username of the transaction.
    • exclude-userids

      Specifies the OID of a blacklisted user.

      Value range: OIDs of blacklisted users. Multiple OIDs are separated by commas (,). The system does not check whether the OIDs exist.

    • exclude-users

      Specifies the name of a blacklisted user.

      Value range: names of blacklisted users. Multiple names are separated by commas (,). dynamic-resolution specifies whether to dynamically parse and identify usernames. If the decoding is interrupted because the user does not exist and the corresponding blacklisted user does not exist at the time when logs are generated, you can set dynamic-resolution to true or delete the username from the blacklist to start decoding and continue to obtain logical logs.

    • dynamic-resolution

      Specifies whether to dynamically parse the names of blacklisted users.

      Value range: Boolean. The default value is true.

      • false: An error is reported and the logical decoding exits when the decoding detects that a user does not exist in blacklist exclude-users.
      • true: Decoding continues when it detects that a user does not exist in blacklist exclude-users.
    • decode-sequence

      Specifies whether to output the decoding result of the change log of the sequence value, which is a logical decoding control parameter.

      Value range: Boolean. The default value is false.

      • The value cannot be set to true currently.
      • false: The decoding result of the change log of the sequence value is not output.

      Currently, decode-sequence can only be set to false. If decode-sequence is set to true, an error is reported when decoding is started and the decoding process exits.

    Some configuration options do not take effect even if they are configured in functions. For details, see "Logical Replication > Logical Decoding > Logical Decoding Options" in the Feature Guide.

Return type: text, xid, bytea

Note: The function returns the decoding result. Each decoding result contains three columns, corresponding to the above return types and indicating the LSN position, XID, and decoded content in binary format, respectively. Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0).

pg_logical_slot_get_binary_changes('slot_name', 'upto_lsn', upto_nchanges, 'options_name', 'options_value')

Description: Performs decoding in binary mode and does not go to the next streaming replication slot.

Parameter: This function has the same parameters as pg_logical_slot_peek_binary_changes. For details, see pg_logical_slot_peek_binary_changes.

Note: Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0). This function cannot be executed on the standby DN.

pg_replication_slot_advance ('slot_name', 'upto_lsn')

Description: Directly goes to the streaming replication slot for a specified CSN (for CSN-based logical replication slots) or LSN (for LSN-based replication slots), without outputting any decoded result.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • upto_lsn

    For the CSN-based logical replication slot, it indicates the target CSN before which logs are decoded. During the next decoding, only the transaction results whose CSN is greater than this value will be output. If the input CSN is smaller than the value of confirmed_csn recorded in the current streaming replication slot, the function directly returns the decoded result. If the input CSN is greater than the latest CSN that can be obtained, the latest CSN will be used for decoding.

    For the LSN-based logical replication slot, it indicates the target LSN before which logs are decoded. During the next decoding, only the transaction results whose LSN is greater than this value will be output. If an input LSN is smaller than the position recorded in the current streaming replication slot, an error is reported. If the input LSN is greater than the LSN of the current physical log, the latter LSN will be directly used for decoding.

    Value range: a string, for example, '1/2AAFC60', '0/A060', or '3A/0' (a hexadecimal uint64 value containing two uint32 values separated by a slash (/); if any uint32 value is 0, 0 is displayed.) NULL indicates that the end position of decoding is not specified.

Return type: name, text

  • The return value corresponds to slot_name and the actual position (CSN or LSN). Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.
  • On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0). This function can be executed only on the logical replication slot on the standby DN and the corresponding logical replication slot on the primary DN is updated synchronously. If this function is executed on the standby DN, a WAL sender of the primary DN is occupied when the replication slot number on the primary DN is updated. The logical decoding function reserves a WAL sender for each logical replication slot. Therefore, if this function is executed in normal scenarios, the logical replication slot number on the primary DN is updated normally. If this function is executed continuously in a short period of time, the primary DN fails to update the slot number and no error is reported.

pg_get_replication_slots()

Description: Obtains the replication slot list.

Return type: text, text, text, oid, Boolean, xid, xid, text, Boolean, text, xid, and text

Example:
Run the following command on the DN:
gaussdb=# SELECT * FROM pg_get_replication_slots();
 slot_name |     plugin     | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush | confirmed_csn | dictionary_csn_min | slot_dictionary_type
-----------+----------------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------+---------------+--------------------+----------------------
 dn_6002   |                | physical  |      0 | t      |      |              | 0/3622B528  | f             |                 |               |                    | 
 dn_6003   |                | physical  |      0 | t      |      |              | 0/3622B528  | f             |                 |               |                    | 
 slot_lsn  | mppdb_decoding | logical   | 131072 | f      |      |        66658 | 0/36252350  | f             | 0/362523D0      |               |                    | 
 slot_test | mppdb_decoding | logical   | 131072 | f      |      |        66658 | 0/36251718  | f             |                 |      10025527 |                    | 
(4 rows)
Run the following command on the CN:
gaussdb=# SELECT * FROM pg_get_replication_slots();
 slot_name |     plugin     | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush | confirmed_csn | dictionary_csn_min | slot_dictionary_type
-----------+----------------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------+---------------+--------------------+----------------------
 slot_test | mppdb_decoding | logical   | 139264 | f      |      |              |             | f             |                 |               |                    | 
(1 row)

Return type: text, text, text, oid, Boolean, xid, xid, text, Boolean, text, xid

Note: In the returned value, slot_name indicates the name of the replication slot, plugin indicates the name of the output plug-in corresponding to the logical replication slot, slot_type indicates the type of the replication slot (physical indicates the physical replication slot, and logical indicates the logical replication slot), datoid indicates the OID of the database where the replication slot resides, active specifies whether the replication slot is activated (f: not activated; t: activated), xmin indicates the transaction ID of the earliest transaction reserved by the database for the replication slot, catalog_xmin indicates the transaction ID of the earliest transaction related to the system catalog reserved by the database for the logical replication slot, restart_lsn indicates the physical location of the earliest Xlog required by the replication slot, dummy_standby is a reserved parameter, confirmed_csn indicates that the client confirms the CSN corresponding to the last transaction in the received log (This parameter is dedicated to logical replication slots.), confirmed_flush indicates that the client confirms the location of the received log (dedicated for logical replication slots), dictionary_csn_min indicates the CSN of the earliest transaction involving the dictionary table that the database must reserve for the logical replication slot, and slot_dictionary_type indicates the type of the logical replication slot.

When a query is performed on a DN, the confirmed_csn query result of the LSN-based logical replication slot is empty, and the confirmed_flush query result of the CSN-based logical replication slot is empty. When a query is performed on the CN, catalog_xmin, restart_lsn, confirmed_flush, and confirmed_csn of the CSN-based logical replication slot are not displayed and the query result is empty.

pg_logical_get_area_changes('start_lsn', 'upto_lsn', upto_nchanges, 'decoding_plugin', 'xlog_path', 'options_name', 'options_value')

Description: Specifies an LSN range or an Xlog file for decoding.

The constraints are as follows:

  • The current network and hardware environment are normal.
  • It is recommended that the size of a single tuple be less than or equal to 500 MB. If the size ranges from 500 MB to 1 GB, an error is reported.
  • Data page replication is not supported for log decoding that does not fall into Xlogs.
  • When an API is called, the log level parameter wal_level must be set to logical, and only the log files generated when wal_level is set to logical can be parsed. If the used Xlog file is not at the logical level, the decoded content does not have the corresponding value or type. There is no other impact. If wal_level is not set to logical, an error is reported and decoding is not performed.
  • The Xlog file can be parsed only by a copy of a fully homogeneous DN to ensure that the metadata corresponding to the data can be found.
  • Do not read too many Xlog files at a time. If no file is specified for decoding within a specified range, you are advised to read one Xlog file each time. Generally, the memory occupied by an Xlog file during decoding is about two to three times the size of the Xlog file.
  • Logs before VACUUM FULL cannot be decoded.
  • The Xlog file before scale-out cannot be decoded.
  • When enable-ddl is disabled, columns of the TOAST, clob, and blob types cannot be decoded. When enable-ddl is enabled, columns of related types can be decoded.
  • In this decoding mode, the content that can be decoded is decoded based on the Xlog text record data, and the decoding is not performed based on transactions. Therefore, data that is not in the Xlog cannot be decoded. If related Xlogs are missing, the missing logs cannot be decoded.
  • When enable-ddl is disabled: If no decoding file is specified from the decoding point, the system checks whether DDL occurs between the decoding start point and the latest redo value. If DDL occurs, the system does not decode all data. If a decoding file is specified, the system checks whether DDL occurs between the start point of the decoding file and the last readable content of the file and between the start point of the Xlog in the data directory and the latest redo value. If a DDL operation is detected, the system does not decode all tables.
  • When enable-ddl is disabled, columns of the TOAST, clob, and blob types cannot be decoded. When enable-ddl is enabled, columns of related types can be decoded.
  • When enable-ddl is enabled, decoding of DDL statements is supported in the range and DDL statements can be output. For details about the output DDL statement range, see "Logical Replication > Logical Decoding > Logical Decoding of DDL Statements" in Feature Guide.
  • To parse the update and delete statements of a table, you need to configure the REPLICA IDENTITY attribute for the table. If the table does not have a primary key, set the REPLICA IDENTITY attribute to FULL. For details, see the REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } column in ALTER TABLE.
  • In M-compatible mode, the decoded data value of the float4 type is output as the actual stored value, which is displayed differently from that of the SQL query.

Note: When separation-of-duties is enabled, only the initial database user can call the function. When separation-of-duties is disabled, the SYSADMIN permission is required.

Parameters:

  • start_lsn

    Specifies the LSN at the start of decoding.

    Value range: a string, in the format of xlogid/xrecoff, for example, '1/2AAFC60'. NULL indicates that the start position of decoding logs is not limited.

  • upto_lsn

    Specifies the LSN at the end of decoding.

    Value range: a string, in the format of xlogid/xrecoff, for example, '1/2AAFC60'. NULL indicates that the end position of decoding is not specified.

  • upto_nchanges

    Indicates the number of decoded records (including the begin and commit timestamps). Assume that there are three transactions, which involve 3, 5, and 7 records, respectively. If upto_nchanges is set to 4, 8 records of the first two transactions will be decoded. Specifically, decoding is stopped when the number of decoded records exceeds the value of upto_nchanges after decoding in the first two transactions is finished.

    Value range: a non-negative integer

    If any of the LSN and upto_nchanges values are reached, decoding ends.

  • decoding_plugin

Decoding plug-in, which is a .so plug-in that specifies the output format of the decoded content.

Value range: mppdb_decoding and sql_decoding.

  • xlog_path

Decoding plug-in, which specifies the Xlog absolute path and file level of the decoding file.

Value range: NULL or a character string of the absolute path of the Xlog file.

  • options: This parameter is optional and consists of multiple pairs of options_name and options_value. You can retain the default values.
    • include-xids

      Specifies whether the decoded data column contains XID information.

      Value range: Boolean. The default value is true.

      false: The decoded data column does not contain XID information.

      true: The decoded data column contains XID information.

    • include-timestamp

      Specifies whether decoded information contains the commit timestamp.

      Value range: Boolean. The default value is true.

      false: The decoded information does not contain the commit timestamp.

      true: The decoded information contains the commit timestamp.

    • only-local

      Specifies whether to decode only local logs.

      Value range: Boolean. The default value is true.

      false: Non-local logs and local logs are decoded.

      true: Only local logs are decoded.

    • force-binary

      Specifies whether to output the decoding result in binary format.

      Value range: Boolean. The default value is false.

      false: The decoding result is output in text format.

      The value cannot be set to true currently.

    • white-table-list

      Whitelist parameter, including the schema and table name to be decoded. Value range: a string that contains table names in the whitelist. Different tables are separated by commas (,). An asterisk (*) is used to fuzzily match all tables. Schema names and table names are separated by periods (.). No space character is allowed.

    • white-database-list

      Whitelist parameter, including names of databases to be decoded. Value range: a character string that contains database names in the whitelist. Different database names are separated by commas (,). No space character is allowed. If white-database-list is not set, only the database Xlogs of the current connection are decoded. If white-database-list is set, only the database Xlogs specified in white-database-list are decoded. If the system database is decoded, ordinary table data in the system database can be retrieved.

    • enable-ddl

      Decoding that contains DDL syntax is supported. If the DDL syntax exists between the start position and the latest log, the function is not affected. The value ranges from 0 to 1.

      • 1: enabled. This function supports decoding of DDL syntax.
      • 0: disabled by default. This function does not support decoding of DDL syntax. If a DDL statement is detected, the function exits directly.
    • data-limit

      Specifies the amount of output data after decoding. The unit is KB.

      Value range: an integer greater than or equal to 0. The default value is 0, indicating that the size of the decoding result is not limited.

      The input parameter data-limit of this function is used together with the GUC parameter data-limit. The decoding ends when the size of data-limit is exceeded.

      Rules:

      1. If the GUC parameter data-limit is set to 0, and data-limit for function decoding is not set or is set to 0, the decoding result has no upper limit.
      2. If the GUC parameter data-limit is not set, and data-limit for function decoding is set to 0, the upper limit of the decoding result is 10 GB.
      3. If the GUC parameter data-limit is set, and data-limit for function decoding is not set or is set to 0, the upper limit of the decoding result is the value of the GUC parameter data-limit.
      4. If both the GUC parameter data-limit and data-limit of the function are set, the smaller value is used as the upper limit of the decoding result.
    • xlog_dir

      The archive log folder can be decoded, and the folder name is the same as the first eight digits of the WAL file. Decoding can be performed only when enable-ddl is set to 1 and the input start_lsn and upto_lsn are valid intervals. Value range: full path of the folder.

      For details, see "Logical Replication > Logical Decoding > Logical Decoding Data Retrieval" in Feature Guide.

      gaussdb=# SELECT * FROM pg_logical_get_area_changes('1/F9FC24C8', '1/FA67B2A0', NULL, 'sql_decoding', NULL ,'enable-ddl','1','xlog_dir','/base/username/xlog_dir/00000000');
    • decode-sequence

      Specifies whether to output the decoding result of the change log of the sequence value, which is a logical decoding control parameter.

      Value range: Boolean. The default value is false.

      • The value cannot be set to true currently.
      • false: The decoding result of the change log of the sequence value is not output.

      Currently, decode-sequence can only be set to false. If decode-sequence is set to true, an error is reported when decoding is started.

pg_logical_get_area_changes takes effect only for the preceding parameters. It does not take effect for the other parameters such as max-txn-in-memory, include-user, exclude-userids, exclude-users and dynamic-resolution (for details, see pg_logical_slot_peel_changes).

Example:

- Perform DML operations on a CN. You can query the DML operations executed on the current DN.
gaussdb=# SELECT * FROM pg_logical_get_area_changes('0/502E418', NULL, NULL, 'sql_decoding', NULL);
 location  |  xid  |                                  data
-----------+-------+------------------------------------------------------------------------
 0/502E448 | 17365 | insert into public.t1 values (1, 1);
 0/502E5A0 | 17365 | COMMIT 17365 (at 2023-11-01 11:28:43.92526+08) 2010016
 0/502E5D0 | 17366 | delete from public.t1 where a = 1;insert into public.t1 values (1, 2);
 0/502E6D8 | 17366 | COMMIT 17366 (at 2023-11-01 11:28:45.889283+08) 2010017
 0/502E7B8 | 17367 | delete from public.t1 where a = 1;
 0/502E8B0 | 17367 | COMMIT 17367 (at 2023-11-01 11:28:48.301307+08) 2010018
(6 rows)

gs_get_parallel_decode_status()

Description: Monitors the length of the read log queue and decoding result queue of each decoder thread to locate the concurrent decoding performance bottleneck.

Return type: text, int, text, text, text, int64, int64, TimestampTz

Example:

gaussdb=# SELECT * FROM gs_get_parallel_decode_status();
 slot_name | parallel_decode_num | read_change_queue_length  | decode_change_queue_length | reader_lsn | working_txn_cnt | working_txn_memory |      decoded_time
-----------+---------------------+---------------------------+----------------------------+------------+-----------------+--------------------+------------------------
 slot1     |                   2 | queue0: 1005, queue1: 320 | queue0: 63, queue1: 748    | 0/1DCE2578 |              42 |          192927504 | 2023-01-10 11:18:22+08
(1 row)

Note: In the return values, slot_name indicates the replication slot name, parallel_decode_num indicates the number of parallel decoder threads in the replication slot, read_change_queue_length indicates the current length of the log queue read by each decoder thread, decode_change_queue_length indicates the current length of the decoding result queue of each decoder thread, reader_lsn indicates the log location read by the reader thread, working_txn_cnt indicates the number of transactions being concatenated in the current sender thread, working_txn_memory indicates the total memory (in bytes) occupied by the concatenation transactions in the sender thread, and decoded_time indicates the time of the latest WAL decoded by the replication slot.

The value of decoded_time comes from checkpoint logs and transaction commit logs, which has a certain error. If no log containing the time is decoded, "2000-01-01 08:00:00+08" (depending on the time zone set in the database) is displayed.

gs_get_slot_decoded_wal_time(slot_name)

Description: Queries the time of the latest WAL decoded by a replication slot.

Parameter:

  • slot_name

    Specifies the name of the replication slot to be queried.

    Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Example:

gaussdb=# SELECT * FROM gs_get_slot_decoded_wal_time('replication_slot');
 gs_get_slot_decoded_wal_time
------------------------------
 2023-01-10 11:25:22+08
(1 row)

Note: The returned values indicate the time of the latest WAL decoded by the replication slot.

The returned time comes from checkpoint logs and transaction commit logs, which has a certain error. If no log containing the time is decoded, "2000-01-01 08:00:00+08" (depending on the time zone set in the database) is displayed. When you query the latest decoded WAL time of a logical replication slot that does not exist, NULL is returned. In gsql, the display of NULL is related to the setting, which can be set using \pset null'null'.

gs_logical_parallel_decode_status('slot_name')

Description: Obtains the decoding statistics of a replication slot for parallel logical decoding, including 26 rows of statistical items.

The descriptions of the statistical items are listed in the following table.

Record - (stat_id int, stat_name TEXT, value TEXT)
Table 1 Description

Statistical Item

Description

slot_name

Name of the logical replication slot.

reader_lsn

Location of the logical logs to be decoded.

wal_read_total_time

Time required for loading the log module.

wal_wait_total_time

Time required for waiting for log decoding.

parser_total_time

Processing duration of the reader thread.

decoder_total_time

Processing duration of all decoder threads.

sender_total_time

Processing duration of the sender thread.

net_send_total_time

Time required for the network to send logical logs.

net_wait_total_time

Time required for the network to wait for sending logical logs.

net_send_total_bytes

Number of logical log bytes sent by the network.

transaction_count

Number of transactions.

big_transaction_count

Number of large transactions.

max_transaction_tuples

Maximum number of transaction operation tuples.

sent_transaction_count

Number of transactions sent (by the local database).

spill_disk_transaction_count

Number of flushed transactions.

spill_disk_bytes

Total number of bytes flushed to disks.

spill_disk_count

Number of disk flushing times.

input_queue_full_count

Total number of times that the input queues of all decoder threads are full.

output_queue_full_count

Total number of times that the output queues of all decoder threads are full.

dml_count

Total number of DML statements in WALs decoded by each decoder thread in the local database.

dml_filtered_count

Total number of DML statements in WALs decoded and filtered by each decoder thread in the local database.

toast_count

Number of modified TOAST table rows.

candidate_catalog_xmin

Indicates the catalog_xmin candidate point of the current logical replication slot.

candidate_xmin_lsn

Updates the log confirmation receiving point required by catalog_xmin.

candidate_restart_valid

Updates the log confirmation receiving point required by restart_lsn.

candidate_restart_lsn

Indicates the restart_lsn candidate point of the current logical replication slot.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

Return type: int, text, text

Example:

gaussdb=# SELECT * FROM gs_logical_parallel_decode_status('replication_slot');
 stat_id |          stat_name           |      value
---------+------------------------------+------------------
       1 | slot_name                    | replication_slot
       2 | reader_lsn                   | 0/357E180
       3 | wal_read_total_time          | 266694599
       4 | wal_wait_total_time          | 266691307
       5 | parser_total_time            | 39971
       6 | decoder_total_time           | 81216
       7 | sender_total_time            | 48193
       8 | net_send_total_time          | 19388
       9 | net_wait_total_time          | 0
      10 | net_send_total_bytes         | 266897
      11 | transaction_count            | 7
      12 | big_transaction_count        | 1
      13 | max_transaction_tuples       | 4096
      14 | sent_transaction_count       | 7
      15 | spill_disk_transaction_count | 1
      16 | spill_disk_bytes             | 244653
      17 | spill_disk_count             | 4096
      18 | input_queue_full_count       | 0
      19 | output_queue_full_count      | 0
      20 | dml_count                    | 4097
      21 | dml_filtered_count           | 0
      22 | toast_count                  | 0
      23 | candidate_catalog_xmin       | 17152
      24 | candidate_xmin_lsn           | 0/420A598
      25 | candidate_restart_valid      | 0/420A598
      26 | candidate_restart_lsn        | 0/420A598
(26 rows)

Note: According to the definitions of statistical items, they must meet the following requirements:

wal_read_total_time >= wal_wait_total_time;

transaction_count >= big_transaction_count;

transaction_count >= sent_transaction_count;

transaction_count >= spill_disk_transaction_count;

dml_count >= dml_filtered_count;

dml_count >= toast_count;

If spill_transaction_count == 0, then spill_disk_bytes == 0;

However, frequent locking and unlocking are required, which greatly affects the performance. As a result, the preceding constraints may not be met in multi-thread cases.

transaction_count indicates the number of transactions in all databases.

sent_transaction_count indicates the number of transactions sent by the local database because transactions that are not in the local database will not be sent.

If the value of slot_name does not exist, the function does not report an error and the return value is empty.

gs_logical_parallel_decode_reset_status('slot_name')

Description: Resets indicators in gs_logical_parallel_decode_status('slot_name').

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

Return type: text

Example:

gaussdb=# SELECT * FROM gs_logical_parallel_decode_reset_status('replication_slot');
 gs_logical_parallel_decode_reset_status
-----------------------------------------
 OK
(1 row)

gaussdb=# SELECT * FROM gs_logical_parallel_decode_status('replication_slot');
 stat_id |          stat_name           |      value
---------+------------------------------+------------------
       1 | slot_name                    | replication_slot
       2 | reader_lsn                   | 0/357E420
       3 | wal_read_total_time          | 0
       4 | wal_wait_total_time          | 0
       5 | parser_total_time            | 0
       6 | decoder_total_time           | 0
       7 | sender_total_time            | 0
       8 | net_send_total_time          | 0
       9 | net_wait_total_time          | 0
      10 | net_send_total_bytes         | 0
      11 | transaction_count            | 0
      12 | big_transaction_count        | 0
      13 | max_transaction_tuples       | 0
      14 | sent_transaction_count       | 0
      15 | spill_disk_transaction_count | 0
      16 | spill_disk_bytes             | 0
      17 | spill_disk_count             | 0
      18 | input_queue_full_count       | 0
      19 | output_queue_full_count      | 0
      20 | dml_count                    | 0
      21 | dml_filtered_count           | 0
      22 | toast_count                  | 0
      23 | candidate_catalog_xmin       | 0
      24 | candidate_xmin_lsn           | 0/0
      25 | candidate_restart_valid      | 0/420A598
      26 | candidate_restart_lsn        | 0/420A598
(26 rows)

Note: If the value of slot_name does not exist, the function does not report an error and the return value is invalid slot name.

Do not reset a replication slot that is being observed. The error information is as follows:
  1. If slot_name is empty, the following error is reported: "ERROR: inputString should not be NULL".
  2. If slot_name is not empty but does not exist, no error is reported but "invalid slot name" is displayed.
  3. If slot_name is not empty but the replication slot corresponding to slot_name is being observed, no error is reported but "can't reset during observing! use gs_logical_decode_stop_observe to stop" is displayed.

gs_logical_decode_start_observe('slot_name', window, interval)

Description: Enables logical replication performance sampling. In a distributed environment, this function must be executed on DNs.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • window

    Specifies the sampling window.

    Value range: an integer ranging from 2 to 1024. Sampling data in the latest interval x window period is collected.

  • interval

    Specifies the performance monitoring interval, in seconds.

    Value range: interval type. The minimum value is 1s and the maximum value is 1 minute. Sampling data in the latest interval x window period is collected.

    Return type: text

gs_logical_decode_stop_observe('slot_name')

Description: Stops logical replication performance sampling. In a distributed environment, this function must be executed on DNs.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • Return type: text

gs_logical_decode_observe_data('slot_name')

Description: Displays the original logical replication performance sampling data. In a distributed environment, this function must be executed on DNs.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • Return type: SETOF record

gs_logical_decode_observe('slot_name')

Description: Displays logical replication performance data. In a distributed environment, this function must be executed on DNs.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • Return type: SETOF record

gs_logical_decode_observe_status('slot_name')

Description: Queries the monitoring status of a specified logical decoding task. In a distributed environment, this function must be executed on DNs.

Parameter:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • Return type: text

gs_get_parallel_decode_thread_info()

Description: Executes on the DN where parallel decoding is performed and returns the thread information of parallel decoding.

Return type: int64, text, text, int

Example:

gaussdb=# SELECT * FROM gs_get_parallel_decode_thread_info();
    thread_id    | slot_name | thread_type | seq_number
-----------------+-----------+-------------+------------
 140335364699904 | slot1     | sender      |          1
 140335214098176 | slot1     | reader      |          1
 140335325312768 | slot1     | decoder     |          1
 140335291750144 | slot1     | decoder     |          2
 140335274968832 | slot1     | decoder     |          3
 140335258187520 | slot1     | decoder     |          4
 140335165404928 | slot2     | sender      |          1
 140335022864128 | slot2     | reader      |          1
 140335129818880 | slot2     | decoder     |          1
 140335113037568 | slot2     | decoder     |          2
(10 rows)

Note: In the return values, thread_id indicates the thread ID, slot_name indicates the replication slot name, thread_type indicates the thread type (including the sender, reader and decoder), and seq_number indicates the sequence number of each thread with same type in the current replication slot. Each parallel decoding connection only has one sender and reader. Therefore, the sequence numbers of the sender and reader are both 1. The sequence numbers of the decoder are arranged from 1 to the decoding degree of parallelism (DOP) of the current replication slot.

pg_replication_origin_create (node_name)

Description: Creates a replication source with a given external name and returns the internal ID assigned to it.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • node_name

    Name of the replication source to be created.

    Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Return type: oid

pg_replication_origin_drop (node_name)

Description: Deletes a previously created replication source, including any associated replay progress.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • node_name

    Name of the replication source to be deleted.

    Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

pg_replication_origin_oid (node_name)

Description: Searches for a replication source by name and returns the internal ID. If no such replication source is found, an error is thrown.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • node_name

    Specifies the name of the replication source to be queried.

    Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Return type: oid

pg_replication_origin_session_setup (node_name)

Description: Marks the current session for replaying from a given origin, allowing you to track replay progress. This parameter can be used only when no origin is selected. Run the pg_replication_origin_session_reset command to cancel the configuration.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • node_name

    Name of the replication source.

    Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

pg_replication_origin_session_reset ()

Description: Cancels the pg_replication_origin_session_setup() effect.

Note: The user who calls this function must have the SYSADMIN permission.

pg_replication_origin_session_is_setup ()

Description: Returns a true value if a replication source is selected in the current session.

Note: The user who calls this function must have the SYSADMIN permission.

Return type: Boolean

pg_replication_origin_session_progress (flush)

Description: Returns the replay position of the replication source selected in the current session.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • flush

    Determines whether the corresponding local transaction has been flushed to disk.

    Value range: Boolean

Return type: LSN

pg_replication_origin_xact_setup (origin_lsn, origin_timestamp)

Description: Marks the current transaction as recommitted at a given LSN and timestamp. This function can be called only when pg_replication_origin_session_setup is used to select a replication source.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • origin_lsn

    Position for replaying the replication source.

    Value range: LSN

  • origin_timestamp

    Time point when a transaction is committed.

    Value range: timestamp with time zone

pg_replication_origin_xact_reset ()

Description: Cancels the pg_replication_origin_xact_setup() effect.

Note: The user who calls this function must have the SYSADMIN permission.

pg_replication_origin_advance (node_name, lsn)

Description:

Sets the replication progress of a given node to a given position. This is primarily used to set the initial position, or to set a new position after a configuration change or similar change.

Note: Improper use of this function may cause inconsistent replication data.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • node_name

    Name of an existing replication source.

    Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

  • lsn

    Position for replaying the replication source.

    Value range: LSN

pg_replication_origin_progress (node_name, flush)

Description: Returns the position for replaying the given replication source.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

  • node_name

    Name of the replication source.

    Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

  • flush

    Determines whether the corresponding local transaction has been flushed to disk.

    Value range: Boolean

pg_show_replication_origin_status()

Description: Displays the replication status of the replication source.

Note: The user who calls this function must have the SYSADMIN permission.

Return type:

  • local_id: OID, which specifies the ID of the replication source.
  • external_id: text, which specifies the name of the replication source.
  • remote_lsn: LSN of the replication source.
  • local_lsn: local LSN.

gs_get_distribute_decode_status()

Description: Obtains the distributed decoding status details (by replication slot) on the current node. This command must be executed on CNs. If it is executed on DNs, null is returned.

Return type: text, int, int, int64, xid, xid, text, text, text

gaussdb=# SELECT * FROM gs_get_distribute_decode_status();
 slot_name | logical_receiver_num | slice_num | walsender_thread_id | last_sent_csn | last_confirmed_csn |      receiver_queue_length      |          connect_times          |                 csn_receive_array
-----------+----------------------+-----------+---------------------+---------------+--------------------+---------------------------------+---------------------------------+---------------------------------------------------
 slot1     |                    1 |         3 |     139958481843968 |       2012169 |            2010107 | queue0: 1, queue1: 1, queue2: 0 | slice0: 2, slice1: 2, slice2: 2 | slice0: 2012244, slice1: 2012244, slice2: 2012244
(1 row)
Table 2 Description

Statistical Item

Type

Description

slot_name

text

Replication slot name.

logical_receiver_num

int

Number of receiver threads started for distributed decoding.

slice_num

int

Number of shards in a cluster.

walsender_thread_id

int64

WAL sender thread ID.

last_sent_csn

xid

CSN that is sent recently.

last_confirmed_csn

xid

The last CSN returned from the client that has been confirmed for reception.

receiver_queue_length

text

Length of the log queue received on each DN (displayed in the character string format after concatenation).

connect_times

text

Number of times that each DN is connected (displayed in the character string format after concatenation).

csn_receive_array

text

Latest CSN obtained by each DN (displayed in the character string format after concatenation).

gs_get_distribute_decode_status_detail()

Description: Obtains the distributed decoding status details (by DN) on the current node. This command must be executed on CNs. If it is executed on DNs, null is returned.

Return type: text, int, int64, int, int, xid

gaussdb=# SELECT * FROM gs_get_distribute_decode_status_detail();
 slot_name | slice_id |    thread_id    | queue_len | connect_times | received_csn
-----------+----------+-----------------+-----------+---------------+--------------
 slot1     |        0 | 139959895848704 |         1 |             2 |      2012244
 slot1     |        1 | 139959895848704 |         1 |             2 |      2012244
 slot1     |        2 | 139959895848704 |         0 |             2 |      2012244
(3 rows)
Table 3 Description

Statistical Item

Type

Description

slot_name

text

Replication slot name.

slice_id

int

Shard ID (starting from 0).

thread_id

int64

ID of the receiver thread started for distributed decoding.

queue_len

int

Length of the log queue received on the current DN.

connect_times

int

Number of times that each DN is connected.

received_csn

xid

Latest CSN obtained from the current DN.

gs_logical_dictionary_baseline()

Description: Baselines the data dictionary data for logical decoding. If the operation is successful, the time required is returned. If the operation fails, the failure cause is returned.

Return type: text

gs_logical_dictionary_disabled()

Description: Disables the logical decoding data dictionary function and stops decoding incremental DDL statements. If the operation is successful, "OK" is returned. If the operation fails, the failure cause is returned.

Return type: text

gs_logical_decode_lock()

Description: Specifies the lock placing API for upgrading and managing the logical decoding component. If the lock is placed, t is returned. If the lock fails to be placed, f is returned. This function is for internal use only. You are advised not to use it. In the multi-tenancy scenario, this function is disabled in a PDB.

Parameter: wait_time specifies the lock waiting time. Value range: a non-negative number, in seconds. If no parameter is input, the lock waiting time is 0 seconds.

Return type: Boolean

gs_logical_decode_unlock()

Description: Specifies the lock releasing API for upgrading and managing the logical decoding component. There is no input parameter. If the lock is released, t is returned. If the lock fails to be released, f is returned. This function is for internal use only. You are advised not to use it. In the multi-tenancy scenario, this function is disabled in a PDB.

Return type: Boolean

gs_change_replication_slot_plugin('slot_name', 'plugin_name')

Description: Modifies the plug-in type of a logical replication slot.

Parameters:

  • slot_name

    Indicates the name of the streaming replication slot.

    Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

  • plugin_name

    Indicates the name of the plug-in.

    Value range: a string. Currently, mppdb_decoding, sql_decoding, parallel_binary_decoding, parallel_json_decoding, and parallel_text_decoding are supported.

    Return type: Boolean. The default value is true.

Example:

Query information about the current replication slot.

gaussdb=# SELECT * FROM pg_get_replication_slots();
 slot_name |          plugin          | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush | confirmed_csn | dictionary_csn_min | slot_dictionary_type 
-----------+--------------------------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------+---------------+--------------------+----------------------
 slot1     | parallel_json_decoding   | logical   |  12834 | f      |      |              | 0/125A15E0  | f             | 0/125E84D0      |               |               7787 | dictionary table
 slot2     | parallel_binary_decoding | logical   |  12834 | f      |      |              | 0/E9C71B0   | f             | 0/E9CA638       |               |               3860 | dictionary table
 slot3     | parallel_text_decoding   | logical   |  12834 | f      |      |              | 0/E9D96D0   | f             | 0/EEE6508       |               |               3867 | dictionary table
 slot4     | mppdb_decoding           | logical   |  12834 | f      |      |              | 0/E9AFC38   | f             | 0/EEE6508       |               |               3853 | dictionary table
(4 rows)

Change the replication slot type.

gaussdb=# SELECT * FROM gs_change_replication_slot_plugin('slot1', 'mppdb_decoding');
 gs_change_replication_slot_plugin 
-----------------------------------
 t
(1 row)

Query the replication slot information again.

gaussdb=# SELECT * FROM pg_get_replication_slots();
 slot_name |          plugin          | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush | confirmed_csn | dictionary_csn_min | slot_dictionary_type 
-----------+--------------------------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------+---------------+--------------------+----------------------
 slot1     | mppdb_decoding           | logical   |  12834 | f      |      |              | 0/125A15E0  | f             | 0/125E84D0      |               |               7787 | dictionary table
 slot2     | parallel_binary_decoding | logical   |  12834 | f      |      |              | 0/E9C71B0   | f             | 0/E9CA638       |               |               3860 | dictionary table
 slot3     | parallel_text_decoding   | logical   |  12834 | f      |      |              | 0/E9D96D0   | f             | 0/EEE6508       |               |               3867 | dictionary table
 slot4     | mppdb_decoding           | logical   |  12834 | f      |      |              | 0/E9AFC38   | f             | 0/EEE6508       |               |               3853 | dictionary table
(4 rows)

gs_get_inter_cluster_version_info()

Description: Queries the version number of primary and standby databases, the version of the logical decoding component, and the replication type (logical replication or physical replication). It is not supported in the current version.

Return type: int, text, int, text, text