Standby Node

hot_standby

Parameter description: Specifies whether the standby node is allowed to accept connections and queries after it is restored to the minrecovery point.

Parameter type: Boolean.

Unit: none

Value range:

• on: The standby node is allowed to accept connections and queries after it is restored to the minrecovery point.
• off: The standby node is not allowed to accept connections and queries after it is restored to the minrecovery point.

Default value: on

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

Setting suggestion: After the read function of the standby node is enabled, certain resources, including the CPU, memory, disk space, and I/O usage, are consumed even if the standby node does not respond to services. If there is no read service on the standby node, you are advised to disable this parameter.

• If this parameter is set to on, wal_level must be set to hot_standby or higher. Otherwise, the database startup fails.
• In a distributed system, hot_standby cannot be set to off, because this setting can affect other features of the HA system.
• If the hot_standby parameter was disabled and the wal_level parameter was set to a value smaller than that of hot_standby, perform the following operations to ensure that the logs to be replayed on the standby node can be queried on the standby node before enabling the hot_standby parameter again:
  1. Change the value of wal_level of the primary and standby nodes to the value of hot_standby or a higher value, and restart the instances for the change to take effect.
  2. Perform the checkpoint operation on the primary node and query the pg_stat_get_wal_senders() function to ensure that the value of receiver_replay_location of each standby node is the same as that of sender_flush_location of the primary node. Ensure that the value adjustment of wal_level is synchronized to the standby nodes and takes effect, and the standby nodes do not need to replay low-level logs.
  3. Set the hot_standby parameter of the primary and standby nodes to on, and restart the instances for the setting to take effect.
• If the read on standby function is enabled, conflicts between replay and query on the standby node may cause the query to cancel. The error information is as follows:
  • ERROR: canceling statement due to conflict with recovery
  • ERROR: terminating connection due to conflict with recovery
• During the standby node read in serial and parallel replay modes, when the primary node rebuilds indexes online, an error may be reported when the standby node is read. Try again later. Error information:
  • could not open relation with OID xxx during recovery delete object, please try again later
  • Catalog is missing xxx attribute(s) for relid xxx
  • cache lookup failed for index xxx, refilenode:xxx, name:"xxx"
  • could not find pg_class entry for xxx
  • could not open block during recovery delete object, please try again later
• During the standby node read in the case of ultimate RTO, the VM with small memory configuration may report an error indicating insufficient memory. You can enable the GUC parameter exrto_standby_read_opt (enabled by default) to effectively reduce the memory and I/O overhead.
• The standby node does not support autonomous transactions.
• Use sysbench to test the read performance of the standby node in typical scenarios: The primary node executes 100 concurrent update services. The primary and standby nodes both execute 200 concurrent read services at the same time. When the I/O and CPU are not limited, the read performance of the standby node in serial replay mode is not lower than 80% of that of the primary node, and the read performance of the standby node in ultimate RTO deteriorates by no more than 10% compared with that of the standby node in serial replay mode.

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

max_standby_archive_delay

Parameter description: Specifies the wait period before queries on the standby node are canceled. A query conflict occurs when you use the standby node to read data in hot standby mode. In the current version, the setting does not take effect and is controlled by the max_standby_streaming_delay parameter.

Parameter type: integer.

Unit: millisecond

Value range: –1 to 2147483647. –1 indicates that the standby node waits until the conflicting queries are complete.

Default value: 3000 (that is, 3s)

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.

max_standby_streaming_delay

Parameter description: Specifies the wait period before queries on the standby node are canceled when the queries conflict with WAL data receiving through streaming replication in hot standby mode. If this parameter is set to a large value or the service load is heavy, an error may be reported, indicating that queries conflict with conflict.

Parameter type: integer.

Unit: millisecond

Value range: –1 to 2147483647

–1 indicates that the standby node waits until the conflicting queries are complete. In the scenario where serial or parallel replay is enabled, if the system detects that the query thread and replayer thread are in the deadlock state, the system still cancels the query to prevent replay blocking.

Default value: 3000 (that is, 3s)

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. The default value is recommended. A larger value indicates a higher query service priority on the standby node, but affects replay on the standby node. A smaller value indicates that long queries are more likely to be canceled, but the replay priority is higher.

Risks and impacts of improper settings: If the value is too large and long queries conflict with replay, replay may be blocked for a long time, affecting the RTO and RPO of the standby node.

