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.

vim /var/lib/pgsql/9.6/data/postgresql.conf

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:

vim /var/lib/pgsql/9.6/data/postgresql.conf

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:

vim /var/lib/pgsql/9.6/data/postgresql.conf

max_wal_senders = 1

Backup

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

Archiving

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.

vim /var/lib/pgsql/9.6/data/postgresql.conf

archive_mode = on archive_command = ‘test ! -f /mnt/nasdb1/pgdata/WAL_Archive/%f && cp %p /mnt/WAL’s/%f’

Author: Kaan Kahraman