'server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request

I am using sqlalchemy(sqlalchemy==1.4.6) to do a query, the database is PostgreSQL 13, this is my Python 3 code look like:

def select_channel_by_id(self, channel_id):
    with session_scope() as local_session:
        rss = None
        try:
            rss = local_session.query(RssSource).filter(RssSource.id == channel_id).one()
        except SQLAlchemyError as e:
            local_session.rollback()
            logger.error("query rss source by id error", e)
        finally:
            local_session.close()
        return rss

when I run to this code, shows error like this:

[2021-10-25 18:42:00,040: WARNING/ForkPoolWorker-3]   File "/usr/local/lib/python3.9/site-packages/celery/app/trace.py", line 731, in __protected_call__
    return self.run(*args, **kwargs)

[2021-10-25 18:42:00,040: WARNING/ForkPoolWorker-3]   File "/root/pydolphin/dolphin/tasks/tasks.py", line 33, in pull_channel_impl
    source = rss.select_channel_by_id(channel_id)

[2021-10-25 18:42:00,040: WARNING/ForkPoolWorker-3]   File "/root/pydolphin/dolphin/common/db/rss_source.py", line 61, in select_channel_by_id
    logger.error("query rss source by id error", e)

[2021-10-25 18:42:00,040: WARNING/ForkPoolWorker-3] Message: 'query rss source by id error'
Arguments: (OperationalError('(psycopg2.OperationalError) server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.\n'),)

why would this happen? what should I do to fix this problem? BTW, this is my session scope look like:

@contextmanager
def session_scope():
    scope_session = Session()
    try:
        yield scope_session
        scope_session.commit()
    except Exception as e:
        scope_session.rollback()
        logger.error("session management error", e)
    finally:
        scope_session.close()

this is my PostgreSQL(deploy in kubernetes cluster) log look like:

2021-10-25 11:03:34.794 GMT [29779] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.562 GMT [29800] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.563 GMT [29801] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.565 GMT [29799] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.566 GMT [29796] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.568 GMT [29795] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.569 GMT [29798] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.571 GMT [29794] LOG:  could not receive data from client: Connection reset by peer
2021-10-25 11:04:07.572 GMT [29797] LOG:  could not receive data from client: Connection reset by peer
python-3.xpostgresql


Solution 1:[1]

Your firewall might be closing the Postgres connection.

Here is how you prevent such occurrence on Windows:

Go to Control Panel -> Systems and Security -> Windows Firewall -> Advanced Settings -> Inbound Rules -> New Rule under the actions tab (top left)

Then select the following:

  • Rule Type: Port (click Next)
  • TCP or UDP: TCP (click Next)
  • Specific local ports: 5432 (click Next)
  • Action: Allow the connection (click Next)
  • When does this rule apply: Domain, Private and Public (all three checked) (click Next)
  • Name: "PostgreSQL Incoming" (click Next)

Any firewall exception will take effect as soon as saved. Test the connection again.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 John Johnson

Related Questions