wal_receiver_status_interval

Parameter description: Specifies the maximum interval for notifying the primary node of the WAL receiver status.

Parameter type: integer.

Unit: second

Value range: 0 to 2147483

Default value: 5

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 this parameter is set to 0, the standby node does not send information, such as the log receiving location, to the primary node. As a result, the transaction commit on the primary node may be blocked, and the switchover may fail. In normal service scenarios, you are advised not to set this parameter to 0.

hot_standby_feedback

Parameter description: Specifies that the minimum transaction ID of a query performed on the standby node is sent to the primary node to avoid query conflicts.

Parameter type: Boolean.

Unit: none

Value range:

• on: The ID of the oldest transaction active on standby nodes will be sent to the primary node.
• off: The ID of the oldest transaction active on standby nodes will not be sent to the primary node.

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: If this parameter is set to on, the deletion of source version data on the primary node is restricted by the transactions that are being read by the standby node. That is, the primary node can delete only the changes whose transaction IDs are smaller than those reported by the standby node. Therefore, the performance of the primary node will be affected when this parameter is enabled. If replay conflicts with query on the standby node and a query error is reported, you are advised to increase the value of max_standby_streaming_delay.

wal_receiver_timeout

Parameter description: Specifies the maximum wait period for a standby node to receive data from the primary node.

Parameter type: integer.

Unit: millisecond

Value range: 0 to 2147483647

Default value: 6000 (that is, 6s)

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 small, the walreceiver thread is frequently disconnected when the network fluctuates.

wal_receiver_connect_timeout

Parameter description: Specifies the timeout interval for a standby node to connect to the primary node.

Parameter type: integer.

Unit: second

Value range: 0 to 2147483

Default value: 2

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.

wal_receiver_connect_retries

Parameter description: Specifies the maximum attempts that a standby node connects to the primary node.

Parameter type: integer.

Unit: none

Value range: 1 to 2147483647

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.

wal_receiver_buffer_size

Parameter description: Specifies the memory buffer size for the standby nodes to store the received Xlog files.

Parameter type: integer.

Unit: KB

Value range: 4096 to 1047552

Default value: 65536 (that is, 64 MB)

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

Setting suggestion: Retain the default value. Do not set this parameter to an excessively large or small value. The following condition must be met: data_replicate_buffer_size + segment_buffers + shared_buffers + wal_buffers + temp_buffers + maintenance_work_mem + work_mem + query_mem + wal_receiver_buffer_size (standby node) < max_process_memory < Memory size of the physical machine.

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

primary_slotname

Parameter description: Specifies the slot name of the primary node corresponding to a standby node. This parameter is used for verifying the primary-standby relationship.

Parameter type: string.

Unit: none

Value range: a string

Default value: ""

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 this parameter is incorrectly set, the standby node may fail to connect to the primary node, and the cluster status may be abnormal.

max_standby_base_page_size

Parameter description: Specifies the maximum storage space of base page files on the standby node after the ultimate RTO function is enabled.

Parameter type: integer.

Unit: KB

Value range: 1048576 to 562949953421311

Default value: 268435456‬ (that is, 256 GB)

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

Setting suggestion: Set this parameter according to the actual size of the physical disk.

Risks and impacts of improper settings: If the value is too small, the probability of read errors on the standby node increases.

max_standby_lsn_info_size

Parameter description: Specifies the maximum size of LSN info files on the standby node after the ultimate RTO function is enabled.

Parameter type: integer.

Unit: KB

Value range: 1048576 to 562949953421311

Default value: 268435456 (that is, 256 GB)

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

Setting suggestion: Set this parameter according to the actual size of the physical disk.

Risks and impacts of improper settings: If the value is too small, the probability of read errors on the standby node increases.

max_keep_csn_info_size

Parameter description: Specifies the maximum size of CSN info linked lists allowed by each DN on the standby node after the ultimate RTO function is enabled.

Parameter type: integer.

Unit: KB

Value range: 16384 to 131072

Default value: 16384 (that is, 16 MB)

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.

base_page_saved_interval

Parameter description: Specifies the interval for generating base pages on the standby node after the ultimate RTO function is enabled. For the same page, a base page is generated each n (specified by this parameter) times.

Parameter type: integer.

Unit: none

Value range: 5 to 2000

