Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

psycopg2 fails with SSL SYSCALL error: EOF detected on a PostgreSQL connection

Errorpsycopg2.OperationalError: SSL SYSCALL error: EOF detected

Short answer

The server or something between you and it closed the SSL connection while your code still held it. Pooled connections that sat idle, an idle TCP link dropped by a network device, and a pool shared with a forked worker are the three usual sources. Test connections on checkout, send TCP keepalives, and give each process its own engine.

  • Applies to psycopg2 2.9, SQLAlchemy 2.0, PostgreSQL 17, Python 3.12
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Note when it happens. After a quiet period it is an idle connection (causes 1 and 2). Right after a worker starts, or only under a forking process manager, it is cause 3. Then see which engine options you already set.

    grep -rn "create_engine\|pool_pre_ping\|pool_recycle\|keepalives" --include=*.py .
  2. 2Match what you see, then jump to its fix

    1. 1The first query after a quiet period fails, and a retry worksA pooled connection went stale while idleFix
    2. 2Long queries or long idle gaps fail on a cloud host or behind a NAT or firewall, and nothing is wrong in the server logA network device drops the idle TCP connectionFix
    3. 3The error shows up in gunicorn, Celery or multiprocessing workers, soon after they start, and the connections were opened before the forkA forked worker is using the parent's connectionFix

What does the error look like?

  • psycopg2.OperationalError: SSL SYSCALL error: EOF detected
  • SSL SYSCALL error: EOF detected

Cause 1 of 3

A pooled connection went stale while idle

1Check

Pools keep connections open between requests. If the server or a proxy closed one meanwhile, the pool hands out a dead one. Look at the engine options: with no pre-ping and no recycle, nothing checks the connection before use.

grep -rn "create_engine" --include=*.py .

2Fix

Turn on pool_pre_ping so every checkout tests the connection, and set pool_recycle below the idle limit of the server, proxy or load balancer in front of the database.

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://user:password@db.example.com/mydb",
    pool_pre_ping=True,
    pool_recycle=1800,
)

3Verify

Leave the app idle past your usual gap, then send a request. It should succeed on the first try.

python -c "from sqlalchemy import text; print(engine.connect().execute(text('select 1')).scalar())"

Confirmed by docs.sqlalchemy.org

Did this fix it?

Cause 2 of 3

A network device drops the idle TCP connection

1Check

libpq can send TCP keepalives so a quiet connection looks alive. keepalives is on by default, but the idle, interval and count values default to the operating system settings, which may be long.

grep -rn "keepalives" --include=*.py .

2Fix

Pass keepalive settings through connect_args. They go straight to the driver's connect call.

engine = create_engine(
    "postgresql+psycopg2://user:password@db.example.com/mydb",
    connect_args={
        "keepalives": 1,
        "keepalives_idle": 30,
        "keepalives_interval": 10,
        "keepalives_count": 5,
    },
)

3Verify

Run a query that sleeps longer than the gap that used to fail.

psql "postgresql://user@db.example.com/mydb?keepalives_idle=30" -c "select pg_sleep(120)"

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

A forked worker is using the parent's connection

1Check

A pooled connection must not be shared by two processes, because both would use the same socket. Check whether the engine is created at import time in the parent and used in children.

grep -rn "create_engine" --include=*.py .

2Fix

In each child, discard the inherited pool without closing the parent's sockets. SQLAlchemy documents engine.dispose(close=False) for this.

def init_worker():
    engine.dispose(close=False)

3Verify

Start the workers and send requests to each one.

for i in 1 2 3 4; do curl -s -o /dev/null -w "%{http_code}\n" http://127.0.0.1:8000/health; done

Confirmed by docs.sqlalchemy.org

Did this fix it?

How do I stop it coming back?

  • Enable pool_pre_ping on every long-lived engine.
  • Choose pool_recycle lower than the shortest idle timeout on the path to the database.
  • Create engines after the process fork, or call dispose(close=False) in the worker start-up hook.
  • Retry a read request once on OperationalError; pre-ping cannot save a connection that breaks in the middle of a transaction.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.