Postgresql – PGPool II – Failover when current master node dies

high-availabilitypgpoolpostgresqlpostgresql-9.5

I have a 2 node high-availability postgresql 9.5 cluster. I'm using streaming replication, PGPool II (3.5.4 [ekieboshi]), and a virtual (or floating) secondary IP all hosted on aws to accomplish this.

I've configured the PGPool Watchdog and it is running well but only seems to be executing a failover script on the failed node when the postgresql service instance dies.

I'd like to be able to execute a failover script on the slave node when the master becomes unreachable. Is there any hook for this in PGPool II? If not, is this not really even a good idea? Thanks in advance for your help.

Best Answer

Sounds like you might have incorrect values for your failover_command value. And Typically the failover script is run on pgpool server, not the database node.

failover_command = '/etc/pgpool-II/failover.sh %d %H %P /tmp/postgresql.trigger.failover [hostname of failover pgpool server]'

This configuration above is passing the new master candidate and an argument to my failover script which is executed on pgpool but ultimately promotes the master candidate (which is a hot standby) to a master using postgresql.

Related Solutions

Postgresql – Streaming replication and failover on PostgreSQL

In our shop we selected repmgr and pgbouncer instead of pgpool. repmgr has some nice tooling to setup and maintain the cluster of replicated database servers. In our case 1 master and 2 slaves (one failover and one live read performance test that can become the failover of the new master). pgpool has issues with changes in the configuration, in most cases you have to restart the service and therefor you have some downtime. This is a problem when you need 24x7x365 availability.

repmgrd (the deamon) helps to select the new master after a failover, you really don't want a split brain situation. We have one virtual ip-address for the master database, the database that is master at that moment. When another server becomes master, this is the only server using this address. Every database server also has it's own ip-address for read only queries.

repmgr is maintained by the same guys that created streaming replication in the first place, so they know what they talk about. Version 2.0 is about to be released.

Prepare for the worst situation, do some serious testing by pulling some power and network plugs! When something goes wrong, many other things already went wrong and will bite you in the back when you can't afford it.

Replication is one thing, a working failover after some serious problems, is another thing.

Postgresql – pgpool-II doesn’t execute the failover script

I had issues with getting the script to run as well. But eventually figured it out.

A few high level steps to keep in mind.

In your failover script redirect results to a log file like:

ssh -T postgres@$SLAVE1 "touch /tmp/postgresql.trigger.5432" > /tmp/failover.log 2>&1

This might help you identify why it is failing.

However, here is a high level overview of how I managed to get the failover script running:

install failover script on pgpool server
pgpool is running under root. So...
as user root run ssh-keygen -t rsa on the pgpool server and cat id_rsa.pub and >> to authorized_keys on the db servers

Doing so will allow the script to run without interactive authentication, which is what my problem was.

Best Answer

Related Solutions

Postgresql – Streaming replication and failover on PostgreSQL

Postgresql – pgpool-II doesn’t execute the failover script

Related Question