Re: Proposal: Implement failover on libpq connect level.

On Wed, Aug 19, 2015 at 11:06 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> Always try with the first server specified in connection string and if that
> is not available try with second and so on. I think for the case of
> failover,
> the design shouldn't be much complicated and it is a standard thing provided
> by most of the client-side drivers in other databases. Considering what
> currently PostgreSQL offers in terms of high-availability functionality, for
> load-balancing, we need to be careful of many more things like redirecting
> read-queries to standby's, write statements should be executed via
> connection
> to master.

This can be really slow, though, if the master server is completely
offline rather than actively rejecting connections. Maybe a good idea
would be to have a new connection string parameter, failover_time. If
not specified or set to a negative value, we use only the
last-specified host=X parameter, just as now. If set to zero, we try
to connect to everything at once, as Victor proposes. If set to a
value >0, we try to connect to the servers one after another,
initiating each connection attempt after the number of milliseconds
specified by the parameter. So then you can do host=A host=B host=C
failover_time=100 and this will launch a connection attempt against A
immediately; if that hasn't completed within 100ms, then we'll also
start one targeting server B, and then after another 100ms we'll try
C. ISTM that this would cater to several different use cases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company