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. In the multi-tenancy scenario, non-PDB creates replication slots for the current database, while PDB creates replication slots for itself.

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.

  • output_order

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

    Value range: 0 and 1. The default value is 0.

    • 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 a non-zero value. This replication slot is called a CSN-based replication slot.

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 primary node.

pg_create_physical_replication_slot('slot_name', 'isDummyStandby')

Description: Creates a physical replication slot. In the multi-tenancy scenario, PDB cannot create physical replication slots.

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.

  • isDummyStandby

    Reserved parameter.

    Type: bool

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. In the multi-tenancy scenario, non-PDB can delete replication slots for both non-PDB and PDB, while PDB can only delete replication slots for itself.

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.

Return type: void

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. Currently, this function can be called only on the primary node.

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.) In the multi-tenancy scenario, non-PDB can only operate replication slots for itself, and PDB can only operate replication slots for itself.

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.

  • 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 multiple pairs 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 disk.

      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 disk.

      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.
    • 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.

      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.

    • restart-lsn:

      Specifies the decoding start point, which is a logical decoding control parameter. A consistency LSN will be found from the restart-lsn to start decoding and output data. The found consistency LSN is flushed to disk and recorded in the replication slot file. If restart-lsn is not specified when pg_logical_slot_peek_changes is called next time, data is output from the consistency lsn recorded in the replication slot file.

      Value range: a string in the format of "XXXXXXXX/XXXXXXXX". The value 0/0 is invalid.

      This option is available only for replication slots of the multi-version data dictionary type.

    • 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=# 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=# 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 examples, 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.

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. In the multi-tenancy scenario, non-PDB can only operate replication slots for itself, and PDB can only operate replication slots for itself.

Parameters: 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. This function can be called on the primary or standby node. If this function is called on the standby node, the corresponding logical replication slot number on the primary node is updated.

If this function is executed on the standby node, a WAL sender of the primary node is occupied when the replication slot number on the primary node 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 node is updated normally. If this function is executed continuously in a short period of time, the primary node 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.) In the multi-tenancy scenario, non-PDB can only operate replication slots for itself, and PDB can only operate replication slots for itself.

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.

  • 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 multiple pairs 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 disk.

      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 disk.

      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.
    • restart-lsn:

      Specifies the decoding start point, which is a logical decoding control parameter. A consistency LSN will be found from the restart-lsn to start decoding and output data. The found consistency LSN is flushed to disk and recorded in the replication slot file. If restart-lsn is not specified when pg_logical_slot_peek_binary_changes is called next time, data is output from the consistency lsn recorded in the replication slot file.

      Value range: a string in the format of "XXXXXXXX/XXXXXXXX". The value 0/0 is invalid.

      This option is available only for replication slots of the multi-version data dictionary type.

    • 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.

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. In the multi-tenancy scenario, non-PDB can only operate replication slots for itself, and PDB can only operate replication slots for itself.

Parameters: This function has the same parameters as pg_logical_slot_peek_binary_changes. For details, see •pg_logical_slot_peek_bi....

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. Currently, this function can be called only on the primary node.

pg_replication_slot_advance ('slot_name', 'upto_lsn')

Description: Directly goes to the streaming replication slot for a specified upto_lsn, without outputting any decoded result. In the multi-tenancy scenario, the PDB on the primary node updates its own replication slot, while an error is reported when the PDB on the standby node updates its own 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.

  • 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 or no CSN is input, 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 the 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 or no LSN is input, the latest 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

Note: A return result contains the slot name and LSN or CSN that is actually used for decoding. 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. This function can be called on the primary or standby node. When this function is called on the standby node, the corresponding logical replication slot number on the primary node is updated.

This function can be executed on the standby node to synchronously update the corresponding logical replication slot number on the primary node. If this function is executed on the standby node, a WAL sender of the primary node is occupied when the replication slot number on the primary node 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 node is updated normally. If this function is executed continuously in a short period of time, the primary node fails to update the slot number and no error is reported.

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 the API is called, only when wal_level is set to logical, the generated log files can be parsed. If the used Xlog file is not of the logical level, the decoded content does not have the corresponding value and type, and 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.
  • 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 a DDL operation occurs between the decoding start point and the latest redo value. If the DDL operation occurs, the system does not decode all data. If a decoding file is specified, the system checks whether a DDL operation 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 the 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.

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 is not specified.

  • 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: Specifies optional parameters, consisting 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 stops if it exceeds either of the data-limit values.

      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.

      The size of a decoding result file may exceed the value of data-limit.

    • 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:

gaussdb=# CREATE TABLE t1(a int, b int);
CREATE TABLE
gaussdb=# SELECT pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/5ECBCD48
(1 row)

gaussdb=# INSERT INTO t1 VALUES(1,1);
INSERT 0 1
gaussdb=# UPDATE t1 SET b = 2 WHERE a = 1;
UPDATE 1
gaussdb=# DELETE FROM t1;
DELETE 1
gaussdb=# SELECT * FROM pg_logical_get_area_changes('0/5ECBCD48', NULL, NULL, 'sql_decoding', NULL);
  location  |  xid  |                                       data
