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

Logical Replication

max_changes_in_memory

Parameter description: Specifies the maximum number of DML statements cached in memory for a single transaction during logical decoding. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: none

Value range: 1 to 2147483647

Default value: 4096

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the value is too large, a large amount of dynamic memory is occupied when large transactions are decoded. If the value is too small, the decoding efficiency decreases.

max_cached_tuplebufs

Parameter description: Specifies the upper limit of the total tuple information cached in the memory during logical decoding. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: none

Value range: 1 to 2147483647

Default value: 8192

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value. You are advised to set this parameter to a value greater than or equal to twice of max_changes_in_memory.

Risks and impacts of improper settings: If the value is too large, a large amount of dynamic memory is occupied when large transactions are decoded. If the value is too small, the decoding efficiency decreases.

logical_decode_options_default

Parameter description: Specifies the global default parameters when a logical decoding task is started. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range: a string of key=value characters separated by commas (,), for example, "parallel-decode-num=4,parallel-queue-size=128,exclude-users=userA,skip-generated-columns=on". An empty string indicates that the default value hardcoded by the program is used.

Default value: ""

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Currently, the following logical decoding options are supported: parallel-decode-num, parallel-queue-size, max-txn-in-memory, max-reorderbuffer-in-memory, exclude-users, skip-generated-columns, decode-sequence, and data-limit. For details about the options, see "Logical Replication > Logical Decoding > Logical Decoding Options" in Feature Guide.

  • The SIGHUP parameter does not affect the started logical decoding process. The options specified by logical_decode_options_default are used as the default settings for subsequent logical decoding startup, and the settings specified in the startup command are used.

  • The exclude-users option specified by logical_decode_options_default is different from the logical decoding startup option exclude-users. You are not allowed to specify multiple blacklisted users.
  • decode-sequence configured in logical_decode_options_default can only be set to false.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

logical_sender_timeout

Parameter description: Specifies the maximum waiting time for the sender to wait for the receiver to receive logical logs. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: ms

Value range: 0 to 2147483647

Default value: 30000 (30s)

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_logicalrepl_xlog_prune

Parameter description: Specifies whether to enable the function of forcibly invalidating a logical replication slot. When enable_logicalrepl_xlog_prune is set to on, max_size_for_xlog_retention is greater than 0, and the number of retained log segments (16 MB for each log segment) exceeds the value of max_size_for_xlog_retention due to the current logical replication slot, or max_size_for_xlog_retention is less than 0 and the disk usage reaches (–max_size_for_xlog_retention)/100, the logical replication slot will be forcibly invalidated, restart_lsn is set to 7FFFFFFF/FFFFFFFF, and the replication slot does not block the recycling of Xlogs.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The function of forcibly invalidating a logical replication slot is enabled.
  • off: The function of forcibly invalidating a logical replication slot is disabled.

Default value: off

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Determine whether to enable this function based on service requirements.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_logical_replication_ddl

Parameter description: Specifies whether the logical decoding supports DDL statements, reverse parsing, and log generation. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

  • on: Logical replication supports DDL statements, reversely parses DDL execution results, and generates DDL WALs.
  • off: DDL statements are not supported, reverse parsing is not performed, and WALs are not generated.

Default value: on

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_logical_replication_dictionary

Parameter description: Specifies whether to enable the function of creating logical replication slots for multi-version dictionary tables. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

  • on: enabled.
  • off: disabled.

Default value: on

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value on. If you need to create a logical replication slot of the online catalog type, set this parameter to off.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

If this parameter is enabled, a logical replication slot can be created only after the system catalog of the dictionary type is baselined. Otherwise, the creation fails.

disable_logical_cache

Parameter description: Specifies whether to cache decoding metadata.

Parameter type: Boolean.

Unit: none

Value range:

  • on: The decoding metadata is not cached.
  • off: The decoding metadata is cached.

Default value: off

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: The default value is off. If you need to disable the cache of decoding metadata, set this parameter to on.

Risks and impacts of improper settings: If this parameter is set to on, meta information needs to be constructed for each decoding, affecting the decoding performance. You are advised not to set this parameter to on when no error is reported during metadata version chain construction.

max_keep_log_seg

Parameter description: Flow control parameter. In logical replication, physical logs are parsed and converted into logical logs locally on the DN. When the number of physical log files that are not parsed is greater than the value of this parameter, flow control is triggered. This parameter can be set at the PDB level.

Parameter type: integer

Unit: none

