Database Failover Runbook

Overview

A failover promotes a read replica to take over as the primary when the original fails or has to be taken offline; the reverse operation — returning traffic to the original node once it’s rebuilt — is the failback covered later in this runbook. Database failovers are high-stakes events where minutes of delay mean lost revenue and eroded trust. A manual runbook reduces panic-driven mistakes by providing exact steps, verification commands, and rollback procedures. This runbook covers primary-to-replica promotion, application reconfiguration, and post-failover validation.

The procedure below assumes you already run streaming replication; if you’re still setting it up, start with the PostgreSQL replication guide. For the architectural trade-offs behind this procedure, see the failover pattern.

When to Use

Use this runbook when:

• The primary database is unresponsive or severely degraded
• A planned maintenance requires switching to a replica
• The primary data center is experiencing an outage
• Automated failover has failed and manual intervention is required

Don’t use this runbook when:

• RDS Multi-AZ or an orchestrator such as Patroni or Stolon is already executing a healthy automatic failover — let automation finish and verify the result instead
• The primary is degraded but recoverable without a promotion (kill blocking queries, restart the instance, add capacity)
• There’s no replica to promote — restore from backup instead and treat it as a disaster recovery scenario

Prerequisites

Before starting:

• Access to database monitoring dashboards (lag, connections, replication status)
• Access to application configuration management (env vars, config files, service mesh)
• Access to DNS or load balancer management console
• On-call team notified and incident channel opened
• Read-replica confirmed healthy and lag < 5 seconds

Solution

# Database Failover Runbook: `<Service Name>`

## 1. Verify the Failure (2 minutes)

### Check Primary Health
```bash
psql -h primary.db.internal -U monitor -c "SELECT pg_is_in_recovery();"

## MySQL
mysql -h primary.db.internal -u monitor -e "SHOW STATUS LIKE 'Threads_connected';"
```

| Check | Expected | Action if Failed |
|-------|----------|----------------|
| Ping primary | < 10ms response | Proceed to failover |
| Connection count | < max_connections | Check for connection storm |
| Replication lag | N/A (primary) | Confirm primary is source |
| Disk space | > 10% free | If full, failover is only option |

### Confirm Replica is Ready
```bash
## PostgreSQL
psql -h replica.db.internal -U monitor -c "SELECT pg_last_xact_replay_timestamp();"

## MySQL 8.0.22+ (use SHOW SLAVE STATUS / Seconds_Behind_Master on older versions)
mysql -h replica.db.internal -u monitor -e "SHOW REPLICA STATUS\G" | grep Seconds_Behind_Source
```

**Decision Gate:** Only proceed if replica lag < 5 seconds and replica disk is healthy.

## 2. Stop Writes to Primary (1 minute)

```bash
## Set application to read-only mode (if available)
curl -X POST http://app.internal/admin/read-only

## Or block at load balancer
## Block port 5432/3306 at primary security group
```

## 3. Promote Replica to Primary (3 minutes)

### PostgreSQL
```bash
## On the replica
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/data

## Verify promotion
psql -h replica.db.internal -U monitor -c "SELECT pg_is_in_recovery();"  # Should return false
```

### MySQL
```bash
## On the replica (MySQL 8.0.22+; use STOP SLAVE / RESET SLAVE ALL on older versions)
mysql -u root -e "STOP REPLICA; RESET REPLICA ALL;"

## Verify
mysql -u root -e "SHOW REPLICA STATUS\G"    # Should return Empty set
mysql -u root -e "SHOW BINARY LOG STATUS;"  # Should show binary log position (SHOW MASTER STATUS on MySQL < 8.4)
```

### AWS RDS
```bash
aws rds promote-read-replica \
  --db-instance-identifier replica-01 \
  --region us-east-1
```

## 4. Update Application Configuration (2 minutes)

```bash
## Update environment variable or config map
export DB_HOST=replica.db.internal

## Reload application (zero-downtime if using connection pool)
sudo systemctl reload app

## Or for Kubernetes
kubectl set env deployment/app DB_HOST=replica.db.internal
kubectl rollout status deployment/app
```

## 5. DNS / Load Balancer Cutover (2 minutes)

| Method | Command | RTO |
|--------|---------|-----|
| DNS A record | Update to replica IP | 5-60 seconds (TTL dependent) |
| Load balancer | Swap target group | 10-30 seconds |
| Service mesh (Consul) | `consul catalog services` update | 5-10 seconds |
| Kubernetes Service | Update endpoint or service selector | Immediate |

```bash
## Example: AWS Route53
aws route53 change-resource-record-sets \
  --hosted-zone-id Z123456789 \
  --change-batch file://failover-dns.json
```

## 6. Verify Application Functionality (3 minutes)

