Connection terminated due to connection timeout error

[JoshMarkF]

We are using NestJS with TypeORM v0.3.25 and pg v8.11.3

Our TypeOrm config is:

const baseOrmConfig: PostgresConnectionOptions & TypeOrmModuleOptions = {
  type: 'postgres',
  host: process.env.DATABASE_HOST ?? 'localhost',
  port: process.env.DATABASE_PORT !== undefined ? Number(process.env.DATABASE_PORT) : 5432,
  username: process.env.DATABASE_USERNAME ?? 'postgres',
  password: process.env.DATABASE_PASSWORD ?? 'password',
  synchronize: false,
  logging: ['error'],
  autoLoadEntities: true,
  ssl: process.env.DATABASE_HOST !== undefined,
  extra: {
    // setting this to 0 to disable auto-disconnect cspell: disable-next-line
    idleTimeoutMillis: 60_000,
    keepAlive: true,
    keepAliveInitialDelayMillis: 60_000,
    max: 10,
    connectionTimeoutMillis: 30_000, // number of milliseconds to wait for connection
  },
};

We host this service on a Azure Kubernetes Service cluster, and are making connections to an Azure Database for PostgreSQL Flexible Server.

Recently, we upgraded our AKS Node SKUs from Standard_B4ms to Standard_D4as_v5, and started seeing a lot of the foll. error:

Error: Connection terminated due to connection timeout 
  at Client.<anonymous> (/usr/src/app/node_modules/pg-pool/index.js:251:17) 
  at Client.patchedClientConnectCallback (/usr/src/app/node_modules/@opentelemetry/instrumentation-pg/build/src/utils.js:309:12) 
  at /usr/src/app/node_modules/@opentelemetry/context-async-hooks/build/src/AbstractAsyncHooksContextManager.js:50:55 
  at AsyncLocalStorage.run (node:internal/async_local_storage/async_hooks:91:14) 
  at SentryContextManager.with (/usr/src/app/node_modules/@opentelemetry/context-async-hooks/build/src/AsyncLocalStorageContextManager.js:33:40) 
  at SentryContextManager.with (/usr/src/app/node_modules/@sentry/opentelemetry/build/cjs/index.js:1430:24) 
  at Client.contextWrapper [as _connectionCallback] (/usr/src/app/node_modules/@opentelemetry/context-async-hooks/build/src/AbstractAsyncHooksContextManager.js:50:32) 
  at Connection.<anonymous> (/usr/src/app/node_modules/pg/lib/client.js:145:18) 
  at Object.onceWrapper (node:events:632:28) 
  at Connection.emit (node:events:518:28) {   [cause]: Error: Connection terminated unexpectedly 
  at Connection.<anonymous> (/usr/src/app/node_modules/pg/lib/client.js:132:73) 
  at Object.onceWrapper (node:events:632:28) 
  at Connection.emit (node:events:518:28) 
  at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:63:12) 
  at Socket.emit (node:events:518:28) 
  at TCP.<anonymous> (node:net:346:12) 
at TCP.callbackTrampoline (node:internal/async_hooks:130:17) } 

We reverted to using the Standard_B4ms SKU and the errors stopped. However, we do need to upgrade our SKUs. We did not have this error on our setup before this.

Any ideas what could be causing this error, and how we could fix this? The only possible explanation we could have found was that it could be related to Azure Accelerated Networking, but have no proof.

[lolistream]

Getting same error with local docker postgres database, but I'm getting this error for 1+ year. Unsure if it is related to this issue. 0 errors in postgresql log

Error: Connection terminated due to connection timeout
    at /node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async getPostgresClient (file:///src/utils/connection.ts:24:12)
... unrelated code ...
  [cause]: Error: Connection terminated unexpectedly
      at Connection.<anonymous> (/node_modules/pg/lib/client.js:136:73)
      at Object.onceWrapper (node:events:633:28)
      at Connection.emit (node:events:519:28)
      at Socket.<anonymous> (/node_modules/pg/lib/connection.js:62:12)
      at Socket.emit (node:events:519:28)
      at TCP.<anonymous> (node:net:346:12)
}

const pool = new Pool({
    host: Settings.POSTGRES_HOST,
    user: Settings.POSTGRES_USER,
    port: Settings.POSTGRES_PORT,
    password: Settings.POSTGRES_PASSWORD,
    max: 500,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 20000,
});

export async function getPostgresClient(): Promise<PoolClient> {
    return await pool.connect();
}

[GP4cK]

Interesting. @thefjhca do you have a way to replicate?

[GP4cK]

I was looking at the codebase and noticed that when a connection is established, we .setKeepAlive() if a value has been provided: https://github.com/brianc/node-postgres/blob/8d493f3b5531bfe226d40c1d64d1d020ee33fd6f/packages/pg/lib/connection.js#L45C5-L50C7

However, when the stream is upgraded with getSecureStream(), it is not done: https://github.com/brianc/node-postgres/blob/8d493f3b5531bfe226d40c1d64d1d020ee33fd6f/packages/pg/lib/connection.js#L98C7-L104C49

Could that be the issue?

[p-mcgowan]

I had this as well (nestjs, typeorm, docker). In my case, it had nothing to do with the pg lib, but with docker network (internal network was created while on vpn).

import { Client } from 'pg';

const client = new Client({
  // connectionTimeoutMillis: 3000,
  host: 'localhost',
  port: 5432,
  database: 'postgres',
  user: 'postgres',
  password: 'postgres',
});
console.log('connecting');
await client.connect();
console.log('connected');

const res = await client.query(`select * from main.migrations`);
console.log(res);
await client.end();

All I had to do was delete the network and recreate it from the compose while off vpn. Not sure if that helps anyone else but it immediately resolved my issues.

services:
  some-db:
    container_name: some-db
    image: postgres:14.17-alpine
    networks:
      - some-network
    ports:
      - "127.0.0.1:5432:5432"

networks:
  some-network: