This blog post is a step by step tutorial on how to set up PostgreSQL Replication between AWS regions. This is an essential part of our disaster recovery plan at Engine Yard. A previous blog post gives a higher level overview on disaster recovery.
We’re going to use AWS on our examples because that’s what we use at Engine Yard. However, the commands should work on any hosting provider. If you want to move your Ruby on Rails application to AWS or discuss setting up a disaster recovery environment, contact us through the form below for a free consultation.
Let’s say our Production environment is on US East 1. We will set up a disaster recovery (DR) environment on a different region, like US West 2. For simplicity, we’ll have 1 app instance and 1 database instance on each of these environments.
We’ll set up the DR db instance as a replica of the Production db instance. Your data will be replicated from US East 1 to US West 2. When you failover to US West 2, the DR db instance is upto date and you can start using the DR environment.
Installation
For this tutorial, we will use Ubuntu 16.04 LTS on AWS. You can use ami-cd0f5cb6 and ami-6e1a0117 on US East 1 and US West 2, respectively. You can access the instances using the ubuntu user.
Install PostgreSQL on both Production and DR db instances.
sudo apt-get update
sudo apt-get install postgresql-9.5
PostgreSQL Config
To set up replication, /etc/postgresql/9.5/main/postgresql.conf on the Production db instance should have the following:
wal_level = hot_standby
archive_mode = on
archive_command = '/bin/true'
Still on the Production db instance, add these 2 lines to /etc/postgresql/9.5/main/pg_hba.conf.
host replication postgres 127.0.0.1/32 md5
host replication postgres localhost md5
On the DR db instance, create /var/lib/postgresql/9.5/main/recovery.conf.
Change MASTER_PASSWORD to the password you used for postgres.
standby_mode = 'on'
primary_conninfo = 'host=127.0.0.1 port=5433 user=postgres password=MASTER_PASSWORD'
trigger_file = '/tmp/postgresql.trigger'
Generate an SSH Keypair
The DR db instance needs to connect to the Production db instance and vice versa through SSH.
Do not use a passphrase.
ssh-keygen -t rsa -b 2048 -f dr_key
Do the following on both Production and DR db instances. We’ll use the postgresql and ubuntu users later.
- Add the contents of the public key to /home/ubuntu/.ssh/authorized_keys and /var/lib/postgresql/.ssh/authorized_keys.
- Put the private key on the /home/ubuntu/.ssh/ and /var/lib/postgresql/.ssh/ directories. Permissions should be 600.
If the .ssh directory doesn’t exist, create it.
sudo mkdir /var/lib/postgresql/.ssh
sudo vi /var/lib/postgresql/.ssh/authorized_keys
sudo chown postgres:postgres /var/lib/postgresql/.ssh/authorized_keys
SSH Tunnel
On the DR db instance, we will set the primary_conninfo to the Production db instance. Instead of using the IP address and port of the Production db instance, we will create an SSH tunnel from DR to Production.
We use an SSH tunnel because port 5432 on the Production db instance is only accessible from the Production instances. We use a Security Group to remove access from the outside world.
On the Production db instance, create the SSH tunnel.
# Hostname of your Production db instance
PRODUCTION_DB=ec2-54-174-79-181.compute-1.amazonaws.com
ssh -i ~/.ssh/dr_key -f -p 22 -N -L 5433:127.0.0.1:5432 ubuntu@$PRODUCTION_DB
On the DR db instance, test the tunnel by connecting to localhost and port 5433 (notice 5433 not 5432).
psql -h 127.0.0.1 --port 5433 -U postgres
Even though you are connecting to localhost, you are actually connecting to the Production db instance through the SSH tunnel. Use the db password of the Production db instance.
Configure Replication
Set PRODUCTION_DB
sudo su
systemctl stop postgresql
psql -U postgres -h localhost -p 5433 -c'select pg_start_backup('`date`');'
su - postgres -c 'rsync -avPz -e 'ssh -i ~/.ssh/dr_key -o StrictHostKeyChecking=no' --delete --exclude recovery.conf --exclude pg_log --exclude pg_xlog $PRODUCTION_DB:/var/lib/postgresql/9.5/main/ /var/lib/postgresql/9.5/main'
psql -U postgres -h localhost -p 5433 -c'select pg_stop_backup();'
su - postgres -c 'rsync -avPz -e 'ssh -i .ssh/dr_key -o StrictHostKeyChecking=no' -av $PRODUCTION_DB:/var/lib/postgresql/9.5/main/pg_xlog /var/lib/postgresql/9.5/main/'
systemctl start postgresql
exit
Free Consultation
Engine Yard has helped numerous customers deploy and scale Rails applications. For a free consultation regarding PostgreSQL replication and disaster recovery, reach out to us.
