This document is an opensql technical document based on postgresql.

Overview

WAL is a method where data is first written to a log file before being written to the actual data file.

This allows the database system to maintain data consistency and stability even in situations such as abnormal shutdowns. It plays an important role especially in large transactions or recovery operations.

This document provides guidance on WAL files, related parameters, and functions.

Note

This is an excerpt from the official PostgreSQL documentation explaining WAL.

Write-Ahead Logging (WAL) is a standard method for ensuring data integrity. A detailed description can be found in most (if not all) books about transaction processing. Briefly, WAL’s central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.)

- PostgreSQL Official Documentation -

Method

WAL File Structure

WAL File

A WAL file is a file composed of a continuous set of WAL Records.

• The WAL file location is recorded in the $PGDATA/pg_wal/ directory inside the Data Cluster ($PGDATA).
• Each WAL file is 16MB in size, and there must always be at least one WAL file.
• The WAL file name is expressed in hexadecimal.
• The first 8 characters represent the timeline-id, followed by 16 characters indicating how many WAL files have been created.

WAL Management

Adjusting the Number of WAL Files

WAL files are continuously created with sequential names, but when the total size of all WAL files in the directory exceeds a certain limit, previously created WAL files are deleted and recycled.

You can adjust the total number of WAL files in the pg_wal directory via parameters in postgresql.conf.

WAL Related Parameters

Adjusting WAL File Size

WAL_level Parameter
The WAL level requires restarting the PostgreSQL server to take effect.

Changing WAL File Size

WAL files are 16MB by default but can be changed during the initdb process or by using pg_resetwal. During initdb, you can specify the WAL file size with the --wal-segsize=<segment_size> option.
<segment_size> is in MB.

$ initdb -U postgres -D $PGDATA -X /opensql/pg/$PGVERSION/pg_wal –wal-segsize=32

$ ls -al /opensql/pg/$PGVERSION/pg_wal

The pg_resetwal command cleans up WAL files and resets various information in the pg_control file. This command must be run with the PostgreSQL server stopped.

Caution
Using pg_resetwal will remove existing WAL files, so use it carefully.

$ pg_ctl stop
$ pg_resetwal -D /opensql/pg/14/data –wal-segsize=16

$ ls -al /opensql/pg/$PGVERSION/pg_wal

Archiving WAL Files

Archive

When archive mode is enabled, archive files are copied to the configured archive directory.
The archive runs based on the configured ‘archive_command’, which typically copies files using the Unix cp command.

Archive Related Parameters

Archive Practice

Enable archive mode and set archive_command

$ vi $PGDATA/postgresql.conf
….
archive_mode=on
archive_command=’test ! -f /opensql/pg/14/archive/%f && cp %p /opensql/pg/14/archive/%f’
….

$ pg_ctl start

Create Test Table and Switch WAL File

$ psql -c “CREATE TABLE archive_test(c1 text); “
$ psql -c “SELECT pg_switch_wal();”
$ ls -al /opensql/pg/14/archive

WAL Functions

• pg_switch_wal()
  • Switches to a new WAL file
  • RETURN: LSN + 1
• pg_walfile_name(lsn)
  • Returns the WAL file name corresponding to the given LSN
  • RETURN: [text] WAL file name
• pg_current_wal_lsn()
  • Returns the last recorded LSN in the current WAL
  • RETURN: [pg_lsn]
• pg_ls_waldir()
  • Retrieves information about WAL files in the current WAL directory
  • RETURN: WAL file name, WAL file size, last update time (timezone)

WAL Function Practice

• Query the last recorded LSN in WAL

$ SELECT pg_current_wal_lsn();

• Query the WAL file name for a specific LSN

$ SELECT pg_walfile_name(pg_current_wal_lsn());

• Query the name of the currently used WAL file

$ SELECT pg_walfile_name(pg_current_wal_lsn());

• Query WAL file information in the current WAL directory (pg_wal)

$ SELECT pg_walfile_name(pg_current_wal_lsn());