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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
