Part 16 – PostgreSQL : What is write ahead logging ( WAL ).
WHAT IS A WAL
WAL is short for Write Ahead Log.
Transaction performed on the database is first written out as a WAL file.T
Then applied to the actual on-disk table data files.
WAL files are strictly sequential
“replay log” of changes.
So are they actual files/location?
Located under data_directory as pg_wal
/var/lib/pgsql/12/data/pg_wal
How to control WAL?
wal_keep_segments (Specifies the minimum number of past log file segments kept in the pg_xlog directory)
max_wal_size (Maximum size to let the WAL grow as soft limit )
ARCHIVING WALS
copying out generated WAL files is called archiving.
THE ARCHIVE COMMAND
The script is set using the archive_command configuration setting
# Copy the file to a safe location (like a mounted NFS volume)
archive_command = ‘cp %p /mnt/nfs/%f’
# Not overwriting files is a good practice
archive_command = ‘test ! -f /mnt/nfs/%f && cp %p /mnt/nfs/%f’
# Copy to S3 bucket
archive_command = ‘s3cmd put %p s3://BUCKET/path/%f’
# Copy to Google Cloud bucket
archive_command = ‘gsutil cp %p gs://BUCKET/path/%f’
# An external script
archive_command = ‘/opt/scripts/archive_wal %p’
THE WAL LEVEL
minimal WAL level.
information needed to recover from a crash or immediate shutdown
archive (or replica in version 9.6 and above)
enough information to allow the archival (and restoration) of WAL files.
replica
information required to run read-only queries on a standby server.
logical
extract logical change sets from WAL.
# The WAL level must be archive or higher.
wal_level = archive
ARCHIVE TIMEOUT
no activity then generate a wal after mentioned interval of time.
archive_timeout = 1h
How to CONTROL size and number of THE WAL FILES
# This is a soft upper limit on the total size of WAL files.
max_wal_size = 1GB
# Keep around at least these many WAL files (aka segments).
wal_keep_segments = 10
THE ARCHIVAL SETTINGS
# The WAL level must be archive or higher.
wal_level = replica
# This is a soft upper limit on the total size of WAL files.
max_wal_size = 1GB
alter system set max_wal_size = ‘1 GB’;
# Keep around at least these many WAL files (aka segments).
wal_keep_segments = 10
alter system set wal_keep_segments = 10;
# The archive_mode must be set to on for archiving to happen.
archive_mode = on
alter system set archive_mode = on;
# This is the command to invoke for each WAL file to be archived.
archive_command = ‘/postgres/logs/archive_wal %p’
alter system set archive_command = ‘/postgres/logs/archive_wal%f’;
alter system set archive_command = ‘cp %p /postgres/logs/archive_wal%f’;
‘cp %p /Archive/Location/%f’
# Ensure there is at least one WAL file for each “archive_timeout” duration.
archive_timeout = 1h
alter system set archive_timeout= ‘1 h’;
sudo systemctl restart postgresql-12
Verification.
show wal_level;
show max_wal_size;
show wal_keep_segments;
show archive_mode;
show archive_command;
show archive_timeout;