Heroku - error KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Questions and Answers
#1

Hi guys, I have a problem on Herku: :thinking:

2021-02-03 14_01_39-Cmder
2021-02-03 14_01_39-Cmder1404×449 129 KB

What’s the problem? - Does it have to do with the fact that my Strapi on Heroku only has the Hobby Dyno Type? :roll_eyes:
Thanks in advance!

#2

There have been a few times this question has come up:

Also from knex itself:

And Stack overflow:

Short answer is you may need to tweak your pool settings which can be found here: https://strapi.io/documentation/developer-docs/latest/concepts/configurations.html#database

#3

If you have a moderately sized project, generally yes, because the hobby DB type has very very small resource limits (below what even PG themselves recommend)

#4

@DMehaffy Thank you for the fast answer!
By the way, I have the database Heroku Postgres (Hobby Basic).

Should I rather upgrade my Strap dyno Type or my database, or both? :beer:
Many thanks!

#5

@DMehaffy I just upgraded my Dyno Type to “Standard 1x”, but nothing happened, the problem is still there. Now it looks like I have to upgrade my Postgres database to “Standard 0”.

#6

@DMehaffy My strapi is now set to Dyno Standard 1x and Postgres Database Standard-0. Unfortunately, the problem has not yet been resolved and must be something else. :thinking:

Do you have another idea?

#7

@mingxi as I linked above, you will probably need to tune your Knex pool (tarn) settings. All of those various issues I linked should have some additional context.

I can’t give you a copy paste answer for the tuning as it varies project to project.

#8

For anyone else that comes across this error while attempting to deploy to heroku, here is how I fixed it.

I added the line ssl: { rejectUnauthorized: false } to the database connection settings.

It looks like the official strapi documentation now includes this, but I was attempting to deploy a legacy application that did not have this config. https://strapi.io/documentation/developer-docs/latest/concepts/configurations.html#example

return {
  connections: {
    default: {
      settings: {
        client: 'postgres',
        host: env('DATABASE_HOST', '127.0.0.1'),
        port: env.int('DATABASE_PORT', 5432),
        database: env('DATABASE_NAME', 'strapi'),
        username: env('DATABASE_USERNAME', 'strapi'),
        password: env('DATABASE_PASSWORD', 'strapi'),
        schema: 'public',
        ssl: { rejectUnauthorized: false }, // this line is required
      },
    }
  }
}
#9

Along with connection and pool, add acquireConnectionTimeout, and give this a big value. This worked for me. I had to take Strapi support inputs to resolve this! In my case, I noted that the error would occur after 60 seconds. I tried multiple connection pool configurations and the issue repeated until adding acquireConnectionTimeout.