Page Comparison

...

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.

...

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

...

/var/lib/PostgreSQL/9.1/main

INSTALLATION

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

...

apt-get install postgresql-contrib
apt-get install pgpool2

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 :

...

psql -c "SELECT pg_start_backup('test-backup', true)"
rsync -ac /var/lib/postgresql/9.1/main/  postgres@10.185.20.2:/var/lib/postgresql/9.1/main --exclude postmaster.pid
psql -c "SELECT pg_stop_backup()"

ON SERVER 2 (SECONDARY)

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

...

Restart standby server. It will then start streaming replication.

VERIFY

Run these commands on primary and standby respectively:

...

[primary]$ ps -aux | grep sender
[standby]$ ps -aux | grep receiver

FAILOVER

Now, consider the pgpool server.

...

#! /bin/sh
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.

failed_node=$1
#new_master=$2
#trigger_file=$3

# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
    exit 0;
        fi 


# Create the trigger file.
/usr/bin/ssh -T postgres@10.185.20.2 /bin/touch /var/lib/postgresql/pg_trigger_test

exit 0;

PGPOOLADMIN (OPTIONAL)

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

...