Updated on 2023-10-23 GMT+08:00

Logical Replication Functions

  • pg_create_logical_replication_slot('slot_name', 'plugin_name')

    Description: Creates a logical replication slot.

    Parameter description:

    • slot_name

      Indicates the name of the streaming replication slot.

      Value range: a string of characters, which can contain only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). The '.' or '..' cannot be used as a replication slot name.

    • plugin_name

      Indicates the name of the plugin.

      Value range: a string, supporting mppdb_decoding

    Return type: name, text

    Note: The first return value is the slot name, and the second is the start LSN position for decoding in the logical replication slot. Users who invoke this function must have the SYSADMIN permission, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role. Currently, this function can be invoked only on the host.

  • pg_create_physical_replication_slot('slot_name', 'isDummyStandby')

    Description: Creates a physical replication slot.

    Parameter description:

    • slot_name

      Indicates the name of the streaming replication slot.

      Value range: a string of characters, which can contain only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). The '.' or '..' cannot be used as a replication slot name.

    • isDummyStandby

      Specifies whether the replication slot is created by connecting the secondary node to the primary node.

      Type: bool

    Return type: name, text

    Note: Users who invoke this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role. Currently, primary/standby/secondary deployment is not supported by default.

  • pg_drop_replication_slot('slot_name')

    Description: Deletes a streaming replication slot.

    Parameter description:

    • slot_name

      Indicates the name of the streaming replication slot.

      Value range: a string of characters, which can contain only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). The '.' or '..' cannot be used as a replication slot name.

    Return type: void

    Note: Users who invoke this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role. Currently, this function can be invoked only on the host.

  • pg_logical_slot_peek_changes('slot_name', 'LSN', upto_nchanges, 'options_name', 'options_value')

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

    Parameter description:
    • slot_name

      Indicates the name of the streaming replication slot.

      Value range: a string of characters, which can contain only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). The '.' or '..' cannot be used as a replication slot name.

    • LSN

      Indicates a target LSN. Decoding is performed only when an LSN is less than or equal to this value.

      Value range: a string, in the format of xlogid/xrecoff, for example, 1/2AAFC60 (If this parameter is set to NULL, the target LSN indicating 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.

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

        Indicates whether the decoded data column contains XID information.

        Valid value: 0 and 1. The default value is 1.

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

        Indicates whether to ignore empty transaction information during decoding.

        Valid value: 0 and 1. The default value is 0.

        • 0: The empty transaction information is not ignored during decoding.
        • 1: The empty transaction information is ignored during decoding.
      • include-timestamp

        Indicates whether decoded information contains the commit timestamp.

        Valid value: 0 and 1. The default value is 0.

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

        Specifies whether to decode only local logs.

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

        • 0: Non-local logs and local logs are decoded.
        • 1: Only local logs are decoded.
      • force-binary

        Specifies whether to output the decoding result in binary format.

        Value range: 0

        • 0: The decoding result is output in text 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_changes('slot1', NULL, 4096, 'white-table-list', 'public.t1,public.t2');

      • max-txn-in-memory

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

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

      • max-reorderbuffer-in-memory

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

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

    Return type: text, xid, text

    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, respectively.

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

  • pg_logical_slot_get_changes('slot_name', 'LSN', upto_nchanges, 'options_name', 'options_value')

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

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

    Note: Users who invoke this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role. Currently, this function can be invoked only on the host.

  • pg_logical_slot_peek_binary_changes('slot_name', 'LSN', upto_nchanges, 'options_name', 'options_value')

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

    Parameter description:
    • slot_name

      Indicates the name of the streaming replication slot.

      Value range: a string of characters, which can contain only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). The '.' or '..' cannot be used as a replication slot name.

    • LSN

      Indicates a target LSN. Decoding is performed only when an LSN is less than or equal to this value.

      Value range: a string, in the format of xlogid/xrecoff, for example, 1/2AAFC60 (If this parameter is set to NULL, the target LSN indicating 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.

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

        Indicates whether the decoded data column contains XID information.

        Valid value: 0 and 1. The default value is 1.

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

        Indicates whether to ignore empty transaction information during decoding.

        Valid value: 0 and 1. The default value is 0.

        • 0: The empty transaction information is not ignored during decoding.
        • 1: The empty transaction information is ignored during decoding.
      • include-timestamp

        Indicates whether decoded information contains the commit timestamp.

        Valid value: 0 and 1. The default value is 0.

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

        Specifies whether to decode only local logs.

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

        • 0: Non-local logs and local logs are decoded.
        • 1: Only local logs are decoded.
      • force-binary

        Specifies whether to output the decoding result in binary format.

        Value range: 0 or 1. The default value is 0. 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');

    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 invoke this function must have the SYSADMIN permission, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.

  • pg_logical_slot_get_binary_changes('slot_name', 'LSN', upto_nchanges, 'options_name', 'options_value')

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

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

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

  • pg_replication_slot_advance ('slot_name', 'LSN')

    Description: Directly goes to the streaming replication slot for a specified LSN, without outputting any decoded result.

    Parameter description:

    • slot_name

      Indicates the name of the streaming replication slot.

      Value range: a string of characters, which can contain only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). The '.' or '..' cannot be used as a replication slot name.

    • LSN

      Indicates a target LSN. Next decoding will be performed only in transactions whose commission position is greater than this value. If an input LSN is smaller than the position recorded in the current streaming replication slot, the function directly returns. If the input LSN is greater than the LSN of the current physical log, the latter LSN will be directly used for decoding.

      Value range: a string, in the format of xlogid/xrecoff

    Return type: name, text

    Note: A return result contains the slot name and LSN that is actually used for decoding. Users who invoke this function must have the SYSADMIN permission, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role. Currently, this function can be invoked only on the host.

  • pg_logical_get_area_changes('LSN_start', 'LSN_end', upto_nchanges, 'decoding_plugin', 'xlog_path', 'options_name', 'options_value')

    Description: Specifies an LSN range or an Xlog file for decoding when no DDL is available.

    The constraints are as follows:
    1. 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.
    2. 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 and no DDL or VACUUM FULL operation is performed.
    3. You can find the XLog to be parsed.
    4. Do not read too many Xlog files at a time. You are advised to read one Xlog file at a time. It is estimated that the memory occupied by one Xlog file is two to three times the size of the Xlog file.
    5. The Xlog file before scale-out cannot be decoded.

    Parameter description:

    • LSN_start

      Specifies the LSN at the start of decoding.

      Value range: a string, in the format of xlogid/xrecoff, for example, 1/2AAFC60 (If this parameter is set to NULL, the target LSN indicating the end position of decoding is not specified.)

    • LSN_end

      Specifies the LSN at the end of decoding.

      Value range: a string, in the format of xlogid/xrecoff, for example, 1/2AAFC60 (If this parameter is set to NULL, the target LSN indicating the end position of decoding is not specified.)

    • upto_nchanges

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

      Value range: a non-negative integer

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

    • decoding_plugin

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

    Value range: mppdb_decoding and sql_decoding.

    • xlog_path

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

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

    • options: This parameter is optional and consists of a series of options_name and options_value. You can retain the default value. For details, see pg_logical_slot_peek_changes.

    Example:

    openGauss=# SELECT pg_current_xlog_location();
     pg_current_xlog_location 
    --------------------------
     0/E62E238
    (1 row)
    
    openGauss=# create table t1 (a int primary key,b int,c int);
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
    CREATE TABLE
    openGauss=# insert into t1 values(1,1,1);
    INSERT 0 1
    openGauss=# insert into t1 values(2,2,2);
    INSERT 0 1
    
    openGauss=# select data from pg_logical_get_area_changes('0/E62E238',NULL,NULL,'sql_decoding',NULL);
     location  |  xid  |                       data                        
    -----------+-------+---------------------------------------------------
     0/E62E8D0 | 27213 | COMMIT (at 2022-01-26 15:08:03.349057+08) 3020226
     0/E6325F0 | 27214 | COMMIT (at 2022-01-26 15:08:07.309869+08) 3020234
    ......

  • pg_get_replication_slots()

    Description: Obtains the replication slot list.

    Return type: text, text, text, oid, boolean, xid, xid, text, boolean, text

    Example:
    openGauss=# select * from pg_get_replication_slots();
     slot_name |     plugin     | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush
    -----------+----------------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------
     dn_s1     |                | physical  |      0 | t      |      |              | 0/23DB14E0  | f             |
     slot1     | mppdb_decoding | logical   |  16304 | f      |      |        60966 | 0/1AFA1BB0  | f             | 0/23DA5700
    (2 rows)
  • gs_get_parallel_decode_status()

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

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

    Example:

    openGauss=# select * from gs_get_parallel_decode_status();
     slot_name | parallel_decode_num | read_change_queue_length  | decode_change_queue_length | reader_lsn | working_txn_cnt | working_txn_memory
    -----------+---------------------+---------------------------+----------------------------+------------+-----------------+--------------------
     slot1     |                   2 | queue0: 1005, queue1: 320 | queue0: 63, queue1: 748    | 0/1DCE2578 |              42 |          192927504
    (1 row)

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

  • pg_replication_origin_create (node_name)

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

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

    Parameter description:

    • node_name

      Name of the replication source to be created.

      Value range: a string, supporting only letters, digits, and the following special characters: _?-.

    Return type: oid

  • pg_replication_origin_drop (node_name)

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

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

    Parameter description:

    • node_name

      Name of the replication source to be deleted.

      Value range: a string, supporting only letters, digits, and the following special characters: _?-.

  • pg_replication_origin_oid (node_name)

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

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

    Parameter description:

    • node_name

      Specifies the name of the replication source to be queried.

      Value range: a string, supporting only letters, digits, and the following special characters: _?-.

    Return type: oid

  • pg_replication_origin_session_setup (node_name)

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

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

    Parameter description:

    • node_name

      Name of the replication source.

      Value range: a string, supporting only letters, digits, and the following special characters: _?-.

  • pg_replication_origin_session_reset ()

    Description: Cancels the pg_replication_origin_session_setup() effect.

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

  • pg_replication_origin_session_is_setup ()

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

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

    Return type: Boolean

  • pg_replication_origin_session_progress (flush)

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

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

    Parameter description:

    • flush

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

      Value range: Boolean

    Return type: LSN

  • pg_replication_origin_xact_setup (origin_lsn, origin_timestamp)

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

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

    Parameter description:

    • origin_lsn

      Position for replaying the replication source.

      Value range: LSN

    • origin_timestamp

      Time point when a transaction is committed

      Value range: timestamp with time zone

  • pg_replication_origin_xact_reset ()

    Description: Cancels the pg_replication_origin_xact_setup() effect.

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

  • pg_replication_origin_advance (node_name, lsn)

    Description:

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

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

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

    Parameter description:

    • node_name

      Name of an existing replication source.

      Value range: a string, supporting only letters, digits, and the following special characters: _?-.

    • lsn

      Position for replaying the replication source.

      Value range: LSN

  • pg_replication_origin_progress (node_name, flush)

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

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

    Parameter description:

    • node_name

      Name of the replication source.

      Value range: a string, supporting only letters, digits, and the following special characters: _?-.

    • 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.
  • pg_get_publication_tables(pub_name)

    Description: Returns the relid list of tables to be published based on the publication name.

    Parameter description:

    • pub_name

      Existing publication name.

      Value range: a string, supporting only letters, digits, and the following special characters: _?-.

    Return type: relid list

  • pg_stat_get_subscription(sub_oid oid) → record

    Description:

    Rerurns the subscription status information after a subscription OID is entered.

    Parameter description:

  • subid

    Subscription OID.

    Value range: oid

    Return type:

    • relid: OID of the table.
    • pid: thread_id, which indicates the thread ID of the background apply/sync thread.
    • received_lsn: pg_lsn, which indicates the latest LSN received from the publisher.
    • last_msg_send_time: timestamp, which indicates the time when the last message is sent from the publisher.
    • last_msg_receipt_time: timestamp, which indicates the time when the last message is received by the subscriber.
    • latest_end_lsn: pg_lsn, which indicates the LSN of the publisher when the last keepalive message is received.
    • latest_end_time: timstamp, which indicates the time when the last keepalive message is received.