1

I want to understand when this error occurs and how to resolve it. I checked pg_stat_activity and pg_locks but couldnt figure out which process is exhausing the connections

We are using sqlalchemy to connect to database as below

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine_url = f'{dbms}://{username}:{password}@{hostname}:{port}/{database}'

engine = create_engine(engine_url, pool_size=20, max_overflow=10)
Session = sessionmaker(bind=engine)

db_session = Session()

This is an extension of Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections"

newbie
  • 1,282
  • 3
  • 20
  • 43
  • 1
    Probably something doesn't close connections properly, so your are running out of database connections. This condition is known as a *connection leak*. – Laurenz Albe Sep 25 '20 at 09:06
  • Exactly! I want to find the applications that's causing this connection leak. Is there a way to find out that? – newbie Sep 25 '20 at 10:50
  • You said you checked `pg_stat_activity`. That's all the information about the existing clients you get: process id, client address, user, database. You'll have to investigate the client side. – Laurenz Albe Sep 25 '20 at 10:59
  • Is there a way to get client process id which creates a lot of connections from this pg_stat_activity pid? – newbie Sep 25 '20 at 11:03
  • No. That is often on a different machine. Use `lsof` to see who holds a network connection to the database. Use `pg_terminate_backend` to kill database sessions. – Laurenz Albe Sep 25 '20 at 11:35

1 Answers1

0

I am adding the client process id in application name of sqlalchemy engine url. This will be stored under application_name in pg_stat_activity and it will be helpful to debug from where did this connection was opened.

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

client_hostname = os.environ.get('HOSTNAME', 'UNKNOWN')
client_pid = os.getpid()


engine_url = f'{dbms}://{username}:{password}@{hostname}:{port}/{database}?application_name={client_hostname}_{client_pid}'

engine = create_engine(engine_url, pool_size=20, max_overflow=10)
Session = sessionmaker(bind=engine)

db_session = Session()

This shows where the connection was originated from and the application name has a limit of 64 chars, so we can include any custom information that you want to pass on to db stats

newbie
  • 1,282
  • 3
  • 20
  • 43