Updated on 2024-06-03 GMT+08:00

Checkpoints

checkpoint_segments

Parameter description: Specifies the minimum number of WAL segment files in the period specified by checkpoint_timeout. The size of each log file is 16 MB.

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

Value range: an integer. The minimum value is 1.

Increasing the value of this parameter speeds up the import of a large amount of data. Set this parameter based on checkpoint_timeout and shared_buffers. This parameter affects the number of WAL segment files that can be reused. Generally, the maximum number of reused files in the pg_xlog folder is twice the number of checkpoint_segments. The reused files are not deleted and are renamed to the WAL segment files which will be later used.

Default value: 1024

checkpoint_timeout

Parameter description: Specifies the maximum time between automatic WAL checkpoints.

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

Value range: an integer ranging from 30 to 3600. The unit is s.

If the value of checkpoint_segments is increased, you need to increase the value of this parameter. The increase of these two parameters further requires the increase of shared_buffers. Consider all these parameters during setting.

Default value: 15min

checkpoint_completion_target

Parameter description: Specifies the target of checkpoint completion.

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

Value range: a double-precision floating-point number ranging from 0.0 to 1.0

Default value: 0.5

The default value 0.5 indicates that each checkpoint should be complete within 50% of the interval between checkpoints.

checkpoint_warning

Parameter description: Specifies a time in seconds. If the checkpoint interval is close to this time due to filling of checkpoint segment files, a message is sent to the server log to suggest an increase in the checkpoint_segments value.

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

Value range: an integer ranging from 0 to INT_MAX. The unit is s. 0 indicates that the warning is disabled.

Default value: 5min

Recommended value: 5min

checkpoint_wait_timeout

Parameter description: Sets the longest time that the checkpoint waits for the checkpointer thread to start.

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

Value range: an integer ranging from 2 to 3600. The unit is s.

Default value: 1min

enable_incremental_checkpoint

Parameter description: Specifies whether to enable incremental checkpoint.

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

Value range: Boolean

Default value: on

enable_double_write

Parameter description: Specifies whether to enable double writing. When the incremental checkpoint function is enabled and enable_double_write is enabled, the enable_double_write dual-write feature is used for protection and full_page_writes is not used to prevent half-page write.

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

Value range: Boolean

Default value: on

incremental_checkpoint_timeout

Parameter description: Specifies the maximum interval between automatic WAL checkpoints when the incremental checkpoint is enabled.

Parameter type: integer

Unit: second

Value range: 1 to 3600

Default value: 60

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

enable_xlog_prune

Parameter description: Specifies whether the primary node recycles logs if the size of Xlogs exceeds the value of max_size_for_xlog_prune when any standby node is disconnected.

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

Value range: Boolean

  • If this parameter is set to on, the primary node recycles logs when any standby node is disconnected.
  • If this parameter is set to off, the primary node does not recycle logs when any standby node is disconnected.

Default value: on

max_size_for_xlog_prune

Parameter description: Specifies the maximum number of Xlogs retained on the primary node when the standby node is faulty. This parameter takes effect when enable_xlog_prune is enabled. The mechanism is as follows:
  1. If all standby nodes specified by the replconninfo series GUC parameters are connected to the primary node, this parameter does not take effect.
  2. This parameter takes effect when one or more standby nodes specified by the replconninfo series GUC parameters are disconnected to the primary node. If the number of Xlogs on the host is greater than the value of this parameter, the Xlogs will be forcibly recycled. Exception: In synchronous commit mode (that is, the synchronous_commit parameter is not set to local or off), if there are standby nodes connecting to the primary node, the primary node retains the logs after the minimum log receiving location in the majority of standby nodes. In this case, the number of retained logs may be greater than the value of max_size_for_xlog_prune.
  3. If a standby node is being built, this parameter does not take effect. All logs on the primary node are retained to prevent failures caused by log recycling during the build operation.

Parameter type: integer

Unit: KB

Value range: 0 to 2147483647

Default value: 256GB

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. If the value of the parameter does not contain a unit, the default unit is KB. The unit can also be MB or GB.

Setting suggestion: If the disk space is small, you are advised to set this parameter to a small value. The maximum value is 256GB.