Automatic Continuous PostgreSQL Backup with Wal-e

Create AWS S3 IAM user and a bucket

1. Create IAM User with with Programmatic Access [AWS Doc]
2. Create new group with AmazonS3FullAccess policy for the user
3. Obtain Access Key and Secret for the user [AWS Doc]
4. Create a Bucket [AWS Doc]

Install wal-e on server

Install pip if necessary.

wget https://bootstrap.pypa.io/get-pip.py
sudo python3 get-pip.py

Install wal-e

sudo apt install daemontools lzop pv
sudo pip3 install 'wal-e[aws]'

Configure backing up of WAL segments

sudo mkdir -p /etc/wal-e.d/env
echo 's3://bucket_name/' | sudo tee /etc/wal-e.d/env/WALE_S3_PREFIX
echo 'ap-south-1' | sudo tee /etc/wal-e.d/env/AWS_REGION
echo 'aws_access_key_id' | sudo tee /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
echo 'aws_secret_access_key' | sudo tee /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY

Configure base backup

POSTGRES_VERSION=9.6
echo -e "\n# Backup settings for WAL-E" | sudo tee -a /etc/postgresql/${POSTGRES_VERSION}/main/postgresql.conf
echo -e "wal_level = replica" | sudo tee -a /etc/postgresql/${POSTGRES_VERSION}/main/postgresql.conf
echo -e "archive_mode = on" | sudo tee -a /etc/postgresql/${POSTGRES_VERSION}/main/postgresql.conf
echo -e "archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'" | sudo tee -a /etc/postgresql/${POSTGRES_VERSION}/main/postgresql.conf
echo -e "archive_timeout = 60" | sudo tee -a /etc/postgresql/${POSTGRES_VERSION}/main/postgresql.conf

Add cronjob for nightly base backup

crontab -l | { cat; echo "0 0 * * * /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/9.6/main"; } | crontab -

Restoring from backup

POSTGRES_VERSION=9.6
sudo systemctl stop postgresql
sudo mv /var/lib/postgresql/${POSTGRES_VERSION}/main /var/lib/postgresql/${POSTGRES_VERSION}/main_old

su postgres
POSTGRES_VERSION=9.6
envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch /var/lib/postgresql/${POSTGRES_VERSION}/main LATEST

vi /var/lib/postgresql/${POSTGRES_VERSION}/main/recovery.conf
restore_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch "%f" "%p"'
#Optionally add recovery point in time
#recovery_target_time = '2017-04-01 03:07:00'

exit

sudo systemctl start potgresql