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, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One period or two periods cannot be used as the replication slot name.
- plugin_name
  Indicates the name of the plugin.
  
  Value range: a string, supporting only mppdb_decoding
Return type: name, text

Note: The first return value is the slot name, and the second one is the start LSN for decoding in the logical replication slot. Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.
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, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One period or two periods cannot be used as the replication slot name.
- isDummyStandby
  Specifies whether the replication slot is created by connecting the standby node to the primary node.
  
  Type: Boolean
Return type: name, text

Note: Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.
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, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One period or two periods cannot be used 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 the gs_role_replication permission of the built-in role.
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 decoding 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, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One period or two periods cannot be used as the 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 a null value, 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 complete.
  
  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
    Specifies 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
    Specifies 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
    Specifies 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.
    
    Valid value: 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 results. Each decoding result contains three columns, corresponding to the above return types and indicating the LSN, XID, and decoded content, respectively. Users who call this function must have the SYSADMIN permission or 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 call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.
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, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One period or two periods cannot be used as the 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 a null value, 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 complete.
  
  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
    Specifies 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
    Specifies 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
    Specifies 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.
    
    Valid value: 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 and 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 results. Each decoding result contains three columns, corresponding to the above return types and indicating the LSN, XID, and decoded content in binary format, respectively. Users who call this function must have the SYSADMIN permission or 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 call 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 decoding result.

Parameter description:
- 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 period or two periods cannot be used as the replication slot name.
- LSN
  Indicates a target LSN. Next decoding will be performed only in transactions whose commit position is greater than this value. If an input LSN is smaller than the position recorded in the current streaming replication slot, the function is directly returned. 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 call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.

pg_get_replication_slots()

Description: Obtains the replication slot list.

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)

Return value: text, text, text, oid, boolean, xid, xid, text, boolean, text

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 operation is performed.

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.

Note: Users who call this function must have the SYSADMIN permission or the O&M administrator permission in O&M mode.

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 a null value, 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 a null value, 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 complete.
  
  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
...
```
gs_get_parallel_decode_status()
Description: Monitors the length of the read log queue and decoding result queue of each decoding 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: Users who call this function must have the SYSADMIN permission.

Parameter description:
- node_name
  Specifies the 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: Users who call this function must have the SYSADMIN permission.

Parameter description:
- node_name
  Specifies the 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: Users who call 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 trace replay progress. This function can be used only when no origin is selected. Run the pg_replication_origin_session_reset command to cancel the configuration.

Note: Users who call this function must have the SYSADMIN permission.

Parameter description:
- node_name
  Specifies the 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: Users who call 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: Users who call 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: Users who call this function must have the SYSADMIN permission.

Parameter description:
- flush
  Specifies whether the corresponding local transaction has been flushed to disks.
  
  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: Users who call this function must have the SYSADMIN permission.

Parameter description:
- origin_lsn
  Specifies the position for replaying the replication source.
  
  Value range: LSN
- origin_timestamp
  Specifies the time 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: Users who call 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: Users who call this function must have the SYSADMIN permission.

Parameter description:
- node_name
  Specifies the name of an existing replication source.
  
  Value range: a string, supporting only letters, digits, and the following special characters: _?-.
- lsn
  Specifies the 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: Users who call this function must have the SYSADMIN permission.

Parameter description:
- node_name
  Specifies the name of the replication source.
  
  Value range: a string, supporting only letters, digits, and the following special characters: _?-.
- flush
  Specifies whether the corresponding local transaction has been flushed to disks.
  
  Value range: Boolean
pg_show_replication_origin_status()
Description: Displays the replication status of the replication source.

Note: Users who call 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
  Specifies the name of an existing publication.
  
  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:

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

Parameter description:
subid
Specifies the OID of a subscription.

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.