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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.