------------+-------+----------------------------------------------------------------------------------
 0/5ECBCD78 | 70718 | insert into public.t1 values (1, 1);
 0/5ECBCEA0 | 70718 | COMMIT 70718 (at 2023-11-01 10:58:51.448885+08) 39319
 0/5ECBCED0 | 70719 | delete from public.t1 where a = 1 and b = 1;insert into public.t1 values (1, 2);
 0/5ECBD028 | 70719 | COMMIT 70719 (at 2023-11-01 10:58:56.487796+08) 39320
 0/5ECBD210 | 70720 | delete from public.t1 where a = 1 and b = 2;
 0/5ECBD338 | 70720 | COMMIT 70720 (at 2023-11-01 10:58:58.856661+08) 39321
(6 rows)

-- For a table with generated columns:
gaussdb=# CREATE TABLE t2(a int, b int GENERATED ALWAYS AS (a + 1) STORED);
CREATE TABLE
gaussdb=# SELECT pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/5F62CFE8
(1 row)

gaussdb=# INSERT INTO t2(a) VALUES(1);
INSERT 0 1
gaussdb=# UPDATE t2 set a = 2 where a = 1;
UPDATE 1
gaussdb=# DELETE FROM t2;
DELETE 1
gaussdb=# SELECT * FROM pg_logical_get_area_changes('0/5F62CFE8', NULL, NULL, 'sql_decoding', NULL, 'skip-generated-columns', 'on');
  location  |  xid  |                                data
------------+-------+---------------------------------------------------------------------
 0/5F62D0C8 | 71293 | insert into public.t2 values (1);
 0/5F62D1F0 | 71293 | COMMIT 71293 (at 2023-11-01 11:11:49.452044+08) 39516
 0/5F62D220 | 71294 | delete from public.t2 where a = 1;insert into public.t2 values (2);
 0/5F62D378 | 71294 | COMMIT 71294 (at 2023-11-01 11:11:54.327701+08) 39517
 0/5F62D408 | 71295 | delete from public.t2 where a = 2;
 0/5F62D530 | 71295 | COMMIT 71295 (at 2023-11-01 11:11:58.362057+08) 39518
(6 rows)

pg_get_replication_slots()

Description: Obtains the replication slot list. In the multi-tenancy scenario, all information is returned if this function is called in a non-PDB, and PDB data is returned if this function is called in a PDB.

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

Example:
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      |               |               2627 | dictionary table
 slot_test | mppdb_decoding | logical   | 131072 | f      |      |        66658 | 0/36251718  | f             |                 |      10025527 |                    | online catalog
(4 rows)

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.

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. In the multi-tenancy scenario, all information is returned if this function is called in a non-PDB, and PDB data is returned if this function is called in a PDB.

Table 1 Description

Column

Return Type

Description

slot_name

text

Replication slot name.

parallel_decode_num

int4

Specifies the number of parallel decoder threads of the replication slot.

read_change_queue_length

text

Current length of the log queue read by each decoder thread.

decode_change_queue_length

text

Current length of the decoding result decoded by each decoder thread.

reader_lsn

text

Specifies the location of the log read by the current reader thread.

working_txn_cnt

int8

Specifies the number of transactions that are being concatenated in the current sender thread.

working_txn_memory

int8

Specifies the total memory occupied by concatenation transactions in the sender thread, in bytes.

decoded_time

TimestampTz

Specifies the time of the latest WAL decoded by the replication slot.

reader_toast_memory

int8

Memory occupied by the TOAST of the current reader thread, in bytes.

large_transactions

text

Large transaction information decoded in the current replication slot (a maximum of 511 bytes).

out_buf_memory

int8

Memory occupied by the out buffer used by the current sender thread, in bytes.

Example:
gaussdb=# SELECT * FROM gs_get_parallel_decode_status();
-[ RECORD 1 ]--------------+-------------------------------------------
slot_name                  | slot1
parallel_decode_num        | 4
read_change_queue_length   | queue0: 0, queue1: 0, queue2: 0, queue3: 0
decode_change_queue_length | queue0: 0, queue1: 0, queue2: 0, queue3: 0
reader_lsn                 | 0/DB2E4C0
working_txn_cnt            | 0
working_txn_memory         | 0
decoded_time               | 2024-04-26 09:57:48+08
reader_toast_memory        | 0
large_transactions         | [xid, first_lsn, memory, entry num] : NULL
out_buf_memory             | 0

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. In the multi-tenancy scenario, a non-PDB can view information about a replication slot of either the non-PDB or the PDB. When a PDB calls this function, it returns information about a replication slot of the PDB itself.

Parameters:

  • 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_get_logical_decode_parameter()

Description: Queries the parameter options of active parallel logical decoding tasks. In the multi-tenancy scenario, all information is returned if this function is called in a non-PDB, and PDB data is returned if this function is called in a PDB.

Table 2 Description

Column

Return Type

Description

slot_name

text

Replication slot name.

include_xids

bool

Specifies whether the decoding information contains xid.

include_timestamp

bool

Specifies whether decoded information contains the timestamp.

skip_empty_xacts

bool

Specifies whether to ignore empty transactions in the decoding result.

only_local

bool

