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

Question

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.

• Craig Ringer: Have a look at repmgr; even if you don't want to use it, the approaches used there should be informative.
• Sudheer Prem: Craig Ringer, Thanks for the reference, I will have a look at it.
• Sudheer Prem: Update: I use rgmanager as the resource manager in RHCS. I found a resource script for hearbeat and used that as a reference and wrote a script for rgmanager myself. Testing is still under progress. Also, Now I use repmgr as the cluster manager for postgres 9.2. Thanks Craig Ringer for the pointer. I appreciate it.

Answer 1

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.