Skip to content

Reconnecting Async Database Pools After Failover

When the database fails over — a managed service promotes a replica, a container restarts, a network path flaps — every connection in your pool dies at once. The common fear is that the pool stays broken until the service is restarted. Tested by restarting PostgreSQL 17 under a probe running every 50 ms, that is not what happens with current drivers: asyncpg's pool and SQLAlchemy's async engine both served queries again 0.84–0.85 s after the server was back, and a burst of concurrent queries afterwards hit no stale connections. What does reach your callers is the outage itself — each probe saw 13–15 errors of five different exception types in the two seconds the database was away. With a bounded retry around the query, four clients making 658 queries through the restart saw 0 errors, at the cost of a worst-case latency of 1.7 s. This guide covers what the drivers already do, what to retry, and how to make failover invisible to users where possible.

Prerequisites

1. Know what the pool does by itself

Both pools detect dead connections and replace them, with no code from you:

pool = await asyncpg.create_pool(dsn, min_size=4, max_size=4)
# A connection that fails is closed and discarded; acquire() opens a new one.

engine = create_async_engine(url, pool_size=4)
# On a disconnect error, SQLAlchemy invalidates the whole pool:
# every connection checked out later is a fresh one.

Measured through a docker restart of PostgreSQL that took 1.0 s, plus the server's own startup: asyncpg, SQLAlchemy, and SQLAlchemy with pool_pre_ping=True all returned to serving queries 0.84–0.85 s after the restart command finished, and four concurrent queries per pool a few seconds later all succeeded. SQLAlchemy's disconnect handling invalidates all pooled connections the first time it sees one fail, so the idle connections that died with the server are never handed out. You do not need a reconnect loop or a restart.

Verify: restart your database in a staging environment under light load; the service recovers without being restarted itself.

A database restart as seen by a pooled client 3 lanes over time. A database restart as seen by a pooled client database up restarting up no retry ok 13-15 errors ok, pool rebuilt with retry ok waiting, up to 1.7 s ok, 0 errors time → The pool recovers on its own; the retry decides whether callers notice the gap.

2. Classify which errors are retryable

During the restart the probes saw ConnectionRefusedError, CannotConnectNowError (the server was starting up), ConnectionError, SQLAlchemy's InterfaceError and DBAPIError wrapping the driver errors — five types for one event. A retry needs to recognise all of them, and nothing else:

import asyncpg
from sqlalchemy.exc import DBAPIError

RETRYABLE_ASYNCPG = (
    OSError,                                    # refused, reset, unreachable
    asyncpg.CannotConnectNowError,              # server starting or shutting down
    asyncpg.ConnectionDoesNotExistError,        # connection died mid-query
    asyncpg.InterfaceError,
    asyncpg.exceptions.AdminShutdownError,
)


def is_retryable(exc: BaseException) -> bool:
    if isinstance(exc, DBAPIError):
        return exc.connection_invalidated or isinstance(exc.orig, RETRYABLE_ASYNCPG)
    return isinstance(exc, RETRYABLE_ASYNCPG)

SQLAlchemy sets connection_invalidated on errors it classified as disconnects, which is the most reliable signal there. Never retry constraint violations, syntax errors or serialization failures with this logic — the last of those has its own retry rule (retry the whole transaction), and the others will fail identically every time.

Verify: unit-test the classifier with each exception type you observed in a failover test.

3. Retry idempotent work with a bounded budget

For reads and idempotent writes, wrap the whole operation — acquire, query, release — in a retry with exponential backoff and an overall time budget:

import random
import time


async def with_retry(fn, budget: float = 10.0):
    deadline = time.monotonic() + budget
    delay = 0.1
    while True:
        try:
            return await fn()
        except Exception as exc:
            if not is_retryable(exc) or time.monotonic() + delay > deadline:
                raise
            await asyncio.sleep(delay * random.uniform(0.5, 1.0))   # jittered
            delay = min(delay * 2, 1.0)


user = await with_retry(lambda: pool.fetchrow("select * from users where id = $1", uid))

Measured with four concurrent clients across the restart: 658 queries, 0 errors, worst latency 1.71 s. Jitter keeps a fleet of clients from reconnecting in lockstep and hammering the database the instant it returns. Keep the budget below the caller's own timeout — retrying for 10 s inside a request with a 5 s deadline helps nobody, as discussed in setting per-attempt and total timeouts for retries.

Verify: run a load test through a database restart; error counts at the API stay at zero for idempotent endpoints, and latency rises only for the duration of the outage.