Specifies whether to decode only the data generated in the current database.

include_username

bool

Specifies whether the BEGIN log of a transaction contains the username.

enable_ddl_decoding

bool

Specifies whether to enable the decoding of DDL statements.

enable_ddl_json_format

bool

Specifies whether to enable the JSON output format of DDL reverse parsing.

max_txn_in_memory

int64

Maximum memory occupied by transactions. (This parameter is deprecated and does not take effect.)

max_reorderbuffer_in_memory

int64

Maximum memory for the current decoding task.

decode_style

char

Decoding format.

parallel_decode_num

int64

Number of decoder threads for parallel decoding.

sending_batch

int64

Specifies whether to send messages in batches.

parallel_queue_size

int64

Specifies the length of the queue for interaction between parallel logical decoding threads.

enable_heartbeat

bool

Specifies whether to generate heartbeat logs

slice_id

int64

Specifies the shard ID of the replication table.

output_start_csn

int64

CSN position where the output starts.

output_start_lsn

int64

LSN position where the output starts.

logical_reader_bind_cpu

int64

ID of the CPU bound to the reader thread.

logical_decoder_bind_cpu_index

int64

Index of the CPU bound to the decoder thread.

auto_advance

bool

Specifies whether to automatically update.

skip_generated_columns

bool

Specifies whether to skip the result output of the generated column.

big_transaction_limit

int64

Memory usage limit for determining whether a transaction is a large transaction, in bytes.

exclUser

text

Specifies the name list of blacklisted users.

table_white_list

text

Whitelist table parameter.

Example:

test=# select * from gs_get_logical_decode_parameter();
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
slot_name | slot1
configs   | include_xids: 1, include_timestamp: 1, skip_empty_xacts: 1, only_local: 1, include_username: 1, enable_ddl_decoding: 1, enable_ddl_json_format: 0, max_txn_in_memory: 3072, max_reorderbuffer_in_memory: 1, decode_style: j, parallel_decode_num: 4, sending_batch: 0, parallel_queue_size: 128, enable_heartbeat: 0, slice_id: -1, output_start_csn: 0, output_start_lsn: 0, logical_reader_bind_cpu: -1, logical_decoder_bind_cpu_index: -1, auto_advance: 0, skip_generated_columns: 0, big_transaction_limit: 10, exclUser: , table_white_list: 

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. In the multi-tenancy scenario, when a non-PDB calls this function, it returns information about a replication slot of either the PDB or the non-PDB. When a PDB calls this function, it returns data from a replication slot of the PDB itself.

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

Record - (stat_id int, stat_name TEXT, value TEXT)
Table 3 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 disk.

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.

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.

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 extreme 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'). In the multi-tenancy scenario, a non-PDB can reset both the PDB's and non-PDB's replication slot metrics, while a PDB can reset its own replication slot metrics.

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.

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 the multi-tenancy scenario, a non-PDB can enable logical replication performance metric sampling for both PDB and non-PDB, while a PDB can enable only its own logical replication performance metric sampling.

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.

  • 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

Example:

gaussdb=# SELECT * FROM gs_logical_decode_start_observe('replication_slot',20,5);
 gs_logical_decode_start_observe 
---------------------------------
 OK
(1 row)

gaussdb=# select * from gs_logical_decode_start_observe('replication_slot',20,5);
 gs_logical_decode_start_observe 
---------------------------------
 observe has started!
(1 row)

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 enable the observe function for 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 the value of window is less than 2, "window has to be >= 2" is displayed.
  4. If the value of window is greater than 1024, "window has to be <= 1024" is displayed.
  5. If the value of interval is less than 1s, "sample interval has to be >= 1s" is displayed.
  6. If the value of interval is greater than 60s, "sample interval has to be <= 60s" is displayed.
  7. If slot_name is not empty but the observe function has been enabled for the replication slot corresponding to slot_name, no error is reported and the message "observe has started!" is displayed.

gs_logical_decode_stop_observe('slot_name')

Description: Stops logical replication performance sampling. In the multi-tenancy scenario, a non-PDB can disable logical replication performance metric sampling for both PDB and non-PDB, while a PDB can disable only its own logical replication performance metric sampling.

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.

  • Return type: text

Example:

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

gaussdb=# SELECT * FROM gs_logical_decode_stop_observe('replication_slot');
 gs_logical_decode_stop_observe 
--------------------------------
 observe not started!
(1 row)

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 disable the observe function for a replication slot that is not 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 observe function has been disabled for the replication slot corresponding to slot_name, no error is reported and the message "observe not started!" is displayed.

gs_logical_decode_observe_data('slot_name')

Description: Displays the original logical replication performance sampling data. In the multi-tenancy scenario, when a non-PDB calls this function, it returns information about a replication slot of either the PDB or the non-PDB. When a PDB calls this function, it returns data from a replication slot of the PDB itself.

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.

  • Return type: SETOF record

Example:

gaussdb=# SELECT * FROM gs_logical_decode_observe_data('replication_slot');
 slot_name |          sample_time          | reader_lsn | wal_read_total_time | wal_wait_total_time | parser_total_time | decoder_total_time | sender_total_time | net_send_total_time | net_send_total_bytes | transaction_count | big_
