I have configured two PostgreSQL servers to have hot standby by streaming.

How can I configure it so that when the master server goes down, the slave server will become the new master automatically?

  • I'm confused that I can edit someone else's post. Who can tell me why? – francs Apr 10 '14 at 9:14

Failover - automatic or otherwise - isn't provided directly by PostgreSQL.

You'll need external tools like repmgr.

The newly released repmgr 2.0 supports autofailover. However, I recommend thinking very hard about whether you should actually use it. Automated manually-triggered failover is usually a LOT safer.

If you do choose to use autofailover, you should make sure to force autofailovers as part of normal operation to make sure it's really working in your environment. Otherwise it's just a false sense of security, and when something actually breaks it'll probably bring the whole system down in a mess.

You should test any failover setup regularly, of course, but at least with manually triggered failover you're aware it's going to happen and ready to step in if there are problems.

It's not just a matter of promoting the server and rebuilding the old master as a replica, either. You've also got to deal with the need to change client connections over to the new server (using something like PgBouncer, PgPool-II, or HAProxy). You also need to reliably terminate the old server so that you don't get divergence, where both servers think they're masters and some clients are writing to each of them.

There's a fair bit of info on all this in the PostgreSQL documentation and on the wiki, so I suggest you start there.

  • Hi, which common automatic failover solutions do you use in production environment ? PgPool ? or pgbouncer+ repmgr? – francs Apr 10 '14 at 9:20
  • Usually pgbouncer + repmgr, with repmgr set up to change pgbouncer's connection settings and restart it on failover. pg_hba.conf and/or firewall is set so clients cannot connect directly to either DB server, only to pgbouncer. – Craig Ringer Apr 10 '14 at 9:24
  • Thanks for replying, If using pgbouncer + repmgr, do we need any script to manager the failover? May be should write a script to modify pgbouncer configuration file? do you have a blog about it? thanks ! – francs Apr 10 '14 at 9:30
  • I don't have anything on hand I'm afraid. I'll add it to my topic list and try to find a moment to write. – Craig Ringer Apr 10 '14 at 9:30
  • @CraigRinger can you please clarify "Automated manually-triggered failover is usually a LOT safer."? – www.tyme-it.com Jun 9 '17 at 5:56

As of PostgreSQL 9.3+ you can also use PAF. Your database start/stop/promotion would be done via a resource on a Pacemaker cluster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy