Overview

Description

Logical decoding provides basic transaction decoding capabilities for logical replication. GaussDB uses SQL functions for logical decoding. This method features easy function calling, requires no tools to obtain logical logs, and provides specific APIs for interconnecting with external replay tools, saving the need of additional adaptation.

Prerequisites

• Currently, logical logs are extracted from CNs or DNs. If logical replication is performed, SSL connections must be used. Therefore, ensure that the ssl parameter on the corresponding node is set to on.
  

  For security purposes, ensure that SSL connections are enabled.

• The GUC parameter wal_level is set to logical.
• The GUC parameter max_replication_slots is set to a value greater than or equal to the number of physical streaming replication slots, backup slots, and logical replication slots required by each node.
  

  Plan the number of logical replication slots as follows:

  • A logical replication slot can carry changes of only one database for decoding. If multiple databases are involved, create multiple logical replication slots.
  • If logical replication is needed by multiple target databases, create multiple logical replication slots in the source database. Each logical replication slot corresponds to one logical replication link.
  • A maximum of 20 logical replication slots can be enabled for decoding on the same instance.
• A user needs to connect to a database through a DN port before using SQL functions to perform logical decoding. For details, see Logical Decoding by SQL Functions. If a CN port is used to connect to the database, EXECUTE DIRECT ON (datanode_name) 'statement' is needed to execute SQL functions.
• Only initial users and users with the REPLICATION permission can perform this operation. When separation of duties is disabled, database administrators can perform logical replication operations. When separation of duties is enabled, database administrators are not allowed to perform logical replication operations.

Precautions

