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

If you're using python 3, it's likely because of [this issue](https://github.com/psycopg/psycopg2/issues/829) — ygesher, Apr 02 '19 at 07:58
@ygesher I am facing a similar issue in python3. I am using psycopg2==2.8.3 and SQLAlchemy==1.3.7. — MSS, Oct 01 '19 at 09:04
Can you explain what `apply_pool_defaults` does and why it solves this issue? — Rylan Schaeffer, Jun 22 '22 at 20:59

score 29 · Answer 1 · answered Mar 10 '20 at 09:39

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

Re the `RetryingQuery` class: I would strongly recommend against retrying queries blindly in the middle of a flow. It is usually better to fail explicitly, fast and hard, meaning to bubble the error up to the point where the caller can decide what's best. Additionally, such a retry pattern can be costly when it is using CPU-time in cloud environments. If using PubSub, then better to let PubSub arrange the retrying of the whole transaction without keeping a CPU allocated (and with exponential backoff). — Pieter Ennes, Nov 23 '22 at 21:14

score 16 · Answer 2 · answered May 06 '21 at 15:44

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,
        }
    )

score 7 · Answer 3 · answered May 11 '20 at 21:29

7

Building on the Solution in the answer and the info from @MaxBlax360's answer. I think the proper way to set these config values in Flask-SQLAlchemy is by setting app.config['SQLALCHEMY_ENGINE_OPTIONS']:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# pool_pre_ping should help handle DB connection drops
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {"pool_pre_ping": True}  
app.config['SQLALCHEMY_DATABASE_URI'] = \
    f'postgresql+psycopg2://{POSTGRES_USER}:{dbpass}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DBNAME}'
db = SQLAlchemy(app)

See also Flask-SQLAlchemy docs on Configuration Keys

answered May 11 '20 at 21:29

Nick

3,172
3
37
49

Can you explain what `pool_pre_ping` is and why it stops this problem? – Rylan Schaeffer Jun 23 '22 at 00:03
@RylanSchaeffer the docs explain `pool_pre_ping` here: https://docs.sqlalchemy.org/en/14/core/pooling.html#dealing-with-disconnects – Nick Jun 24 '22 at 01:07

score 1 · Answer 4 · answered Nov 05 '20 at 14:44

1

My db configuration:

app = Flask(__name__)
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {"pool_pre_ping": True}
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ['DATABASE_URL']
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Play with following options:
app.config['SQLALCHEMY_POOL_SIZE'] = 10
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 20
app.config['SQLALCHEMY_POOL_RECYCLE'] = 1800

db = SQLAlchemy(app)

answered Nov 05 '20 at 14:44

zemil

3,235
2
24
33

2

Can you explain what these different variables (pool size, max overflow, pool recycle) are and why they solve this problem? – Rylan Schaeffer Jun 23 '22 at 00:04