I run PostgreSQL on two AWS EC2 instances and have binary streaming replication between them for high availability. Since both VMs are in AWS, it only makes sense to use S3 for archiving and backups. I will assume you already have two working EC2 instances running PostgreSQL with replication and only wish to add WAL-E with S3 into the mix.
Before we get started you will need to make sure you have python pip and python virtualenv installed. I also needed to install zlib but that may have been because I started from a very minimal install.
I will go over the setup once as I have my master and slave configured 100% identical with the exception that the master has recovery.conf named recovery.conf.use to keep it from becoming a slave.
We will need the AWS CLI tools before we get into WAL-E.
# pip install awscli
You will need an AWS account/IAM user that has permissions on the S3 bucket we will be using. If you haven’t set an account or an S3 bucket do that now. Both my EC2 instance and S3 bucket are in the US-West region so be sure to adjust that if needed. Now we need to create config and credential files for user postgres:
$ sudo -u postgres -i
postgres ~ $ mkdir ~/.aws
postgres ~ $ echo -e “[default]\nregion = us-west-2\noutput = json” > ~/.aws/config
postgres ~ $ chmod 600 ~/.aws/config
postgres ~ $ echo -e “[default]\naws_secret_access_key = <REPLACE WITH YOUR KEY>\naws_access_key_id = <REPLACE WITH YOUR KEY ID>” > ~/.aws/credentials
postgres ~ $ chmod 600 ~/.aws/config
Now would be a good time to test your awscli install and your credentials:
postgres ~ $ aws s3 ls <REPLACE WITH YOUR BUCKET NAME>
If you get an error go back and check your credentials as well as your IAM permissions for the user and bucket.
Next we will install WAL-E:
# pip install wal-e
We need to make a directory to hold the configuration files:
# mkdir -p /etc/wal-e.d/env/
# chown root:postgres /etc/wal-e.d
# chmod 750 /etc/wal-e.d
# chown root:postgres /etc/wal-e.d/env
# chmod 750 /etc/wal-e.d/env
Now we need to create the configuration/credential files:
# echo “<REPLACE WITH YOUR KEY ID>” > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
# echo “us-west-2” > /etc/wal-e.d/env/AWS_REGION
# echo “<REPLACE WITH YOUR ACCESS KEY>” > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
# echo “s3://<REPLACE WITH YOUR S3 bucket URL>” > /etc/wal-e.d/env/WALE_S3_PREFIX
# chown postgres:postgres /etc/wal-e.d/env/*
# chmod 640 /etc/wal-e.d/env/*
Note that the S3 URL prefix must be a lowercase “s3://” or it will fail!
Now is a good place to stop and manually do a basebackup to test the WAL-E install, configuration, and credentials (be sure to adjust the path to match where your database resides on the filesystem):
postgres ~ $ envdir /etc/wal-e.d/env wal-e backup-push /var/lib/postgresql/9.5/main
If everything goes properly it should automatically start a backup, copy the files, and finish. If you encounter any errors be sure to double check your credentials and paths.
Next we will edit our postgresql.conf to use WAL-E for archiving:
archive_command = ‘envdir /etc/wal-e.d/env /usr/bin/wal-e wal-push %p’
Then we will edit our recovery.conf to also use WAL-E:
restore_command = ‘envdir /etc/wal-e.d/env /usr/bin/wal-e wal-fetch “%f” “%p”‘
At this point we can restart postgresql to make use of the new configuration (be sure to adjust this commend if you are using systemd or another init system):
service postgresql-9.5 restart
We also want our basebackups to go to S3 so we’ll create a cronjob for that:
postgres ~ $ crontab -e
Then add a line similar to this (be sure to adjust the path to match where your database resides on the filesystem):
0 2 * * * postgres envdir /etc/wal-e.d/env wal-e backup-push /var/lib/postgresql/9.5/main
We should be good to go at this point and you can watch your postgresql log file (postmaster.log for me) to make sure everything is running smoothly on both the master and slave respectively.
If you plan on keeping a lot of archive files but do not need quick access to them you may want to consider setting up an S3 bucket policy that moves things to glacier after a certain number of days. For me I have it configured to move files older than one week into a cheaper storage medium and then into glacier after two weeks.
That’s about all there is to it and if you need further reading check out the WAL-E github page and of course Google is your friend when it comes to error messages!