Page Comparison

...

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. prayPray.

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

...

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: 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:

...