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 DNs. If logical replication is performed, SSL connections must be used. Therefore, ensure that the ssl parameter on the corresponding DN 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 plus 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.
• 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 supports DDL constraints. For details, see Specification Restrictions.
• 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.
• The size of a single tuple cannot exceed 1 GB, and decoded data 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.
• Decoding of DML operations on an M-compatible database is supported. The supported data types are as follows:
  • Integer: tinyint(m), smallint(m), mediumint(m), bigint(m), int(m)/integer, bool, and boolean.
  • Floating point: float(m,d) and double(m,d).
  • Fixed point: decimal(m,d) and numeric(m,n)
  • BIT: BIT.
  • Character string: char(n), varchar(n), binary, and varbinary.
  • Text: tinytext, text, mediumtext, and longtext.
  • Date: data, time, timestamp, and year.
  • LOB: tinyblob, blob, mediumblob, and longblob.
  • Data type attributes: unsigned (partially supported) and zerofil (not supported).
  • The following types are not supported: enum and set.
• 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.
• After the database where a logical replication slot resides is deleted, the replication slot becomes unavailable and needs to be manually deleted.
• 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.
• The same replication slot for decoding cannot be used between the primary node and standby node or between different standby nodes at the same time. Otherwise, data inconsistency occurs.
• Replication slots can only be created or deleted on hosts. 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.
• After the database is restarted due to a fault or the logical replication thread 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.
• 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.
• 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.
• 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 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.
• Only LSN-based logical replication slots can be created by connecting to DNs using protocols.
• 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.
• 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 max_files_per_process to a value greater than twice the upper limit of sub-transactions.
• 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.
• DDL statements cannot be decoded for an M-compatible database. (When the logical decoding function is used for the M-compatible database, the enable-ddl-decoding option must be disabled.)

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.