PostgreSQL WAL Archival

Owned by James deVos
Last updated: Dec 12, 2013Version comment
3 min read

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  %f  %p' (%f represents the file name,  %p represents path to file's directory)
    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 valid archive shell scripts

Up-to-date examples of the tDAR WAL scripts are attached to this document.   These are the guidelines for creating the scripts called by postgres during the archival/recovery process

Archive Command Script

The "archive command" is the command specified by "archive_command" in postgresql.conf.  This is the command postgres executes to indicate that a WAL file is ready for archival.  Rules for this script:

  1. Exit with zero code only if successful.  Postgres will repeatedly execute the command if a previous attempt failed  until the operation is successful (or until pg_xlog runs out of space and postgres shuts down).
  2. The script should not attempt to move or modify the original file - postgres will take care of that. 

Baseline Backup Script

Backups created via pg_dump or pg_dumpall are insuffiencent for a restore baseline; you must use a "base backup", e.g.

pg_basebackup --pgdata=$BASELINE_DEST --format=tar --gzip --xlog --username=postgres 1>>$LOG

Recovery Command Script

Similar to the 'archive_command' script,  the recovery.conf file specifies a "restore_command" that the postgres recovery process executes when it requests a previously-archived file.  This can be as simple as a unix copy command, but will likely need to be a shell script of some kind. 

  1. Postgres will execute the comand specified by the 'restore_command' property in recovery.conf, e.g. "cp /archives/%f %p". Occurances '%f' are replaced by the filename, and '%p' are replaced by the full path to the destination where your script should place the retrieved archive.
  2. The restore script must exit with zero code only when successful
  3. The restore script must exit with non-zero if a requested archive is not present (note: this is expected behavior - postgres *will* request a missing file at least once during a normal recovery process.
  4. The script and it's parent directory need "rwx" permissions for userid=postgres

HOWTO: Restore a database  from WAL archive  

Follow these general steps to recover an archive.  Using  the following terminology:

  • PGDATA - path to the postgres cluster data direcory
  • PGRESTORE - this is a temporary path we will use for staging the files.
  • base.tar.gz - path to a previous baseline backup
  • PGWALARCHIVE - path to WAL archive directory

Procedure:

  1. Stop postgres, if it's running

    pg_ctl -D $PGDATA -m smart

     

  2. Restore the database files from base.tar.gz to $PGRESTORE

    gunzip base.tar.gz 
    tar -xvf base.tar --directory $PGRESTORE

  3. Duplicate the permissions from PGDATA to PGRESTORE,  including file owner and file permissions, for example

    chmod 700 $PGRESTORE
    find $PGRESTORE | xargs chown postgres:daemon

     

  4. Copy configuration files from $PGDATA (e.g. $PGDATE/*.conf) to $PGRESTORE.
  5. (Important!) comment out the following properties from $PGRESTORE/postgresql.conf
    1. "data_directory"
    2. "hba_file"
    3. "ident_file"
    4. "external_pid_file"
  6. Add a recovery.conf file to $PGRESTORE.  At the very least this should include a 'restore_command' entry (see previous section).

  7. Start postgres

    pg_ctl -D $PGRESTORE start

Postgres will log it's progress to the standard postgres log file.  Outside of the log file, the only way to tell if the restore was successful is that postgres will rename the 'recovery.conf' file  to 'recovery.done'.