14

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.

tripleee
  • 175,061
  • 34
  • 275
  • 318
Max
  • 404
  • 2
  • 17
  • 39
  • What is the reason for using async here? Does the code function if not called asynchronously? Given that the async functions can in theory start in any order relative to each other, I would think this is the most likely cause here. – match Jun 12 '21 at 20:43
  • Yeah, according to fastapi docs it doesn't make sense for sync example. I tried to remove `async` but still the same. Also it works locally perfectly fine in both cases and using asyncpg there should be `async` and it also works locally as expected. Problems occure only with DO hosted db. Looks like db settings are different (but I can't get hosted db config). Also in 99% of similar issues just setting `pool_pre_ping` fixes the issue and it fixed it for me as well in the same setup for DO hosted db but when it was a Flask app instead. – Max Jun 12 '21 at 21:49

2 Answers2

11

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.

Life is complex
  • 15,374
  • 5
  • 29
  • 58
1

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
Alexandr Panchenko
  • 111
  • 1
  • 8