```bash
## Health check
curl -f http://app.internal/health

## Write test
curl -X POST http://app.internal/api/test \
  -H "Content-Type: application/json" \
  -d '{"test": "failover-write-2026-06-26"}'

## Read verification — replace <id> with the ID returned by the write test
curl http://app.internal/api/test/<id>
```

| Verification | Status | Time |
|------------|--------|------|
| Health checks passing | [ ] | ___ |
| Write successful | [ ] | ___ |
| Read-back correct | [ ] | ___ |
| Replication lag (new replica) | < 1s | ___ |
| Error rate < 0.1% | [ ] | ___ |

## 7. Establish New Replication (5 minutes)

### Option A: Repair Old Primary (if recoverable)
```bash
## Reconfigure old primary as replica
## PostgreSQL — the target data directory must be empty (move or wipe the old one first)
pg_basebackup -h new-primary.db.internal -D /var/lib/postgresql/data -Fp -Xs -P
## Edit recovery.conf or postgresql.auto.conf with primary_conninfo
sudo -u postgres pg_ctl start
```

### Option B: Spin Up New Replica
```bash
## From snapshot or base backup
aws rds create-db-instance-read-replica \
  --db-instance-identifier new-replica-01 \
  --source-db-instance-identifier new-primary-01
```

## 8. Post-Incident Actions

- [ ] Update incident timeline with exact times for each step
- [ ] Capture logs from old primary for root cause analysis
- [ ] Document data loss (if any) with exact transaction IDs
- [ ] Schedule postmortem within 24 hours
- [ ] Update this runbook with lessons learned

Explanation

The runbook separates verification (confirm failure, confirm replica health) from execution (promotion, cutover) and validation (write/read tests). The decision gate at step 1 prevents failovers into an unhealthy replica. DNS cutover is preferred over application restarts because it minimizes RTO and avoids connection pool warm-up delays.

Two numbers decide how bad a failover can go: RPO, the amount of committed data you can afford to lose, and RTO, how long the database can stay down. The 5-second lag gate in step 1 is the RPO control — every transaction the replica has not replayed at promotion time is data the application will never see again. The cutover method is the RTO control: DNS is cheap but bound by TTL, a load balancer or service mesh converges in seconds, and a connection pooler such as PgBouncer can pause and resume client traffic without a reconnect storm at all.

The step ordering also protects against split-brain, the worst outcome of a botched failover. Writes are stopped (step 2) before the replica is promoted (step 3) precisely so both nodes can never accept writes at the same time. If the old primary comes back unexpectedly after cutover, it must never serve traffic again until it is rebuilt as a replica — that is why step 7 and the failback procedure below exist.

Failback: Returning to the Original Primary

A failover is only half of the operation. Once the old primary is healthy again, decide whether to keep the promoted replica as the permanent primary or fail back to the original.

Keep the promoted replica as primary when:

• It has equal or better capacity than the old primary
• The incident review found no hardware or placement advantages on the original node
• You want to avoid a second outage window — every cutover carries risk

Fail back to the original primary when:

• The replica was emergency capacity (smaller instance, fewer provisioned IOPS)
• Licensing, backup tooling, or regional placement favors the original node
• The old primary holds pinned configuration the replica lacks

Failback procedure

1. Rebuild the old primary as a replica of the new primary (step 7 of the runbook). On PostgreSQL, pg_rewind is faster than a full pg_basebackup when both nodes share a common checkpoint history:

## On the old primary, stopped and with a valid (not wiped) data directory
pg_rewind --target-pgdata=/var/lib/postgresql/data \
  --source-server="host=new-primary.db.internal user=replicator"

pg_rewind requires wal_log_hints = on or data checksums enabled — check SHOW data_checksums; before relying on it. If it fails, fall back to pg_basebackup into an empty data directory.

2. Wait until replication lag on the rebuilt node is under 1 second.
3. Schedule a maintenance window — a failback is a planned failover, not an emergency.
4. Run the same runbook in reverse: stop writes, promote the original primary, cut DNS or the load balancer back, verify writes.
5. Re-establish replication in the final direction and update dashboards and alerting to match the new topology.

Do not skip the verification table from step 6 during a failback. A promotion that works in one direction can still fail in the other because of stale primary_conninfo settings or leftover replication slots.

Variants

Context	Approach	Notes
PostgreSQL streaming replication	pg_ctl promote	Fastest, requires WAL streaming healthy
MySQL GTID	STOP SLAVE; RESET SLAVE ALL;	GTID simplifies finding correct position
AWS RDS Multi-AZ	Automatic failover	Only use this runbook for cross-region or manual promotion
Kubernetes StatefulSet	Patroni / Stolon orchestrator	Operator handles promotion; runbook for operator failure

