sequelize: Postgres: TimeoutError/SequelizeConnectionError until node restart

Hi! I have issues using Sequelize with Postgres on Azure.

At the random moment (after 3, 5, 10 days of normal use) all SQL queries using PostgreSQL are starting to serve pool error TimeoutError: ResourceRequest timed out at ResourceRequest._fireTimeout (/generic-pool/lib/ResourceRequest.js:62:17) and sequelize SequelizeConnectionError: Connection terminated unexpectedly at connection.connect.err (/sequelize/lib/dialects/postgres/connection-manager.js:132:20)

I guess problem starts with SQL server connection problem (but I have no proofs) but is not handled and is freezing service until node restart. If I will not restart node - it will capture this errors forever.

My db settings:

{
    dialect: 'postgres',
    pool: {
      max: 5,
      min: 0,
      idle: 10000,
    },
    dialectOptions: {
      ssl: true,
    },
    operatorsAliases: false,
}

As a DB server I’m using PostgreSQL server provided by Azure. The only logs that are corresponding to this situation look like could not receive data from client: An existing connection was forcibly closed by the remote host. and this logs are not shown anymore after node restart

My current workaround is process.exit() when receiving error TimeoutError or SequelizeConnectionError which is not the best of course(

Dialect: postgres Dialect version: pg@7.4.1 Database version: PostgreSQL 9.6.7, compiled by Visual C++ build 1800, 64-bit (Azure Postgres server) Sequelize version: 4.37.5 Tested with latest release: 4.37.5

UPDATE:

On development server (same versions, with own 9.6 Postgres) tried stopping postgres, trying requests to running node app and then starting postgres and trying node requests again.

Result: errors while postgres is down: SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432 and immediate reconnection when postgres is up.

This means that source of problem is not connection drop itself, but something else.

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 1
Comments: 16

Most upvoted comments

@milesalex @ricardopolo @bsenyk @rasjani I was investigating phenomena with Azure support for month now.

What I see now:

This is mostly not problem of Sequelize or driver or something.
When problem happens, entire database freezes for 20-120 seconds and all existing connections and queries are working, but will respond only after downtime. So if you have 200 seconds tiemout for example - you’ll receive results in 200 seconds. Not only using Sequelize but any other client including binary.
Problem is still not solved and there are no clues of what is causing it. It can happen 10 times per day or hide for two weeks, it’s not connected to your db activity anyhow and not connected with specific requests, pool size, etc.
For Azure it was not connected with region (I used db in Singapore and US with same problems)
Local db (using ssl+password) is absolutely ok (yep, I moved to local db)
My db was only 2mb size, so size is also not a problem.

cavinsmith on Aug 16, 2018

@ricardopolo problem is combined: it exists both in DB and driver. For example if you will receive error and continue queries - error will exists forever. If you will restart app you’ll have gap of 20-25 seconds before database connection caused by database!

Investigation with Azure team shows nothing: db usage, connections, everything looks good, they haven’t found any problems, but research is still in progress.

cavinsmith on Jul 13, 2018