Updated on 2025-05-29 GMT+08:00

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.

Logical logs are generated by transaction and can be output only after a transaction is committed. In addition, logical decoding is driven by users. Therefore, to prevent Xlogs from being recycled by the system at the beginning of a transaction or required transaction information from being recycled by VACUUM, a logical replication slot is added to GaussDB to block Xlog recycling.

A logical replication slot represents a stream of changes that can be re-executed in other databases in the order they were generated in the original database. Each logical replication slot is maintained by the person who obtains the corresponding logical logs. If the database where the logical replication slot in streaming decoding resides does not have services, the replication slot is updated based on the log location of other databases. The LSN-based logical replication slot in the active state may be updated based on the LSN of the current log when processing the active transaction snapshot log. The CSN-based logical replication slot in the active state may be updated based on the CSN of the current log when processing the virtual transaction log.

Prerequisites

  • Currently, logical logs are extracted from DNs. If logical replication is performed, SSL connections must be used. Therefore, ensure that the SSL-related GUC 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.
    • Each logical replication slot decodes modifications to only a single database. To decode multiple databases, you need to create multiple logical replication slots.
    • During multi-channel logical replication and synchronization, the source database needs to create an independent logical replication slot for each logical replication link.
    • A maximum of 20 decoding tasks can be executed concurrently for a single instance.
  • Only the initial user 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 Limitations.
  • Decoding of DML operations on data page replication is not supported.
  • 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 in a non-M-compatible database and floating-point data without scale in an M-compatible database, the decoding result precision parameter extra_float_digits is set to 3. For details about this parameter, see the description of the GUC parameter float_shortest_precision.
  • Decoding of DDL operations on the PUBLIC SCHEMA is not supported.
  • Logical decoding of DML and DDL 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, and BOOL/BOOLEAN.
    • Floating-point: FLOAT(M,D) and DOUBLE(M,D).
    • Fixed-point: DECIMAL(M,D) and NUMERIC(M,N).
    • BIT: BIT.
    • Binary string: CHAR(N), VARCHAR(N), BINARY, and VARBINARY.
    • Text: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.
    • Date: DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
    • LOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.
    • Data type attribute: UNSIGNED and ZEROFILL.
    • ENUM
    • SET
    • JSON
  • For M-compatible databases, the special restrictions on logical decoding are as follows:
    • Options such as [partition_options], ENGINE, ROW_FORMAT, algorithm_option, and lock_option in CREATE TABLE, ALTER TABLE, and DROP TABLE have no syntax effect. Such syntax is not decoded and output.
    • In the ALTER SCHEMA, CREATE SCHEMA, DROP SCHEMA, ALTER DATABASE, CREATE DATABASE, and DROP DATABASE syntaxes, ALTER DATABASE, CREATE DATABASE, and DROP DATABASE are decoded as ALTER SCHEMA, CREATE SCHEMA, and DROP SCHEMA because they are equivalent to schemas in M-compatible mode.
    • Different character setting syntaxes, such as CHARACTER SET, CHAR SET, and CHARSET, are decoded as CHARACTER SET.
    • For the ALTER TABLE tbl_name DROP {INDEX | KEY} index_name syntax, the logical decoding result is DROP INDEX. For the ALTER TABLE tbl_name DROP {primary key | {index | key} index_name} syntax for deleting a primary key, the logical decoding result is ALTER TABLE tbl_name DROP CONSTRAINT index_name.
    • For the ALTER TABLE tbl_name ADD INDEX syntax, the logical decoding result is CREATE INDEX.
    • During DML statement decoding, if the inserted data is of the time type (time, timestamp, or datetime) with precision, the maximum precision 6 is used for decoding.
    • For data types (BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB) that support binary input, if the data contains '\0', the data will be truncated regardless of output from serial decoding/function decoding or parallel decoding in the JSON/TEXT format. Only the output in BINARY format can be decoded in parallel (that is, the decoding task parameter decode-style is set to 'b').
  • REPLACE is a DML syntax and will be decoded as an actual data operation, such as INSERT or DELETE+INSERT (in case of a primary key or unique key conflict).
  • The COPY/LOAD syntax is a DML syntax and is decoded as an actual operation on data, for example, multiple INSERT operations.
  • The name of a logical replication slot must contain less than 64 characters. When you use SQL functions to create or use a replication slot, the replication slot name can contain only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). In addition, one period (.) or two periods (..) cannot be used as a replication slot name. When the JDBC API is called to create or use a replication slot, the replication slot name can contain only lowercase letters, digits, and underscores (_). (Uppercase letters are allowed, but they will be converted into lowercase letters. For example, if you enter the name as MSLOT, the actual input is mslot.)
  • After the database where a logical replication slot resides is deleted, the replication slot becomes unavailable and needs to be manually deleted; otherwise, WALs cannot be recycled.
  • 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 during logical decoding. 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.
  • If you need to delete a database during decoding on the standby node, ensure that the database of the corresponding logical replication slot is deleted by the primary node after data migration is complete. Currently, it is not necessary to delete the logical replication slot on a database before the database is deleted. As a result, the database may be deleted from the primary node before the decoding on the standby node is complete. In this case, when the standby node replays to the time when the database is deleted, the standby node exits decoding even if it has not completed all decoding tasks in the current database. If the standby node reconnects to the database, decoding on the standby node cannot be started because the database has been deleted. As a result, decoding tasks are damaged.
  • 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 or other errors may occur.
  • Replication slots can only be created or deleted on the primary node. 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.
  • When a replication slot on the primary node is deleted, the replication slot on the standby node may not be deleted in time due to Xlog replay delay. If such replication slot is used for decoding on the standby node, a decoding error will be reported when the replication slot is deleted during replay. Before decoding on the standby node, check whether the value of restart_lsn of the replication slot on the standby node is the same as that on the primary node. If they are different, the replication slot is a residual replication slot.
  • 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.
  • Interval partitioned tables support DML replication. If automatic partitioning is triggered during DML replication of interval partitioned tables and the number of autonomous transaction sessions exceeds the value of max_concurrent_autonomous_transactions, DML statements replicated after automatic partitioning cannot be decoded.
  • Decoding of DML operations on global temporary tables is not supported.
  • Decoding of DML operations on local temporary tables is not supported.
  • 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 > A > 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 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, and 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.
  • During backup and restoration, all logical replication slots will be cleared after the instance is restored. If required, rebuild the slots.
  • 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.
  • Information constraint options (such as not enforced) in PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints are not supported. Information constraints in DDL statements are not decoded, while other parts of the DDL statements are decoded as usual. For example, the "CREATE TABLE test(a int primary key not enforced);" statement is decoded as "CREATE TABLE test(a int);".
  • In upgrade scenarios, for instances upgraded from an earlier version, the default value of the GUC parameter enable_logical_replication_dictionary is off, that is, the created logical replication slot is of the online catalog type. To create a replication slot of the data dictionary type, you must set the GUC parameter enable_logical_replication_dictionary to on and execute the baselined system function as an administrator. Then, you can create a logical replication slot.
  • Only WALs with wal_level set to logical can be decoded. For non-logical logs, serial decoding (including function decoding) does not output the corresponding value and type, and parallel decoding does not output the logical logs.
  • The logical decoding feature supports the following types of DML operations in Xlogs:
    • Astore: INSERT, DELETE, UPDATE, and MULTI-INSERT.
    • Ustore: INSERT, DELETE, UPDATE, and MULTI-INSERT.
  • Compared with the scenario where wal_level is set to hot_standby, the Xlog data volume increases when wal_level is set to logical. For example, in the TPC-C scenario, the Astore Xlog bloat rate is about 11%, and the Ustore Xlog bloat rate is about 110%.
  • In M-compatible mode, do not perform logical replication between instances with different lower_case_table_names parameter settings. Otherwise, data may be lost.
  • When decoding is performed in a case-insensitive database, the table name or username in the decoding option whitelist (white-table-list) or blacklist (exclude-users) must be lowercase regardless of whether the table name or username is created in uppercase or lowercase.
  • In an M-compatible database, when data of the binary or string data type contains zero characters for logical replication, data truncation may occur if the decode_style option is not 'b' during streaming parallel decoding. Logical decoding of zero characters is the same as that in the source version during the upgrade observation period.
  • When the gs_logical_decode_start_observe function is used for monitoring, if the replication slot is not in the active state, the error message "invalid slot name" is displayed.
  • Function decoding does not support the '\0' character.
  • The minimum node specifications supported by logical decoding are 8 vCPUs and 64 GB. If the node specifications are lower than the minimum specifications, logical decoding is not recommended.
  • The logical replication slot protects the system catalog records of the version corresponding to the Xlog that is not parsed from being cleared too early. If a logical replication slot is inactive or the client consumes data slowly, services may be affected as follows:
    1. If there are a large number of DDL service statements, there will be too many versions of system catalogs, affecting the SQL command execution efficiency.
    2. Due to the limitations of GaussDB data page management, two records whose XID difference exceeds 2^32 cannot be stored on the same page. If there are too many versions of system catalogs, service operations such as DDL and DML may fail to be executed, reporting error code GAUSS-21297.
    Solution:
    1. Clear the logical replication slots that are no longer used in a timely manner.
    2. If the logical replication slot is updated too slowly, rectify the fault by referring to "Common Fault Demarcation and Troubleshooting > Faults Related to Logical Decoding > Logical Decoding > Abnormal Logical Decoding Replication Slot" and "Common Fault Demarcation and Troubleshooting > Common Logical Decoding Faults > Logical Decoding > Slow Logical Decoding" in Troubleshooting.
    3. If the problem in 2 persists, delete the replication slot and re-create the logical decoding task.

