Strapi v4 + Postgres + Render.com = Error "Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?"

System Information
  • Strapi Version: 4.1.3
  • Operating System: MacOS
  • Database: PostgreSQL 13
  • Node Version: 16
  • NPM Version:
  • Yarn Version:

I am often running in the following error. I’m hosting on www.render.com.

KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?    at Client_PG.acquireConnection (/opt/render/project/src/node_modules/knex/lib/client.js:295:26)    at async Runner.ensureConnection (/opt/render/project/src/node_modules/knex/lib/execution/runner.js:259:28)    at async Runner.run (/opt/render/project/src/node_modules/knex/lib/execution/runner.js:30:19)    at async PostgresqlSchemaInspector.getForeignKeys (/opt/render/project/src/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js:205:22)    at async /opt/render/project/src/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js:127:29    at async Promise.all (index 32)    at async PostgresqlSchemaInspector.getSchema (/opt/render/project/src/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js:123:21)    at async Object.syncSchema (/opt/render/project/src/node_modules/@strapi/database/lib/schema/index.js:51:24)    at async Strapi.bootstrap (/opt/render/project/src/node_modules/@strapi/strapi/lib/Strapi.js:380:5)    at async Strapi.load (/opt/render/project/src/node_modules/@strapi/strapi/lib/Strapi.js:408:5)    at async Strapi.start (/opt/render/project/src/node_modules/@strapi/strapi/lib/Strapi.js:161:9)

My node Strapi service is running on 2 CPU and 4GB RAM.

My Postgres database is running on 2 CPU and 4GB RAM.

I’m using the following settings in /config/env/production/database.js

const { parse } = require("pg-connection-string");

module.exports = ({ env }) => {
  const { host, port, database, user, password } = parse(env("DATABASE_URL"));

  return {
    connection: {
      client: "postgres",
      connection: {
        host,
        port,
        database,
        user,
        password,
        ssl: {
          rejectUnauthorized: false,
        },
      },
      debug: true,
      pool: {
        min: 0,
        max: 10,
        idleTimeoutMillis: 30000000,
        createTimeoutMillis: 30000000,
        acquireTimeoutMillis: 30000000,
        propagateCreateError: false,
      },
    },
  };
};

Has anyone found good settings to use on Render.com to avoid running in this error?

3 Likes

Same problem here!

1 Like

I moved to Railway.app

@gvocale I have the exact same issue…

Did you find out how to solve it?

Or do you have a workaround?
The only thing helping me right now is redoing the deploy again and again until it is (randomly?) successful…

No solution…

We switched from PostgreSQL to MariaDB out of frustration. We don’t get anymore the Knex: Timeout acquiring a connection, but we get some more descriptive errors.

One bug we figure out is sometimes columns name were too long (more then 64 characters), like described into this bug Tracking Issue - Database table, column, index, and foreign keys are too long · Issue #13117 · strapi/strapi · GitHub. So for now we manually shorten the collectionName as described here Error: ER_TOO_LONG_IDENT | Too long column names hit character restriction in database · Issue #12101 · strapi/strapi · GitHub

Another bug we’re always running into is ER_DUP_KEYNAME: Duplicate key name when running automatic migrations on production. So far we’ve been able to solve it by manually purging the strapi_content_types_schema key from the strapi_core_store_settings table as suggested here Discord

Thank you for your reply!

Sorry to hear you couldnt fix it. Did you try to keep icreasing the server specs? It feels like it gets “better” when I switch to more CPU/RAM but its not “fixed” for me, just less often.

Right now I am thinking about increasing my server specs on render to the highest plan just for deployment and then switch to a less expensive one after. But this does not feel too good :smiley:

When you have MariaDB now, you are not longer at render.com?

Right now we’re running Strapi on a Render Node Pro Plan (4GB RAM, CPU 2), and MariaDB on a Render Docker Standard Plan (2GB RAM, CPU 1).

I think when we were on Render Hosted PostgreSQL I tried go up to 8GB RAM, but would still run in the Knex issues… I found some post saying they’ve tried with higher spec’d machines, but the problem was still happening, so probably unrelated.

Also you can’t downgrade I think, at least for the database. So no chance to try a more powerful database machine, run the automatic migration, then take it to a lower spec.

We’re still with render.com with MariaDB (Dockerized). No more Knex issue, but still the deploy fails often with the ER_DUP_KEYNAME: Duplicate key error. In fact we’ve been delaying launching our strapi powered frontend few weeks just because of all these deployment issues… Haven’t figure out all these quirks yet sadly.

Did you resolve that issue? We’re deciding where to move our stack right now and this thread scares me about Render. I’ve been using it for pure UI deployments and it works like a charm.

Thanks,

I cannot recommend it. See my explanation here: Strapi Postgres Connection Pool breaks connection - #20 by N_H

Along with connection and pool, add acquireConnectionTimeout, and give this a big value. This worked for me. We 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. This configuration is not a part of pool, but, needs to be put separately. i.e.

module.exports = ({ env }) => ({
connection: {
client: ‘postgres’,
connection: {
host: env(‘DATABASE_HOST’, ‘127.0.0.1’),
port: env.int(‘DATABASE_PORT’, 5432),
database: env(‘DATABASE_NAME’, ‘strapi’),
user: env(‘DATABASE_USERNAME’, ‘strapi’),
password: env(‘DATABASE_PASSWORD’, ‘strapi’),
schema: env(‘DATABASE_SCHEMA’, ‘public’), // Not required
ssl: env(‘DATABASE_SSL’, false)
},
acquireConnectionTimeout: 1000000,
pool: {
min: 0,
max: 5,
acquireTimeoutMillis: 300000,
createTimeoutMillis: 300000,
destroyTimeoutMillis: 300000,
idleTimeoutMillis: 30000,
reapIntervalMillis:1000,
createRetryIntervalMillis: 2000
},
debug: false,
},
});