Postgresql – Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

clusteringpostgresqlpostgresql-9.2redhat

I'm in the process of configuring PostgreSQL 9.2 streaming replication with Redhat Clustering Suite for high availability.

I have 1 master and 1 hot standby configured to replicate the data from master to standby using streaming replication.

Now I will have to configure automatic fail over to standby using RedHat Clustering Suite (RHCS).
It looks like PostgreSQL 9.2 is not a supported resource type for RHCS and I will have to add a resource type manually.
But I couldn't find any resources which tells how to go about it.

Following is the approach I'm planning to follow in the status and start command in the resources script.

  1. Status command:
    Check the database cluster state is "in production"
    using the command: /usr/pgsql-9.2/bin/pg_controldata /var/lib/pgsql/9.2/data | grep "Database cluster state:" | cut -d':' -f2 | sed 's/^ *//g'
    If yes return success. If no, return failure.
  2. Start command:
    Check the database cluster state of the master and follow the steps below:

    1. If it is "in production" state , do nothing
    2. If it is "in archive recovery" state, touch the trigger_file configured in the recovery.conf file and return true.
    3. If it is "in shutdown" state, rename the recovery.conf and restart the standby.

I would like to get the expert opinion on whether the approach is correct or is there any better/well defined approach for this.

Best Answer

repmgr is a nice place to go indeed. If you use pacemaker/corosync you shoud check this as well, its a Resource Agent handling repmgr : https://github.com/xmm/repmgr. Pelase note you might need to either patch the provided Resource Agent or patch repmgr.