SQL Function Decoding Performance

  1. In the Benchmarksql-5.0 with 100 warehouses, when pg_logical_slot_get_changes is used:
    • If 4,000 lines of data (about 5 MB to 10 MB of logs) are decoded at a time, the decoding performance ranges from 0.3 MB/s to 0.5 MB/s.
    • If 32,000 lines of data (about 40 MB to 80 MB of logs) are decoded at a time, the decoding performance ranges from 3 MB/s to 5 MB/s.
    • If 256,000 lines of data (about 320 MB to 640 MB of logs) are decoded at a time, the decoding performance ranges from 3 MB/s to 5 MB/s.
    • If the amount of data to be decoded at a time still increases, the decoding performance is not significantly improved.

    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.

  2. In the Benchmarksql-5.0 with 100 warehouses, when pg_logical_get_area_changes is used:
    • If 4,000 lines of data (about 5 MB to 10 MB of logs) are decoded at a time, the decoding performance ranges from 0.3 MB/s to 0.5 MB/s.
    • If 32,000 lines of data (about 40 MB to 80 MB of logs) are decoded at a time, the decoding performance ranges from 3 MB/s to 5 MB/s.
    • If 256,000 lines of data (about 320 MB to 640 MB of logs) are decoded at a time, the decoding performance ranges from 3 MB/s to 5 MB/s.
    • If the amount of data to be decoded at a time still increases, the decoding performance is not significantly improved.
  3. In the Benchmarksql-5.0 with 100 warehouses, when pg_logical_slot_get_binary_changes is used:
    • If 4,000 lines of data (about 5 MB to 10 MB of logs) are decoded at a time, the decoding performance ranges from 0.3 MB/s to 0.5 MB/s.
    • If 32,000 lines of data (about 40 MB to 80 MB of logs) are decoded at a time, the decoding performance ranges from 2 MB/s to 3 MB/s.
    • If 256,000 lines of data (about 320 MB to 640 MB of logs) are decoded at a time, the decoding performance ranges from 2 MB/s to 3 MB/s.
    • If the amount of data to be decoded at a time still increases, the decoding performance is not significantly improved.

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

Streaming Decoding Performance

The decoding performance (Xlog consumption) is greater than or equal to 100 MB/s in the following standard parallel decoding scenario: 16-core CPU, 128 GB memory, network bandwidth > 200 Mbit/s, 10 to 100 columns in a table, 0.1 KB to 1 KB data in a single row, INSERT as main DML operations, less than 4096 statements in a single transaction (that is, no flush to disk is required), parallel-decode-num set to 8, decoding format as 't', and batch sending function enabled. To ensure that the decoding performance meets the requirements and to minimize the impact on services, you are advised to set up only one parallel decoding connection on a standby node to ensure that the CPU, memory, and bandwidth resources are sufficient.