Обсуждение: [PATCH] libpq: Allow specifying multiple host names to try to connect to

On Sun, Apr 19, 2015 at 11:18 AM, Mikko Tiihonen
<Mikko.Tiihonen@nitorcreations.com> 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.
>
> 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.

I'm not sure if this exact idea is what we want to do, but I like the
concept, and I think a lot of users would find it handy.

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

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
>
>
>

Steve Singer <steve@ssinger.info> writes:

> 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).
>
> 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.

I agree with this; it seems to me that it's more powerful to be able to
specify complete urls for when they may differ.

For the non-url case though, I don't see a clean way of doing this.  We
could always, e.g., locally bind port specification to the closest host
specification, but that seems nasty, and is still less powerful than
passing urls (or we could just do the same for all parameters, but
that's just a mess).

Might it be reasonable to only allow the multi-host syntax in the
url-style and not otherwise?

On Wed, Jul  8, 2015 at 12:24:37PM -0400, Robbie Harwood wrote:
> > 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.
> 
> I agree with this; it seems to me that it's more powerful to be able to
> specify complete urls for when they may differ.
> 
> For the non-url case though, I don't see a clean way of doing this.  We
> could always, e.g., locally bind port specification to the closest host
> specification, but that seems nasty, and is still less powerful than
> passing urls (or we could just do the same for all parameters, but
> that's just a mess).
> 
> Might it be reasonable to only allow the multi-host syntax in the
> url-style and not otherwise?

First, I agree this is a very useful feature that we want.  Many NoSQL
databases are promoting multi-host client libraries as HA, which is kind
of humorous, and also makes sense because many NoSQL solution are
multi-host.

I can see this feature benefitting us for clients to auto-failover
without requiring a pooler or virtual IP reassignment, and also useful
for read-only connections that want to connect to a read-only slave, but
don't care which one.  The idea of randomly selecting a host from the
list might be a future feature.

I agree we should allow the specification of multiple hosts, e.g. -h
"host1,host2", but anything more complex should require the URL syntax,
and require full URLs separated by commas, not commas inside a single
URL to specify multiple host names, as shown above.  If repeating
information inside each URL is a problem, the user can still use
connections-specific options to controls things, e.g. by using -p 5433,
it is not necessary to specify the port number in the URLs:
$ psql -p 5433  postgres://localhost/test,postgres://localhost/test2

I realize this is libpq-feature-creep, but considering the complexities
of a pooler and virtual IP address reassignment, I think adding this
makes sense.  The fact that other DBs are doing it, including I think
VMWare's libpq, supports the idea of adding this simple specification.

Can someone work on a patch to implement this?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On Wed, Aug 5, 2015 at 11:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Jul  8, 2015 at 12:24:37PM -0400, Robbie Harwood wrote:
>> > 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.
>>
>> I agree with this; it seems to me that it's more powerful to be able to
>> specify complete urls for when they may differ.
>>
>> For the non-url case though, I don't see a clean way of doing this.  We
>> could always, e.g., locally bind port specification to the closest host
>> specification, but that seems nasty, and is still less powerful than
>> passing urls (or we could just do the same for all parameters, but
>> that's just a mess).
>>
>> Might it be reasonable to only allow the multi-host syntax in the
>> url-style and not otherwise?
>
> First, I agree this is a very useful feature that we want.  Many NoSQL
> databases are promoting multi-host client libraries as HA, which is kind
> of humorous, and also makes sense because many NoSQL solution are
> multi-host.
> I can see this feature benefitting us for clients to auto-failover
> without requiring a pooler or virtual IP reassignment, and also useful
> for read-only connections that want to connect to a read-only slave, but
> don't care which one.  The idea of randomly selecting a host from the
> list might be a future feature.

Yep. The JDBC driver is doing it as well.

> I realize this is libpq-feature-creep, but considering the complexities
> of a pooler and virtual IP address reassignment, I think adding this
> The fact that other DBs are doing it, including I think
> VMWare's libpq, supports the idea of adding this simple specification.

Not exactly (the change has been open-sourced). Some extra logic has
been added in pghost parsing handling so as it is possible to grab
from it an ldap search filter, and then override pghostaddr using the
result found.
-- 
Michael

On Thu, Aug 6, 2015 at 03:15 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
>On Wed, Aug 5, 2015 at 11:53 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> On Wed, Jul  8, 2015 at 12:24:37PM -0400, Robbie Harwood wrote:
>>> > 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.
>>>
>>> I agree with this; it seems to me that it's more powerful to be able to
>>> specify complete urls for when they may differ.
>>>
>>> For the non-url case though, I don't see a clean way of doing this.  We
>>> could always, e.g., locally bind port specification to the closest host
>>> specification, but that seems nasty, and is still less powerful than
>>> passing urls (or we could just do the same for all parameters, but
>>> that's just a mess).
>>>
>>> Might it be reasonable to only allow the multi-host syntax in the
>>> url-style and not otherwise?
>>
>> First, I agree this is a very useful feature that we want.  Many NoSQL
>> databases are promoting multi-host client libraries as HA, which is kind
>> of humorous, and also makes sense because many NoSQL solution are
>> multi-host.
>> I can see this feature benefitting us for clients to auto-failover
>> without requiring a pooler or virtual IP reassignment, and also useful
>> for read-only connections that want to connect to a read-only slave, but
>> don't care which one.  The idea of randomly selecting a host from the
>> list might be a future feature.
>
>Yep. The JDBC driver is doing it as well.

I added the JDBC driver support similar feature. Currently it supports the following tuning parameters given a list of
hostname/portcombinations to connect to: targetServerType=any|master|slave|preferSlave loadBalanceHosts=false|true 

For an example 2 node master,replica setup one would open write connections with host1,host2 & targetServerType=master
and read-only connections with host1,host2 & targetServerType=preferSlave.

>> I realize this is libpq-feature-creep, but considering the complexities
>> of a pooler and virtual IP address reassignment, I think adding this
>> The fact that other DBs are doing it, including I think
>> VMWare's libpq, supports the idea of adding this simple specification.

Because the feature as its simplest is a for loop in libpq. I would not think it much of a feature creep, especially
sincemy original patch to libpq showed the loop already has been hidden in libpq for a long time, it just needed a
specialdns record for the postgresql hosts that returned dns records for all hosts. 

Even there are poolers in front of postgres they can be set up in much simpler and reliable non-cluster mode when the
libpqcan be given multiple pooler addresses to connect to. 

-Mikko

On Thu, Aug 6, 2015 at 4:02 PM, Mikko Tiihonen wrote:
> Because the feature as its simplest is a for loop in libpq. I would not think it much of a feature creep, especially
sincemy original patch to libpq showed the loop already has been hidden in libpq for a long time, it just needed a
specialdns record for the postgresql hosts that returned dns records for all hosts. 
>
> Even there are poolers in front of postgres they can be set up in much simpler and reliable non-cluster mode when the
libpqcan be given multiple pooler addresses to connect to. 

Patch marked as returned with feedback, there has been review input,
but unfortunately no patch updates lately.
--
Michael