Postgresql database throwing errors on connection

Question

I am running a Postgresql database on AWS and the backend is Django. After some requests, let's say 50, it raises the error "OperationalError: terminating connection due to administrator command SSL connection has been closed unexpectedly" but the database will still remain active. At first, it was throwing "OperationalError: FATAL: remaining connection slots are reserved for non-replication superuser connections" after some requests, so I have a script that closes open connections. Here is the script:

export PGPASSWORD='mypassword'
psql --host=dbhost.myregion.rds.amazonaws.com --port=5432 --username=user --dbname=name \
     -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND usename != 'rdsadmin';"

but the errors keep coming. I have also tried increasing the max_connections to 150, but it still doesn't help. I have also tried using AWS RDS proxy, but still no hope.

Here is how I connect to the DB from django:

DATABASES = {
    'default': {
        'ENGINE': config('DB_ENGINE'),
        'NAME': config('DB_NAME'),
        'USER': config('DB_USER'),
        'PASSWORD': config('DB_PASSWORD'),
        'HOST': config('DB_HOST'),
        'PORT': config('DB_PORT'),
        'CONN_MAX_AGE': 0
    }
}

Laurenz Albe · Accepted Answer · 2022-11-20 11:24:49Z

0

The errors you see are caused by your script that closes your connections.

Rather than randomly killing sessions, fix the connection leak in your application (and don't ask us how to do that – we cannot debug your program).

answered Nov 20, 2022 at 11:24

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Starbody Over a year ago

Thank you for responding. Do you have any ideas on what could possibly cause a connection leak?

Laurenz Albe Over a year ago

Bad code that does not return the connections to the pool after it is done. I told you you have to debug this yourself, as we don't know your code. This is always a bug in the application.

Starbody Over a year ago

I can't think of any possible place in the code base to track such errors cause the application is a simple django application with celery and nothing much is happening. Could you suggest any tool or pattern to track this issue?

Collectives™ on Stack Overflow

Postgresql database throwing errors on connection

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related