Adapting This Runbook

The template is written for a generic two-node setup, so replace every *.db.internal hostname, port, and credential role (monitor, replicator) with your real values before an incident ever happens. Tune the 5-second lag gate to your actual RPO target — a payments database may need synchronous replication and a zero-lag gate, while an analytics replica can tolerate more. Map each step to your tooling: Cloud SQL and Aurora have their own promotion commands, and managed proxies replace the DNS step on some stacks.

Two details teams forget until the first drill: version drift and permissions. The replica must run the same major version as the primary or promotion will fail, and the on-call engineer needs the cloud or SSH credentials to promote before the incident — write down where they live. Finally, keep a one-page printed or pinned copy of steps 1–6 in the incident channel — during a real outage nobody scrolls. The downloadable versions (the runbook template, the automation script, the Patroni config, and the consistency checker) live in the companion repository.

What Works

1. Test this runbook monthly on a staging environment — not during the incident; a disaster recovery test plan gives the drill a pass/fail structure
2. Automate health checks in step 1 and step 6 with scripts, not manual queries
3. Use connection pooling (PgBouncer, ProxySQL) to avoid DNS TTL delays
4. Monitor replication lag continuously — lag > 30s should page on-call
5. Document the exact transaction ID at promotion for data loss calculation
6. Communicate during the event — post status updates with the downtime communication template so stakeholders are not guessing

Common Mistakes

1. Failing over to a lagging replica — results in data loss and application errors
2. Not stopping writes before promotion — split-brain, diverging data sets
3. Forgetting to update application config — apps reconnect to old failed primary
4. Not verifying writes post-failover — silent failures go unnoticed for hours
5. Skipping new replica setup — running without redundancy after failover

Advanced Solutions

Automated failover script with pre-flight checks

Combine all runbook steps into a single executable script with safety gates:

#!/bin/bash
# failover.sh - Automated database failover with pre-flight checks
# Usage: ./failover.sh [--force] [--dry-run]

set -euo pipefail

FORCE=false
DRY_RUN=false

for arg in "$@"; do
  case $arg in
    --force) FORCE=true ;;
    --dry-run) DRY_RUN=true ;;
  esac
done

PRIMARY_HOST="primary.db.internal"
REPLICA_HOST="replica.db.internal"
DB_USER="monitor"
MAX_LAG_SECONDS=5

log() { echo "[$(date -u +%H:%M:%S)] $1"; }

# Step 1: Pre-flight checks
log "Running pre-flight checks..."

# Check primary is actually down
if ping -c 1 -W 2 "$PRIMARY_HOST" &>/dev/null && ! $FORCE; then
  log "ERROR: Primary is reachable. Use --force to override."
  exit 1
fi

# Check replica lag
log "Checking replica lag..."
PG_LAG=$(psql -h "$REPLICA_HOST" -U "$DB_USER" -t -c \
  "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));" 2>/dev/null | xargs)

if (( $(echo "$PG_LAG > $MAX_LAG_SECONDS" | bc -l) )) && ! $FORCE; then
  log "ERROR: Replica lag is ${PG_LAG}s (max: ${MAX_LAG_SECONDS}s). Use --force to override."
  exit 1
fi

log "Pre-flight checks passed. Replica lag: ${PG_LAG}s"

if $DRY_RUN; then
  log "DRY RUN: Would proceed with failover."
  exit 0
fi

# Step 2: Enable read-only mode
log "Enabling read-only mode..."
curl -sS -X POST http://app.internal/admin/read-only || log "WARN: Could not enable read-only mode"

# Step 3: Promote replica
log "Promoting replica to primary..."
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/data

# Verify promotion
IS_RECOVERY=$(psql -h "$REPLICA_HOST" -U "$DB_USER" -t -c "SELECT pg_is_in_recovery();" | xargs)
if [ "$IS_RECOVERY" != "f" ]; then
  log "ERROR: Promotion failed. pg_is_in_recovery returned: $IS_RECOVERY"
  exit 1
fi
log "Promotion successful."

# Step 4: Update application config
log "Updating application configuration..."
kubectl set env deployment/app DB_HOST="$REPLICA_HOST"
kubectl rollout status deployment/app --timeout=120s

