POSTGRESQL - WAL_LEVEL AND ARCHIVE_MODE OPTIONS

Two PostgreSQL configuration settings that are associated with Write-Ahead Logging (WAL) and data durability are wal_level and archive_mode.

This is an in-depth explanation:
1. wal_level :
Description : Establishes how much data is written to the Write-Ahead Log (WAL) for replication and recovery.
Values :
  • minimal : Sends WAL just enough data to enable recovery in the event of a crash or sudden shutdown. does not support replication or PITR (Point-in-Time Recovery).
  • replica : (Default) writes enough information to enable PITR and streaming replication. needed in the majority of replication configurations.
  • Logical : Provides data required to replicate logic. allows for the logical capture of data changes (e.g., for use with pglogical or logical replication slots).
  • How to Set : Add or modify the setting in postgresql.conf: 
        wal_level = replica
    OR
    ALTER SYSTEM SET wal_level = 'replica'; 
    SELECT pg_reload_conf();
    2. archive_mode :
    Description : Allows or prohibits WAL file archiving for PITR and long-term storage. VALUES :
  • off : (Default) Archiving is not enabled.
  • on : If an archive_command is defined, it permits the archiving of WAL files.
  • How to Set : Add or modify the setting in postgresql.conf: 
        archive_mode = on 
    archive_command = 'cp %p /path/to/archive/%f'
    OR
    ALTER SYSTEM SET archive_mode = 'on'; 
    ALTER SYSTEM SET archive_command = 'cp %p /path/to/archive/%f'; 
    SELECT pg_reload_conf();
    Relationship Between wal_level and archive_mode :
    Archive_mode requires wal_level to be set to at least replica. With WAL archiving, the minimal level is incompatible. WAL files can be utilized for Point-in-Time Recovery (PITR) and are archived once they are finished when both are enabled. Preserving a database modification history.
    Validation :
    To check the current values: 
        SHOW wal_level; 
    SHOW archive_mode; 
    SHOW archive_command;
    This configuration ensures reliable backup and replication options in PostgreSQL.


    (archive_command with File Formats WAL Archival Process)