Search code examples
postgresqlstreamingreplicationfailoverpgpool

Postgresql 9.2 failover

I have set up streaming replication from a master DB to a slave DB. If the master is shut down, the slave will take over. The replication and failover works fine.

I have a web app using the master database for storing data.

Some details:

  • Both servers running Centos 6.4 and Postgres 9.2.
  • Streaming replication is set up from the master to the slave using Postgres built-in replication.
  • Failover is handled by the Postgresql JDBC driver (v9.2-1003) by specifying master/slave in the connection string.

I want to continue using this method of doing replication.

The questions:

  • The slave server is read-only. How can i make this a master (writable) after the failover automatically?
  • What if the original master suddenly starts working again and we now have two masters? How can I shoot the original master in the head? Automatically.
Solution

  • I suggest having a look at pgpool with the failover_command option. There you can have a small shell script to restart the slave in read/write mode. pgpool

    In case you run into some issues with pgpool, this process which I followed to troubleshoot might help - pgpool - stracing