Run a Database Failover Drill

Practice promoting a replica, reconnecting the application, verifying writes, and switching back before a real outage forces an untested failover.

advanced~90 mindatabasereliabilitydrills

Objective

Prove that the team can fail over the primary database within the recovery time objective and that the application recovers without manual data repair.

Environment

A primary database with at least one streaming replica
A staging environment that mirrors production topology
Application connection strings that can be changed without a code deploy
Monitoring for replication lag, connection errors, and write failures

Steps

1. Record the baseline

Capture replication lag, current primary, connection counts, and a checksum of a representative table.

Expected state: The baseline is stored and the replica is caught up within the acceptable lag.

2. Stop writes and promote the replica

Pause application writes, confirm the replica has applied all changes, promote it, and update the application's write endpoint.

Expected state: The replica accepts writes and reports read-write mode.

3. Verify reads and writes

Run a write, read it back, and compare row counts or checksums for a known table against the baseline.

Expected state: The application can commit and read new data through the new primary.

4. Exercise the rollback plan

Reconfigure the old primary as a replica, wait for it to catch up, and switch back in a controlled step.

Expected state: The original topology is restored without losing committed rows.

5. Capture timings and gaps

Record detection, promotion, reconnection, and verification durations, plus every manual step that was required.

Expected state: Each step has a measured duration and an owner.

Decision points

Should the drill run during business hours?

During a low-traffic window with stakeholder notice
At full peak to test real conditions

Start in a low-traffic window. Move to higher traffic only after the basic runbook is proven.

What proves the failover succeeded?

The database reports read-write mode
The application completes a write and reads it back

Require an application-level write. Database role alone does not prove connectivity, credentials, or schema compatibility.

Expected outcome

The team knows the real failover duration, the application reconnects correctly, and the runbook reflects the steps that were actually required.

Verification

Compare the post-failover checksum with the baseline, confirm committed writes survive a switch back, and verify that monitoring alerts fired within the expected window.

Cleanup

Restore the original primary/replica roles, remove temporary write pauses, and update the runbook with any deviations.

A failover you have not practiced is a guess

Managed databases advertise automatic failover, but the application still has to reconnect, refresh credentials, and handle a brief write outage. Connection pools may hold dead sockets, read replicas may lag, and the new primary may have a different endpoint. A drill turns those assumptions into measured steps.

-- On the standby, confirm it has caught up before promotion
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
SELECT pg_is_in_recovery();

-- After promotion
SELECT pg_is_in_recovery();  -- must be false

Pause writes deliberately

A clean drill starts with a write pause so both nodes agree on the last committed transaction. Without the pause, a promotion can lose the final writes or produce a split-brain window. In production, the pause must be bounded and communicated; in staging, it can be longer to allow checksums.

Verify through the application

A database that reports read-write mode is not a successful failover. The application must obtain a connection, commit a row, and read it back. Check that background workers, migration tools, and analytics jobs also point at the new primary; they often use separate connection strings and fail silently.

Roll back on purpose

The drill is not complete until the old topology is restored. Reconfigure the former primary as a replica, let it catch up, and switch back with the same controls. Record every manual step and the time it took. The next real incident should require fewer decisions, not more.