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;
16 июл 2024
