时间戳

首页 > 解决方案 > how to fix "OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly"

postgresql - how to fix "OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly"

问题描述

Services

My service based on flask + postgresql + gunicorn + supervisor + nginx

When deploying by docker, after running the service, then accessing the api, sometimes it told the error message, and sometimes it workes well.

And the sqlachemy connect database add the parameters '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.

Information

Docker for Mac: version: 2.0.0.3 (31259)

macOS: version 10.14.2

Python: version 2.7.15

Recurrence method

When view port information by command

lsof -i:5432

the port 5432 is postgresql database default port，if the outputconsole was

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)

it would display the error message:

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

but if the outputconsolelog show this:

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)

the situation, the api would work well.

Becauce of Docker for mac?

Refer link https://github.com/docker/for-mac/issues/2442 , the issue can not solve my problem.

Notice a similar problem?

Refer link Python & Sqlalchemy - Connection pattern -> Disconnected from the remote server randomly

also this issue can not solve my problem.

Solution

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

标签： postgresqlmacosdockerflask

解决方案

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 代码，请参阅：重试失败的 sqlalchemy 查询

推荐阅读