FastAPI - (psycopg2.OperationalError) server closed the connection unexpectedly

Question

I have a web app built with FastAPI and SQLAlchemy and it works OK locally with Docker but the db query fails with an error on DigitalOcean with hosted Postgres DB:

(psycopg2.OperationalError) server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.\n\n(Background on this error at: http://sqlalche.me/e/14/e3q8)"}

I had this error before while working with Flask and the problem was I had to set the engine option pool_pre_ping=True and add my cluster/droplet IP to the database 's trusted sources. But looks like with FastAPI this is not enough. What else can I do to successfully perform queries?

Background

• Python 3.9
• DigitalOcean hosted Postgres 13
• psycopg==2.8.6 but also tried 2.8.5 (which 100% worked in the similar case with Flask for me) and 2.7.4 just in case
• I have pool_pre_ping=True set
  • I checked it is really set to True right before a request using session.get_bind().pool._pre_ping and it is actually True
• I checked that my cluster nodes' IPs are in DB trusted sources
• I run the app with gunicorn using one uvicorn.workers.UvicornH11Worker worker
• I use a middleware to access my db session inside FastAPI enpoints like this:

class DBMiddleware:
    def __init__(self, app, sqlalchemy_uri):
        self.app = app
        self.sqlalchemy_uri = sqlalchemy_uri
        self.engine = None

    async def __call__(self, scope: Scope, receive: Receive, send: Send):
        if scope['type'] not in ['http', 'websocket']:
            await self.app(scope, receive, send)
            return

        if not self.engine:
            self.engine = create_engine(self.sqlalchemy_uri, pool_pre_ping=True, pool_recycle=3600)

        session = Session(autoflush=False, autocommit=False, bind=self.engine)
        scope['db'] = session
        await self.app(scope, receive, send)
        session.close()


def get_db(request: Request):
    return request.scope.get('db')

...

@app.on_event('startup')
async def startup():
    ...
    app.add_middleware(DBMiddleware, sqlalchemy_uri=config.SQLALCHEMY_DATABASE_URI)

@router.post('/endpoint')
async def endpoint(db: Session = Depends(get_db)):
    ...

• Also I tried to use the globally defined engine with session context (just to check) but still has the same behaviour so looks like middleware is not a problem
• No useful logs from the Postgres side
• I also tried to change my app query to just db.execute('SELECT 1') in case some weird timeouts or something - still the same
• I read a lot of similar issues in general about psycopg2 and very few about FastAPI that I could found e.g. this and that and official docs of course.

After all that tries the issue is still there. I'm not too aware of async Python so I can suspect the problem can be in the way how the connection is shared or something (but I use only one worker at the moment).

UPDATE

I tried to switch to asyncpg (docs: https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html). Also works locally but on DigitalOcean query hangs and I receive the following error:

[Errno 104] Connection reset by peer

Looks like the reason is the same but the error looks different for asyncpg.

Also tried to create a connection pool on DigitalOcean and connect to it - still the same error.

Answer 1

Have your tried to add any connect_args to your sqlalchemy create_engine? These arguments should allow you to maintain the connection to your database.

Here is a list of various libpq connection parameters that might be useful.

create_engine(self.sqlalchemy_uri, 
              pool_pre_ping=True, 
              pool_recycle=3600, # this line might not be needed
              connect_args={
                  "keepalives": 1,
                  "keepalives_idle": 30,
                  "keepalives_interval": 10,
                  "keepalives_count": 5,
              }
            )

It's worth noting that anyone using psycopg can use these libpq connection parameters too.

Answer 2

This may be a somewhat general answer, but these steps will help localize the problem:

1. Try to connect via SSH, so you will be sure that your requests work and the reason is not in the database.
2. Check the port numbers for connections to see if they match the uvicorn, Digital Ocean settings, and python scripts.
3. Try to temporarily allow access from any IP - maybe some weird routing won't let you