PostgreSQL Write-Ahead Logging (WAL) Mechanism
Write-Ahead Logging (WAL) is the standard method used to ensure data integrity.
According to WAL;
“Before writing changes in data files where tables and directories exist, we must first save changelogs in permanent storage.”
If we follow this procedure, we don’t need to discard the data pages on the disk in each process because we can recover the database by logging in the event of a crash.
By default, it comes as wal_level = minimal, and logging is not done to a point where it causes PITR (Point In Time Recovery) operation.
wal_level = archive
Unlike other settings, this change requires a restart.
systemctl restart postgresql-9.6
For performance issues, the max_wal_size and min_wal_size settings can be changed. After running for a while, these settings can be optimized based on the logs.
Recommendations from PostgreSQL website
* If you know your database write traffic is “bursty” with long periods of inactivity followed by furious writes, increase min_wal_size.
* If you do bulk loads larger than 1GB, increase max_wal_size to the size of a bulk load.
* If you write more than 1GB of data every 10 minutes, increase max_wal_size.
* If you get “Checkpoints occurring too frequently” error messages, try increasing both parameters incrementally.
You can make the below change to increase both values to 3 times the default:
max_wal_size = 3GB
min_wal_size = 240MB
By default, old WALs are not retained. If you want to back up, you can change the setting as follows:
wal_keep_segments = 10
Let’s change max_wal_senders to 1 in postgresql.conf:
max_wal_senders = 1
$ sudo -u postgres pg_basebackup — xlog — format=plain -D /var/lib/pgsql/9.6/backups/`date +%Y%m%d`
By putting this process in a cron job, you can have it backed up at specific intervals.
To return to the old backups, we must obtain base backups as well as the old WALs. If this is desired, we should specify archive_mode = on and archive_command while archiving.
When necessary, we can create backups based on the date by playing with WAL logs in pg_xlog.
archive_mode = on
archive_command = ‘test ! -f /mnt/nasdb1/pgdata/WAL_Archive/%f && cp %p /mnt/WAL’s/%f’
Published on: May 14, 2019