rebuilding connections in Nodejs, pg-promise

Question

In the scenario where master/replica postgres connections are built using pg-promise, is there a way to rebuild these connections in case of replica outages?

Instead of doing process.exitCode = 1; in the error function passed with the initOptions and rebuilding only working connections on service start-up... Is there a better way to remove the failing connection (even better if its a replica and process.exitCode if its a primary)?

const initOptions = {
    // global event notification;
    error: (error, e) => {
        if (e.cn) {
            //log
        }
        process.exitCode =1;
    }
};

//singleton
const pgp = require('pg-promise')(initOptions);


// then for each database in config, we connect and start the service

Luigi

Could I do pgp.end() but then the application needs to know to stop accepting connections in the meantime – Luigi

Answer 1

Module pg-promise is built on top node-postgres, which uses the connection pool, capable of restoring broken connections automatically.

There is nothing needed on your side to that end. Once the connection is available again, your queries will start succeeding again.

And according to the logic you seek, you can do process.exit() specifically when the primary connection is lost, while ignoring (or only logging) the loss of the replica connection.

Provided that the two are used through separate Database objects, you can differentiate them with the help of the dc parameter - Database Context that you can pass in during the object's construction, which can be anything.

Example:

const dbPrimary = pgp(primaryConnection, 'primary');
const dbReplica = pgp(replicaConnection, 'replica');

Then inside the global error handler:

const initOptions = {
    error(err, e) {
        if(e.cn) {
            // connectivity issue:
            console.log(e.dc, 'connection error:', err);
            if(e.dc === 'primary') {
                process.exit(1);
            }
        }
    }
};

Keyes

Super. Thank you kindly for the explanation :) . For the instances of pg-promise talking to a misbehaving replica connection, its client requests will fail. Therefore we should either ensure another replica is queried as an immediate retry, or in fact process.exit on replica outages as well. – Luigi