transaction_count | sent_transaction_count | spill_transaction_count | spill_disk_bytes 
-----------+-------------------------------+------------+---------------------+---------------------+-------------------+--------------------+-------------------+---------------------+----------------------+-------------------+-----
------------------+------------------------+-------------------------+------------------
 repl      | 2023-01-12 20:09:40.416798+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:09:45.416849+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:09:50.417006+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:09:55.417057+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:00.417115+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:05.417165+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:10.417217+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:15.417271+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:20.417342+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:25.417433+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:30.417526+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:35.417532+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:40.417644+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:45.417763+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
(14 rows

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

gs_logical_decode_observe('slot_name')

Description: Displays logical replication performance data. In the multi-tenancy scenario, a PDB displays only its own logical replication performance metrics, while a non-PDB displays the metrics of both non-PDB and PDB.

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.

  • Return type: SETOF record

Example:

gaussdb=# SELECT * FROM gs_logical_decode_observe('replication_slot');
    slot_name     |          sample_time          | logical_decode_rate | wal_read_rate | parser_rate  |  decoder_rate  |  sender_rate  | net_send_rate 
------------------+-------------------------------+---------------------+---------------+--------------+----------------+---------------+---------------
 replication_slot | 2023-01-12 20:16:50.42448+08  | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:16:55.424537+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:00.424641+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:05.424645+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:10.424795+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:15.424848+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:20.424849+08 | 57.600              | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000
 replication_slot | 2023-01-12 20:17:25.424959+08 | 0.000               | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000
 replication_slot | 2023-01-12 20:17:30.42496+08  | 0.000               | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000
 replication_slot | 2023-01-12 20:17:35.425059+08 | 0.000               | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000

Note: If the value of slot_name does not exist, the function does not report an error and the return value is an empty record. If the denominator is 0, the latest collected valid data is returned. If the denominator is not 0 and the numerator is 0, 0 is returned.

Formula:

logical_decode_rate = (reader_lsn1 - reader_lsn2) / (sample_time1 - sample_time2)

wal_read_rate = (reader_lsn1 - reader_lsn2) / (wal_read_total_time1 - wal_read_total_time2) - (wal_wait_total_time1 - wal_wait_total_time2)

parser_rate = (reader_lsn1 - reader_lsn2) / (parser_total_time1 - parser_total_time2)

decoder_rate= (reader_lsn1 - reader_lsn2) / avg(decoder_total_time1[i]- decoder_total_time2[i])

sender_rate = (reader_lsn1 - reader_lsn2) / (sender_total_time1 - sender_total_time2) - (net_send_total_time1 - net_send_total_time2)

net_send_rate = (net_sent_bytes1 - net_sent_bytes2) / (net_send_total_time1 - net_send_total_time2) - (net_wait_total_time1 - net_wait_total_time2)

gs_logical_decode_observe_status('slot_name')

Description: Queries the monitoring status of a specified logical decoding task. In the multi-tenancy scenario, a PDB can view only the monitoring status of its own logical decoding task, while a non-PDB can view the monitoring status of both non-pdb and PDB logical decoding tasks.

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.

  • Return type: text

Example:

gaussdb=# SELECT * FROM gs_logical_decode_observe_status('replication_slot');
 gs_logical_decode_observe_status 
----------------------------------
 START
(1 row)

gaussdb=# SELECT * FROM gs_logical_decode_observe_status('replication_slot');
 gs_logical_decode_observe_status 
----------------------------------
 invalid slot name
(1 row)

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

gaussdb=# SELECT * FROM gs_logical_decode_observe_status('replication_slot');
 gs_logical_decode_observe_status 
----------------------------------
 STOP
(1 row)

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.

gs_get_parallel_decode_thread_info()

Description: Executes on the DN where parallel decoding is performed and returns the thread information of parallel decoding. In the multi-tenancy scenario, all information is returned if this function is called in a non-PDB, and PDB data is returned if this function is called in a PDB.

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.

Parameters:

  • 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.

Parameters:

  • 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.

Parameters:

  • 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.

Parameters:

  • 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.

Parameters:

  • flush

    Determines whether the corresponding local transaction has been written 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.

Parameters:

  • 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.

Parameters:

  • 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.

Parameters:

  • 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 decoding status details (by replication slot) on the current node. This function is not supported in the current version, and an error is reported.

Return type: text, int, int, bigint, xid, xid, text, text, text

gs_get_distribute_decode_status_detail()

Description: Obtains the decoding status details (by DN) on the current node. This function is not supported in the current version, and an error is reported.

Return type: text, int, bigint, int, int, xid

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. In the multi-tenancy scenario, when this function is called within a non-PDB, it baselines existing data on all non-PDBs; when called within a PDB, it baselines existing data only on the current session's PDB.

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. In the multi-tenancy scenario, when this function is called within a non-PDB, it disables the logical decoding data dictionary feature for all non-PDBs; when called within a PDB, it only disables the logical decoding data dictionary feature for the current session's PDB.

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_logical_decode_gaussdb_version()

Description: Specifies the query API for logical decoding component, which is used to view the commit records of the decoding component. There is no input parameter. 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: text

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). There is no input parameter. This is an internal function used for rolling upgrade of a two-cluster major version and is not recommended for users. In the multi-tenancy scenario, this function is disabled in a PDB. Five columns are returned: 1. remote_working_version (remote kernel version); 2. remote_logical_decoding_version (remote decoding component version); 3. local_working_version (kernel version of the current node); 4. local_logical_decoding_version (decoding component version of the current node); 5. replication_type (replication type, logic indicating logical replication; physic indicating physical replication).

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

