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

On Thu, Aug 6, 2015 at 03:15 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
>On Wed, Aug 5, 2015 at 11:53 PM, Bruce Momjian <bruce(at)momjian(dot)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/port combinations 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 since my original patch to libpq showed the loop already has been hidden in libpq for a long time, it just needed a special dns 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 libpq can be given multiple pooler addresses to connect to.

-Mikko