Heroku "psql: FATAL: the remaining connection slots are reserved for connections with unrecoverable superusers"

Question

I got the above error message that starts Heroku Postgres Basic ( in accordance with this question ) and tries to diagnose the problem.

One suggestion is to use a connection pool, but it seems that Rails has a built-in function . Another suggestion is that the application is not configured correctly and opens up too many connections.

My application manages all its connections with Active Record, and I had one direct connection to the database from Navicat (or at least I thought I had).

How do I debug this?

Decision

It turns out this is a Heroku problem. From Heroku Support:

We detected a problem on the server with the Basic database. Although we define this precisely and consider it, we recommend that you provide a new base database and migrate it using PGBackups as more details here: https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups , This should put your database on a new server. I apologize for this failure - we are working to resolve this problem and prevent it from happening in the future.

Answer 1

You might find why you have so many connections by checking the pg_stat_activity view :

SELECT * FROM pg_stat_activity 

Most likely, you have a parasitic cycle that opens new connections without closing it.

Answer 2

This happened several times in my application - somehow a connection leak occurs, and suddenly the database receives 10 times more connections than necessary. If this is the case that you get a swamp with such an error, and not traffic, try running this:

 heroku pg:killall 

This will terminate all database connections. If this is dangerous for your situation, to possibly turn off requests, be careful. I just have a rails app, and if it goes down, losing a few requests doesn't really matter, because browser requests will have looooooong since then, anyway.

Answer 3

To save your support call, here is the answer I received from Heroku support for a similar problem:

Hello,

One of the limitations of hobby-level databases is immediate maintenance. Many hobby databases work on one common server, and sometimes we need to restart this server for equipment maintenance purposes or transfer the databases to another server for load balancing. When this happens, you will see an error message in your logs or connection problems. If the server restarts, it may take 15 minutes or more for the database to return to the network.

Most applications that support connection pooling (such as ActiveRecord in Rails) can simply open a new database connection. However, in some cases, the application will not be able to reconnect. If this happens, you can re-launch the application to bring it back.

This is one of the reasons we recommend using hobby databases for mission-critical manufacturing applications. Standard and premium databases include downtime event notifications and are much more efficient and stable overall. You can use pg: copy to switch to a standard or premium plan.

If this continues, you can try creating a new database (on another server) using the hero addons: add, and then use pg: copy to move the data. Keep in mind that hobby level rules apply to the basic plan for $ 9, as well as to a free database.

Thanks Bradley