Re: Master-slave failover question

On Tue, Jan 7, 2014 at 8:16 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
>> > promoted to a slave (this I have found out how to do automatically). How
>> > will the clients know to connect to the new master? Any ideas on this
>> > one?
>>
>> One of the ideas, and BTW my favorite method, is to use PgBouncer to
>> redirect queries from master to slave with pause. The full description
>> of the process is by the link below.
>>
>> https://github.com/grayhemp/pgcookbook/blob/master/switching_to_another_server_with_pgbouncer.md
>
> I checked it out. Looks like the process involves manual identification of
> failure(?). But this is a nice method for manual switchovers.

Correct, this is about manual detection of failure. There were a lot
of discussions around automatic failover but I have never seen anyone
defined a really working (for me) criteria to detect the failure.

> As a suggestion, won't it be a good idea to run the pgbouncer on Application
> Server [so that even if the Master Server has to be shutdown, IP address
> changes or starting pgbouncer on slave is not needed]?

It depends. In some cases it is worth doing if you have a single
application server (and do not planning to have more), or the overhead
of a high number of app->db network connections per second is
significantly high in comparison to a local one. However, modern
architectures usually use multiple application servers and persistent
connections, and in this case installing pgbouncer on database servers
is preferable. I also do not recommend to install it on a separate
machine as it brings another point of failure in any case. And again,
it depends on your situation.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com