Handling Aurora failover and read-only transaction errors

After a failover, pooled connections stay healthy but point at a node that is now a reader, so writes fail with cannot execute INSERT in a read-only transaction — add a handle_error listener that marks SQLSTATE 25006 as a disconnect so SQLAlchemy invalidates the pool and reconnects through the cluster endpoint. This guide belongs to dialect-specific gotchas and driver quirks.

Quick Answer

pool_pre_ping tests whether a connection works, not whether it is connected to the right node. After a failover it works perfectly — against a read-only replica.

Before — the error reaches the application and the pool keeps the connection:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://shop:secret@shop.cluster-abc.eu-west-1.rds.amazonaws.com/shop",
    pool_size=10,
    pool_pre_ping=True,      # passes: the connection is alive
)

# After a failover, every write on a pooled connection:
# sqlalchemy.exc.InternalError: (asyncpg.exceptions.ReadOnlySQLTransactionError)
# cannot execute INSERT in a read-only transaction
# ... and the connection goes back into the pool, so the next request fails too.

After — the error is treated as a disconnect, so the pool recovers:

from sqlalchemy import event
from sqlalchemy.ext.asyncio import create_async_engine

# Codes that mean "this connection is no longer usable for its purpose".
DISCARD_CONNECTION = {
    "25006",   # read_only_sql_transaction — this node is now a reader
    "57P01",   # admin_shutdown — the backend was terminated
    "57P03",   # cannot_connect_now — the server is starting up
}

engine = create_async_engine(
    "postgresql+asyncpg://shop:secret@shop.cluster-abc.eu-west-1.rds.amazonaws.com/shop",
    pool_size=10,
    pool_pre_ping=True,
    pool_recycle=300,
)


@event.listens_for(engine.sync_engine, "handle_error")
def _treat_as_disconnect(context) -> None:
    original = context.original_exception
    code = getattr(original, "sqlstate", None) or getattr(original, "pgcode", None)
    if code in DISCARD_CONNECTION:
        context.is_disconnect = True

Setting is_disconnect is what makes SQLAlchemy discard the connection and invalidate the rest of the pool, so the next checkout opens a fresh connection through the cluster endpoint — which by then resolves to the new writer.

Execution Context & Async Workflow Integration

An Aurora failover promotes a replica to writer and demotes the old writer to a reader. The cluster's writer endpoint is a DNS name whose record is updated to point at the new writer, so new connections reach the right node within a DNS TTL. Existing connections are unaffected as TCP connections — they remain open, to an instance that is now read-only.

That is why pool_pre_ping does not help. Pre-ping runs a trivial statement at checkout to detect connections the server has closed; a SELECT 1 against a reader succeeds. The connection is healthy and useless, and nothing in the pool's model of health captures the difference.

SQLAlchemy does have the right machinery, in the form of disconnect handling. When the dialect judges an error to be a disconnect, the pool discards that connection and invalidates every other connection created before that moment, so the whole pool is refreshed as connections are returned. The handle_error event lets you extend that judgement, which is exactly what the listener above does: it tells SQLAlchemy that a read-only error means this connection is no longer usable for its purpose.

The recovery then takes about as long as one failover plus one DNS lookup: the first write fails, the pool is invalidated, and the next checkout connects through the endpoint to the new writer.

Three related settings shorten the window further.

pool_recycle in the low hundreds of seconds means any connection somehow missed by the invalidation is replaced soon anyway. It also covers the reverse case — a connection to a node that has become the writer again — and idle-timeout drops from load balancers, as configuring pool_pre_ping to handle stale connections describes.

The cluster writer endpoint must be the connection target. An instance endpoint names one specific node and cannot follow a failover at all: after promotion, it simply points at a reader forever.

asyncpg also supports target_session_attrs, which asks the server whether it is writable during connection set-up:

engine = create_async_engine(
    URL, connect_args={"target_session_attrs": "read-write"}, pool_pre_ping=True
)

With that, a connection attempt that lands on a reader is rejected at connect time rather than at the first write — useful when a stale DNS answer would otherwise put a fresh connection on the wrong node.

Resolving Warnings, Errors & Common Mistakes

Retrying deserves care, because a failover can interrupt a transaction whose outcome is unknown. A read is always safe to retry. A write is safe only if it is idempotent, or if the retry can determine whether the first attempt committed:

import asyncio
import logging

from sqlalchemy.exc import DBAPIError

log = logging.getLogger("db.retry")
RETRYABLE = {"25006", "57P01", "57P03", "08006", "08003"}


def _code(exc: DBAPIError) -> str | None:
    original = exc.orig
    return getattr(original, "sqlstate", None) or getattr(original, "pgcode", None)


async def with_failover_retry(operation, *, attempts: int = 3, base_delay: float = 0.5):
    """Retry an idempotent unit of work across a failover."""
    for attempt in range(1, attempts + 1):
        try:
            return await operation()
        except DBAPIError as exc:
            if _code(exc) not in RETRYABLE or attempt == attempts:
                raise
            delay = base_delay * (2 ** (attempt - 1))
            log.warning("retrying after %s (attempt %d)", _code(exc), attempt)
            await asyncio.sleep(delay)

