Connection terminated unexpectedly

[JohnsiJohn]

Im trying to connecto to remote database.
I can connect using psql client.
But i get "Connection terminated unexpectedly" error while trying to run this (with same connection string as in psql clinet):

const { Pool, Client } = require('pg')
const connectionString = '...'

const pool = new Pool({
  connectionString: connectionString,
})

pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  pool.end()
})

const client = new Client({
  connectionString: connectionString,
})
client.connect()

client.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  client.end()
})

ive been trying to connect within sequelize ORM, but got same error.

[cavinsmith]

I guess you're not waiting for db to connect and making requests to early.

[JohnsiJohn]

Using native mode fixed problem for client query

const { Pool, Client } = require('pg').native

[cavinsmith]

@JohnsiJohn oh, that's interesting! Thank you for that discover, I will try to use it for sequelize

[adam-nielsen]

Although I'm not using pools, for the benefit of anyone else here from Google I had this problem when the connection sat idle for a while and was disconnected. The problem is that the exception that is thrown as a result of this unexpected disconnection cannot be caught and causes Node to terminate. I fixed the problem by adding an error handler so that the exception was never thrown in the first place:

db = new pg.Client();
db.on('error', e => {
  console.error('Database error', e);
  db = null;
});

I then just make sure the db object is valid before I use it, and reconnect to the DB first if not.

[Upperfoot]

Although I'm not using pools, for the benefit of anyone else here from Google I had this problem when the connection sat idle for a while and was disconnected. The problem is that the exception that is thrown as a result of this unexpected disconnection cannot be caught and causes Node to terminate. I fixed the problem by adding an error handler so that the exception was never thrown in the first place:

db = new pg.Client();
db.on('error', e => {
  console.error('Database error', e);
  db = null;
});

I then just make sure the db object is valid before I use it, and reconnect to the DB first if not.

I've just had a hell of a time debugging this from the NestJS TypeORM package, and came to the same conclusion, pg-native skirts around this problem entirely

[tocosastalo]

@Upperfoot and how did you solve this setup in NestJS TypeORM?

[Upperfoot]

@Upperfoot and how did you solve this setup in NestJS TypeORM?

Hey @tocosastalo I just installed the pg-native npm library which solved it, it's automatically used if detected, and to be honest it's comparatively faster than the normal pg npm library anyway

[tocosastalo]

@Upperfoot and how did you solve this setup in NestJS TypeORM?

Hey @tocosastalo I just installed the pg-native npm library which solved it, it's automatically used if detected, and to be honest it's comparatively faster than the normal pg npm library anyway

@Upperfoot Thanks for the quick reply. So I added "pg-native": "^3.0.0", to my package.json and tried following test:

• rebuild server docker container
• start server and postgres containers
• kill postgres container using docker stop
• server crashed with:

events.js:187
throw er; // Unhandled 'error' event
       ^
Error: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

    at module.exports.Client._readError (/usr/src/cvd/node_modules/pg-native/index.js:154:13)
    at module.exports.Client._read (/usr/src/cvd/node_modules/pg-native/index.js:203:17)
    at PQ.emit (events.js:210:5)
    at PQ.EventEmitter.emit (domain.js:476:20)
Emitted 'error' event on  instance at:
     at module.exports.<anonymous> (/usr/src/cvd/node_modules/pg/lib/native/client.js:112:14)
     at module.exports.emit (events.js:210:5)
     at module.exports.EventEmitter.emit (domain.js:476:20)
     at module.exports.Client._readError (/usr/src/cvd/node_modules/pg-native/index.js:155:8)
     at module.exports.Client._read (/usr/src/cvd/node_modules/pg-native/index.js:203:17)
     at PQ.emit (events.js:210:5)
    at PQ.EventEmitter.emit (domain.js:476:20)

Didn't you encounter this error?

[Upperfoot]

@Upperfoot and how did you solve this setup in NestJS TypeORM?

Hey @tocosastalo I just installed the pg-native npm library which solved it, it's automatically used if detected, and to be honest it's comparatively faster than the normal pg npm library anyway

@Upperfoot Thanks for the quick reply. So I added "pg-native": "^3.0.0", to my package.json and tried following test:

• rebuild server docker container
• start server and postgres containers
• kill postgres container using docker stop
• server crashed with:

events.js:187
throw er; // Unhandled 'error' event
       ^
Error: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

    at module.exports.Client._readError (/usr/src/cvd/node_modules/pg-native/index.js:154:13)
    at module.exports.Client._read (/usr/src/cvd/node_modules/pg-native/index.js:203:17)
    at PQ.emit (events.js:210:5)
    at PQ.EventEmitter.emit (domain.js:476:20)
Emitted 'error' event on  instance at:
     at module.exports.<anonymous> (/usr/src/cvd/node_modules/pg/lib/native/client.js:112:14)
     at module.exports.emit (events.js:210:5)
     at module.exports.EventEmitter.emit (domain.js:476:20)
     at module.exports.Client._readError (/usr/src/cvd/node_modules/pg-native/index.js:155:8)
     at module.exports.Client._read (/usr/src/cvd/node_modules/pg-native/index.js:203:17)
     at PQ.emit (events.js:210:5)
    at PQ.EventEmitter.emit (domain.js:476:20)

Didn't you encounter this error?

No, I didn't get that error, looks like the connection attempt was blocked, can you check connectivity via https://www.postgresql.org/docs/9.3/app-pg-isready.html or something similar to narrow the issue down a bit more?

[tocosastalo]

@Upperfoot
The connection to the DB seems to be OK, but the server cannot automatically recover from this error and must be restarted. After the restart, it will reconnect to the DB without problem.

[efreethy]

node pg Client and Pool instances extend EventEmitter

node event emitter docs state:

If an EventEmitter does not have at least one listener registered for the 'error' event, and an 'error' event is emitted, the error is thrown, a stack trace is printed, and the Node.js process exits.

So if you don't want your node process to exit - register an error handler. eg: client.on('error', handleError)

[gunar]

Try using Pool instead of Client (solved my problem).

[PierBover]

Try using Pool instead of Client (solved my problem).

I've been having this error when using Pool too. I'm handling the error event on the pool and yet Node says it's not handled and the process crashes.

node:events:498
      throw er; // Unhandled 'error' event
      ^

Error: Connection terminated unexpectedly
    at Connection.<anonymous> (.../node_modules/pg/lib/client.js:132:73)
    at Object.onceWrapper (node:events:639:28)
    at Connection.emit (node:events:520:28)
    at Socket.<anonymous> (.../node_modules/pg/lib/connection.js:107:12)
    at Socket.emit (node:events:532:35)
    at endReadableNT (node:internal/streams/readable:1346:12)
    at processTicksAndRejections (node:internal/process/task_queues:83:21)
Emitted 'error' event on Client instance at:
    at Client._handleErrorEvent (.../node_modules/pg/lib/client.js:319:10)
    at Connection.<anonymous> (.../node_modules/pg/lib/client.js:149:16)
    at Object.onceWrapper (node:events:639:28)
    [... lines matching original stack trace ...]
    at processTicksAndRejections (node:internal/process/task_queues:83:21)

This is a dumb Fastify project I made to reproduce the error:

https://github.com/PierBover/fly-postgres-node-test/blob/main/index.js

When using Client the connection drops and the error is correctly caught by client.on('error', ...). I could reconnect but unfortunately there would be some downtime and this situation should be handled correctly by the pool.

[charmander]

@PierBover pool.connect() acquires a client from the pool. It doesn’t serve to initialize the pool, and that example leaks the resulting client. (If you do acquire a client intentionally, you currently need to attach an error listener to it in addition to the pool, and detach the listener before releasing the client back to the pool. This isn’t very convenient, but it is the correct use of the API as it exists now.)

[PierBover]

@charmander but the docs say:

If you don't need a transaction or you just need to run a single query, the pool has a convenience method to run a query on any available client in the pool. This is the preferred way to query with node-postgres if you can as it removes the risk of leaking a client.

const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [1])

https://node-postgres.com/features/pooling

If this is wrong, why is it in the docs?

I will try what you suggest and report back.

BTW I also tried using the undocumented keepAlive setting and it didn't help.

Edit:

Oh I think I get it now. I can still use the pool to query but I don't need to use pool.connect().

Edit 2:

You were 100% right @charmander .

Now I just need to figure out how to have a persistent client with reconnect for listen/notify.

[boromisp]

For listen/notify connections I use this pattern:

function randomBetween(min, max) {
  return Math.floor(Math.random() * (max - min + 1) + min);
}

function *backoff() {
  let sleep = initialDelay;
  do {
    yield sleep;
    sleep = Math.min(maxDelay, randomBetween(initialDelay, sleep * 2));
  } while (true)
}



async function reconnect() {
  if (client) {
    cleanupSomehow(client);
  }

  for (const nextDelay of backoff()) {
    try {
      client = await connectSomehow();
    } catch {
      await new Promise(res => setTimeout(res, nextDelay));
    }
  }

  client.on('error', reconnect);

  client.connection.stream.setTimeout(heartbeatDealy);

  client.connection.stream.on('timeout', async function ()  {
    const timeoutId = setTimeout(reconnect, heartbeatTimeout);
    try {
      await client.query('SELECT 1');
    } catch (err) {
      reconnect();
    } finally {
      clearTimeout(timeoutId);
    }
  });

  setupListenNotify(client);
}

This executes a heartbeat query, after a set amount of inactivity on the connection. If the query times out or fails for whatever reason, I create and setup a new connection.

You could use the pool.connect() to get a client for this purpose, or just create a new Client outside of the pool.