postgres Connection Timeout

[saugatadutta]

We are seeing this issue recently, where PostgreSQL has not been under much load but all DB statements are getting connection time-outs. We have set a DB_CONNECTTION_TIMEOUT set at 10 secs.

host: DB_HOST,
    port: DB_PORT,
    database: DB_NAME,
    user: DB_USER,
    password: DB_PASSWORD,
    pg: pg,
    max: 15,
    connectionTimeoutMillis: 10000,
    statementTimeout: 10000,
   keepAlive: true,
   idleTimeoutMillis: 10000

err: {
message: "timeout exceeded when trying to connect"
stack: "Error: timeout exceeded when trying to connect
at Timeout.setTimeout [as _onTimeout] (/app/node_modules/pg-pool/index.js:199:27)
at ontimeout (timers.js:436:11)
at tryOnTimeout (timers.js:300:5)
at listOnTimeout (timers.js:263:5)
at Timer.processTimers (timers.js:223:10)"
type: "Error"
}

[mcollina]

I would recommend reading https://wiki.postgresql.org/wiki/Number_Of_Database_Connections.
It seems your pg client cannot connect to PostgreSQL. You might also consider lowering the maximum amount of connections for each instance.

[saugatadutta]

The PostgreSQL database instance(8VCPU, 30GB, 200Gb SSD) supports max 600 connections, The application runs a max of 20 replications with max 15 connections each, i.e 300 max connections which is well within the limit. Do you still think it needs to be lowered. Also even though the app can ask for max 300 connections, it actually gets max 60-80 connection , even that drops to 2-3 even though there are incoming requests.

[mcollina]

This seems a production issue that would be hard to debug without direct access. We do nothing special about the connection pool in fastify-postgresql as you can see in

fastify-postgres/index.js

Line 72 in a1765a3

const pool = new pg.Pool(options)

.

[hvardhan617]

I am also facing the same issue...is there any resolution to this

[hamochigames]

Am facing the same issue. We are working on a new project using fastify and hosted it in Digital Ocean. The api is tested to be working. Occasionally, it will respond error 504 for all apis used fastify-postgres. Once this happens, the pm2 and postgres need to be restarted for it to function normally again.

Note that this is only a develop server used by two programmers who call less than 10-20 apis per day. Load is definitely not the issue.

Ubuntu 20.04
Nginx
pm2
fastify3

We also checked the postgres and pm2 status as soon as the error is spotted. Both are running still. Examining the fastify non-api route, it is running hence fastify is active and not the issue.

Everytime i have to force restart both pm2 and postgres to have it work again.

Following are the logs:

$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2021-10-30 10:02:33 UTC; 1 weeks 2 days ago
   Main PID: 57368 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 1136)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Oct 30 10:02:33 dev-hg-classroom-api-fastify3 systemd[1]: Starting PostgreSQL RDBMS...
Oct 30 10:02:33 dev-hg-classroom-api-fastify3 systemd[1]: Finished PostgreSQL RDBMS.

netstat -tulpn | grep LISTEN
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      57350/postgres      

psql -V
psql (PostgreSQL) 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)

Also don't know why this always show as inactive even when api is working...

service postgresql@12.8-main status
● postgresql@12.8-main.service - PostgreSQL Cluster 12.8-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
     Active: inactive (dead)

[hamochigames]

As an additional information, connection with Jmeter seems working flawlessly. This issue tends to occur randomly after a while when calling API from Unity Game

[voxpelli]

@hamochigames Then something is not closing connections to the Postgres server so you accumulate open connections over time.

Inspect the places where connections are opened and double check that they all properly give back and/or close the connection.