Forums

I have a web app with a postgres database, or to be precise, three databases within one server. I have recently added an always-on task that periodically checks my database for jobs, and when a job is there, handles it. This always-on task crashes with the error:

FATAL: remaining connection slots are reserved for non-replication superuser connections

I have monitored my connections. When starting my web app fresh, it's around 6-7, when doing a few interactions it goes up to 18 and more. Most of them are idle. I am not quite sure if there is any problem on my side that is causing leaking, of if this is usual behavior. I am using sqlalchemy for my connections.

When the always-on task starts, the connections jump up to 22 and it gets an error.

This is the main loop of my always on task:

if __name__ == "__main__":
    while True:
        with app.app_context():
            session = db.session
            try:
                job_id = find_pending_job(session)
                if job_id:
                    process_job(session, job_id)
                    delete_completed_jobs(session)
                else:
                    time.sleep(5)  
            except Exception as e:
                print(f"Error processing job: {e}")
                session.rollback()  
            finally:
                session.close()

I found this thread that maybe describes the same problem.

I applied the same changes.

ALTER SYSTEM SET max_connections = 500;

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

If this is a reasonable approach from your perspective, I would appreciate it if you could restart my postgres server as well. If not, please let me know what I can do to fix it.

It's done for you.