How should I perform PostgreSQL Database Replication

Replication is a basic technology for any database server because the downtime or the data loss can result in reducing accessibility, productivity and product confidence. Using data replication from a master server to one or more standbys decreases the possibility of any data loss. With PostgreSQL, I can easily create a database cluster with one or more standby servers, which are ready to inherit and perform operations in case the master server fails.

Using WAL (Write-Ahead Logging) data is the fastest available way of replication with a great performance, so-called asynchronous replication. In this case the master database server works in archiving mode just writing the data to the storage. While the standby database cluster operates in recovery mode, reading the master's WAL files. These files are transferred to the standby DB promptly after writing is completed. In such a way, if master server totally fails, the WAL content streams to the standby cluster with just a few seconds delay.

So, let's see how to get advantages of PostgreSQL database clustering and high availability by setting up the hot standby (or streaming) replication, i.e. asynchronous binary replication to one or more standbys, queried as a read-only databases.

I am able to set a DB cluster in a two ways:

• automatically (via one-click widget)
• manually (following the step-by-step instruction)

At the very beginning let's create two identical environments with PostgreSQL nodes: the first one for master DB and the second one for the standby.

Log into the CloudJiffy dashboard.

Click the Create environment button in the top left corner of the dashboard. Then in the topology wizard select PostgreSQL 9 as the database I want to use and set the cloudlet limits for it. Enter the name for the environment with my master database and click Create.

It will take just a minute to create the environment. After that I'll see a new environment with PostgreSQL database in the CloudJiffy dashboard.

Create another environment with the PostgreSQL database in the same way as it is described above or I can just clone already existing one.

PostgreSQL in the second environment will be my standby database, located on another hardware node. It is much more secure and reliable for storing my data.

For now I have two identical environments with PostgreSQL database node in each one.

Find the environment with the master database in my environment list. Click the Config button next to the PostgreSQL node.

Open the conf directory and navigate to the postgresql.conf file. Find the following lines in the file, uncomment them, and specify the next settings’ values for enabling streaming replication:
wal_level = hot_standby

Press the Save button above the editor.

3.Open the pg_hba.conf configuration file, which is located in the same conf folder. Permit the standby database cluster connection by stating the following parameters:

host replication all {standby_IP_address}/32 trust

In order to get the IP address of any server, click the Info button next to it and copy the address in the opened frame:

4.Now I should access my environment with master DB via SSH and enter the PostgreSQL container it contains.

Once I’ve entered the container, stop it with the following command:

sudo service postgresql stop

Then execute the next line in order to delete the old WAL log file and simultaneously create a new empty one:

/usr/pgsql-9.3/bin/pg_resetxlog -f /var/lib/pgsql/data/

Finally, start the container:

sudo service postgresql start

That’s all for master! Let’s proceed to the standby server’s configuration.

Access the environment with standby DB server via SSH in the same way I did it for the master one:

Just after I’ve entered the DB container I need to stop it:

sudo service postgresql stop

Then remove the old database with the following command:

rm -rf /var/lib/pgsql/data/*

Copy a new database from the master server:

pg_basebackup -U webadmin -R -D /var/lib/pgsql/data/ --host={master_IP_address} --port=5432

Note: I should substitute the {master_IP_address} value with the IP address of my master PostgreSQL server.

To complete the configuration of standby DB switch back to the CloudJiffy dashboard and press the Config icon for PostgreSQL server in standby environment.

In the opened Configuration manager locate the postgresql.conf file in the conf directory, uncomment the hot_standby = offparameter in the Standby Servers section, and change its value to on.

Save the changes I’ve performed.

Finally, switch to my SSH console and run the container with slave DB:

sudo service postgresql start

Replication is configured! For now let’s check everything works fine.

 Results Checking

Open the phpPgAdmin panel for my master database by clicking Open in browser button next to it.

Log in with the database credentials I've got via email earlier and create a new database.

Then I should open the admin panel of my standby database server (in the same way as for master one) and check if my new database was replicated successfully. 

NOTE: as we’ve copied all the data and configurations from the master PG server to the standby PG server, credentials for admin access to the standby database became the same as to the master’s one.

That's all. I've got a PostgreSQL highly-available database cluster. Enjoy!