PostgreSQL High Availability and Failover for Django

Problem statement

With a single PostgreSQL server, a hardware failure, a full disk, or even a routine OS reboot takes the Django app down until someone restores service, and backups only help after a long restore. High availability adds a standby that takes over automatically when the primary fails, usually within seconds to a minute. Django needs to reconnect to the new primary cleanly, without serving errors for longer than the failover itself, and without accidentally writing to a read-only standby.

1. Prefer managed high availability: RDS or Aurora Multi-AZ, Cloud SQL HA, Azure Flexible Server zone-redundant HA.
2. Self-hosted: Patroni (with etcd) on VMs, or CloudNativePG on Kubernetes, with a stable endpoint (VIP, HAProxy, or Kubernetes Service) that always points at the primary.
3. Point Django at that endpoint, never at a node's own address.
4. Enable CONN_HEALTH_CHECKS = True and keep CONN_MAX_AGE moderate so dead connections are replaced.
5. Retry idempotent operations briefly in Celery tasks; let web requests fail fast during the switch.
6. Test failover regularly in staging and measure the impact.

Step-by-step solution

1. Choose the HA mechanism

• Managed services run a synchronous or semi-synchronous standby in another zone and switch the DNS endpoint on failure. RDS Multi-AZ typically fails over in about a minute; Aurora faster.
• Patroni manages streaming replication between nodes, uses a distributed configuration store (etcd, Consul) for leader election, and exposes the leader through HAProxy health checks or a VIP.
• CloudNativePG on Kubernetes provides -rw (primary) and -ro (replicas) Services that update on failover.

2. Connect to the endpoint that follows the primary

DATABASES = {
    "default": {
        **env.db("DATABASE_URL"),           # host = the HA endpoint, e.g. db-rw.app.svc or RDS endpoint
        "CONN_MAX_AGE": 60,
        "CONN_HEALTH_CHECKS": True,
        "OPTIONS": {"connect_timeout": 5, "target_session_attrs": "read-write"},
    }
}

target_session_attrs=read-write (libpq) makes the client reject a connection to a standby, which protects against writing to a node that is no longer primary. With multiple hosts listed in the connection string (host=db1,db2), libpq tries them in order and picks the one that accepts writes.

3. Handle stale connections

After failover, existing connections point at a dead or demoted server. CONN_HEALTH_CHECKS pings reused connections at the start of each request and reconnects if needed. Without it, each persistent connection fails once before being replaced. Celery workers and long-running commands hold connections outside requests; call close_old_connections() before work, or rely on task retries.

4. Retries for background work

from django.db import OperationalError, InterfaceError

@shared_task(autoretry_for=(OperationalError, InterfaceError), retry_backoff=5, max_retries=6)
def sync_inventory(): ...

For web requests, fail fast and show an error page rather than retrying inside the request; clients can retry.

5. Data loss and synchronous replication

Asynchronous replication can lose the last few transactions on failover; synchronous replication (synchronous_commit with a synchronous standby) prevents that at the cost of write latency. Managed Multi-AZ products typically use synchronous or storage-level replication. Know your provider's guarantee and make it part of your recovery objectives.

6. Test failover

Trigger a failover in staging (RDS "reboot with failover", patronictl switchover, deleting the CloudNativePG primary pod) while running a load test. Measure the error window and confirm the app recovers without restarts.

Verification check: during a test failover with steady traffic, errors are confined to the failover window (seconds to about a minute), Django and Celery recover without restarts, and SELECT pg_is_in_recovery() on the endpoint returns false afterwards.

Explanation

PostgreSQL high availability builds on streaming replication: standbys replay the primary's write-ahead log continuously. A cluster manager detects primary failure, promotes the most up-to-date standby, and redirects clients through an endpoint that tracks the current primary. Django sees this as connections dropping and new connections succeeding against a different server. Health checks on reused connections, a client-side guard against connecting to read-only nodes, and retries for background work turn a failover into a brief blip instead of a prolonged outage.

Edge cases / notes

• PgBouncer in front of an HA cluster must itself follow the primary (point it at the HA endpoint) and may need a reconnect after failover.
• DNS caching. Java and some resolvers cache DNS long; Python's libpq resolves on each connect, but host-level caches (nscd) can delay updates.
• Split brain is prevented by consensus stores (etcd) in Patroni; never run two primaries manually.
• Read replicas for scaling are separate from HA standbys, though they can overlap.

Next steps

• Scale reads with Django Database Read Replicas.
• Protect against bad data and deletions with PostgreSQL Point-in-Time Recovery for Django.

FAQ

How does Django handle a PostgreSQL failover?

Existing connections fail; with CONN_HEALTH_CHECKS, Django reconnects to the endpoint, which now points at the new primary.

How long does a database failover take?

Typically 20-60 seconds on managed services, depending on detection and DNS updates.

Can Django write to a standby by mistake?

Use target_session_attrs=read-write so connections only succeed against a writable primary.

Is high availability a backup?

No. It protects against server failure, not against deleted or corrupted data. Keep backups and PITR.