Value range: 0 to 2147483647. The value 0 indicates that the stream control function is disabled.

Default value: 0. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Determine whether to perform flow control based on service requirements.

Risks and impacts of improper settings: If the value is too large, flow control will not be triggered.

logical_replication_dictionary_retention_time

Parameter description: Specifies the retention period of data in the system catalogs related to GS_TXN_LSN_TIME and the data dictionary.

Parameter type: integer

Unit: day

Value range: 1 to 3650

Default value: 365

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Set this parameter based on service requirements.

Risks and impacts of improper settings: If the value is too small, the available interval for decoding the positioning point is too small. If the value is too large, the decoding performance is affected.

sqlapply_apply_writeset_maxsize

Parameter description: Specifies the number of historical row IDs stored in the global hash table in the row set conflict detection method.

Parameter type: integer

Unit: none

Value range: –1 to 2147483647

  • 0 indicates that row set conflict detection is disabled.
  • –1 indicates that the number is not limited.

Default value: 10000

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the value is too large, excessive system resources will be consumed.

sqlapply_preserve_commit_order

Parameter description: Specifies whether the logical standby node replays and commits transactions based on the transaction commit sequence of the remote primary node.

Parameter type: Boolean.

Unit: none

Value range:

  • on: Transactions on the remote primary node are committed in sequence.
  • off: Transactions on the remote primary node are not committed in sequence.

Default value: off

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

After this parameter is set, if the physical-to-logical conversion is complete, you need to restart the logical replay service on the logical standby node for the setting to take effect.

sqlapply_apply_worker_count

Parameter description: Specifies the number of logical replayer threads.

Parameter type: integer

Unit: none

Value range: 1 to 1024. If the memory size does not exceed 16 GB, the value of this parameter cannot exceed 8.

Default value: 4

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: For details about how to set sqlapply_apply_worker_count for different CPUs and memories, see Table 1.

Risks and impacts of improper settings: If the value is too large, too many system resources are consumed. If the value is too small, the replay efficiency decreases.

sqlapply_cache_memory_maxsize

Parameter description: Specifies the total memory that can be used by the logical replay cache.

Parameter type: integer

Unit: KB

Value range: –1 to 2147483647. –1 indicates that the memory usage is not limited.

Default value: 102400

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: For details about how to set sqlapply_cache_memory_maxsize for different CPUs and memories, see Table 1.

Risks and impacts of improper settings: If the value is too large, too many system resources are consumed. If the value is too small, the replay efficiency decreases.

Table 1 Parameter settings for different CPUs and memories

No.

Number of CPUs

Memory (GB)

sqlapply_apply_worker_count

sqlapply_cache_memory_maxsize (KB)

Remarks

1

4

-

4

102400

-

2

8

-

16

102400

-

3

8

64

64

1024000

-

4

16

128

64

2048000

-

5

32

256

128

4096000

-

6

64

512

192

5120000

-

7

96

768

256

10240000

Set the parameter to the recommended value for larger hardware specifications.

logical_replica_identity_force

Parameter description: Specifies the record level of old tuples for global logical replication.

Parameter type: enumerated type

Unit: none

Value range:

  • 'full': All columns are recorded as replica identifiers.
  • 'unique': The replica identifier is the primary key and unique key.
  • 'default': The replica identifier is selected by default (primary key or none).
  • 'nothing': There is no replica identifier for recording this relationship.
  • 'none': The global control parameter does not take effect.

Default value: 'none'

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

To reset logical_replica_identity_force that has been set to a non-default value to 'none', you need to restore the GUC parameter to the default value.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

sqlapply_logical_decode_options

Parameter description: Specifies whether to start a decoding task for logical replay. The options are parallel-decode-num, parallel-queue-size, max-txn-in-memory, max-reorderbuffer-in-memory, exclude-users, skip-generated-columns, enable-ddl-decoding, only-local, and decode-sequence.

Parameter type: string.

Unit: none

