On 04/19/2015 11:18 AM, Mikko Tiihonen wrote:


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.


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,, -p 5555

psql: could not connect to server: Connection refused
Is the server running on host "," ( and accepting
    TCP/IP connections on port 5555?
could not connect to server: Connection refused
Is the server running on host "," ( 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 "," ( 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

seems to work as expected but I don't like that syntax I would rather see

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


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to