PostgreSQL uses Write-Ahead Logging (WAL) to ensure data consistency and recoverability. WAL files record every change made to the database before the actual data files are updated. By enabling WAL archiving, administrators can retain WAL files outside the database server and use them for Point-in-Time Recovery (PITR) and disaster recovery.

What is WAL in PostgreSQL?

WAL (Write-Ahead Log) is PostgreSQL’s transaction logging mechanism. Before any changes are written to the database files, they are first recorded in WAL files.

Benefits of WAL:

• Ensures data durability
• Supports crash recovery
• Enables streaming replication
• Supports Point-in-Time Recovery (PITR)
• Allows continuous backup strategies

Check Current WAL Configuration

Connect to PostgreSQL and check the current settings:

SHOW archive_mode;
 archive_mode
--------------
 off
SHOW archive_command;
 archive_command
-----------------
(disabled)
SHOW wal_level;
 wal_level
-----------
 replica

Locate PostgreSQL Configuration File

Find the location of the PostgreSQL configuration file:

SHOW config_file;
config_file
----------------
/var/lib/pgsql/18/data/postgresql.conf

Create an Archive Directory

Create a directory where archived WAL files will be stored.

mkdir -p /postgres_archive
chown postgres:postgres /postgres_archive
chmod 700 /postgres_archive
ls -ld /postgres_archive   -- verify

Configure WAL Archiving

Edit the PostgreSQL configuration file.

vi  /var/lib/pgsql/18/data/postgresql.conf
-- Modify the following parameter to configure WAL:
# WAL Configuration
wal_level = replica
# Enable Archiving
archive_mode = on
# Archive Command
archive_command = 'cp %p /postgres_archive/%f'
# Replication and WAL Retention
max_wal_senders = 10
wal_keep_size = 1GB

Parameter Explanation

Restart PostgreSQL

The parameter archive_mode requires a database restart.

sudo systemctl restart postgresql

Verify Archiving Configuration

After the restart, verify the settings.

SHOW archive_mode;
SHOW archive_command;
SHOW wal_level;

Generate WAL Activity

Create a sample table and insert data.

CREATE TABLE wal_test (
   id INT
);
-- insert data
INSERT INTO wal_test
SELECT generate_series(1,100000);

Force a WAL Switch

To immediately archive the current WAL file, execute:

SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/3000000

Verify Archived WAL Files

Check the archive directory:

ls -ltr /postgres_archive
000000010000000000000001
000000010000000000000002
000000010000000000000003

Monitor WAL Archiving Status

PostgreSQL provides statistics through the pg_stat_archiver view.

SELECT * FROM pg_stat_archiver;