CODEWITHSUNDEEP
djangopostgresqlherokudjango-rest-frameworkdjango-channels
Tomiwa
Tomiwa

Reputation: 1044

Django Postgresql Heroku : Operational Error - 'FATAL too many connections for role "usename"'

I am running a web application using Django and Django Rest Framework on Heroku with a postgresql and redis datastore. I am on the free postgresql tier which is limited to 20 connections.

This hasn't been an issue in the past, but recently I started using django channels 2.0 and the daphne server (switched my Procfile from gunicorn to daphne like this tutorial) and now I have been running into all sort of weird problems.

The most critical is that connections to the database are being left open so as the app runs, the number of connections keep increasing until it reaches 20 and gives me the following error message: Operational Error - 'FATAL too many connections for role "usename"'

Sentry Error Description

postgresql connection utilization Then I have to manually go to shell and type heroku pg:killall each time, this is obviously not a feasible solution and this is production so my users cant get access to site and get 500 errors. Would really appreciate any help.

I have tried:

Adding this to my different views in different places

from django.db import connections conections.close_all()

for con in connections: con.close()

I also tried doing SELECT * from pg_activity and saw a bunch of stuff but have no idea what to make of it:

Heroku Dataclip pg_activity

Upvotes: 4

Views: 4268

Answers (3)

Marvin Correia
Marvin Correia

Reputation: 871

There is the solution:

Nowadays heroku provide the django_heroku package that deal with default django-heroku app configuration, so when you call django_heroku.config(locals()) on the end of your settings.py the default CONN_MAX_AGE database config is set to 600 seconds, so the default of django is 0 what mean all database connections are been closed after request complete, if you don't replace the value of CONN_MAX_AGE after calling django_heroku.config(locals()) the value of this field is default to 600 what mean the DB connections still alive for 600 seconds causing this trouble.

Put this line on the end of your settings.py, its mandatory to be after heroku config:

django_heroku.config(locals())
DATABASES['default']['CONN_MAX_AGE'] = 0

Upvotes: 3

Leonid Ivanov
Leonid Ivanov

Reputation: 381

We figured out whats the problem. I assume that you are using dj_database_url like in heroku manual. All you have to do is to drop conn_max_age.

db_from_env = dj_database_url.config()

Upvotes: 6

Tomiwa
Tomiwa

Reputation: 1044

I think I may have solved it.

One of the changes I made was modifying how I closed my connections.

The key is to close old connections before and after various view functions.

from django.db import close_old_connections

@csrf_exempt
@api_view(['GET', ])
def search(request):
    close_old_connections()
    # do stuff
    close_old_connections()

Upvotes: 0

Related Questions