Django django.db.utils.OperationalError: FATAL: remaining connection slots are reserved for non-replication superuser connections

Question

  File "/usr/local/lib/python3.7/dist-packages/psycopg2/__init__.py", line 126, in connect    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
django.db.utils.OperationalError: FATAL:  remaining connection slots are reserved for non-replication superuser connections

According to the Postgres documentation, this error occurs when Django makes more connections to the Postgres DB then it's default connection limit.

• I would like to know what could be the reasons for Django to be initiating/an opening lot of connections to the DB.
• I would like to know what are the best practices we can do to prevent it from the exception.
• How to increase the default connection limit for Postgres?

#settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'xxxxx',
        'USER': 'xxxxx',
        'PASSWORD':'xxxxx',
        'HOST':'localhost',
        'PORT':'5432',
    }

Answer 1

I had a similar issue using concurrent in a Django project with a Postgresql database. I always ran out of connections (either with the same OperationalError as you or some variation).

The solution for me was using the package django-db-connection-pool. It worked out-of-the-box for me and seems to solve the absence of database pooling in django for my purpose.

Simply install the package with:

pip install django-db-connection-pool[postgresql]

Replace the backend engine in settings.py with

DATABASES = {
    'default': {
        'ENGINE': 'dj_db_conn_pool.backends.postgresql'
    }
}

You can also add some options with POOL_OPTIONS if needed (see the package documentation).

Answer 2

This is a Django PostgreSQL Solution using Threading and Middleware.

Database View You will need to create a view in you DB Format: app_viewname I called mine "configuration_terminate_idle_connections"

SELECT row_number() OVER (PARTITION BY true::boolean) AS id,
pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE 
datname = 'database_name'
    AND pid <> pg_backend_pid()
    AND state = 'idle'
    AND extract(epoch from (current_timestamp - query_start)) > 15*60;

Model

class terminate_idle_connections(models.Model):
    pg_terminate_backend = models.BooleanField(default=True)

    class Meta:
        ordering = ['id']
        managed = False
        db_table = 'appname_terminate_idle_connections'
        verbose_name_plural = "Database Terminate Idle Connections"

Admin (Run it manually through /admin)

class terminate_idle_connectionsView(admin.ModelAdmin):
    list_display = ('id', 'pg_terminate_backend')             


admin.site.register(terminate_idle_connections,terminate_idle_connectionsView)

settings.py Set to what you want

DB_MAX_CONNECTIONS = 700
DB_MAX_IDLE_CONNECTIONS = 150

Middleware (Run it when a request is submitted) I called mine "DbConnections.py" within the my_project_folder/middleware, the same location as the settings.py file Full file path: "my_project_folder/middleware/DbConnections.py"

from django.conf import settings
from appname.models import *

class DbConnections:
    
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        response = self.get_response(request)
        return response
    # PROCESSED BEFORE THE REQUESTED VIEW FUNCTION OR CLASS
    def process_view(self, request, view_func, view_args, view_kwargs):
        if settings.DEBUG: print("\nRemove idle Connections")
        dbConection= database_activity.objects.all()
        dbConectionCountAll= dbConection.count()
        dbConectionCountIdle= dbConection.filter(state="idle").count()
        if settings.DEBUG: print("    - Allowed Connections:",settings.DB_MAX_CONNECTIONS, "Actual Connections:",dbConectionCountAll)
        if settings.DEBUG: print("    - Allowed Idle Connections:",settings.DB_MAX_IDLE_CONNECTIONS, "Actual Idle Connections:",dbConectionCountIdle)
        if dbConectionCountAll >= settings.DB_MAX_CONNECTIONS or dbConectionCountIdle >= settings.DB_MAX_IDLE_CONNECTIONS:
            terminateIdelConections = terminate_idle_connections.objects.all()
            if settings.DEBUG: print("\n    - Terminated Connections:", terminateIdelConections.count(),"\n")

Settings Middleware

MIDDLEWARE = [
    'my_project_folder.Middleware.DbConnections.DbConnections',
    'django.middleware.security.SecurityMiddleware',
    ...
]

View Threading (Run it on a timer) Within you app views.py file:

import threading
from django.conf import settings
from appname.models import *

def removeIdleDbConnections():
    threading.Timer(900, removeIdleDbConnections).start()


    # REMOVE IDLE CONNECTIONS
    try:
        dbConection= database_activity.objects.all()
        dbConectionCountAll= database_activity.objects.all().count()
        dbConectionCountIdle= database_activity.objects.filter(state="idle").count()
        if dbConectionCountAll >= settings.DB_MAX_CONNECTIONS or dbConectionCountIdle >= settings.DB_MAX_IDLE_CONNECTIONS:
            terminateIdelConections = terminate_idle_connections.objects.all()
            if settings.DEBUG: print("Terminated Connections:", terminateIdelConections.count())
    except:
        pass

removeIdleDbConnections()

Answer 3

I believe that this is ocuring given Django framework don't make database pooling. Looking into Django docs (https://docs.djangoproject.com/en/3.2/ref/databases/#transaction-pooling-and-server-side-cursors) i could find a sugestion to user pgBouncer (http://www.pgbouncer.org/) to deal with that.