Re: [PATCH] libpq: Allow specifying multiple host names to try to connect to

On 04/19/2015 11:18 AM, Mikko Tiihonen wrote:
>
> Hi,
>
>
> I would like allow specifying multiple host names for libpq to try to
> connecting to. This is currently only supported if the host name
> resolves to multiple addresses. Having the support for it without
> complex dns setup would be much easier.
>
>
> Example:
>
> psql -h dbslave,dbmaster -p 5432 dbname
>
> psql 'postgresql://dbslave,dbmaster:5432/dbname'
>
>
> Here the idea is that without any added complexity of pgbouncer or
> similar tool I can get any libpq client to try connecting to multiple
> nodes until one answers. I have added the similar functionality to the
> jdbc driver few years ago.
>
>
> Because libpq almost supported the feature already the patch is very
> simple. I just split the given host name and do a dns lookup on each
> separately, and link the results.
>
>
> If you configure a port that does not exist you can see the libpq
> trying to connect to multiple hosts.
>
>
> psql -h 127.0.0.2,127.0.0.3, -p 5555
>
> psql: could not connect to server: Connection refused
> Is the server running on host "127.0.0.2,127.0.0.3" (127.0.0.2)
> and accepting
> TCP/IP connections on port 5555?
> could not connect to server: Connection refused
> Is the server running on host "127.0.0.2,127.0.0.3" (127.0.0.3)
> and accepting
> TCP/IP connections on port 5555?
>
> Further improvement would be to add a connection parameter to limit
> connection only to master (writable) or to slave (read only).
>
>

I like the idea of allowing multiple hosts to be specified where if it
can't connect to the server libpq will try the next host.

psql -h dns-fail-name,localhost
psql: could not translate host name "dns-fail-name,localhost" to
address: System error

If name in the list doesn't resolve it fails to try the next name. I
think it should treat this the same as connection refused.

In the error messages when it can't connect to a host you print the
entire host string not the actual host being connected to. Ie
Is the server running on host "127.0.0.2,127.0.0.3" (127.0.0.3) and
accepting

It should print just the host that had the failed connection.

We also need to decide how we want this feature to behave if libpq can
contact the postmaster but can't establish a connection (user/password
failure, the database is in recovery mode etc..) do we want to try the
next host or stop.

My thinking is that the times you would actually use this feature are

1) To connect to a group of replica systems (either read-only streaming
replicas or FDW proxies or BDR machines)
2) To connect to a cluster of pgbouncer or plproxy systems so the proxy
isn't a single point of failure
3) To connect to a set of servers master1, standby-server1,
standby-server2 where you would want it to try the next server in the list.

In all of these cases I think you would want to try the next machine in
the list if you can't actually establish a usable connection.
I also don't think the patch is enough to be helpful with case 3 since
you don't actually want a connection to a standby-server unless that
server has been promoted to the master.

Another concern I have is that the server needs to be listening on the
same port against all hosts this means that in a development environment
we can't fully test this feature using just a single server. I can't
think of anything else we have in core that couldn't be tested on a
single server (all the replication stuff works fine if you setup two
separate clusters on different ports on one server)

You update the documentation just for psql but your change effects any
libpq application if we go forward with this patch we should update the
documentation for libpq as well.

This approach seems to work with the url style of conninfo

For example
postgres://some-down-host.info,some-other-host.org:5435/test1

seems to work as expected but I don't like that syntax I would rather see
postgres://some-down-host.info:5435/test1,postgres://some-other-host.org:5435/test1

This would be a more invasive change but I think the syntax is more usable.

>
> -Mikko
>
>
>