node-postgres Seamless Application Failover using libpq

Is this possible using pure JavaScript pg? The psql accepts a URL like this where libpq figures out which of the IP addresses are still the read-write server:

postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=read-write

See examples.

This could change at runtime so the psql library has to keep monitoring this. Would I need to use pg-native for this instead? I'm also using Sequalize, but Sequalize appears to simply pass the URL through to pg so the best case would be to have the URL contain everything.

Aug 11 '22 01:08 jcalfee

Looks like pg-native does not work either. Unlike the example above, I need to add the username and password to each host:

const { Client, Pool } = require('pg').native
pool=new Pool({connectionString: `postgres://postgres_user:[email protected]:5432,postgres_user:[email protected]:5432/postgres`})
pool.query('select now()').then((e, res) => {console.log(res)})
Promise {
  <pending>,
  [Symbol(async_id_symbol)]: 86,
  [Symbol(trigger_async_id_symbol)]: 84,
  [Symbol(destroyed)]: { destroyed: false }
}
> (node:1561) UnhandledPromiseRejectionWarning: Error: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

I think it is URL parsing because if I send the URL through node, it tries to put most of it in the password field:

> new URL(`postgres://postgres_user:[email protected]:5432,postgres_user:[email protected]:5432/postgres`)
URL {
  href: 'postgres://postgres_user:password%40192.168.70.20%3A5432,postgres_user%[email protected]:5432/postgres',
  origin: 'null',
  protocol: 'postgres:',
  username: 'postgres_user',
  password: 'password%40192.168.70.20%3A5432,postgres_user%3Apassword',
  host: '192.168.70.30:5432',
  hostname: '192.168.70.30',
  port: '5432',
  pathname: '/postgres',
  search: '',
  searchParams: URLSearchParams {},
  hash: ''
}

But it is a valid URL to psql..

See libpg-connect postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

Aug 11 '22 01:08 jcalfee

Native only: I was able to get a query to run with a simple test, one host offline and the other online regardless of the hostaddr order, by editing pg/lib/connection-parameters.js::getLibpqConnectionString in a way that these parameters are sent as:

  params.push(`hostaddr='192.168.70.10,192.168.70.30'`)
  params.push(`port='5432,5432'`)

In getLibpqConnectionString, I had to by-pass the DNS lookup dns.lookup(this.host, function (err, address) { which looks like it would take an IP or hostname but only one (IP or hostname) and the one host is mandatory.

A first step approach I am considering is to just use the URL parameters which would greatly simplify URL parsing. If the key parameters like host and port in the main URL could be optional, then the parameters could take over. Then the URL parameters could be used to provide this info as well as anything additional supported by libpq. So, a patch to support any pass-through URL parameters might be both easier to implement as a work-around and also provide support for extra parameters like target_session_attrs:

postgres:///postgres?hostaddr=192.168.70.10,192.168.70.30&port=5432,5432&target_session_attrs=any etc &...

Along these lines, I noticed that comma separated passwords and users is not parsed by libpq (ex params.push('password=pw1,pw2')) and auth info in each hostaddr is not parsed by libpq (ex: params.push('hostaddr='un:[email protected],un:[email protected]')). It is not a requirement in my case (I can use the same user and pw), but for correctness I do wonder how would auth specific info for each host be passed down into libpq as parameters?

Aug 11 '22 13:08 jcalfee

I made some progress by-passing the URL and using a configuration object instead. In my case, I did this using Sequelize configured to use pg-native. I used multiple host IP addresses and a matching list of multiple port numbers (example above).

The following were the only problematic points:

https://github.com/brianc/node-postgres/blob/3e53d06cd891797469ebdd2f8a669183ba6224f6/packages/pg/lib/connection-parameters.js#L66

There was no other apparent reasons in the code to parse the port number as an integer. I assume this may have been simple validation? The parseInt function does not validate though, it converts a string like 5432,5433 into just 5432 (the first value). So, if you would like to support driver-level fail-over and really want to validate here, I would split on the , and ensure they are all numbers. Otherwise, simply removing parseInt and leaving it as a string will let the driver decide if this is valid or not (that is what I did in my patch).

And here: https://github.com/brianc/node-postgres/blob/3e53d06cd891797469ebdd2f8a669183ba6224f6/packages/pg/lib/connection-parameters.js#L159

If dns.lookup fails when there is a comma separated list of hosts. Instead if it could just split on the , and loop, I think that will be ideal. It is already checking to see if the host is an IP address or not (skip or lookup). And of course, a host name or host names would still resolve to their IP address as intended.

Finally, Sequelize does not support extra parameters like target_session_attrs, however, it does have a config called dialectOptions specifically for the driver, so it does not look difficult to pass parameters like this there. And I see you have have something similar using ssl here where you could use that pattern to accommodate this parameter:

https://github.com/brianc/node-postgres/blob/3e53d06cd891797469ebdd2f8a669183ba6224f6/packages/pg/lib/connection-parameters.js#L81

Reference for target_session_attrs. There maybe others like replication but I'm unfamiliar with that.

In any event, I hope you will consider this. I realize this may not be heavily requested, but please consider it does offer a more decentralized way to access a HA setup of databases without the extra complexity and cost of a Load Balancer. Even the Load Balancer may be a single point of failure. The driver part is really important. I suspect that people just have not discovered this or are just out-sourcing their database to managed cloud providers that make money on Load Balancers. I'm finding it super easy to use docker-compose and bitnami/postgresql-repmgr and like the full control over updates and upgrades, so I'm looking forward to using extra feature in pg-native. Thank you.

Aug 11 '22 16:08 jcalfee