[PATCH] libpq: Allow specifying multiple host names to try to connect to
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).
-Mikko
Attachments:
libpq_multiple_hosts.patchtext/x-patch; name=libpq_multiple_hosts.patchDownload
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 62a3b21..e79f96c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -232,6 +232,12 @@ EOF
with a slash, it is used as the directory for the Unix-domain
socket.
</para>
+ <para>
+ It is possible to provide multiple host names to connect to. The first host name that accepts
+ the connection will be used. To explicitly specify multiple host names they must be separated
+ by comma.
+ An alternative is to use dns record for the host name that resolves to multiple addresses.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/libpq/ip.c b/src/backend/libpq/ip.c
index db939b5..90d3c1e 100644
--- a/src/backend/libpq/ip.c
+++ b/src/backend/libpq/ip.c
@@ -56,6 +56,9 @@ static int getnameinfo_unix(const struct sockaddr_un * sa, int salen,
int flags);
#endif
+/* Marker for multiple dns resolver results linked together */
+#define AI_PG_LINKED (1<<30)
+
/*
* pg_getaddrinfo_all - get address info for Unix, IPv4 and IPv6 sockets
@@ -65,6 +68,9 @@ pg_getaddrinfo_all(const char *hostname, const char *servname,
const struct addrinfo * hintp, struct addrinfo ** result)
{
int rc;
+ char *hostnames;
+ char *host_start;
+ struct addrinfo *addr;
/* not all versions of getaddrinfo() zero *result on failure */
*result = NULL;
@@ -73,10 +79,46 @@ pg_getaddrinfo_all(const char *hostname, const char *servname,
if (hintp->ai_family == AF_UNIX)
return getaddrinfo_unix(servname, hintp, result);
#endif
-
/* NULL has special meaning to getaddrinfo(). */
- rc = getaddrinfo((!hostname || hostname[0] == '\0') ? NULL : hostname,
- servname, hintp, result);
+ if (!hostname || hostname[0] == '\0')
+ return getaddrinfo(NULL, servname, hintp, result);
+
+ hostnames = strdup(hostname);
+ if (!hostnames)
+ return EAI_MEMORY;
+
+ host_start = hostnames;
+
+ /* dns lookups in reverse order to make result links in correct order */
+ do
+ {
+ host_start = strrchr(hostnames, ',');
+ if (host_start)
+ {
+ *host_start = '\0';
+ host_start++;
+ }
+ else
+ host_start = hostnames;
+
+ addr = NULL;
+ rc = getaddrinfo(host_start, servname, hintp, &addr);
+ if (rc || !addr)
+ {
+ if (addr)
+ freeaddrinfo(addr);
+ break;
+ }
+ if (*result)
+ {
+ (*result)->ai_flags |= AI_PG_LINKED;
+ addr->ai_next = *result;
+ }
+ *result = addr;
+
+ } while (host_start > hostnames);
+
+ free(hostnames);
return rc;
}
@@ -94,6 +136,9 @@ pg_getaddrinfo_all(const char *hostname, const char *servname,
void
pg_freeaddrinfo_all(int hint_ai_family, struct addrinfo * ai)
{
+ struct addrinfo * last_addrinfo;
+ struct addrinfo * ai_ptr;
+
#ifdef HAVE_UNIX_SOCKETS
if (hint_ai_family == AF_UNIX)
{
@@ -109,10 +154,24 @@ pg_freeaddrinfo_all(int hint_ai_family, struct addrinfo * ai)
}
else
#endif /* HAVE_UNIX_SOCKETS */
+ /* struct was built by getaddrinfo() */
+ if (ai != NULL)
{
- /* struct was built by getaddrinfo() */
- if (ai != NULL)
- freeaddrinfo(ai);
+ while (true)
+ {
+ /* unlink extra addrinfo structures before freeing */
+ last_addrinfo = NULL;
+ for (ai_ptr = ai; ai_ptr->ai_next; ai_ptr = ai_ptr->ai_next)
+ {
+ if (ai_ptr->ai_next->ai_flags & AI_PG_LINKED)
+ last_addrinfo = ai_ptr;
+ }
+ if (!last_addrinfo)
+ break;
+ freeaddrinfo(last_addrinfo->ai_next);
+ last_addrinfo->ai_next = NULL;
+ }
+ freeaddrinfo(ai);
}
}
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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/test1seems 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/test1This 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/test1seems 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/test1This 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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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/test1seems 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/test1This 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
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/test1seems 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/test1This 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
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