Instantly share code, notes, and snippets.

Embed
What would you like to do?
Basic setup of WAL-E for continuous archiving and recovery

WAL-E needs to be installed on all machines, masters and slaves.

How to install WAL-E

Only one machine, the master, writes WAL segments via continuous archiving. The configuration for the master postgresql.conf is:

archive_mode = on
archive_command = 'envdir /etc/wal-e.d/env wal-e wal-push %p'
archive_timeout = 60

When a master is first setup, a base backup needs to be performed. As the postgres user:

envdir /etc/wal-e.d/env wal-e backup-push $PGDATA

This will perform a base backup and push it to S3. The above archiving commands will stream all deltas in realtime as generated by the Postgres master.

The flow for setting up a new Postgres machine slave, which will be based on a base backup.

  1. Install Postgres, configure as a slave.
  2. Remove the $PGDATA dir, if it exists. This will be re-created in step 3.
  3. Pull a base backup using WAL-E:

envdir /etc/wal-e.d/env wal-e backup-fetch $PGDATA LATEST

  1. Implement a $PGDATA/recovery.conf which is configured with WAL-E to replay remote segments:
standby_mode     = 'on'
primary_conninfo = 'host=$HOST user=$USER password=$PASSWORD'
restore_command  = 'envdir /etc/wal-e.d/env wal-e wal-fetch "%f" "%p"'
trigger_file     = '/data/postgresql/9.1/main/trigger'

4b. During recovery, by default WAL-E will attempt to pull and restore all WAL segments available. If you wish to restore to a specific POINT IN TIME than specify

# restore to 4:38PM on 3/6/2012
recovery_target_time = '2012-03-06 16:38:00'

In recovery.conf to recover to that specific point in time. This can be helpful it something bad happened at 4:39PM and you want to restore to 4:38PM.

See Recovery Target Settings for more details.

  1. Ensure proper permissions:
chown -R postgres:postgres $PGDATA
  1. Start Postgres and tail its log. It should start up and fetch WAL segments from S3, which is being performed by the restore_command in the above recovery.conf.

Setup a CRON to perform base backups on a regular schedule

We have WAL-E configured to push a base backup nightly:

/etc/cron.d/pg_base_backup:

0 8 * * * postgres envdir /etc/wal-e.d/env wal-e backup-push /data/postgresql/9.1/main/
@jokull

This comment has been minimized.

jokull commented Jan 18, 2013

Great tutorial. Thanks! ⭐️

@gtaylor

This comment has been minimized.

gtaylor commented Jan 31, 2013

It looks like postgresql.conf isn't backed up by WAL-E, so the base backup lacks it. How do you account for this?

@ruckus

This comment has been minimized.

Owner

ruckus commented May 21, 2013

@gtaylor you're right in that WAL-E doesnt take into account any configuration files (postgresql.conf, pg_hba.conf, etc). I account for this by having those files in a git repo that contains configs for all my services / apps.

@shyamnambiar

This comment has been minimized.

shyamnambiar commented Jun 11, 2013

How to install this on centos6?

@neilmcguigan

This comment has been minimized.

neilmcguigan commented Jul 20, 2013

404 page for your "how to install wal-e" link. thx

@ghost

This comment has been minimized.

ghost commented Jul 26, 2013

Recovery will not complete if standby_mode is set to 'on' . See https://groups.google.com/forum/#!topic/wal-e/nrNZW8deP2k for more info.

@kcivey

This comment has been minimized.

kcivey commented Dec 19, 2013

I had to put the full path for wal-e into the archive_command:

archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'

Without it postgres wasn't finding the command.

@TheCorp

This comment has been minimized.

TheCorp commented Jan 24, 2014

Just curious, I haven't really been able to find this in the documentation but is each base backup from scratch or is it done as a diff from the past base backup? Thanks!

@wunki

This comment has been minimized.

wunki commented Jan 28, 2014

@TheCorp each base backup is a full backup, no diff.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment