Crashing after database restart

[jonathanroze]

Hi,

I'm facing to a pretty annoying issue.

Here my connection code:

class Database {
    public client: Pool = new Pool();

    public async connect() {
        console.log('INIT DATABASE CONNECTION')
        this.client = new Pool({
            host: process.env.DB_HOST,
            port: Number(process.env.DB_PORT),
            user: process.env.DB_USERNAME,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_DATABASE,
            min: 0,
            max: 50,
            idleTimeoutMillis: 60000,
            connectionTimeoutMillis: 60000,
            keepAlive: true,
            keepAliveInitialDelayMillis: 60000,
        })

        this.client.on('error', (err, client) => {
            console.log('postgres connection error : ' + err)
        })


        await this.client.connect((e, pool, done) => e === undefined ? console.log('Database connected'): this.onError(e, done));
 }
   ...
} 

Sometimes, my database restart, and I've receive an error error: terminating connection due to administrator command and Error: Connection terminated unexpectedly

I read a lot about this issue on this repo or internet, and I can't know why my app is exiting.

I also added on error event to my transaction

    public async transaction(callback: (client: PoolClient) => void): Promise<any> {
        const client = await this.client.connect();

        client.on('error', () => {});
        try {
            await client.query('BEGIN')
            const res = await callback(client);
            await client.query('COMMIT')

        } catch (e) {
            await client.query('ROLLBACK')
            console.log('TRANSACTION ERROR: ', e)
             throw e
        } finally {
            await client.release()
        }


    }

Also tried to add on error event on every query I've done

Can you help me on this point ?

"pg": "^8.7.1"

Thanks

[tpuza]

Hi Jonathan,

your app crashes because of the uncaught exception in general. You can catch all uncaught exeptions in your application by following code.

process.on('uncaughtException', (err: Error) => {
  // Do some clean up
  process.exit(1);
});

But it is too late here for you, because in this place you should just clean up and exit.

You need to catch the exception earlier, at the place where it occures, and that is on the connected client / pool client.

You need to update Database class connect function last row implementation like this to catch the client's error too. Than this error would not be thrown as uncaught exception.

this.client.connect((error, client: PoolClient, done) => {
  // This is place where you the uncaught error "leaks" to your application
  client.on('error', (err: Error) => {
    // Do something on client error
  });

  if (error) {
    // Do something on connect error
    return;
  }

  console.log('Database connected');
});

[jonathanroze]

Hi @tpuza,

Thanks for your explanation,

I just tried it but my apps is crashing even by adding event handler in connect method

[2021-10-25T12:57:09.619Z] postgres connection error : error: terminating connection due to administrator command
[2021-10-25T12:57:09.619Z] postgres connection error : error: terminating connection due to administrator command
[2021-10-25T12:57:09.619Z] postgres connection error : error: terminating connection due to administrator command
[2021-10-25T12:57:09.619Z] postgres connection error : error: terminating connection due to administrator command
[2021-10-25T12:57:09.619Z] postgres connection error : error: terminating connection due to administrator command
[2021-10-25T12:57:09.619Z] postgres connection error : error: terminating connection due to administrator command
[2021-10-25T12:57:09.621Z] Error: Connection terminated unexpectedly
    at Connection.<anonymous> (/.../server/node_modules/pg/lib/client.js:132:73)
    at Object.onceWrapper (events.js:482:28)
    at Connection.emit (events.js:376:20)
    at Connection.emit (domain.js:470:12)
    at Socket.<anonymous> (/.../server/node_modules/pg/lib/connection.js:107:12)
    at Socket.emit (events.js:388:22)
    at Socket.emit (domain.js:470:12)
    at endReadableNT (internal/streams/readable.js:1336:12)
    at processTicksAndRejections (internal/process/task_queues.js:82:21)

And crash

[tpuza]

Actualy I'am not a 100% sure, but I would not mix the async and callback and would use one or other. Try also look deeper in your code, if there is not another client call without handling the error.

[tukusejssirs]

I am experiences this too, although via @databases/pg. I’d like to make @databases/pg/pg to wait for some predefined time to reconnect with the DB server and if the server won’t come back online until then, it should raise this error.

Is there a way to accomplish this please? Thanks in advance.

[abcfy2]

Same issue here. I think pg.Pool should retry connect, but in fact it did not.

[GregTurner]

I noticed this as well but only under high concurrency. I can recreate locally by killing Postgres while having a number of queries in progress. It's the same stack trace above. I suspect it's how the pool and/or clients remove/unlisten themselves when things are terminating unexpectedly but haven't found it yet.

[GregTurner]

This suggestion fixes it: https://github.com/brianc/node-postgres/issues/2439#issuecomment-757691278