Default value: 400

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If the interval is too long, a large number of Xlogs need to be replayed each time the standby node reads data, and the performance deteriorates. If the interval is too short, more disk space is occupied and the forcible reclamation point is reached more quickly.

standby_force_recycle_ratio

Parameter description: Specifies the percentage of files read by the standby node to trigger forcible recycling after the ultimate RTO function is enabled. When the total size of base page files exceeds the value of max_standby_base_page_size x standby_force_recycle_ratio or the total size of LSN info files exceeds the value of max_standby_lsn_info_size x standby_force_recycle_ratio, forcible recycling is triggered and some queries are canceled. When the value of standby_force_recycle_ratio is 0, forcible recycling is not started, and the setting of max_standby_base_page_size and max_standby_lsn_info_size does not take effect.

Parameter type: floating point

Unit: none

Value range: 0.0 to 1.0

Default value: 0.8

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.

standby_recycle_interval

Parameter description: Specifies the interval for the standby node to recycle read files after the ultimate RTO function is enabled. The thread for recycling read resources on the standby node attempts to clear read files on the standby node at the interval specified by this parameter.

Parameter type: integer.

Unit: second

Value range: 0 to 86400

Default value: 10

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 interval is too long, historical pages cannot be reclaimed in a timely manner, which may cause excessive disk usage.

standby_max_query_time

Parameter description: Specifies the maximum query time supported on the standby node after the ultimate RTO function is enabled. If the query time exceeds the value of this parameter, the query will be canceled.

The time when the query is canceled is affected by the interval parameter standby_recycle_interval of the recycling thread and the time when the snapshot is obtained. Therefore, the actual execution time of the query on the standby node must be greater than the value of this parameter.

Parameter type: integer.

Unit: second

Value range: 0 to 86400

Default value: 600

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

Setting suggestion: Set the value based on service requirements.

Risks and impacts of improper settings: If the value is too large, the disk space usage will be too high.

exrto_standby_read_opt

Parameter description: Specifies whether to support read optimization of the standby node with ultimate RTO. This parameter is not synchronized between the primary and standby nodes.

Parameter type: Boolean.

Unit: none

Value range:

• on: The optimization is enabled.
• off: The optimization is disabled.

Default value: on

Setting method: This is a POSTMASTER 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.

walrcv_writer_crc_check_level

Parameter description: Specifies whether to enable Xlog verification on the standby node in the primary/standby clusters in the streaming DR scenario. By default, the parameter is enabled only in the standby cluster.

Parameter type: integer.

Unit: none

Default value: 1

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

Setting suggestion: This parameter is automatically set in the DR establishment process.

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

standby_read_use_az_info

Parameter description: Specifies whether the GTM-Lite standby node uses AZ information to read data. The standby node in the same AZ as the CN is preferentially selected. This parameter is disabled by default.

Parameter type: Boolean.

Unit: none

Value range:

• on: enabled.
• off: disabled.

Default value: off

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

Setting suggestion: Set this parameter based on service requirements. After this function is enabled, all available standby DNs need to be traversed before being selected, which may cause query performance deterioration.

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

standby_read_use_load_balance

Parameter description: Specifies whether the GTM-lite standby node uses the load balancing function to randomly select DNs that meet the consistency point (and AZ information). This function is disabled by default.

Parameter type: Boolean.

Unit: none

Value range:

• on: enabled.
• off: disabled.

Default value: off

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

Setting suggestion: Set this parameter based on service requirements. After this function is enabled, all available standby DNs need to be traversed before being selected, which may cause query performance deterioration.

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

enable_standby_bufferpool

Parameter description: Specifies whether to enable the function of reading data from an independent buffer on the standby node with ultimate RTO.

Parameter type: Boolean.

Unit: none

Value range:

• on: enabled.
• off: disabled.

Default value: on

Setting method: This is a POSTMASTER 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.

When this function is enabled, it takes effect only when enable_incremental_checkpoint is enabled and the difference between the value of standby_shared_buffers_fraction and that of standby_bufferpool_scale is greater than or equal to 0.1.

standby_bufferpool_scale

Parameter description: Specifies the percentage of the shared_buffers memory buffer used by the independent buffer after the function of independent buffer for read on the standby node with ultimate RTO is enabled.

Parameter type: floating point.

Unit: none

Value range: 0.1 to 0.9

Default value: 0.5

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

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If this parameter is set to an improper value, page refreshing will be accelerated.