0Pricing
SQL Academy · Lesson

Failover and Leader Election (Patroni, Stolon)

Use Patroni or Stolon for automated failover, and configure quorum to avoid split-brain.

Failover and Leader Election (Patroni, Stolon) is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Automated Failover?

Manual failover is slow and error-prone. Tools detect primary failure and promote a replica without human intervention.

Failover Steps

What needs to happen:

  1. Detect primary is down (health checks, consensus)
  2. Pick a replica with the most recent WAL
  3. Promote it (pg_promote / pg_ctl promote)
  4. Reconfigure other replicas to follow the new primary
  5. Update application connection routing

Split Brain Risk

If the network partitions, you might promote a replica while the old primary is still alive. Two primaries → conflicting writes → data corruption. Avoid with quorum.

Patroni

Python daemon that uses an external DCS (Distributed Configuration Store) — typically etcd, Consul, or Zookeeper — for leader election:

# patroni.yml
name: pg1
scope: my_cluster
etcd:
  hosts: 10.0.0.10:2379,10.0.0.11:2379,10.0.0.12:2379
postgresql:
  data_dir: /var/lib/postgresql/data

How Patroni Picks the New Leader

Each node's Patroni daemon races to acquire a leader lock in etcd. Only one can hold it; that node becomes the primary. Others follow it.

Stolon

Go-based alternative. Uses similar consensus model but with different operational characteristics. Splits responsibilities between sentinel, keeper, and proxy components.

repmgr

Lighter-weight tool from 2ndQuadrant — less automation, more manual control. Good for smaller setups.

Cloud-Managed Failover

RDS, Cloud SQL, Aurora handle failover for you. Trade flexibility for ops simplicity.

Connection Routing After Failover

Apps need to know the new primary. Options:

  • DNS update (slow due to TTL)
  • Floating IP managed by the failover tool
  • Proxy layer: HAProxy, pgbouncer + script, AWS RDS endpoint

Synchronous Replication and Failover

Synchronous standby = guaranteed zero data loss. Pair with automated failover for the strongest HA.

Quorum

For sync replication, set synchronous_standby_names with quorum: ANY 2 of 3 replicas must confirm. Tolerates one slow/failed replica without blocking commits.

synchronous_standby_names = 'ANY 2 (replica1, replica2, replica3)'

Read-Your-Writes

After failover or with replication lag, an app might write to the new primary and immediately read stale data from a replica. Either route reads-after-writes to the primary or use pg_last_wal_replay_lsn tracking.

Testing Failover

Test before you need it. Kill the primary in staging monthly. Practice the runbook. Real failover stress is bad enough; surprises are worse.

Recap

Automated failover removes humans from a critical path.

  • Patroni / Stolon for self-managed
  • RDS / Cloud SQL for managed
  • Beware split brain — use quorum
  • Practice failover regularly

Quick Check

What is "split brain" in the context of PostgreSQL replication?

Frequently asked questions

Is the “Failover and Leader Election (Patroni, Stolon)” lesson free?

Yes — the full text of “Failover and Leader Election (Patroni, Stolon)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Failover and Leader Election (Patroni, Stolon)”?

Use Patroni or Stolon for automated failover, and configure quorum to avoid split-brain. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Failover and Leader Election (Patroni, Stolon)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Streaming Replication and WAL
  2. Logical Replication for Sharding
  3. Failover and Leader Election (Patroni, Stolon)
  4. Read Replicas and Connection Routing
← Back to SQL Academy