Support for multiple hosts

[vitaly-t]

PostgreSQL v10 was released on Oct 05, 2017, see the Release Notes.

---

One of the most interesting features that affects node-postgres is multiple host names or addresses in libpq connection strings and URIs.

This feature allows automatic support for replicas through the connection string, i.e. the first connection is tried, and if fails - the next one, and so on.

From documentation:

It is possible to specify multiple hosts to connect to, so that they are tried in the given order. In the Keyword/Value format, the host, hostaddr, and port options accept a comma-separated list of values. The same number of elements must be given in each option, such that e.g. the first hostaddr corresponds to the first host name, the second hostaddr corresponds to the second host name, and so forth. As an exception, if only one port is specified, it applies to all the hosts.

It may require some work within the driver to support it, but it will be very much worthwhile :wink:

[vitaly-t]

Renamed the title again, because the connection attempts as such are out of the scope, libpq should handle it. This library only needs to support the new syntax for the connection strings, and perhaps update some connection logic internally.

[charmander]

@vitaly-t Isn’t this something the pure-JS library should have feature parity with?

[vitaly-t]

I believe it should, yes.

[vitaly-t]

Notation that the connection string should support: https://github.com/mongodb/specifications/blob/master/source/connection-string/connection-string-spec.rst

And then the driver needs to be extended accordingly.

[saule1508]

This would be super useful, the connection string is also described here http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/ and here https://www.postgresql.org/docs/10/static/libpq-connect.html

A typical use case would be where repmgrd would take care of the automatic failover and the client would automatically reconnect to the new primary when a failover did occur

I tried this but it is not working (primary and standby are running in docker containers on local host)

const { Pool } = require('pg');
const url = 'postgresql://repmgr:[email protected]:15432,127.0.0.1:25432/repmgr?target_session_attrs=read-write';

const pool = new Pool({
  connectionString:  url
})

[vitaly-t]

Just a health check, since this was open 6 month ago... Any attempt at implementing this yet?

[vitaly-t]

Generic connection-string is now available, which supports the full multi-host notation. This library should be used in place of the current connection string parser.

[denchistyakov]

Good day, have you plans to fix this issue and move to connection-string module?

[denchistyakov]

I make PR to connection-string lib for compatible with node-postgres. After merging it in master and releasing of new version in npm, I will make PR with using it in node-postgres.

[denchistyakov]

https://github.com/vitaly-t/connection-string/pull/8

[PavelPolyakov]

Any news? I see a bunch of PR open, but can't get what blocks it currently.

[charmander]

@PavelPolyakov There’s a single PR that doesn’t contain the implementation of the feature.

[PavelPolyakov]

@charmander Thanks, that was confusing. Is there a consensus about how to approach that?

@vitaly-t , can you, please, describe the current state of your endeavour? I mean connection-string vs pg-connection-string. Does this block this issue?

If things are clearer it would be easier to help.

[vitaly-t]

@PavelPolyakov Nothing changed about connection-string, and not going to. It was and remains the correct URL parser for connection strings. And I rejected PR from @denchistyakov because it contradicts the URL specification, and the hack for spaces that's used in node-postgres is not an excuse to break the correct implementation. Instead, node-postgres should follow the correct URL Connection String specification for encoding all parts of the URL.

See also the useful Adapters.

[PavelPolyakov]

@vitaly-t Thanks for the answer, but current state is still not clear to me.

I have two questions:

1. Is this a fact, that integration of connection-string into node-postgres will solve the issue and it will be possible to use several hosts in the connection string? Are there any blockers which prevents us of doing so? Is there a consensus between the contributors, that this particular feature of supporting several hosts should be implemented by migration from pg-connection-string to connection-string?
2. I see that you are the author of connection-string and contributor/author of node-postgres, is there something else, except of time constraints which prevents you to do this integration? Don't get me wrong, I'm not trying to push you to do anything and value your time and effort as well as your responses to this thread. Just trying to understand if there is a real blocker which I don't see so far.

I also see that there is this MR open: https://github.com/brianc/node-postgres/pull/1711.

However it is said, that it lacks implementation :)

Thank you

[vitaly-t]

I can integrate connection-string, that's not a problem, and that will solve the issue with multi-tiple host details in the connection string. The real issue is how to implement support for multiple hosts in the driver itself. To this I cannot answer at the moment. And I'm guessing this will need to be somehow correlated between the native and JS implementations. And that PR does seem odd to me, incomplete, and using both old and new connection strings - that doesn't seem right.

[PavelPolyakov]

Cool, that makes it more clear.

So, basically, the real blocker is that multiple hosts support is not yet available here: https://www.npmjs.com/package/pg-native and here https://github.com/brianc/node-postgres/blob/master/lib/client.js . Is that right?

[vitaly-t]

Seems that way :smile:

[PavelPolyakov]

@vitaly-t are you sure? I've just tested if pg-native supports multiple hosts in the url and it seems that it does. Here is the reproduction: https://github.com/PP-etc/pg-native-test .

It is able to connect to the localhost, when first host is not accessible.

[charmander]

Correct, libpq already supports the feature. It’s the JS driver that doesn’t.

[PavelPolyakov]

@charmander , good, thanks for the confirmation. So you mean we need to update the code around here: https://github.com/brianc/node-postgres/blob/master/lib/client.js#L86 .

Are there any particular expectations about the logic which should be implemented?

[chaporgin]

Hi, guys! Any progress about this issue?

[nyurik]

Would it work if all my postgreSQL readonly replicas are using the same DNS name (i.e. sitting in a Kubernetes cluster and all replica IPs are dynamically added to the same hostname) - would the pool automatically do the round-robin when making a new connection, and if connection fails, will automatically pick the next IP? Or is this something that has to be implemented on a lower level?

Thanks for all the work on this feature!

[PavelPolyakov]

@nyurik I assume if you put something like HAProxy on top of your cluster and use that single host, then everything will work fine, since HAProxy will route your traffic to the healthy nodes.

[nyurik]

Thanks @PavelPolyakov , I was hoping to avoid the extra complexity (e.g. still not sure which I shuold use - HAProxy vs PgPool-II vs Pgbouncer), and also possibly solve the dynamic nature of the pool - e.g. as replicas are added and removed, I was hoping my node-js code could transparently scale the number of requests to the servers, or at least assume that the client-side pg pool or the proxy would throttle the execution based on how many replicas are available. Thanks for the quick reply!

[shreeramk]

Any update on this? Or if there is another npm library?

[nyurik]

I ended up writing multi-pool logic as part of my lib, load-balancing between multiple pools, and taking per-serve weight into account. I'm still hoping for a more generic solution. https://github.com/nyurik/tilelive-pgquery/blob/master/lib/PgQuery.js#L106

[shreeramk]

@nyurik Does the lib you mentioned handles detection of primary and secondary nodes basically write and read query redirection to primary and secondary respectively? Also, consider a failover scenario at postgres(using an automatic failover libraries so that if primary goes down then one of the secondary will promoted to primary), how does the lib you mentioned behaves in that case?

[nyurik]

@shreeramk my lib implements just one specific use case - load balancing reads from identical replicas or the master. It simply runs the SELECT query without considering if it is a primary or secondary, but it could be configured to send fewer queries to the primary (based on the server's hostname, not on its present status)

[shreeramk]

@nyurik ok. Have you implemented automatic failover in postgres?

Also, in case of failover, do you modify manually the connections in your nodejs code?