How to run Vidispine with high availability Part 3 – pgpool AND PostgreSQL

In the third part of the Vidispine High Availability series we take a look on how you can use pgpool-II to configure PostgreSQL for high availability.

In this third part of the high availability series we will show you how to configure Postgresql for high availability. In Part #1 we made a simple Vidispine cluster, and in Part #2 we added HAProxy, and in the final Part #4 we will show how you can work with SolrCloud.

PostgreSQL HA Configuration

In this part, we will make use of the PostgreSQL “stream replication” (binary replication) feature and pgpool-II to setup a HA PostgreSQL backend. “Stream replication” is a new feature since PostgreSQL 9.0, so make sure PostgreSQL 9.0 or above is used.

In stream replication, one or multiple standby server(s) will need to be configured to receive WAL logs from the primary server.

When the primary server fails, a trigger file will need to be created on one of the standby server. So that standby can stop reading WAL logs and switch to read-write or “master” mode. In this case, the trigger file will be created by pgpool

Please note that if there are multiple standby servers, promoting one of them would break the others. The others need be reconfigured to pointing to the new primary.

For more detailed explanation about stream replication, please refer to https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
https://www.postgresql.org/docs/9.1/static/runtime-config.html

Testing environment

Ubuntu 12.04 + PostgreSQL 9.1.8

PostgreSQL server 1 (primary): 10.185.20.1
PostgreSQL server 2 (standby): 10.185.20.2
pgpool server: 10.185.20.100

PostgreSQL data folder is

Installation

Install PostgreSQL on server 1 and server 2 as usual, and make sure the databases are initialized and running without problems.

Install pgpool-II on server 10.185.20.100

Configuration

STREAM REPLICATION

First of all, please make sure that the user running PostgreSQL primary server is able to access standby servers using the following command without entering password:

Stop both Server 1 and Server 2

ON SERVER 1 (PRIMARY)

Edit /etc/postgresql/9.1/main/postgresql.conf :

Edit /etc/postgresql/9.1/main/pg_hba.conf to allow hot standby server to read WAL logs:

Restart primary server

Make a base backup by copying the primary server’s data directory to the standby server.

ON SERVER 2 (SECONDARY)

Edit /etc/postgresql/9.1/main/postgresql.conf

Create a recovery.conf under the standby server’s data directory.

Restart standby server. It will then start streaming replication.

Verify

Run these commands on primary and standby respectively:

They should give the same results.

There should be one “sender” and one “receiver” process running on primary and standby server, respectively:

Failover

Now, consider the pgpool server.

Before starting, please make sure that the user running pgpool is able to execute this command successfully without entering the password manually:

Now, configure pgpool to handle failover (by creating the trigger file automatilly)

Edit /etc/pgpool2/pgpool.conf :

In configuration above, two backend servers were defined for pgpool, and pgpool is configured in master/slave mode.

When a server failure is detected, it will try to execute the failover_command .

pgpool uses syslog for logging, so you would need to add one more line to your syslog configuration file ( /etc/rsyslog.conf under Ubuntu):

the failover_stream.sh script looks like:

Pgpooladmin (optional)

There is a web tool called pgpoolAdmin that can assist with the configure files and monitor the backend servers.

Download the source from https://www.pgpool.net/mediawiki/index.php/Downloads#pgpoolAdmin_.28…

Unzip it into your apache folder and then follow the installation guide.

Please make sure that you have php5-pgsql installed.

For more information, please refer to https://pgpool.projects.pgfoundry.org/pgpoolAdmin/doc/index_en.html

Read the next part in the series here: