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

Question

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

Answer 1

Same logic for sqlalchemy.orm, ( on which flask_sqlalchemy is based btw )

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

More protection strategies can be setup such as it is described in the doc: https://docs.sqlalchemy.org/en/13/core/pooling.html#disconnect-handling-pessimistic

For example, here is my engine instantiation:

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)

For RetryingQuery code, cf: Retry failed sqlalchemy queries