Purpose

This document intends to outline the steps necessary to instantiate PostgreSQL Streaming Replication (SR), pursuant to providing a level of High Availability (HA) and Disaster Recovery (DR) to a Flow Production Tracking installation.

Scope and assumptions

The scope of this document is to cover how to configure PostgreSQL Streaming Replication in a manner favorable to a Flow Production Tracking application install. With a good understanding of the process, the instructions in this document can be modified to fit a particular environment that may differ from the normal Flow Production Tracking environment.

This document assumes the following:

• The OS is a Centos 7 64-bit distribution
• PostgreSQL is already installed and is being used to store a working Flow Production Tracking site
• The person has an advanced knowledge of PostgreSQL

Disclaimer

Setting up Streaming Replication is not without risk as it requires a configuration change on your production servers. We highly recommend to test your set-up on a staging environment first. When implementing for Production, make a traditional backup first and proceed in a maintenance window outside of your studio operation hours.

The following procedure is given as an example only. Always check the latest PostgreSQL documentation and validate every step.

Overview

PostgreSQL Streaming Replication advances the prior write ahead logs (WAL) shipping methodology used in PostgreSQL 8.4.x and below. Prior to SR, WAL shipping relied on copying transaction logs from a primary server and replaying them on a secondary (or tertiary, or quaternary) server. Thus, in the event of a failure, there was a possibility of losing an entire WAL's worth of transactions.

With Streaming Replication, transactions are replayed in real time on the secondary server, thus providing a much more immediate means of failover that gives the most lossless recovery of both data and functionality. In order to make the setup and failover as painless as possible, the following points should be determined prior to setup:

• Is there already a secondary server available? Is it as identical as possible to the primary? At the very least, the versions of PostgreSQL should be precisely the same, and provisions should be made to ensure that the secondary server has the requisite capacity to store and run the database in the event of a failover.
• Means of failover. Will it be IP-based, using something like KeepAlive? Or will it be a DNS-based failover? A manual process, or automated based on monitoring? As each environment is different, this document does not provide a recommended technological means of failover.
• Will replication traffic run over a private network, or over the same network that normal database traffic runs on? Depending on the rate of change on your database or the level of traffic it is already experiencing, it may be better to run replication traffic through a private network between the cluster hosts. It is recommended that you use internal monitoring to determine which is better for your instance.

Once the architectural points of information have been determined, it's time to step into the process of actually setting up Streaming Replication.

1. Configure primary and replica for streaming replication

All the information you'll need can be found on the PostgreSQL wiki. Make sure to double-check that your version of PostgreSQL matches the documentation you are consulting.

PostgreSQL Streaming Replication Wiki

2. Determine failover steps for your environment

Each environment is different and calls for a different approach to failover. For some, DNS-based failover will work better; for others, IP-based failover is the more appropriate choice. In any case, just make sure that the procedure is clear and available to all members of your Infrastructure or IT team. It is also preferable to have test the failover procedure before you actually need it!