Round-robin DNS

[hendrikmoree]

We use Round-robin DNS for connecting to an postgres backend. This is just a simple AWS Network ELB hostname.

We see our connection pool always seems to use the same backend postgres DB, and it's not round robin. This made the load over multiple postgres DB really unbalanced. 3 clients connecting to 2 DB's, made 1 DB with 1 client, the other had 2. Even with pooling where you have multiple connections.

The issue seems to be in https://github.com/brianc/node-postgres/blob/68160a29bd8dfe97c74ab9a74000977da7783d6f/packages/pg/lib/connection.js#L38 because this always connect to the same ip, even if multiple are specified via DNS. There's no other way to do this, passing another socket using config.stream is not possible as well.

Should we do this completely different? Or is something else wrong?

[hendrikmoree]

I did find a solution that did work for us. Maybe it's interesting to implement as well? The way we use this library on AWS is quite a simple setup with an EC2 with an postgres DB, connection from another EC2 using an ELB. So I think more people will have problems like this.

const PG = require('pg')
const Net = require('net')
const Dns = require('dns')
const Pool = PG.Pool

class DnsFailoverSocket extends Net.Socket {
    connect(port, host) {
        super.connect({
            port, host, lookup: (hostname, options, callback) => {
                Dns.resolve(hostname, (err, records) => {
                    callback(err, records[0], 4)
                })
            }
        })
    }
}

class DnsFailoverClient extends PG.Client {
    constructor(options) {
        options.stream = new DnsFailoverSocket()
        super(options)
    }
}

new Pool({
    host: ..., port: ...,
    Client: DnsFailoverClient
})