postgresql - 如何修复 "OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly"

Question

服务

我的服务基于flask + postgresql + gunicorn + supervisor + nginx

docker部署的时候，运行服务后，再访问api，有时候会报错，有时候还可以。

并且sqlachemy连接数据库添加参数'sslmode:disable' .

File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    Return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    Distilled_params,
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    Util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    Reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    Cursor, statement, parameters, context
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    Cursor.execute(statement, parameters)
OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

信息

Docker for Mac:版本:2.0.0.3 (31259)

macOS:版本 10.14.2

Python:版本 2.7.15

复现法

通过命令查看端口信息时

lsof -i:5432

端口 5432 是 postgresql 数据库默认端口，如果 outputconsole 是

COMMAND    PID        USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
postgres 86469 user    4u  IPv6 0xxddd      0t0  TCP *:postgresql (LISTEN)
postgres 86469 user    5u  IPv4 0xxddr      0t0  TCP *:postgresql (LISTEN)

它会显示错误信息:

OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly

但如果 outputconsolelog 显示:

COMMAND     PID        USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
com.docke 62421 user   26u  IPv4 0xe93      0t0  TCP 192.168.2.7:6435->192.168.2.7:postgresql (ESTABLISHED)
postgres  86460 user    4u  IPv6 0xed3      0t0  TCP *:postgresql (LISTEN)
postgres  86460 user    5u  IPv4 0xe513      0t0  TCP *:postgresql (LISTEN)
postgres  86856 user   11u  IPv4 0xfe93      0t0  TCP 192.168.2.7:postgresql->192.168.2.7:6435 (ESTABLISHED)

在这种情况下，api会很好用。

因为 Docker for mac？

引用链接 https://github.com/docker/for-mac/issues/2442 ，问题不能解决我的问题。

注意到类似的问题了吗？

引用链接 Python & Sqlalchemy - Connection pattern -> Disconnected from the remote server randomly

这个问题也不能解决我的问题。

解决方案

flask_sqlachemy need the parameter pool_pre_ping

from flask_sqlalchemy import SQLAlchemy as _BaseSQLAlchemy

class SQLAlchemy(_BaseSQLAlchemy):
    def apply_pool_defaults(self, app, options):
        super(SQLAlchemy, self).apply_pool_defaults(self, app, options)
        options["pool_pre_ping"] = True

db = SQLAlchemy()

Answer 1

sqlalchemy.orm 的逻辑相同，(flask_sqlalchemy 基于 btw)

engine = sqlalchemy.create_engine(connection_string, pool_pre_ping=True)

可以设置更多的保护策略，如文档中所述: https://docs.sqlalchemy.org/en/13/core/pooling.html#disconnect-handling-pessimistic

例如，这是我的引擎实例化:

engine = sqlalchemy.create_engine(connection_string,
                                      pool_size=10,
                                      max_overflow=2,
                                      pool_recycle=300,
                                      pool_pre_ping=True,
                                      pool_use_lifo=True)

sqlalchemy.orm.sessionmaker(bind=engine, query_cls=RetryingQuery)

对于 RetryingQuery 代码，请参阅: Retry failed sqlalchemy queries