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
- Identify/create the associated paths on the postgres server. Unless otherwise stated It is not necessary for these paths to be unique.
- BASEBACKUP - the directory that will hold the "base backup" folder. There should only be one copy of this file at any given time
- XLOG - the directory that holds the archived WAL files
- PGDATA - location of the postgres database directory
- PGCONFIG - location of the postgres configuration files (postgresql.conf, pg_hba.conf, etc.)
- ARCHIVE_SCRIPT - script that postgres executes to process WAL files that are ready to be archived. See separate HOWTO.
- 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
- Configure postgresql.conf. Add / modify the following properties to these values:
- wal_level = archive
- archive_mode = on
- archive_command = '/path/to/ARCHIVE_SCRIPT %f %p' (%f represents the file name, %p represents path to file's directory)
- max_wal_senders = 2
- archive_timeout = 3600 # sets minimum WAL creation every 3600s (1 hr) - can be set at your descretion but consider impact on space requirements.
- Enable replication for postgres user
- pg_hba.conf - add separate line for replication connections (examples in the file's comments).
- ALTER USER postgres WITH REPLICATION;
- register BACKUP_SCRIPT to execute daily - via cron, cron.daily, or (whatever you windows people use).
- restart postgres
- 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:
- 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).
- 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.
- 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.
- The restore script must exit with zero code only when successful
- 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.
- 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:
- Stop postgres, if it's running
e.g. pg_ctl -D $PGDATA -m smart - Restore the database files from base.tar.gz to $PGRESTORE
gunzip base.tar.gz
tar -xvf base.tar --directory $PGRESTORE - TBD: SET CORRECT OWNER/PERMISSIONS
- TBD: SETUP RECOVERY.CONF AND PLACE INTO PGRESTORE
- start postgres
pg_ctl -D $PGRESTORE start
Troubleshooting
tbd
Related articles