Re: [PATCH] libpq: Allow specifying multiple host names to try to connect to - Mailing list pgsql-hackers

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