Re: [HACKERS] [PATCH] libpq: Allow specifying multiple host names to try to connect to
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
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
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
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
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
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
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
