Heroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”

Question

I got the above error message running Heroku Postgres Basic (as per this question) and have been trying to diagnose the problem.

One of the suggestions is to use connection pooling but it seems Rails has this built in. Another suggestion is that the app is configured improperly and opens too many connections.

My app manages all it's connections through Active Record, and I had one direct connection to the database from Navicat (or at least I thought I had).

How would I debug this?

RESOLUTION

Turns out it was an Heroku issue. From Heroku support:

We've detected an issue on the server running your Basic database. While we pinpoint this and address it, we would recommend you provision a new Basic database and migrate over with PGBackups as detailed here: https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups . That should put your database on a new server. I apologize for this disruption – we're working to fix this issue and prevent it from occurring in the future.

Answer 1

This has happened a few times on my app -- somehow there is a connection leak, then all of a sudden the database is getting 10 times as many connections as it should. If it is the case that you are getting swamped by an error like this, not traffic, try running this:

heroku pg:killall

That will terminate all connections to the database. If it is dangerous for your situation to possibly cut off queries be careful. I just have a rails app, and if it goes down, losing a couple queries is not a big deal, because the browser requests will have looooooong since timed out anyway.

Answer 2

You might be able to find why you have so many connections by inspecting view pg_stat_activity:

SELECT * FROM pg_stat_activity

Most likely, you have some stray loop that opens new connection(s) without closing it.

Answer 3

To save you the support call, here's the response I got from Heroku Support for a similar issue:

Hello,

One of the limitations of the hobby tier databases is unannounced maintenance. Many hobby databases run on a single shared server, and we will occasionally need to restart that server for hardware maintenance purposes, or migrate databases to another server for load balancing. When that happens, you'll see an error in your logs or have problems connecting. If the server is restarting, it might take 15 minutes or more for the database to come back online.

Most apps that maintain a connection pool (like ActiveRecord in Rails) can just open a new connection to the database. However, in some cases an app won't be able to reconnect. If that happens, you can heroku restart your app to bring it back online.

This is one of the reasons we recommend against running hobby databases for critical production applications. Standard and Premium databases include notifications for downtime events, and are much more performant and stable in general. You can use pg:copy to migrate to a standard or premium plan.

If this continues, you can try provisioning a new database (on a different server) with heroku addons:add, then use pg:copy to move the data. Keep in mind that hobby tier rules apply to the $9 basic plan as well as the free database.

Thanks, Bradley