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 parameter 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 export 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: 64
checkpoint_timeout
Parameter description: Specifies the maximum time between automatic WAL checkpoints.
This parameter 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 second.
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 completion target of each checkpoint, as a fraction of total time between checkpoints.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: a floating point number ranging from 0.0 to 1.0
Default value: 0.5
NOTE: 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 parameter 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 second. 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 parameter 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 second.
Default value: 1min
enable_incremental_checkpoint
Parameter description: Specifies whether to enable incremental checkpointing.
This parameter 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 the doublewrite buffer. When the incremental checkpointing is enabled, the doublewrite buffer instead of full_page_writes is used to prevent partial page writes.
This parameter 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 checkpointing is enabled.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 1 to 3600. The unit is second.
Default value: 1min
enable_xlog_prune
Parameter description: Specifies whether the primary server reclaims logs when the size of an .xlog file exceeds the value of max_size_for_xlog_prune when any standby server is disconnected.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
Default value: on
max_size_for_xlog_prune
Parameter description: This parameter is valid only when enable_xlog_prune is enabled. If a standby node is disconnected and the size of an .xlog file is greater than the threshold, the .xlog file is reclaimed.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer from 0 to 576460752303423487. The unit is MB.
Default value: 100000. Unit: MB