PostgreSQL WAL Archival

This page describes how to turn on enable PostgreSQL's WAL file archive feature and how to perform certain workflows, assuming a tDAR infrastructure  or development environment.

HOWTO: Configure PostgreSQL for WAL archiving

1. Identify/create the associated paths on the postgres server. Unless otherwise stated It is not necessary for these paths to be unique.
   
   1. BASEBACKUP - the directory that will hold the "base backup" folder. There should only be one copy of this file at any given time
   2. XLOG - the directory that holds the archived WAL files
   3. PGDATA - location of the postgres database directory
   4. PGCONFIG - location of the postgres configuration files (postgresql.conf, pg_hba.conf, etc.)
   5. ARCHIVE_SCRIPT - script that postgres executes to process WAL files that are ready to be archived.  See separate HOWTO.
   6. BACKUP_SCRIPT- script that is responsible for pruning the XLOG folder and creating a new creating a new baseline backup in BASEBACKUP. See separate HOWTO
2. Configure postgresql.conf.  Add / modify the following properties to these values:
   1. wal_level = archive 
   2. archive_mode = on
      
   3. archive_command = '/path/to/ARCHIVE_SCRIPT'
   4. max_wal_senders = 2    
   5. archive_timeout = 3600    # sets minimum WAL creation every 3600s (1 hr) - can be set at your descretion but consider impact on space requirements.
3. Enable replication for postgres user
   1. pg_hba.conf - add separate line for replication connections  (examples in the file's comments).  
   2. ALTER USER postgres WITH REPLICATION;
4. register BACKUP_SCRIPT to execute daily - via cron, cron.daily,  or (whatever you windows people use).
5. restart postgres
6. pray.

HOWTO: Create a valid "archive command" script

tbd

HOWTO: Create a valid baseline backup script

tbd

HOWTO: Restore a database  from WAL archive  

tbd

Troubleshooting 

tbd

Related articles

• Page:
  PostgreSQL WAL Archival