gs_get_logical_decoding_version()

Description: Views the current logical decoding component version and the baseline logical decoding component version. 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.

Parameters:

  • current: displays the version of the current logical decoding component.
  • basic: displays the version of the baseline logical decoding component.

Return type: text

gs_add_logical_decoding_position_xlog()

Description: After this function is executed, Xlogs of the XLOG_STANDBY_DECODE_POSITION type are generated. The logs will be read by a decoding task in the current database to feed back the decoding position. Only users with the SYSADMIN, replication, or OPRADMIN permission can call this function; otherwise, the call fails. The execution frequency of this function is calculated based on the database statistics. The frequency of calling this function by each database is controlled by node. The minimum calling interval is 1 second.

Return type: text

gs_prepare_logical_standby_on_primary()

Description: Prepares for the physical-to-logical conversion on a primary node, writing logical standby metadata to physical logs. There is no input parameter. A UUID is returned. This function is for internal use only and can be executed only by administrators. You are advised not to use it. If a long transaction is not committed, no response is returned for a long time. In the multi-tenancy scenario, this function is disabled in a PDB.

Return type: text

gs_get_logical_standby_meta_for_switchover()

Description: Specifies the metadata required for preparing the logical standby node during a logical DR switchover. There is no input parameter or return value. This function is for internal use only and can be executed only by administrators. You are advised not to use it. In the multi-tenancy scenario, this function is disabled in a PDB.

Remarks: This is a high-risk operation function in logical DR scenarios. This function generates a logical standby node status file in the data directory. If this function is executed during a non-logical cluster switchover, the database instance status will be abnormal.

Return type: text

gs_get_logical_standby_info()

Description: Queries logical standby metadata. There is no input parameter, and five columns are returned. Column 1: current confirmed_csn of the logical standby node. Column 2: current restart_lsn of the logical standby node. Column 3: checksum corresponding to restart_lsn. Column 4: length recorded by restart_lsn. Column 5: UUID of each version metadata. 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: text

gs_get_logical_data_dictionary_column_num()

Description: Obtains the version information of the decoding data dictionary. There is no input parameter, and two columns are returned. Column 1: system catalog name of a data dictionary. Column 2: the number of columns in a data dictionary table. 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: text

gs_get_current_lgc_state()

Description: Obtains the status of the current logical standby node. There is no input parameter. 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: text

gs_stop_sqlapply()

Description: Manually stops the logical standby node. After the logical standby node is stopped, you need to use the gs_start_sqlapply() system function to start it again. After the logical standby node is manually stopped, it will not be restarted even if sql_apply_autorun is set to on. In the multi-tenancy scenario, this function is disabled in a PDB.

Return type: Boolean

gs_start_sqlapply()

Description: Manually starts the logical standby node. In the multi-tenancy scenario, this function is disabled in a PDB.

Return type: Boolean

gs_get_sqlapply_replaying_status()

Description: Queries the replay status of the current logical standby node. In the multi-tenancy scenario, this function is disabled in a PDB.

Table 4 Description

Column

Description

oldest_commited_txn_xid

XID of the latest committed transaction among the currently committed transactions.

oldest_commited_txn_csn

CSN of the latest committed transaction among the currently committed transactions.

oldest_commited_txn_sn

Sequence number of the latest committed transaction among the currently committed transactions.

restart_lsn

restart_lsn of the current logical standby node.

confirmed_csn

confirmed_csn of the current logical standby node.

playback_confirmed_csn

confirmed_csn of the transaction corresponding to the array head in the replay file of the logical standby node.

last_sec_replay_txn_cnt

Number of transactions replayed in the last second.

conflict_record_start_txn_sn

Start transaction ID of the row modified in conflict detection records.

conflict_record_total_num

Total number of rows modified in conflict detection records.

max_committed_txn_sn

Maximum ID of the committed transaction.

schedule_txn_xid

XID of the current scheduling transaction.

schedule_txn_sn

ID of the current scheduling transaction.

schedule_txn_commit_csn

commit_csn of the current scheduling transaction.

schedule_txn_depend_txn_max_sn

Maximum ID of the transaction on which the current scheduling transaction depends.

schedule_txn_number

Number of times the current transaction is scheduled.

replay_txn_total_number

Total number of replayed transactions.

replay_txn_total_time

Total time required for replaying all transactions. (unit: μs)

replay_txn_max_time

Maximum duration for replaying a single transaction. (unit: μs)

replay_txn_max_change_number

Maximum number of changes that can be replayed for a single transaction.

wait_schedule_txn_count

Number of waiting times for transaction scheduling.

wait_schedule_txn_total_time

