Database failover when the host disappears

Every stateless service has the same answer to a host failure: reschedule somewhere else, it'll be serving in seconds. Databases don't get that answer, because the machine wasn't just running the process — it was holding the data.

There are two real strategies, they have very different cost and recovery profiles, and choosing between them is one of the few architecture decisions where the honest answer is a number rather than a preference. I build database hosting at PandaStack, and we shipped a broken version of this before we shipped a working one, which taught me the rule in the last section the expensive way.

Replica promotion versus rebuild

Strategy one: keep a replica running

A second database on a different host continuously replays the primary's write-ahead log. When the primary dies, you promote the replica and point traffic at it. This is what multi-AZ RDS does and what most self-managed high-availability setups do.

Recovery time is tens of seconds — mostly detection and the decision to promote, not the promotion itself. Data loss depends on replication mode: asynchronous replication can lose whatever hadn't been shipped when the primary died, while synchronous replication loses nothing but makes every write wait for the replica to acknowledge, which is a permanent latency tax on the happy path.

The cost is the honest objection: you're running and paying for a second machine that serves no traffic. For a production database that's obviously worth it. For two hundred per-customer databases where most are idle, doubling the fleet to protect against a rare event is a hard sell.

Strategy two: rebuild from the archive

No standby. When a host is lost, provision a new machine elsewhere, restore the base backup, replay archived WAL to the latest available point, and bring it up with the same identity so connection strings still work.

Recovery time is minutes to tens of minutes depending on data size and how much WAL must be replayed. Data loss is bounded by the archive lag — typically seconds to a couple of minutes, the same RPO as point-in-time recovery. The cost is close to zero when nothing is failing, which is almost all the time.

This is the strategy that fits a fleet of many mostly-idle databases, and it's what our failover operation does: rebuild on a healthy agent from the archive, keeping the database's identity so nothing in the application changes.

Detection is harder than recovery

The recovery mechanism is the easy part. Deciding that recovery is warranted is where systems go wrong, because 'the host is dead' and 'I cannot currently reach the host' are indistinguishable from where you're standing.

If you fail over on a network partition, you now have two machines that both believe they're the database. Clients on either side of the partition write to different copies, and when the partition heals you have two divergent histories and no automatic way to merge them. This is split-brain, and it is strictly worse than the downtime you were trying to avoid — downtime is recoverable, divergent writes generally are not.

• Require multiple independent observers to agree the host is unreachable before acting. One monitor's opinion is not evidence.
• Fence the old primary — make sure it cannot accept writes — before promoting or rebuilding anything. If you cannot fence it, you cannot safely fail over automatically.
• Prefer waiting to acting when uncertain. Automatic failover on a marginal signal causes more incidents than it prevents, and a well-tested manual failover triggered by a human in three minutes is often the better system.

Planned migration is the same operation, done calmly

The same machinery is worth having for a case that isn't an emergency at all: moving a healthy database to a different host, deliberately. Host maintenance, rebalancing a lopsided fleet, migrating off hardware you're retiring.

Planned migration is far more pleasant because you control the timing and the source is healthy: build the new copy, verify it, switch over, keep the old one until you're confident. Which is why the failover API accepts an explicit force flag for the planned case — the emergency path is conservative by default and refuses to act when the source still looks fine, and the planned path is you saying 'yes, I know it's healthy, move it anyway.'

# Emergency: rebuild on a healthy host from the archive.
# Preflight runs first; if the source turns out to be fine, this aborts
# rather than proceeding -- taking a healthy database down while 'fixing'
# it is the worst possible outcome.
curl -sS -X POST https://api.pandastack.ai/v1/databases/$DB_ID/failover \
  -H "Authorization: Bearer $PANDASTACK_API_KEY"

# Planned migration: you KNOW it is healthy and want it moved anyway.
curl -sS -X POST https://api.pandastack.ai/v1/databases/$DB_ID/failover \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"force": true}'

# Both return 202 -- the work is asynchronous. Poll GET for status.
# A synchronous API here would time out mid-recovery and leave the caller
# with no idea whether the operation is still running.

The rule that cost us a rewrite

Our first failover implementation could take a healthy database offline while trying to recover it. The preflight checks and the action weren't properly separated, so a failure partway through left the database in a worse state than before the operation started. A recovery tool that can cause the outage it exists to prevent is not a recovery tool.

Never make something healthy unavailable in the process of trying to fix something you believe is broken. Verify first, act second, and treat 'the source is actually fine' as a reason to abort rather than to continue.

Concretely, that means preflight-then-act as separate phases. Check that a healthy target host exists with capacity, that the archive is reachable and current, and that the source really is in the state you think it is — all before touching anything. If any check fails, abort having changed nothing. Only when everything passes do you begin the destructive part, and even then the old copy stays until the new one is verified serving.

This is also why the operation is asynchronous and idempotent. A rebuild takes minutes; a synchronous call would time out and leave the caller unsure whether it's still running, at which point someone helpful retries it. Idempotency means that retry doesn't start a second concurrent rebuild — which, if you're wondering, is a genuinely creative way to lose data.

Test it, or you don't have it

Failover is the least-exercised code path in most systems and the one that runs when everything else has already gone wrong. Untested, it is a hypothesis.

1. Kill a host in a non-production environment and watch what happens end to end, timed.
2. Verify the data afterwards, not just that the database is up. Compare row counts and checksums against what you expect — 'it started' is not the same as 'it's correct'.
3. Test with a realistic data size. A 100 MB test database recovers in seconds and tells you nothing about a 500 GB one.
4. Test the partition case specifically, not just the clean-death case. Clean deaths are the easy failure; partitions are the ones that cause split-brain.
5. Time the whole thing including human decision time, and write that number in the runbook. That is your actual RTO, and it is always larger than the mechanical one.

When we ran this properly — killing a host and confirming a cross-agent restore came back byte-identical — the exercise found three separate autostart bugs that no amount of code review had surfaced. That's the normal outcome. The value of the drill is not proving the system works; it's finding the three things that don't before they matter.