Error: remaining connection slots are reserved for non-replication superuser connections. sorry, too many clients already

michaelstokes93 · July 27, 2021, 5:16am

Hello,

We seem to be getting the following error was frequently whilst there might be three at most working on this in development at one time.

error: remaining connection slots are reserved for non-replication superuser connections. sorry, too many clients already

We’re using PostgreSQL and DigitalOcean Managed Databases.

I did explore setting up a pool but then when deploying this with Strapi, Strapi said that cross-database references are not implemented.

I’m not sure exactly what the issue is but I don’t seem to have problems like this when using a local database (I know I’m the only connection). It’s just we opted for Managed Databases because of the simplicity of managing them.

Thanks for your help,

Michael

motofacil · July 29, 2021, 3:42am

Could you solve it?

I’m having same issue with google cloud SQL.

michaelstokes93 · July 29, 2021, 6:35am

Hello,

I’ve not been able to resolve it yet.

I’m not sure as to whether it is Strapi or Digital Ocean PostgreSQL Managed Database (in my case) related.

I find it surprising though that I would get this error with only 3 developers working on this at the same time.

Further details, I have been using ssl with the following configuration in database.js:

const fs = require('fs');

const ca = fs.readFileSync(`${__dirname}/ca-certificate.crt`).toString();

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'postgres',
        host: env('DATABASE_HOST'),
        port: env('DATABASE_PORT'),
        database: env('DATABASE_NAME'),
        username: env('DATABASE_USERNAME'),
        password: env('DATABASE_PASSWORD'),
        ssl: {
          ca,
          rejectUnauthorized: false
        }
      },
      options: {
        ssl: true
      }
    },
  },
});

Will continuing looking into it.

michaelstokes93 · July 30, 2021, 7:31am

I’ve explored using connection pooling although have had the following error with both database tool and server:

cross database references are not implemented

I’ve now explored creating and hosting my own PostgreSQL database on a DigitalOcean Ubuntu droplet and the error no longer seems to occur.

So whilst I’ve found a solution or workaround, I am interested as to why this error occurs with DigitalOcean Managed PostgreSQL Database.

michaelstokes93 · August 3, 2021, 5:50pm

I hadn’t configured pooling as documented here Configurations - Strapi Developer Documentation

But I don’t expect this would resolve the error:

cross database references are not implemented

It looks like, this is something that might have to be manually done unless Strapi can handle it?

1119 · September 5, 2021, 1:50pm

I am having the same problem after deploying with Google App Engine. Server just shuts down randomly.

RenderMaster · September 6, 2021, 10:06pm

I am also having this problem with Google Cloud SQL. Will try setting up connection pooling as outlined above and let you know if that works.

Edit: It seems to have worked so far! Having to add connection pooling to the options may be caused by using a ‘low’ memory Cloud SQL server (~0.6gb) which restricts the max_connections flag to 25. It may be good to add this stipulation to the Google Cloud hosting guide. I can propose an edit to it in a few days if nobody has by then.

Thanks for everybody’s help!

Best,
RenderMaster

gkkirilov · September 17, 2021, 6:27am

@RenderMaster
Hey, can you raise an issue about the edit, as I am having the same problem with App Engine.
I opened an issue about it Server can't start "error: remaining connection slots are reserved for non-replication superuser connections" · Issue #10999 · strapi/strapi · GitHub

RenderMaster · September 17, 2021, 1:59pm

@gkkirilov Actually, even with the connection pooling set up as so: (note: options.pool)

return ( {
        defaultConnection: 'default',
        connections: {
            default: {
                connector: 'bookshelf',
                settings: {
                    client: 'postgres',
                    host: `${dbSocketPath}/${env( 'INSTANCE_CONNECTION_NAME' )}`,
                    database: env( 'DATABASE_NAME' ),
                    username: env( 'DATABASE_USERNAME' ),
                    password: env( 'DATABASE_PASSWORD' ),
                },
                options: {
                    pool: {
                        min: 0,
                        max: 7,
                    },
                },
            },
        },
    } )

the limit was still being hit. I ended up configuring the instance to use 1.7 GB of memory (and ensured that the max_connections was 50) and that seems to have solved the problem so far.

Best,
RM

murilobd · October 20, 2021, 8:58pm

This week my website went down twice because of this error (using postgres on aws - rds)
I had setup the pooling to support max connections of 50. Then I went to check the status and realize that, twice, it went over the 50. Does someone has faced this already?

michaelstokes93 · October 21, 2021, 8:45am

Hello everyone,

Thank you so much for your comments here. It means a lot.

the limit was still being hit. I ended up configuring the instance to use 1.7 GB of memory (and ensured that the max_connections was 50) and that seems to have solved the problem so far.

Currently exploring the above plan having 2GB, 1vCPU

The plan seems to have 47 backend connections available which I believe should be sufficient.

Plan Size	Available Backend Connections
2 GB RAM	47

Looks like I can then configure options to be

as 3 are reserved for maintenance.

There will be less than this number of active users.

I’m not sure how Strapi handles connections and whether it is closing them appropriately once finished?

Did anyone have to setup cross database references?

I also posted a question here on DigitalOcean Question | DigitalOcean

oneredstar · November 17, 2021, 12:47am

In case anyone face this issue on Heroku - I faced the same error when deploying in Heroku Postgresql.

Running this command on the Heroku CLI fixes this -

heroku pg:killall

braulio.balanza · February 4, 2022, 5:44pm

So I was having the same problem when deploying Strapi to Google Cloud Run, and it turns out that Cloud Run was keeping a connection alive for every successful revision (a.k.a. every time I deployed successfully to Cloud Run). Hence I had dozens of connections alive.

I know Cloud Run keeps the connection alive to balance traffic among revisions, but in this case it was swamping my connections with inactive instances. Once I removed the revisions the problem stopped.

I do not know if App Engine works the same by keeping a connection alive for every successful deployment, but it might be worth to check if the cloud service employed keeps connections of past deployments alive as in the case of Cloud Run.

Vitor_Araujo · January 31, 2023, 6:24pm

I had the same issue and this comment helped me a lot. I’m on strapi v3, on digitalocean apps platform and using DO’s managed databases running postgres v14.

I did include the pool config to my database.js file inside strapi config fodler and increased the db RAM from 1gb to 2gb. Could not connect to digital ocean’s Connection Pools though, strapi kept crashing saying that cross-database references was not configured and I couldn’t manage to make it work, so I just reverted back to connecting directly to my pg db.

Last thing I recommend ( according to knex.js docs ) that you set pool.min : 0 that means that tarn.js (what knex uses to manage pooling) will terminate all idle connections, preventing this error to happen again.

so thanks @michaelstokes93 and @RenderMaster