Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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 "archive command" script

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. 

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: Create a valid baseline backup script

tbd

 

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
    e.g.  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. TBD: SET CORRECT OWNER/PERMISSIONS
  4. TBD: SETUP RECOVERY.CONF AND PLACE INTO PGRESTORE
  5. start postgres
    pg_ctl -D $PGRESTORE start

 

Troubleshooting 

tbd

 

  • No labels