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.

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:

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.

Christopher Rigor

Comments

Guillermo Siliceo

Hey great article, will probably coming back to it in the future, just saw a small typo on the first sentence of the article
This blog post is a step by step tutorial on how to set up MySQL
MySQL wink wink