• Logical decoding does not support DDL statements.
• Decoded data may be lost when a specific DDL statement (for example, to truncate an ordinary table or exchange a partitioned table) is executed.
• When the logical replication function is used, it is recommended that the number of DDL operations in a transaction be less than or equal to 1000. The memory occupied by the data generated by the DDL operations in a logical decoding task is released when the commit log of the transaction where the DDL operations are located is decoded.
• Decoding of DML operations on data page replication is not supported.
• After a DDL statement (for example, ALTER TABLE) is executed, the physical logs that are not decoded before the DDL statement execution may be lost.
• Logical replication does not support online cluster scale-out. Before online scale-out, you need to delete existing logical replication slots. After the scale-out is complete, create logical replication slots again.
• The size of a single tuple cannot exceed 1 GB, and decoding results may be larger than inserted data. Therefore, it is recommended that the size of a single tuple be less than or equal to 500 MB.
• GaussDB supports the following types of data to be decoded: INTEGER, BIGINT, SMALLINT, TINYINT, SERIAL, SMALLSERIAL, BIGSERIAL, FLOAT, DOUBLE PRECISION, BOOLEAN, BIT(n), BIT VARYING(n), DATE, TIME[WITHOUT TIME ZONE], TIMESTAMP[WITHOUT TIME ZONE], CHAR(n), VARCHAR(n), TEXT, and CLOB (decoded into the text format).
• For floating-point data, the precision parameter extra_float_digits of decoding results is set to 3.
• The name of a logical replication slot can contain no more than 64 characters, including lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). In addition, periods (. or ..) cannot be used as the name of a logical replication slot independently.
• To decode multiple databases, you need to create a streaming replication slot in each database and start decoding. Logs need to be scanned for decoding of each database.
• Forcible switchover is not supported. After forcible switchover, you need to export all data again.
• During decoding on the standby node, the decoded data may increase due to switchover or failover, which needs to be manually filtered out. When the quorum protocol is used, switchover and failover should be performed on the standby node that is to be promoted to primary, and logs must be synchronized from the primary node to the standby node.
• Replication slots can be created or deleted only on CNs and primary DNs. If the replication slot to be deleted is the last one, alarms "replicationSlotMinLSN is INVALID_WAL_REC_PTR!!!" and "replicationSlotMaxLSN is INVALID_WAL_REC_PTR!!!" are generated after the deletion is complete.
• The same replication slot for decoding cannot be used between the primary and standby DNs or between different standby DNs at the same time. Otherwise, data inconsistency occurs.
• After the database is restarted due to a fault or the logical replication process is restarted, duplicate decoded data may exist. You need to filter out the duplicate data.
• If the computer kernel is faulty, garbled characters may be displayed during decoding, which need to be manually or automatically filtered out.
• Ensure that no long transaction is started during the creation of a logical replication slot. If a long transaction is started, the creation of the logical replication slot will be blocked.
• Replication of DML operations on interval partitioned tables is not supported.
• Decoding of DML operations on global temporary tables is not supported.
• Decoding of DML operations on local temporary tables is not supported.
• In M-compatible mode, decoding of the SELECT INTO statement is not supported. In non-M-compatible mode, for the SELECT INTO statement, only the DDL operations for creating the target table are decoded; the DML operations for inserting data will not be decoded.
• To parse the UPDATE and DELETE statements of an Astore 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 about the configuration method, see column REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } in "SQL Reference > SQL Syntax > ALTER TABLE" in Developer Guide.
• Do not perform operations on the replication slot on other nodes when the logical replication slot is in use. To delete a replication slot, stop decoding in the replication slot first.
• Considering that the target database may require the system status information of the source database, logical decoding automatically filters only logical logs of system catalogs whose OIDs are less than 16384 in the pg_catalog and pg_toast schemas. If the target database does not need to copy the content of other related system catalogs, the related system catalogs need to be filtered during logical log replay.
• When logical replication is enabled, if you need to create a primary key index that contains system columns, you must set the REPLICA IDENTITY attribute of the table to FULL or use USING INDEX to specify a unique, non-local, non-deferrable index that does not contain system columns and contains only columns marked NOT NULL.
• If a replication table exists before scale-in or upgrade, you need to manually set the logical_repl_node attribute or reset to the default value for the replication table. For details, see the usage of the storage_parameter parameter and the logical_repl_node attribute in "SQL Reference > SQL Syntax > ALTER TABLE" in Developer Guide.
• If a transaction has too many sub-transactions, too many files are flushed to disks. To exit decoding, you need to run the SQL function pg_terminate_backend (walsender thread ID for logical decoding) to manually stop decoding. In addition, the exit delay increases by about 1 minute per 300,000 sub-transactions. Therefore, when logical decoding is enabled, if the number of sub-transactions of a transaction reaches 50,000, a WARNING log is generated.
• When a logical replication slot is inactive, GUC parameters enable_xlog_prune is set to on, enable_logicalrepl_xlog_prune is set to on, max_size_for_xlog_retention is set to a non-zero value, the number of retained log segments caused by the backup slot or logical replication slot exceeds the value of wal_keep_segments, and other replication slots do not cause more retained log segments, if the value of max_size_for_xlog_retention is greater than 0 and the number of retained log segments (the size of each log segment is 16 MB) caused by the current logical replication slot exceeds the value of max_size_for_xlog_retention, or if the value of max_size_for_xlog_retention is less than 0 and the disk usage reaches the value of –max_size_for_xlog_retention/100, the logical replication slot is forcibly invalidated and restart_lsn is set to 7FFFFFFF/FFFFFFFF. Logical replication slots in this state do not participate in the recycling of blocked logs or historical system catalogs, but the limitation on the maximum number of replication slots still takes effect. In this case, you need to manually delete them.
• After the standby node starts decoding and sends an instruction of updating the replication slot number to the primary node, the standby node occupies a corresponding logical replication slot (identified as an active state) on the primary node. Before that, the corresponding logical replication slot on the primary node is inactive. In this state, if the condition for forcibly invalidating the logical replication slot is met, the logical replication slot is marked as invalid (that is, restart_lsn is set to 7FFFFFFF/FFFFFFFF). As a result, the standby node cannot update the replication slot on the primary node. In addition, after the standby node replays the logs indicating that the replication slot is invalid, the standby node of the current replication slot cannot be reconnected if decoding is disconnected.
• Inactive logical replication slots block WAL recycling and historical system catalog tuple clearing. As a result, disk logs are accumulated and system catalog scanning performance deteriorates. Therefore, you need to clear logical replication slots that are no longer used in time. During the observation period before the upgrade is committed, the extended IP address of the DN is used to connect to the logical replication slot created on the DN. Before the upgrade rollback, manually clear the logical replication slot. Otherwise, the DN cannot be directly connected to clear the logical replication slot when the extended IP address feature of the DN is rolled back.
• Logical decoding with strong consistency in a distributed system (with CNs connected) supports only GTM-lite distributed deployment and streaming decoding. It does not support CNs connecting to standby DNs for decoding, SQL logical decoding functions, online scale-out, or global indexes.
• For logical decoding with strong consistency in a distributed system (with CNs connected), the CN HA is switched by the service.
• The CSN-based logical replication slot on the CN is only a placeholder, its slot number does not update along with logical decoding, and log recycling is not blocked.
• If a protocol is used to connect to a CN to create a logical replication slot, only CSN-based replication slots are supported. If a protocol is used to connect to a DN to create a logical replication slot, only LSN-based replication slots are supported.
• For distributed decoding, if an error is reported or the decoding client is manually stopped, wait for 15 seconds and try decoding again. If a replication slot is occupied, run the command pg_terminate_backend(ID of the thread that occupies the replication slot) to manually release the replication slot.
• If an error is reported when a replication slot fails to be created on a CN, delete the replication slot on the CN and create a replication slot on the CN again.
• When a logical replication slot is deleted from a CN, if the logical replication slot is an LSN-based logical replication slot, only the replication slot of the current node is deleted. Replication slots with the same name on other nodes are not affected. When a CSN-based logical replication slot with the same name exists on other nodes, no error is reported because some nodes do not have replication slots. In addition, replication slots with the same name on all nodes are successfully deleted. If no replication slot exists on any node, an error is reported.
• When a CSN-based logical replication slot is created on a CN, if there are residual LSN-based logical replication slots with the same name on some nodes, you need to delete the residual replication slots on these nodes. Otherwise, CSN-based logical replication slots will be created on CNs and primary DNs that do not have replication slots with the same name except the current CN.
• If an LSN-based logical replication slot remains on the current CN and a CSN-based logical replication slot with the same name remains on other nodes, deleting the replication slot on the current CN will delete only the local LSN-based logical replication slot. After the deletion is complete, perform the deletion operation again to delete the replication slots with the same name on other nodes.
• When the JSON format is used for decoding, the data column cannot contain special characters (such as the null character '\0'). Otherwise, the content in the decoding output column will be truncated.
• Decoding of DML operations on unlogged tables is not supported.
• Replication slots must be deleted before a database is deleted. If the database where logical replication slots reside is deleted, these replication slots will become unavailable.
• When a transaction generates a large number of sub-transactions that need to be flushed to disks, the number of opened file handles may exceed the upper limit. In this case, set GUC parameter max_files_per_process to a value greater than twice the upper limit of sub-transactions.
• Global secondary indexes are not supported, and decoding of DML statements for modifying distribution keys is not supported.
• The standby cluster of a DR cluster does not support logical decoding using SQL system functions or tools.
• The ledger database function is not supported. In the current version, if DML operations related to the ledger database function exist in the database where decoding is enabled, the decoding result contains hash columns. As a result, the replay fails.
• In scale-out scenarios, if logical replication slots are created in the cluster, the scale-out will fail. Therefore, you need to delete the existing logical replication slots before scale-out.

SQL Function Decoding Performance

If pg_logical_slot_peek_changes and pg_replication_slot_advance are used, the decoding performance is 30% to 50% lower than that when pg_logical_slot_get_changes is used.