Value range:

  • parallel-decode-num

    Specifies the number of decoder threads for parallel decoding.

    Value range: an integer ranging from 1 to 20. The default value is 2.

  • parallel-queue-size

    Specifies the length of the queue for interaction between parallel logical decoding threads.

    Value range: an integer ranging from 2 to 1024. The value must be a power of 2. The default value is 128.

  • max-txn-in-memory

    Specifies the 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 the control is disabled.

  • max-reorderbuffer-in-memory

    Specifies the 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 the control is disabled.

  • exclude-users

    Specifies the name of a blacklisted user.

    Value range: names of blacklisted users. Multiple names are separated by commas (,).

  • skip-generated-columns

    Specifies whether to skip stored generated columns in the logical decoding result. This parameter is invalid for UPDATE and DELETE on old tuples, and the corresponding tuples always output the stored generated columns.

    Value range: Boolean. The default value is false.

    • false: The decoding result of stored generated columns is output.
    • true: The decoding result of stored generated columns is not output.

    Virtual generated columns are not controlled by this parameter. The DML decoding result does not output virtual generated columns.

  • enable-ddl-decoding

    Specifies whether to enable logical decoding for DDL statements.

    Value range: Boolean. The default value is true.
    • false: Logical decoding of DDL statements is disabled.
    • true: Logical decoding of DDL statements is enabled.
  • only-local

    Specifies whether to decode only local logs.

    Value range: Boolean. The default value is true.

    • false: Non-local logs and local logs are decoded.
    • true: Only local logs are decoded.
  • decode-sequence

    Specifies whether to output the decoding result of the change log of the sequence value, which is a logical decoding control parameter.

    Value range: Boolean. The default value is true.

    • false: The decoding result of the change log of the sequence value is not output.
    • true: The decoding result of the change log of the sequence value is output.

Default value: true for both enable-ddl-decoding and decode-sequence.

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. The SIGHUP parameter does not affect the logical decoding process that has been started.

Setting suggestion: Set this parameter based on service requirements.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

sql_apply_autorun

Parameter description: Specifies whether the logical backup automatically starts logical replay.

Parameter type: Boolean.

Unit: none

Value range:

  • on: enabled.
  • off: disabled.

Default value: off

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: The default value of this parameter is off. Set this parameter to on for the standby database during the rolling upgrade. Do not disable this parameter for the logical standby node during the rolling upgrade of the database.

Risks and impacts of improper settings: If this parameter is set to off for the standby database during the rolling upgrade, the logical replay stops and the rolling upgrade is abnormal.

sqlapply_guard_mode

Parameter description: Specifies the data protection mode of the logical standby node.

Parameter type: enumerated type

Unit: none

Value range:

  • guard_none: The logical standby node does not provide extra data protection.
  • guard_standby: Data objects synchronized from the primary and standby nodes cannot be modified on the logical standby node.
  • guard_all: The logical standby node allows only logical replay to modify data.

Default value: guard_all

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

logical_switch_time

Parameter description: Specifies the timeout interval for switching a physical standby database for DR to a logical replication database.

Parameter type: integer

Unit: minute

Value Range: 0 to 60. 0 indicates that the timeout interval is not set.

Default value: 10

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: If the RTO or RPO of the standby database and primary database is greater than 10 minutes, the physical-to-logical switchover may fail based on the default time. In this case, change the value of this parameter to the current DR RTO or RPO plus 10 minutes or more. You are advised to perform the physical-to-logical switchover during off-peak hours.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

sqlapply_dumptxn_when

Parameter description: Specifies the time when a transaction is dumped during logical replay.

Parameter type: enumerated type

Unit: none

Value range:

  • any: All transaction changes are dumped to a file for logical replay.
  • error: Transaction changes are dumped to a file when an error occurs during the execution of a logical replay transaction.
  • none: Transaction changes are not dumped to a file.

Default value: error

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

sqlapply_sharestorage_delaytime

Parameter description: Specifies the delay for logical replay to overwrite logs on a shared disk.

Parameter type: integer

Unit: second

Value range: –1 to 2147483647. The meanings of different values are as follows:

  • –1: The log overwrite point of the shared disk is always determined by logical replay. This ensures that unfinished logical replay logs can be obtained from the shared disk.
  • 0: The log overwrite point of the shared disk is determined by both physical replay and logical replay. The maximum log flush point in all standby databases for DR is used.
  • > 0: delay for logical replay to overwrite logs on a shared disk. When the specified delay is not exceeded, the execution logic is the same as that when the value is –1. When the specified delay is exceeded, the execution logic is the same as that when the value is 0.

Default value: –1

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

sqlapply_shared_storage_log_replay_type

Parameter description: Specifies the type of data logs to be replayed on the shared disk during logical replay.

Parameter type: enumerated type

Unit: none

Value range:

  • physic: The shared disk is used to replay physical logs.
  • logic: The shared disk is used to replay logical logs.
  • none: The shared disk is not used to replay logs.

Default value: physic

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.