PostgreSQL could be configured to limit number of simultaneous connections to the database. The heroku comes with plans having connection limits. The 'Hopby' plans comes with 20 connections where as standard plans comes startings with 120 conection. When we start developing and testing, especially automated testings, the hobby plans raise error
PG::Error (FATAL: too many connections for role "xxxxxxx"). If we check the connections with heroku cli, we get
$ heroku pg:info --app atemon === DATABASE_URL Plan: Hobby-dev Status: Available Connections: 20/20 PG Version: 10.3 Created: 2017-01-07 15:32 UTC Data Size: 10.7 MB Tables: 36 Rows: 4325/10000 (In compliance) Fork/Follow: Unsupported Rollback: Unsupported Continuous Protection: Off Add-on: postgresql-xyz-1234
The immediate solution is to kill all connections with command :
$ heroku pg:killall --app <app name>
This is not a permanant solution. We had the same issue with this website also. We tried many solutions available in the internet, especially in stack overflow.
It is very important to know how to calculate the no of connections required. Heroku documentation says...
Assuming that you are not manually creating threads in your application code, you can use your web server settings to guide the number of connections that you need. The Unicorn web server scales out using multiple processes, if you aren’t opening any new threads in your application, each process will take up 1 connection. So in your unicorn config file if you have
Then your app will use 3 connections for workers. This means each dyno will require 3 connections. If you’re on a “Dev” plan, you can scale out to 6 dynos which will mean 18 active database connections, out of a maximum of 20. However, it is possible for a connection to get into a bad or unknown state.
Solution - Limit connections with PgBouncer
The easiest fix is to limit the connections with PG bouncer. For many frameworks, you must disable prepared statements in order to use PgBouncer. Then add the PgBouncer buildpack to your app.
$ heroku buildpacks:add https://github.com/heroku/heroku-buildpack-pgbouncer
The output will be something like
Buildpack added. Next release on <app name> will use: 1. heroku/python 2. https://github.com/heroku/heroku-buildpack-pgbouncer Run git push heroku master to create a new release using these buildpacks.
Now you must modify your
Procfile to start PgBouncer. In your
Procfile add the command
bin/start-pgbouncer-stunnel to the beginning of your
web entry. So if your
web: gunicorn <app name>.wsgi:application --worker-class gevent
Change it to:
web: bin/start-pgbouncer-stunnel gunicorn <app name>.wsgi:application --worker-class gevent
Commit the results to git, test on a staging app, and then deploy to production.
On deploy, you will see
remote: -----> pgbouncer-stunnel app detected remote: Using pgbouncer version: 1.7-heroku remote: Using stunnel version: 5.28