# Step 5: Verify
log "Running post-failover verification..."
sleep 5
HTTP_CODE=$(curl -sS -o /dev/null -w "%{http_code}" http://app.internal/health)
if [ "$HTTP_CODE" != "200" ]; then
  log "ERROR: Health check failed with HTTP $HTTP_CODE"
  exit 1
fi

WRITE_RESULT=$(curl -sS -X POST http://app.internal/api/test \
  -H "Content-Type: application/json" \
  -d "{\"test\": \"failover-$(date +%s)\"}" -w "\n%{http_code}")

WRITE_CODE=$(echo "$WRITE_RESULT" | tail -1)
if [ "$WRITE_CODE" != "200" ] && [ "$WRITE_CODE" != "201" ]; then
  log "ERROR: Write test failed with HTTP $WRITE_CODE"
  exit 1
fi

log "Failover complete. All verifications passed."
log "Next steps:"
log "  1. Establish new replication (Step 7 of runbook)"
log "  2. Update incident timeline"
log "  3. Schedule postmortem"

Patroni automated failover configuration

For PostgreSQL, Patroni handles automated failover with health checks and cluster management:

# patroni.yml
name: postgres-cluster
scope: pgsql
restapi:
  listen: 0.0.0.0:8008
  connect_address: $(HOSTIP):8008

etcd:
  hosts: etcd1.internal:2379,etcd2.internal:2379,etcd3.internal:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 20
    maximum_lag_on_failover: 1048576  # 1MB
    maximum_lag_on_syncnode: 1048576
    synchronous_mode: true
    synchronous_mode_strict: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 128MB  # wal_keep_segments on PostgreSQL < 13
        archive_mode: "on"
        archive_timeout: 1800s
      recovery_conf:
        restore_command: "wal-g wal-fetch %f %p"

postgresql:
  listen: 0.0.0.0:5432
  connect_address: $(HOSTIP):5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/bin
  pg_hba:
    - "replication replicator 0.0.0.0/0 md5"
    - "host all all 0.0.0.0/0 md5"
  replication:
    username: replicator
    password: "${REPLICATION_PASSWORD}"
    network: 0.0.0.0/0
  superuser:
    username: postgres
    password: "${POSTGRES_PASSWORD}"

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

Post-failover data consistency verification

Verify data integrity after failover by comparing transaction logs:

import psycopg2
from dataclasses import dataclass

@dataclass
class FailoverVerification:
    promoted_node: str
    old_primary_lsn: str  # Log sequence number at failure
    new_primary_lsn: str  # LSN at promotion
    transactions_lost: int
    consistency_ok: bool

def verify_failover_consistency(
    new_primary_host: str,
    old_primary_lsn: str,
    db_user: str = "monitor",
) -> FailoverVerification:
    """Verify data consistency after a database failover."""
    conn = psycopg2.connect(host=new_primary_host, user=db_user, dbname="postgres")
    cur = conn.cursor()

    # Get current LSN on new primary
    cur.execute("SELECT pg_current_wal_lsn();")
    new_lsn = cur.fetchone()[0]

    # Count transactions since promotion point
    cur.execute("""
        SELECT count(*) FROM pg_stat_activity
        WHERE state = 'active' AND xact_start > now() - interval '5 minutes';
    """)
    active_txns = cur.fetchone()[0]

    # Check for replication slot health
    cur.execute("""
        SELECT slot_name, active, restart_lsn
        FROM pg_replication_slots;
    """)
    slots = cur.fetchall()

    # Verify all slots are active
    all_active = all(slot[1] for slot in slots) if slots else True

    # Estimate lost transactions (simplified)
    lost = 0 if old_primary_lsn == "unknown" else estimate_lost(old_primary_lsn, new_lsn)

    result = FailoverVerification(
        promoted_node=new_primary_host,
        old_primary_lsn=old_primary_lsn,
        new_primary_lsn=new_lsn,
        transactions_lost=lost,
        consistency_ok=all_active,
    )

    cur.close()
    conn.close()
    return result

def estimate_lost(old_lsn: str, new_lsn: str) -> int:
    """Estimate lost transactions between two LSNs."""
    # Parse LSN format (e.g., '0/17000058')
    try:
        old_parts = [int(x, 16) for x in old_lsn.split("/")]
        new_parts = [int(x, 16) for x in new_lsn.split("/")]
        old_bytes = old_parts[0] * 0x100000000 + old_parts[1]
        new_bytes = new_parts[0] * 0x100000000 + new_parts[1]
        diff = new_bytes - old_bytes
        # Rough estimate: 1 transaction ~ 200 bytes average
        return max(0, diff // 200)
    except (ValueError, IndexError):
        return 0

# Example usage
result = verify_failover_consistency(
    new_primary_host="replica.db.internal",
    old_primary_lsn="0/17000058",
)
print(f"Promoted node: {result.promoted_node}")
print(f"Transactions lost (est.): {result.transactions_lost}")
print(f"Replication slots healthy: {result.consistency_ok}")

Failover Decision Tree

Further Reading

• AWS RDS — Promoting a read replica to a standalone instance
• Patroni documentation — automated PostgreSQL failover
• PostgreSQL documentation — Log-shipping standby servers