Errors reaching callers during a PostgreSQL restart 4 horizontal bars comparing asyncpg pool, no retry with the others. Errors reaching callers during a PostgreSQL restart asyncpg pool, no retry 14 errors SQLAlchemy, no retry 15 errors SQLAlchemy + pre_ping 15 errors asyncpg + bounded retry 0 errors, worst 1.7 s One probe every 50 ms per client; restart took about 1 s plus server startup. pre_ping does not help during the outage itself; only a retry hides it.

4. Do not blindly retry non-idempotent transactions

A failover in the middle of a write leaves an unknown outcome: the commit may or may not have happened before the connection died. Retrying a non-idempotent write can apply it twice:

async def charge(pool, order_id: str, amount: int, idem_key: str) -> None:
    async def attempt():
        async with pool.acquire() as conn, conn.transaction():
            await conn.execute(
                "insert into charges(idem_key, order_id, amount) values ($1, $2, $3) "
                "on conflict (idem_key) do nothing",
                idem_key, order_id, amount,
            )
    await with_retry(attempt)          # safe: a second attempt is a no-op

Make writes idempotent with a unique key, as above, before wrapping them in a retry. Where that is not possible, surface the error rather than retrying, and let the caller reconcile — the general rules are in making background jobs idempotent.

Verify: kill the database connection between the insert and the commit in a test; the retried operation produces exactly one row.

5. Follow the new primary and expose health

Managed databases fail over by moving a DNS name to the new primary. asyncpg and psycopg resolve the host on every new connection, so new connections follow the DNS change — provided old connections to the demoted server are closed. Cap connection lifetime so nothing lingers, and for multi-host setups ask for a writable server explicitly:

pool = await asyncpg.create_pool(
    "postgresql://app@db1:5432,db2:5432/app",          # try hosts in order
    target_session_attrs="read-write",                  # only connect to the primary
    max_inactive_connection_lifetime=60,
)

engine = create_async_engine(url, pool_recycle=1800, pool_pre_ping=True)

pool_pre_ping adds a round trip per checkout but catches connections that died quietly — a firewall dropping idle connections, for example — which a restart test does not exercise. Expose a readiness check that runs select 1 through the pool, so the orchestrator stops routing traffic to an instance whose database is unreachable instead of letting every request time out; see implementing health and readiness probes for asyncio.

Verify: fail over a staging database; within the DNS TTL plus one connection lifetime, all connections point at the new primary (check inet_server_addr()).

How should this operation behave during failover? A decision on What kind of operation is it with 3 outcomes. How should this operation behave during failover? What kind of operation is it? read or idempotent write bounded retry invisible to users non-idempotent write add idempotency key then retry multi-statement transaction retry the whole transaction never resume Pools reconnect; correctness depends on what you retry.

Verification

Failover handling works when:

  • The service recovers on its own after a database restart, without a redeploy.
  • Retryable errors are classified from the exceptions actually observed.
  • Idempotent operations retry within a budget shorter than the caller's timeout.
  • Non-idempotent writes carry idempotency keys or are not retried.

Diagnostic Hook: count database errors by exception type and by whether a retry eventually succeeded. A spike of retried-then-succeeded with no user-facing errors is a failover handled well; user-facing errors during a failover mean the budget is too short or the classifier missed a type. Errors that continue long after the database is healthy point at connections stuck to an old host — check connection lifetimes and DNS.

Pitfalls & edge cases

  • Writing a custom reconnect loop. The pools already replace dead connections.
  • Retrying everything. Constraint and syntax errors fail identically on every attempt.
  • Retrying non-idempotent writes. The first attempt may have committed.
  • Unbounded retries. They outlive the request and pile up load on the recovering database.

Frequently Asked Questions

Does an asyncpg pool reconnect after the database restarts?

Yes. Dead connections are discarded and new ones opened on acquire. In testing, queries succeeded again 0.84 s after a restarted PostgreSQL was back, with no application restart.

Does SQLAlchemy pool_pre_ping prevent errors during failover?

Not during the outage itself: in testing, errors were the same with and without it. It helps with connections that died silently while idle, such as those dropped by a firewall.

Which database errors should be retried?

Connection errors: refused or reset connections, CannotConnectNowError while the server starts, connections that no longer exist, and SQLAlchemy errors marked connection_invalidated. Not constraint violations or syntax errors.

Is it safe to retry a write after a connection error?

Only if the write is idempotent, for example guarded by a unique idempotency key, because the original commit may have succeeded before the connection dropped.