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 😉

[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]

vitaly-t/connection-string#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: #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 😄

[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.

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

[nyurik]

@shreeramk no, that's also a todo. In theory all this functionality could be refactored out into the node-postgres lib, or a standalone lib.

[RazgrizHsu]

Hi, all, I want to ask a question,
so how did you failover in your application before this feature was completed?
Should I create a new connection for each db node separately in the program,
Maybe use a short timeout query to confirm whether the db node is alive,
And switch destination connections within the app?

[kalemayur8]

@PavelPolyakov, @vitaly-t is there any fix done for JS driver supporting multi-host, and we can use it?

[PavelPolyakov]

@kalemayur8 I believe there is none so far

[vitaly-t]

Still nada, as of this day 😞

We need JS version of the library to start supporting multiple hosts. I have provided the solid implementation of connection-string that follows the standard, but that's as far as I could contribute.

[ghost]

@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.

the whole point is to switch between hosts when ?target_session_attrs=read-write provided.
that's not supported(yet?) by https://github.com/brianc/node-libpq

getting [Error: invalid URI query parameter: "target_session_attrs" when connecting with

postgresql://[email protected],192.168.4.4:5432/database?target_session_attrs=read-write

[K3UL]

Hi, all, I want to ask a question,
so how did you failover in your application before this feature was completed?
Should I create a new connection for each db node separately in the program,
Maybe use a short timeout query to confirm whether the db node is alive,
And switch destination connections within the app?

@RazgrizHsu Doing what libpq does under the hood is actually pretty straightforward if your goal is failover (equivalent to a read-write or primary setting).

All you need to do is loop over the different hosts, finding the first one that is available and indicating it is not in recovery. To check if a host is in recovery (if it is, it is the secondary), you can use a special query such as SHOW in_recovery;.

Note that there is a slight difference if you want to :

• Act as read-write, the query is SHOW transaction_read_only;, which returns on or off
• Act as primary, the query is SHOW in_recovery; if PG13+, for an older version it is SELECT pg_is_in_recovery(); which sends back TRUE or FALSE

[jahudka]

Hi, I've run across this because I'd like to connect to CockroachDb (which is wire-compatible with PostgreSQL) from my app using either TypeORM or MikroORM, both of which use pg internally (via Knex in the case of MikroORM). In the case of CockroachDb the target_session_attrs=read-write parameter might not be needed because unlike Postgres replication, where replicas are read-only, all nodes in a CockroachDb cluster should be equivalent.

Is there anything I can do to help add support for this feature? I'm happy to try writing a PR implementing the logic described by @K3UL, would that be acceptable?

[rerime]

Guys any news with multi host support?)

[EddieBenji]

Hello
Any news about the multi-hosts?
using pg-native instead of pg could be a good solution?

More info on this is very appreciated.

Thanks

[ShowmanStone]

hi all,

Any news about the multi-hosts?

[lz000]

we had to switch to this lib https://github.com/porsager/postgres?tab=readme-ov-file#multi-host-connections---high-availability-ha, and can confirm it works and also support pretty much all functionalities. The ability to support multiple hosts is a must to have for a enterprise level system

[Scrib3r]

we had to switch to this lib https://github.com/porsager/postgres?tab=readme-ov-file#multi-host-connections---high-availability-ha, and can confirm it works and also support pretty much all functionalities. The ability to support multiple hosts is a must to have for a enterprise level system

Does it work with any ORMs? I saw that driver a few years ago and really liked it but I was not sure it was production ready.

[bombillazo]

Hey, also looking into this, we recently started using pg but now we're going to enable read replicas and have no way to easily incorporate them using this driver.

[AndrewJackson2020]

+1 for this feature. This feature is supported in rust-postgres, psycopg, asyncpg, sqlalchemy, even ODBC postgres. I see no reason why there should not be some form of support for this functionality, extremely useful in enterprise environments to implement high availability and load balancing without any middleware or DNS to configure.