POSTGRESQL - ARCHIVE COMMAND WITH FILE FORMATS WAL ARCHIVAL PROCESS

The archive_command in PostgreSQL controls the archiving of finished WAL (Write-Ahead Log) files. You may efficiently manage and arrange the archived WAL files by using file format naming conventions.

A comprehensive guide on setting up the archive_command with file types and the WAL archival process in general can be found here:
archive_command Syntax :
When a WAL segment is prepared for archiving, the archive_command is run. Frequently utilized placeholders:

    %p: Full path to the WAL file that PostgreSQL is trying to archive.
    %f: File name of the WAL file (e.g., 00000001000000000000000A).

Examples of archive_command :
1. Simple Copy to Directory :
Archives the WAL file into a specified directory :

    archive_command = 'cp %p /var/lib/pgsql/wal_archive/%f'

Result : The WAL file is copied to /var/lib/pgsql/wal_archive using its original name.

2. File Format with Timestamps : Archives WAL files with a timestamp in the file name for better organization:

    archive_command = 'cp %p /var/lib/pgsql/wal_archive/%f-$(date+%Y%m%d%H%M%S)'

Result : WAL files are copied with names like 00000001000000000000000A-20250110123045.
Note : Since archive_command does not execute in a shell by default, wrap commands with shell expansions like $(...) in a shell,
example :

    archive_command = '/bin/bash -c "cp %p /var/lib/pgsql/wal_archive/%f-$(date +%Y%m%d%H%M%S)"'

3. Compressed Archive :
Compresses WAL files to save storage space:

    archive_command = 'gzip < %p > /var/lib/pgsql/wal_archive/%f.gz'

Result : WAL files are compressed with .gz extension (e.g., 00000001000000000000000A.gz).

4. Organized by Date :
Stores WAL files in directories organized by date:

    archive_command = '/bin/bash -c "mkdir -p /var/lib/pgsql/wal_archive/$(date +%Y/%m/%d) 
    && 
    cp %p /var/lib/pgsql/wal_archive/$(date +%Y/%m/%d)/%f"'

Result :WAL files are saved in subdirectories like /var/lib/pgsql/wal_archive/2025/01/10/00000001000000000000000A.

5. Remote Archival :
Stores WAL files on a remote server using rsync or scp:

    archive_command = 'rsync -av %p user@remote:/path/to/wal_archive/%f'

Result : WAL files are archived on the remote server.
Steps for WAL Archival Process :
1.Enable Archiving : Configure archive_mode in postgresql.conf:

    archive_mode = on 
    archive_command = 'cp %p /var/lib/pgsql/wal_archive/%f'

2.Ensure WAL Directory Exists: Create the target directory where WAL files will be stored :

    mkdir -p /var/lib/pgsql/wal_archive

« Previous Next Topic » (Point In Time Recovery (PITR))