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 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.