-->

PG::Error (FATAL: too many connections for role "xxxxxxx")

By: Varghese Chacko 1 year, 6 months ago

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 worker_processesset to 3 like this:

worker_processes 3

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 Procfile was

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

Wish to hire us?

We take the vision which comes from dreams and apply the magic of science and mathematics, adding the heritage of our profession and our knowledge to create a design.