psql: FATAL: sorry, too many clients already, HEROKU

Question

I have deployed a Rails app on Heroku since 2 years without trouble Todays the app crash.

Rails log are:

/app/vendor/bundle/ruby/2.3.0/gems/pg-0.21.0/lib/pg.rb:56:in `initialize': FATAL:  sorry, too many clients already (PG::ConnectionBad)
FATAL:  sorry, too many clients already

My rails app is v5.2.0

I use Heroku with 2 dyno The database is a postgres with "Hobby Dev". I try:

• To upgrade the database but I got the same error

  heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL --app locabri
  Creating heroku-postgresql:standard-0 on ⬢ xxxx... !
  ▸    An error was encountered when contacting the add-on partner to create heroku-postgresql:standard-0: The database you are attempting to follow was not found.
  

• to change DB_POOL in env variable

• heroku pg:info

  === DATABASE_URL
  Plan:                  Hobby-dev
  Status:                Available
  Connections:           0/20
  PG Version:            10.6
  Created:               2017-05-29 07:40 UTC
  Data Size:             138.8 MB
  Tables:                12
  Rows:                  5748/10000 (In compliance)
  Fork/Follow:           Unsupported
  Rollback:              Unsupported
  Continuous Protection: Off
  Region:                Europe
  Add-on:                postgresql-regular-79163
  

But nothing work. I can't do anything on the database because I can't connect on it and and don't know how to restart it. Thanks for your help or question

EDIT

• heroku pg:killall doesnt work

SOLUTION

I finally find the solution by changing the available dyno !

  heroku ps:scale web=0
  heroku ps:scale web=2
  heroku restart

Now I can check the "connexion leak"

Answer 1

Recently I encountered a the same error message for one of my Python apps. I've reached out to Heroku and they acknowledged the error:

FATAL: sorry, too many clients already

"It is generally triggered because the hobby tier databases are a shared resource which means that several databases will share computation and storage from a host. We monitor these regularly and always aim to run them with spare capacity but occasionally coincidental spikes in usage from neighboring databases can cause the host machine to have trouble. This is one of the reasons we don't endorse the hobby tier for use in production applications."

They suggested trying one of the following options:

1. Provision a new hobby database on the same plan and migrate the data over. This will spin up on a new database host which will mean that you no longer have the same noisy neighbors.
2. Upgrade to one of the professional plans

I've not tried the 1st option, but the second one solved my issue. This answer shows you how to do the migration step by step.