Conventions:

• master: Your main server
• backup: Your backup server
• Postgresql 9.3 in both servers with the standard data paths (/var/postgresql/<version>/<cluster>)

Very important

• All commands to install packages are done by a sudo user or by the root user
• All commands to manage the postgresql server are done by the postgres user

Packages and configurations in both servers

Install packages in both backup and master

sudo apt-get install daemontools
sudo easy_install pip
sudo pip install wal-e

For a weird reason, if you install pip using apt-get, all packages installed by pip will not be able to be run by the postgres user.

Create an environment directory to use wal-e

envdir is part of the applications comming with daemontools that enables a command to use files inside a directory as environment variables using the filename as a key, and its content as the value. You can see more examples here: http://thedjbway.b0llix.net/daemontools/envdir.html

An /etc/wal-e.d folder is going to be created in order to store the environment variables needed. Just run the following commands:

umask u=rwx,g=rx,o=
mkdir -p /etc/wal-e.d/env
echo "secret-key-content" > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
echo "access-key" > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
echo 's3://some-bucket/directory/or/whatever' > /etc/wal-e.d/env/WALE_S3_PREFIX
chown -R root:postgres /etc/wal-e.d

Backup & Recover your server

Setup your master postgresql.conf file

It is located at /etc/postgresql/9.3/main/

It should contain the following lines:

wal_level = archive
archive_mode = yes
archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'
archive_timeout = 60

Since the archive_command is going to be executed as postgres user, we need to ensure it will run well after we restart the server. If you can run as postgres user the following command:

envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list

... then everything works well. Now the server should be restarted to take the new configuration.

You can find more options for the postgresql.conf here: http://www.postgresql.org/docs/9.3/static/runtime-config-wal.html

Finally, restart your server.

Push your backup

Login as postgres and run wal-e backup-push:

su postgres
envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/9.3/main

Setup your backup server

If this is a new backup, you will have to remove the whole data dir and copy the backup to a cluster folder. Run the following:

su postgres
rm -rf /var/lib/postgresql/9.3/main
envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch /var/lib/postgresql/9.3/main LATEST

The word LATEST could be changed to whatever backup file you want listed by:

envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list

The format of a backup filename is base_XXXXXX_XXXX. Example:

base_00000001000000010000002A_00000040

Stop your server and create a recovery.conf file inside your data folder with the following line:

restore_command = '/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch %f %p'

Restart your server. It should start recovering.

After the database is recovered, the recovery.conf filename is changed to recovery.done in order to prevent human mistakes.

You should have everything backed up now.

Stand by server

If you want this to be you stand by server for backups, just add the following line to your recovery.conf file and restart your server:

standby_mode = 'on'

You won't be able to access to your data by psql, but it will be updating once in a while. This is shown in the log.

Don't worry if there are not found files, it is just a worker looking for the next segment of the wal :).

Extra

During my research I found a package to pimp your postgresql server. You can check it out here:

pg_tune

The post where I took this link from: Adventures in Scaling, Part 2: PostgreSQL

I hope this post was useful for those who have spent more than 3 hours looking for a very described tutorial like me.