Django+Postgres FATAL: Sorry, Too Many Clients Already

I get the 'FATAL: sorry, too many clients already' every now and then because I have a lot of idle connecions in Postgres, and I cannot understand where they are coming from or ho

Solution 1:

This question already exists for some time, but if anyone ever gets here, this is the problem I faced.

The development server (when you run manage.py runserver) is multi-threaded by default, which means that every request was creating its own connection, and i had a server with a pooling endpoint. I don't know if this will help anyone, but remember to check this possibility, run the server passing --nothreading to the runsever command.

https://docs.djangoproject.com/en/2.1/ref/django-admin/#cmdoption-runserver-nothreading

Solution 2:

Actually you have more idle sessions than idle transaction in idle sessions: this looks like a possible connection leak on application side. PostgreSQL does not have timeout for idle sessions that don't run any transaction. A possible workaround on PostgreSQL side is to schedule a job to kill these idle sessions: see Is there a timeout for idle PostgreSQL connections?

Solution 3:

• manually close those idle pg connections with two methods, it's work for me.

# core codes
from django.db import close_old_connections
from django.db import connection
close_old_connections()
with connection.cursor() as cursor:
    sql = "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle'"
    print(sql)
    cursor.execute(sql)
    row = cursor.fetchall()
    print(row)

• I use these codes in an api decorator, and if too many client in the error message, I will call these codes.

# pip install bddjango

# use the api_decorator for views that you often use. 
from bddjango import api_decorator

class Task(APIView):

    @api_decorator
    def get(self, request):
        pass