server close the connection unexpected nt这可能意味着服务器在处理请求之前或

Question

我使用sqlalchemy(sqlalchemy==1.4.6)做查询，数据库是PostgreSQL 13，这是我的Python 3代码看起来像:

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

当我运行这段代码时，显示如下错误:

[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 unexpectedlyntThis probably means the server terminated abnormallyntbefore or while processing the request.n'),)

为什么会发生这种情况?我该怎么做才能解决这个问题?顺便说一句，这是我的会话范围看起来像:

@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()

这是我的PostgreSQL(部署在kubernetes集群)日志:

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

Answer 1

您的防火墙可能正在关闭Postgres连接

以下是如何在Windows上防止这种情况的发生:转到控制面板→系统和安全Windows防火墙→→高级设置→入站规则→新规则在actions选项卡下(左上角)

然后选择以下内容:

• 规则类型:端口(单击Next)
• TCP或UDP:TCP(单击Next)
• 特定本地端口:5432(单击Next)
• Action:允许连接(单击Next)
• 此规则何时适用:Domain私人公众和(所有三个(点击下一步)
• 名称:">PostgreSQL Incoming";(单击Next)

任何防火墙异常保存后立即生效。再次测试连接