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.

You might also like:   That's Not a Memory Leak, It's Bloat

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.

You might also like:   Services, the Missing Element in Rails Applications: A Discussion with Riaz Virani

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, reach out to us.

Want more posts like this?

What you should do now:


Easy Application Deployment to AWS

Focus on development, not on managing infrastructure

Deploying, running and managing your Ruby on Rails app is taking away precious resources? Engine Yard takes the operational overhead out of the equation, so you can keep innovating.

  • Fully-managed Ruby DevOps
  • Easy to use, Git Push deployment
  • Auto scaling, boost performance
  • Private, fully-configured Kubernetes cluster
  • Linear pricing that scales, no surprises
  • Decades of Ruby and AWS experience

14 day trial. No credit card required.

Sign Up for Engine Yard

14 day trial. No credit card required.

Book a Demo