WAL & Archiving
wal_level
Enum type. WAL (Write-Ahead Log) level. Available values: minimal, replica, logical. Default value replica. This parameter can only be set at instance startup.
wal_level = replica # minimal, replica, or logical (change requires restart)
fsync
Boolean type. Whether to attempt to ensure data has been written to disk. Disabling this parameter may result in unrecoverable data corruption. Default value on.
fsync = on # flush data to disk for crash safety (turning this off can cause unrecoverable data corruption)
synchronous_commit
Enum type. Transaction synchronous commit level. Available values: off, local, remote_write, remote_apply, on. Default value on.
synchronous_commit = on # synchronization level;
# off, local, remote_write, remote_apply, or on
full_page_writes
Boolean type. Whether to write full pages to the WAL log. Default value on.
full_page_writes = on # recover from partial page writes
wal_log_hints
Boolean type. When set to on, the database writes the full contents of each disk page to the WAL during the first page modification after a checkpoint. Default value off. It is recommended to enable this. This parameter can only be set at instance startup.
wal_log_hints = on # (change requires restart)
wal_compression
Enum type. Whether to compress full-page writes in the WAL log. Available values: off, pglz, lz4, zstd, on. Default value off.
wal_compression = off # enables compression of full-page writes; off, pglz, lz4, zstd, or on
wal_recycle
Boolean type. Whether to recycle WAL log files. Default value on.
wal_recycle = on # recycle WAL files
wal_buffers
Numeric type (memory). WAL buffer size. Default value -1, which is approximately 3% x shared_buffers. It is recommended to set this to 16MB. This parameter can only be set at instance startup.
wal_buffers = 16MB # (change requires restart)
min_wal_size
Numeric type (disk space). Lower limit for WAL log space consumption. As long as WAL disk usage remains below this setting, old WAL files are always recycled at checkpoints. Default value 80MB. It is recommended to increase this based on your workload.
min_wal_size = 8GB
max_wal_size
Numeric type (disk space). Maximum size that WAL can grow to between automatic WAL checkpoints. This is a soft limit; under special circumstances, the WAL size may exceed it. Default value 1GB. It is recommended to increase this based on your workload.
max_wal_size = 2GB
checkpoint_completion_target
Numeric type. Sets the target time for checkpoint completion, representing the percentage of the checkpoint interval within which writing should be completed. Default value 0.9.
checkpoint_completion_target = 0.9
Archive Configuration
Archiving can be omitted during the testing phase, but is recommended for production environments.
archive_mode
Enum type. Whether to enable log archiving. Default value off. This parameter can only be set at instance startup.
archive_mode = on # (change requires restart)
archive_command
String type. Command used for archiving. %p is the path of the WAL file, and %f is the filename of the WAL file.
archive_command = 'test ! -f /data/halo/archivedir/%f && cp %p /data/halo/archivedir/%f'
# placeholders: %p = path of file to archive
# %f = file name only
restore_command
String type. Restore command used to copy WAL files from the archive directory when database recovery is needed.
restore_command = '' # command to use to restore an archived WAL file
# placeholders: %p = path of file to restore
# %f = file name only
Archive Configuration Steps:
- Create the archive directory:
mkdir -p /data/halo/archivedir
chown -R halo:halo /data/halo/archivedir
- Modify the database configuration file:
vi $PGDATA/postgresql.conf
Add the following:
archive_mode = on
archive_command = 'test ! -f /data/halo/archivedir/%f && cp %p /data/halo/archivedir/%f'
restore_command = 'cp /data/halo/archivedir/%f %p'