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

Answer 2

I'm posting my own answer to this, since none of the above addressed my particular setup (Postgres 12.2, SQLAlchemy 1.3).

To stop the OperationalErrors, I had to pass in some additional connect_args to create_engine:

create_engine(         connection_string,         pool_pre_ping=True,         connect_args={             "keepalives": 1,             "keepalives_idle": 30,             "keepalives_interval": 10,             "keepalives_count": 5,         }     )