PostgreSQL‎ > ‎Standby‎ > ‎

Standby WAL Workflow

Quick rundown of the lifecycle of WAL files in a primary/standby setup.

Primary

  1. Primary writes WAL logs under $PGDATA/pg_xlog
    1. This can/should be symlinked to a separate disk for better I/O performance (less contention).
  2. Primary wal.conf has: archive_command = '/path/to/archive_script.sh -p %p -f %f'
    1. Should copy WAL file from pg_xlog to another directory (NFS, for example) for longer term storage
    2. Should copy WAL file to archive directory on standby server (eg /path/to/archive)
    3. If archive_command returns zero exit status (success), postgres will remove the original WAL file

Standby

Presence of $PGDATA/recovery.conf file means database is in standby recovery
  1. restore_command = 'cp /path/to/archive/%f %p'
    1. This copies files from the archive directory to the pg_xlog directory
    2. Optional if we're also attempting streaming replication (see primary_conninfo)
  2. primary_conninfo points to the primary to connect to primary and get WAL segments
  3. archive_cleanup_command will delete files under pg_archive that are no longer needed by the standby server
    1. archive_cleanup_command = '/usr/pgsql-9.2/bin/pg_archivecleanup /path/to/archive %r'
    2. pg_archivecleanup is provided by postgresql.
  4. WAL file is cleaned up automatically. Per documentation: "In archive recovery or standby mode, the server periodically performs restartpoints which are similar to checkpoints in normal operation: the server forces all its state to disk, updates the pg_control file to indicate that the already-processed WAL data need not be scanned again, and then recycles any old log segment files in pg_xlog directory."
Comments