Waiting time for transaction scheduling. (unit: μs)

free_sqlapply_worker_count

Number of idle replayer threads.

need_analyze_txn_count

Number of transactions to be concurrently analyzed in the analyzer thread queue.

need_schedule_txn_count

Number of transactions to be scheduled in the dispatcher thread queue.

is_dispatcher_suspended

Specifies whether the dispatcher thread is suspended.

dispatcher_suspended_start_time

Start time when the dispatcher thread is suspended.

log_receive_thread_restart_times

Number of restart times of the logical replay logs receiving thread.

sqlapply_failover_status

Current failover status of logical replay.

Value range:

  • FAILOVER_INIT: A failover has not been performed. This is the initial state.
  • FAILOVER_NOTICE_KILL: notifies the WAL receiver to exit.
  • FAILOVER_CHECK_SQL_APPLY_END: checks whether the logical standby node has exited.
  • FAILOVER_COPY_XLOG: copies log for failover.
  • FAILOVER_SUCCESS: The failover is successful.

sqlapply_cache_session_count

Number of cached sessions for logical replay.

sqlapply_recycle_log_ptr

Position of the last recycled log for logical replay.

sqlapply_receive_log_ptr

Position of the received log for logical replay.

sqlapply_end_ptr

End position of logical replay failover (or switchover).

sqlapply_decode_log_read_ptr

Read position of logical replay decoding log.

sqlapply_decode_txn_max_commit_csn

Maximum commit_csn of decoded transactions during logical replay.

sqlapply_init_dorado_write_log_ptr

Write position of the Dorado disk initialization log during a failover or switchover.

sqlapply_failover_start_time

Start time of a logical replay failover (or switchover).

sqlapply_logical_init_time

Time when the logical standby device is initialized.

sqlapply_thread_status

The status of threads related to SQL apply.

sqlapply_manual_stop

Specifies whether threads related to SQL apply have been manually stopped.

Example:

gaussdb=# SELECT * FROM gs_get_sqlapply_replaying_status();
 stat_id |             stat_name              |              value               
---------+------------------------------------+----------------------------------
       1 | oldest_commited_txn_xid            | 51675053
       2 | oldest_commited_txn_csn            | 51676580
       3 | oldest_commited_txn_sn             | 50847157
       4 | restart_lsn                        | 1A/5E9E2560
       5 | confirmed_csn                      | 829423
       6 | playback_confirmed_csn             | 51676492
       7 | last_sec_replay_txn_cnt            | 554
       8 | conflict_record_start_txn_sn       | 50841174
       9 | conflict_record_total_num          | 8645
      10 | max_committed_txn_sn               | 50847156
      11 | schedule_txn_xid                   | 51675057
      12 | schedule_txn_sn                    | 50847160
      13 | schedule_txn_commit_csn            | 51676583
      14 | schedule_txn_depend_txn_max_sn     | 50841174
      15 | schedule_txn_number                | 1784642
      16 | replay_txn_total_number            | 1784638
      17 | replay_txn_total_time              | 8644147345
      18 | replay_txn_max_time                | 511107457
      19 | replay_txn_max_change_number       | 2
      20 | wait_schedule_txn_count            | 674
      21 | wait_schedule_txn_total_time       | 4482
      22 | free_sqlapply_worker_count         | 0
      23 | need_analyze_txn_count             | 511
      24 | need_schedule_txn_count            | 511
      25 | is_dispatcher_suspended            | false
      26 | dispatcher_suspended_start_time    | 2024-07-31T23:06:33.676173+08:00
      27 | log_receive_thread_restart_times   | 1
      28 | sqlapply_failover_status           | FAILOVER_INIT
      29 | sqlapply_cache_session_count       | 4
      30 | sqlapply_recycle_log_ptr           | 1A/5DEA26A8
      31 | sqlapply_receive_log_ptr           | 1A/6CFD3218
      32 | sqlapply_end_ptr                   | 0
      33 | sqlapply_decode_log_read_ptr       | 1A/5EDB2038
      34 | sqlapply_decode_txn_max_commit_csn | 51677694
      35 | sqlapply_init_dorado_write_log_ptr | 0
      36 | sqlapply_failover_start_time       | 0
      37 | sqlapply_logical_init_time         | 0
      38 | sqlapply_thread_status             | SQLAPPLY_THREAD_START
      39 | sqlapply_manual_stop               | NO                                                                                                                                                  (39 rows)

gs_get_sqlapply_playback_progress()

Description: Queries the array information in the replay file of the logical standby node. The query result is the remote transaction information from the head position to the tail position in the replay file of the current logical standby node. All remote transactions before the head position have been replayed. Transaction in the tail position is the latest remote transaction that needs to be replayed in the current array. Remote transactions from the head position to the tail position have or have not been replayed. In the multi-tenancy scenario, this function is disabled in a PDB.

Note: If the current database instance is not a logical standby database instance, the query result is inaccurate and some fields use the default values.

Table 5 Description

Column

Description

xid

XID of the current remote transaction.

sequence_number

Sequence number of the current remote transaction that is being replayed.

remote_csn

CSN of the current remote transaction.

begin_time

Start time of the current remote transaction replay on the logical standby node.