operation must open its own session, because the session that failed is no longer usable — its transaction was rolled back and its connection discarded. Making the unit of work a callable that takes no session, and opens one itself, is what makes the retry correct rather than superficially plausible.

For non-idempotent writes, add an idempotency key the operation checks first — the pattern in handling IntegrityError on concurrent inserts — so a retry after an unknown outcome cannot double-charge or double-ship.

Advanced: Reads, Replicas and Read-Your-Own-Writes

An Aurora cluster offers a reader endpoint that load-balances across replicas, and using it is the main reason to have replicas at all. It also introduces two problems that the failover handling above does not address.

Replication lag. A replica is behind the writer by milliseconds to seconds. A request that writes and then reads its own write through the reader endpoint can legitimately not find it, which surfaces as a user creating something and being told it does not exist. The rule that avoids it is to read your own writes from the writer: route by operation, not by endpoint availability.

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

writer_engine = create_async_engine(WRITER_URL, pool_size=10)
reader_engine = create_async_engine(READER_URL, pool_size=10)

WriterSession = async_sessionmaker(writer_engine, expire_on_commit=False)
ReaderSession = async_sessionmaker(reader_engine, expire_on_commit=False)

Two engines and two factories make the choice explicit at every call site, which is clumsier than a clever router and much easier to reason about during an incident. The more automatic approaches — a session that picks a bind per operation — are covered in routing reads to replicas with async engines.

Failover affects the reader endpoint too. During a failover the promoted replica leaves the reader pool and the demoted writer joins it, so reader connections are also invalidated and re-established. The same handle_error listener should be installed on both engines — a read against a node being restarted raises 57P01 just as a write does.

Write the listener once and apply it to every engine:

from sqlalchemy import event
from sqlalchemy.ext.asyncio import AsyncEngine

DISCARD_CONNECTION = {"25006", "57P01", "57P03"}


def install_failover_handling(engine: AsyncEngine) -> None:
    @event.listens_for(engine.sync_engine, "handle_error")
    def _treat_as_disconnect(context) -> None:
        original = context.original_exception
        code = getattr(original, "sqlstate", None) or getattr(original, "pgcode", None)
        if code in DISCARD_CONNECTION:
            context.is_disconnect = True


for engine in (writer_engine, reader_engine):
    install_failover_handling(engine)

One caution about the reader endpoint and 25006: a reader connection is legitimately read-only, so a write attempted on it produces the same error for an entirely different reason — a routing bug rather than a failover. Marking it a disconnect on the reader engine is harmless but hides the bug, so it is worth logging the endpoint alongside the code, and asserting in tests that read-only sessions are never handed write operations.

Testing Failover Behaviour Before It Happens

Failover handling is code that runs once or twice a year, in the worst conditions, and is therefore worth testing deliberately rather than hoping. Three tests are practical without an actual Aurora cluster.

Force a read-only transaction. PostgreSQL's default_transaction_read_only reproduces exactly the error a demoted writer produces, on any instance:

import pytest
from sqlalchemy import text
from sqlalchemy.exc import DBAPIError

from shop.models import Customer


@pytest.mark.asyncio
async def test_read_only_error_invalidates_the_pool(engine, session_factory):
    pool = engine.sync_engine.pool

    async with session_factory() as session:
        await session.execute(text("SET default_transaction_read_only = on"))
        session.add(Customer(email="a@example.com"))
        with pytest.raises(DBAPIError):
            await session.commit()

    # The listener marked it a disconnect, so the pool discarded the connection.
    assert pool.checkedout() == 0
    async with session_factory() as session:          # a fresh connection
        session.add(Customer(email="b@example.com"))
        await session.commit()                        # succeeds

Terminate the backend. pg_terminate_backend against your own connection produces 57P01, which is what a failover does to in-flight work:

import pytest
from sqlalchemy import text


@pytest.mark.asyncio
async def test_survives_a_terminated_backend(session_factory):
    async with session_factory() as session:
        pid = await session.scalar(text("SELECT pg_backend_pid()"))
        await session.execute(text("SELECT pg_terminate_backend(:pid)"), {"pid": pid})
        # The next statement on this session fails; a new session works.
    async with session_factory() as session:
        assert await session.scalar(text("SELECT 1")) == 1

Verify the retry helper is actually idempotent. A retry test that only asserts "it eventually succeeded" misses the important failure: an operation applied twice. Assert the end state, not the return value — one row, one charge, one shipment.

Beyond tests, two production practices close the loop. Run a failover in a staging cluster deliberately, at a time of your choosing, and watch how long errors last — the number should be seconds, and if it is minutes the invalidation is not working. And alert on the error codes themselves: a sudden burst of 25006 or 57P01 is the clearest possible signal that a failover has happened, often arriving before the provider's own notification. Those codes belong in the metrics described in instrumenting and observing async queries.

Frequently Asked Questions

Related

• Dialect-Specific Gotchas and Driver Quirks — The parent guide: behaviour that differs by driver and platform.
• Handling ConnectionDoesNotExistError and server disconnects — The general disconnect and retry machinery.
• Configuring pool_pre_ping to handle stale connections — What pre-ping does catch.
• Routing reads to replicas with async engines — Reader endpoints, lag and read-your-own-writes.