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 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 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 double-precision 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 s.
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 double writing. When incremental checkpointing and double writing are both enabled, double writing is used for protection, and full_page_writes is no longer used to prevent half-page writing problems.
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 s.
Default value: 1min
enable_xlog_prune
Parameter description: Specifies whether the primary server recycles logs when any standby server is disconnected.
This parameter 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 server reclaim logs when any standby server is disconnected.
- If this parameter is set to off, the primary server does not reclaims logs when any standby server is disconnected.
Default value: on
max_redo_log_size
Parameter description: Specifies the size of logs before the recovery point and the current log location. You are advised not to set this parameter to a large value if the RTO is concerned.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 163840 to 2147483647. The unit is KB.
Default value: 1 GB
max_size_for_xlog_prune
Parameter description: Takes effect when enable_xlog_prune and synchronous_commit is enabled. If the standby server is disconnected and the size of Xlogs is greater than the threshold, the logs are recycled.When all standby machines are disconnected and there is no logical replication slot, logs are not collected.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 0 to 2147483647. The unit is KB.
Default value: 2147483647. The unit is KB.