end_time

End time of the current remote transaction replay on the logical standby node.

status

Specifies whether the remote transaction has been replayed on the logical standby node. t indicates that the replay is complete, and f indicates that the replay is not complete.

Example:

gaussdb=# SELECT * FROM gs_get_sqlapply_playback_progress();
  xid   | sequence_number | remote_csn |          begin_time           |           end_time            | status 
--------+-----------------+------------+-------------------------------+-------------------------------+--------
 254203 |             215 |     192108 | 2024-06-17 11:25:36.72872+08  | 2024-06-17 11:25:36.728941+08 | t
 254204 |             216 |     192109 | 2024-06-17 11:25:36.771637+08 | 2024-06-17 11:25:36.771723+08 | t
 254205 |             217 |     192110 | 2024-06-17 11:25:36.771663+08 | 2024-06-17 11:25:36.771854+08 | t
 254206 |             218 |     192111 | 2024-06-17 11:25:36.771777+08 | 2024-06-17 11:25:36.771977+08 | t
 254207 |             219 |     192112 | 2024-06-17 11:25:36.771902+08 | 2024-06-17 11:25:36.772105+08 | t
(5 rows)

gs_change_replication_slot_plugin('slot_name', 'plugin_name')

Description: Modifies the plug-in type of a logical replication slot. In the multi-tenancy scenario, this function is disabled in a PDB.

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_logicalstandby_skip(stmt text, dbname text, schema text, object text, dump text)

Description: Configures a rule for skipping objects during logical replay. It can only be executed by an administrator on the primary node of a standby database for DR to handle logical replay blocking issues. In the multi-tenancy scenario, this function is disabled in a PDB.

If any of the parameters, that is, stmt, dbname, schema, and object, differs from an existing configured rule, calling this function will add a new rule.

If all parameters, that is, stmt, dbname, schema, and object, are the same as those of an existing configured rule, calling this function will update the dump value.

Parameters:

  • stmt

    Type of a statement to be skipped during replay.

    Value range: a string. For details about the supported types, see Table 6.
    Table 6 Level-1 and level-2 statements that can be skipped

    Level-1 Statement Type

    Level-2 Statement Type

    dml

    insert/update/delete

  • dbname

    Name of a database to be skipped during replay.

    Value range: a string. The value is the same as that of database_name in CREATE DATABASE.

  • schema

    Name of a schema to be skipped during replay.

    Value range: a string. The value is the same as that of schema_name in CREATE SCHEMA.

  • object

    Name of an object to be skipped during replay. Currently, only the database table name is supported.

    Value range: a string.

  • dump

    Mode of recording transaction details.

    Value range: a string. The supported types are as follows: all records details of the entire transaction, no does not record details of the transaction, and skip records details of the transaction skip change.

    This parameter can be left empty. The default value is skip.

Return type: Boolean. The default value is true.

Example:

  • Set a rule to skip all DML statements for the systest database, public schema, and table t1, with dump set to all.
    gaussdb=# SELECT gs_logicalstandby_skip('dml', 'systest', 'public', 't1', 'all');
     gs_logicalstandby_skip 
    ------------------------
     t
    (1 row)
  • Set a rule to skip INSERT statements for the systest database, schema_test schema, and table t2, with dump left empty.
    gaussdb=# SELECT gs_logicalstandby_skip('insert', 'systest', 'schema_test', 't2');
     gs_logicalstandby_skip 
    ------------------------
     t
    (1 row)

gs_logicalstandby_unskip(stmt text, dbname text, schema text, object text)

Description: Cancels a rule for skipping objects during logical replay. It can only be executed by an administrator on the primary node of a standby database for DR to cancel the skipping rules during logical replay. In the multi-tenancy scenario, this function is disabled in a PDB.

Parameters:

  • stmt

    Type of a statement to be skipped during replay.

    Value range: a string. For details about the supported types, see Table 6.

  • dbname

    Name of a database to be skipped during replay.

    Value range: a string. The value is the same as that of database_name in CREATE DATABASE.

  • schema

    Name of a schema to be skipped during replay.

    Value range: a string. The value is the same as that of schema_name in CREATE SCHEMA.

  • object

    Name of an object to be skipped during replay. Currently, only the database table name is supported.

    Value range: a string.

Return type: Boolean. The default value is true.

Example:

  • Cancel a rule that skips all DML statements for the systest database, public schema, and table t1.
    gaussdb=# SELECT gs_logicalstandby_unskip('dml', 'systest', 'public', 't1');
     gs_logicalstandby_unskip 
    --------------------------
     t
    (1 row)

gs_logicalstandby_skip_txn(csn int64, xid int64, dump text)

Description: Configures a rule for skipping transactions during logical replay. It can only be executed by an administrator on the primary node of a standby database for DR to handle logical replay blocking issues. In the multi-tenancy scenario, this function is disabled in a PDB.

If any of the parameters, that is, csn and xid, differs from an existing configured rule, calling this function will add a new rule.

If all parameters, that is, csn and xid, are the same as those of an existing configured rule, calling this function will update the dump value.

