Enabling PostgreSQL Replication in the Cloud

| September 17, 2013

IMPORTANT: The updated instructions on PostgreSQL Replication are available here:

Replication is a primary technology for any database server, and downtime or data loss can result in lost productivity, revenue and product confidence. By replicating data from a master to one or more standby servers, you can greatly reduce the possibility of any data loss. Today’s blog post is focused on PostgreSQL high availability and clustering features.  With Jelastic Cloud PaaS, you can easily create a cluster with one or more standby servers, ready to take over operations if the master server fails. In this case, the master database server works in archiving mode, while the standby operates in recovery mode, reading the WAL (Write-Ahead Logging) files from the master.

PostgreSQL Replication

In the case of a total failure of the master, the contents of the WAL will be streamed to standby servers with a nominal delay of just a few seconds or less.

This is a tutorial on how to set up a PostgreSQL hot standby (or streaming) replication, which provides asynchronous binary replication to one or more standbys. In this case, a standby database can be queried as a read-only database. It is the fastest way of replication available as WAL data, which is sent immediately from master to standby.

Let’s get started!

Create Environments

The first step is the creation of two identical environments with PostgreSQL: one for the master database and one for the standby.

1. Log into your Jelastic account and click on the Create environment button, select PostgreSQL 9 as the database you want to use and set the resource limits for it. Then specify your master database environment name.

Create PostgreSQL Database Master

In a moment you’ll see the new environment with Postgres on the Jelastic dashboard.

Postgres Master Environment

2. Create one more environment with Postgres or just clone the existing one. This will be your standby database, located on the other hardnode.  This is even more secure and reliable for storing your data.

Now you have two identical environments with two PostgreSQL databases.

Postgres Standby Database

Configure PostgreSQL Replication

1. Click the Config button for your master database and navigate to the conf directory. Change the following settings in the postgresql.conf file in order to turn on streaming replication:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3
PostgreSQL Database Master Config

2. Navigate to the pg_hba.conf file on the master database (it is also located in the conf folder) and allow the standby connection.

 host  replication   all   {standby_ip_address}/32      trust
PostgreSQL Standby Connection

3. Next, click Config for the standby database, go to postgresql.conf  and switch on the hot standby mode. This provides the ability to connect to the server and run read-only queries while the server is in archive recovery or standby mode.

 hot_standby = on
PostgreSQL Standby Configuration

4. Create a recovery.conf file in the standby’s data directory (in the same directory as postgresql.conf and pg_hba.conf) containing the following lines:

standby_mode = 'on'
primary_conninfo = 'host={master_ip_address}'
PostgreSQL Standby Recovery Conf

5. Restart both nodes (master and standby) in order to apply the new settings.

Check the Results

1. Open your master base in a web browser, login to phpPgAdmin using the credentials which Jelastic sent you when you created the environment and create the new database.

PostgreSQL Master

2. Then you should open the admin panel of your standby database server and check if your new database was replicated successfully.
Note that standby DB web-interface is unavailable while standby mode is enabled. Therefore navigate to the conf folder of standby server and rename the recovery.conf file to recovery.con:


Restart the standby database.

3. In the same way as with master database, open the admin panel of your standby database.

Note: in case you used environment cloning feature while creating second environment with standby database, you should recover the password for this database in order to get access to it. For that click additional button next to the standby DB and choose Reset password line.


You’ll receive an email with new credentials.

4. Check if your new database was replicated successfully.

PostgreSQL Replication Standby

5. When everything is configured correctly, don’t forget to rename recovery.con file back to the recovery.conf and Restart the standby DB.

The above mentioned steps are the only changes you need to make to get a PostgreSQL highly-available database cluster. Just give it a try!

Related Articles: