Re: Patch: Implement failover on libpq connect level.

On Wed, Oct 14, 2015 at 12:41 PM, Victor Wagner <vitus(at)wagner(dot)pp(dot)ru> wrote:

> On 2015.08.18 at 07:18:50 +0300, Victor Wagner wrote:
>
> > Rationale
> > =========
> >
> > Since introduction of the WAL-based replication into the PostgreSQL, it
> is
> > possible to create high-availability and load-balancing clusters.
> >
> > However, there is no support for failover in the client libraries. So,
> only
> > way to provide transparent for client application failover is IP address
> > migration. This approach has some limitation, i.e. it requires that
> > master and backup servers reside in the same subnet or may not be
> > feasible for other reasons.
> >
> > Commercial RDBMS, such as Oracle, employ more flexible approach. They
> > allow to specify multiple servers in the connect string, so if primary
> > server is not available, client library tries to connect to other ones.
> >
> > This approach allows to use geographically distributed failover clusters
> > and also is a cheap way to implement load-balancing (which is not
> > possible with IP address migration).
> >
>
> Attached patch which implements client library failover and
> loadbalancing as was described in the proposal
> <20150818041850(dot)GA5092(at)wagner(dot)pp(dot)ru>.
>
> This patch implements following fuctionality:
>
> 1. It is allowed to specify several hosts in the connect string, either
> in URL-style (separated by comma) or in param=value form (several host
> parameters).
>

So what exactly would happen with this command: PGHOST=host1 psql -h host2

Or this one: PGHOST=host1 psql host=host2

What about service files?

2. Each host parameter can be accompanied with port specifier separated
> by colon. Port, specified such way takes precedence of port parameter or
> default port for this particular host only.
>
> 3. There is hostorder parameter with two possible valies 'sequential'
> and 'random' (default sequential). 'parallel' hostorder described in the
> proposal is not yet implemented in this version of patch.
>
> 4. In the URL-style connect string parameter loadBalanceHosts=true is
> considered equal to 'hostorder=random' for compatibility with jdbc.
>
> 5. Added new parameter readonly=boolean. If this parameter is false (the
> default) upon successful connection check is performed that backend is
> not in the recovery state. If so, connection is not considered usable
> and next host is tried.
>
> 6. Added new parameter falover_timeout. If no usable host is found and
> this parameter is specified, hosts are retried cyclically until this
> timeout expires. It gives some time for claster management software to
> promote one of standbys to master if master fails. By default there is
> no retries.
>
> Some implementation notes:
>
> 1. Field pghost in the PGconn structure now stores comma separated list
> of hosts, which is parsed in the connectDBStart. So, expected results of
> some tests in src/interfaces/libpq/test are changed.
>

trying postgresql://[::1]:12345/db
-dbname='db' host='::1' port='12345' (inet)
+dbname='db' host='[::1]:12345' (inet)

Such a change doesn't look really nice to me.

> 2. Url with colon but no port number after the host no more considered
> valid.
>

We could live with that, but is there a good reason for backward
compatibility break in this case?

--
Alex