Parameters:

  • csn

    CSN of the transaction to be skipped.

    Value range: a 64-bit integer ranging from 1 to 2^63 – 1.

  • xid

    ID of the transaction to be skipped.

    Value range: a 64-bit integer ranging from 3 to 2^63 – 1.

  • dump

    Mode of recording transaction details.

    Value range: a string. The supported types are as follows: all records details of the entire transaction, no does not record details of the transaction, and skip records details of the transaction skip change.

    This parameter can be left empty. The default value is skip.

    Values 'all' and 'skip' of dump in this system function are equivalent.

Return type: Boolean. The default value is true.

Example:

  • Set a rule to skip transaction with CSN 123456 and ID 345678, with dump set to all.
    gaussdb=# SELECT gs_logicalstandby_skip_txn(123456, 345678, 'all');
     gs_logicalstandby_skip_txn 
    ----------------------------
     t
    (1 row)
  • Set a rule to skip transaction with the CSN 123456 and ID 345678, with dump left empty.
    gaussdb=# SELECT gs_logicalstandby_skip_txn(123456, 345678);
     gs_logicalstandby_skip_txn 
    ----------------------------
     t
    (1 row)

gs_logicalstandby_unskip_txn(csn int64, xid int64)

Description: Cancels a rule for skipping transactions during logical replay. It can only be executed by an administrator on the primary node of a standby database for DR to cancel the skipping rules during logical replay. In the multi-tenancy scenario, this function is disabled in a PDB.

Parameters:

  • csn

    CSN of the transaction to be skipped.

    Value range: a 64-bit integer ranging from 1 to 2^63 – 1.

  • xid

    ID of the transaction to be skipped.

    Value range: a 64-bit integer ranging from 3 to 2^63 – 1.

Return type: Boolean. The default value is true.

Example:

  • Cancel a rule that skips transaction with the CSN 123456 and ID 345678.
    gaussdb=# SELECT gs_logicalstandby_unskip_txn(123456, 345678);
     gs_logicalstandby_unskip_txn 
    ------------------------------
     t
    (1 row)

gs_logicalstandby_skip_err(stmt text, dbname text, schema text, object text, dump text)

Description: Configures a rule for skipping transactions where error statements exist during logical replay. It can only be executed by an administrator on the primary node of a standby database for DR to handle logical replay blocking issues. In the multi-tenancy scenario, this function is disabled in a PDB.

If any of the parameters, that is, stmt, dbname, schema, and object, differs from an existing configured rule, calling this function will add a new rule.

If all parameters, that is, stmt, dbname, schema, and object, are the same as those of an existing configured rule, calling this function will update the dump value.

Parameters:

  • stmt

    Type of a statement to be skipped during replay.

    Value range: a string. For details about the supported types, see Table 6.

  • dbname

    Name of a database to be skipped during replay.

    Value range: a string. The value is the same as that of database_name in CREATE DATABASE.

  • schema

    Name of a schema to be skipped during replay.

    Value range: a string. The value is the same as that of schema_name in CREATE SCHEMA.

  • object

    Name of an object to be skipped during replay. Currently, only the database table name is supported.

    Value range: a string.

  • dump

    Mode of recording transaction details.

    Value range: a string. The supported types are as follows:

    • all: records details of the entire transaction.
    • no: does not record details of the transaction.
    • skip: records details of the transaction skip change.

    This parameter can be left empty. The default value is skip.

Return type: Boolean. The default value is true.

Example:

  • Set a rule to skip error DML statements for the systest database, public schema, and table t1, with dump set to all.
    gaussdb=# SELECT gs_logicalstandby_skip_err('dml', 'systest', 'public', 't1', 'all');
     gs_logicalstandby_skip_err 
    ----------------------------
     t
    (1 row)
  • Set a rule to skip error INSERT statements for the systest database, schema_test schema, and table t2, with dump left empty.
    gaussdb=# SELECT gs_logicalstandby_skip_err('insert', 'systest', 'schema_test', 't2');
     gs_logicalstandby_skip_err 
    ----------------------------
     t
    (1 row)

gs_logicalstandby_unskip_err(stmt text, dbname text, schema text, object text)

Description: Cancels the rule for skipping transactions where error statements exist during logical replay. It can only be executed by an administrator on the primary node of a standby database for DR to cancel the skipping rules during logical replay. In the multi-tenancy scenario, this function is disabled in a PDB.

Parameters:

  • stmt

    Type of a statement to be skipped during replay.

    Value range: a string. For details about the supported types, see Table 6.

  • dbname

    Name of a database to be skipped during replay.

    Value range: a string. The value is the same as that of database_name in CREATE DATABASE.

  • schema

    Name of a schema to be skipped during replay.

    Value range: a string. The value is the same as that of schema_name in CREATE SCHEMA.

  • object

    Name of an object to be skipped during replay. Currently, only the database table name is supported.

    Value range: a string.

Return type: Boolean. The default value is true.

Example:

  • Cancel a rule that skips error DML statements for the systest database, public schema, and table t1.
    gaussdb=# SELECT gs_logicalstandby_unskip_err('dml', 'systest', 'public', 't1');
     gs_logicalstandby_unskip_err 
    ------------------------------
     t
    (1 row)