Strapi v4, postgres, Error "Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?"

[smaranjit]

Bug report

Describe the bug

I am getting an error message on Postgres database (version 14) but working perfectly with MySQL database

[2021-12-09 19:26:58.233] debug: ⛔️ Server wasn't able to start properly.
[2021-12-09 19:26:58.235] error: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
    at Client_PG.acquireConnection (/home/xxxx/xxx/node_modules/knex/lib/client.js:295:26)
    at runNextTicks (internal/process/task_queues.js:62:5)
    at listOnTimeout (internal/timers.js:523:9)
    at processTimers (internal/timers.js:497:7)
error Command failed with exit code 1.

when I have 34+ collections, 200+ relation types, 250+ tables in the database and when adding, removing or modifying field or relation

Steps to reproduce the behavior

1. 34+ collections, 200+ relation type, 250+ tables in database
2. modify any schema
3. See error

Update: https://github.com/smaranjit/strapi4-knex-issue-demonstration, we can use this repository to reproduce the error.

Expected behavior

the server should restart without error

System

• Node.js version: Node LTS version v14
• NPM version: npm v6.14.15
• Strapi version: v4
• Database: postgres
• Operating system: ubuntu 21.10

Additional context

working fine for less no of collections or relations.

[scoorpionx]

Did you configure the connection pool?
https://docs.strapi.io/developer-docs/latest/setup-deployment-guides/configurations/required/databases.html#database-pooling-options

[smaranjit]

Did you configure the connection pool? https://docs.strapi.io/developer-docs/latest/setup-deployment-guides/configurations/required/databases.html#database-pooling-options

yes, for postgres I have tried with different pool settings but nothing worked. but for MySQL, it's working without any pool settings.

[ihmpavel]

We had same issue on Postgres. It was because duplicated name/id. The only way to restore everything was resetting production database and importing data from scratch.

It was because incorrected database model, which was exhausting our database (but never ended). Only on production. Same settings - on localhost was working fine.

[derrickmehaffy]

Most likely not a bug but an environment issue. Recommend continuing discussion on our discord for forum.

[smaranjit]

We had same issue on Postgres. It was because duplicated name/id. The only way to restore everything was resetting production database and importing data from scratch.

It was because incorrected database model, which was exhausting our database (but never ended). Only on production. Same settings - on localhost was working fine.

for me, it is happening in my localhost, why name or id conflicting issue only happening for Postgres, but working fine with MySQL?

[smaranjit]

Most likely not a bug but an environment issue. Recommend continuing discussion on our discord for forum.

I think this is a bug, just think how frustrating the situation will be when we have to delete and recreate and refresh the whole database for any minor model changes when customizing the backend. There should be some warning or some properly described error, like which model or which id is conflicting. Instead of that, it just shows that generic error, which is very hard to debug or find out actually what is causing the problem,

[ihmpavel]

It was very frustrating. I tried everything. Only one version from backup was working, but newer version with slightly changed models (eg.: just new name) destroyed everything again.

The issue was created before and happened only after some changes... I really do not wish anyone to experience same issues...

[smaranjit]

It was very frustrating. I tried everything. Only one version from backup was working, but newer version with slightly changed models (eg.: just new name) destroyed everything again.

The issue was created before and happened only after some changes... I really do not wish anyone to experience same issues...

do we have any solution right now for Postgres, other than recreating the database? I have frustratingly tried many solutions for 5 days without any success, after that I tried MySQL and magically all is working perfectly till now with the MySQL database.

[reijerteunis]

I have the same issue, so frustrating. What to do?

[ihmpavel]

Our solution:

Pooling (to limit the number of sql queries made) did not work on server (for us). Our solution is to dump the database from production. Import it on local machine. Run strapi develop, export the dump from local machine. Upload the database dump (before the application itself) on production. After that we deploy the application to production and start it. It is working, but really frustrating. our database has a lot of tables (not data).

[Pablo-Aldana]

I'm having same issue when trying to deploy for the first time into PROD with empty DB. is this issue related just to Postgres?

[smaranjit]

I'm having same issue when trying to deploy for the first time into PROD with empty DB. is this issue related just to Postgres?

for me, it seems like this issue is occurring for Postgres only, cause with the same backend when doing the same operation with the MySQL database then there is no error. probably there is some problem with the way strapi4 communicates with the pg npm library.

[derrickmehaffy]

I'm having same issue when trying to deploy for the first time into PROD with empty DB. is this issue related just to Postgres?

for me, it seems like this issue is occurring for Postgres only, cause with the same backend when doing the same operation with the MySQL database then there is no error. probably there is some problem with the way strapi4 communicates with the pg npm library.

Every pull request we do has end to end testing on every database we support including pg and MySQL.

There is no known issue on pg in our tests not my own personal testing on v4. The issue is almost certainly environmental.

[ari-motors-team]

I think, I am facing a similar issue.
#12086

I tested it on multiple environments. Unlikely, that this is an environmental issue tbh.
Also on Render I was in contact with the support for about two weeks. They couldn't find an issue withing their service.