PostgreSQL Replication Tutorial For Disaster Recovery



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.


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 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= 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

ssh -i ~/.ssh/dr_key -f -p 22 -N -L 5433: ubuntu@$PRODUCTION_DB

On the DR db instance, test the tunnel by connecting to localhost and port 5433 (notice 5433 not 5432).

psql -h --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


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

Free Consultation

Engine Yard has helped numerous customers deploy and scale Rails applications. For a free consultation regarding PostgreSQL replication and disaster recovery, fill out the form below.

Start a Free Trial:

Engine Yard is so much more than just a Ruby on Rails PaaS platform. But don't just take our word for it. Request a free trial of Engine Yard platform today, and one of our Engineers will be in contact within one business day to get you going


Related posts

Memcached Security aka Don't Attack GitHub 

March 7, 2018

GitHub recently experienced the largest attack we've seen to date. At the peak, they received

Read More

Ajax on Rails with Unobtrusive JavaScript

March 1, 2018

This blog post is for Rails developers that want to use Ajax in their application using

Read More

The 13 Ruby Conferences You Cannot Miss in 2018

February 28, 2018

Nothing remains constant, especially in technology. As we continue to develop new DevOps

Read More

Christopher Rigor

Christopher Rigor is a Senior Technical Evangelist at Engine Yard. He’s a long time Rails user, system administrator, and recently became a contributor of RailsInstaller. Previously, he was the DevOps Support Manager for Asia-Pacific at Engine Yard.
Find me on:


Subscribe Here!