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

2015-08-25 Thread Michael Paquier
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 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.

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


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


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

2015-08-06 Thread Mikko Tiihonen
On Thu, Aug 6, 2015 at 03:15 AM, Michael Paquier  
wrote:
>On Wed, Aug 5, 2015 at 11:53 PM, Bruce Momjian  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

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


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

2015-08-05 Thread Michael Paquier
On Wed, Aug 5, 2015 at 11:53 PM, Bruce Momjian  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


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


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

2015-08-05 Thread Bruce Momjian
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  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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


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

2015-07-08 Thread Robbie Harwood
Steve Singer  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 
>>
>> 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 ?
>> 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 ?
>>
>> 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?


signature.asc
Description: PGP signature


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

2015-07-08 Thread Steve Singer

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 

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

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


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

2015-04-29 Thread Robert Haas
On Sun, Apr 19, 2015 at 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.
>
> 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


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