pg_failover_readiness — Can I Safely Failover Right Now? — Blog

What Does This Tool Do?

pg_failover_readiness evaluates the immediate failover capability of a PostgreSQL cluster with a weighted score from 0 to 100. The tool queries replication status on both the primary and replicas and delivers a clear can_failover answer: yes or no.

The weighted formula considers five factors:

Replication Lag < 1 MB (30 points) — The most critical factor: how current is the replica?
Sync State = streaming (25 points) — Is replication active and in streaming mode?
Slot Active = true (20 points) — Is the replication slot active and retaining WAL segments?
WAL Receiver Status = streaming (15 points) — Is the replica actively receiving WAL data?
Replica Count ≥ 1 (10 points) — Does at least one replica exist?

How Is It Called?

Configuration

DR tools are configuration-driven. Instead of a direct database connection, system and environment are read from the DR config — allowing multiple cluster pairs to be managed:

● ● ● Claude CLI — DR Configuration

claude> Show me the DR status for hubport

▶ Calling tools...
→ pg_cnpg_dr_readiness { "system": "hubport", "environment": "lab" }

// Reads primary + replica from ~/.config/mcp-postgres-enterprise/dr/cnpg-dr.json
// Primary: k8s-primary/mcp-test/mcp-test-cnpg (2 instances)
// Replica: k8s-replica/mcp-test/mcp-test-cnpg-replica (1 instance)

Claude CLI (Natural Language)

● ● ● Claude CLI — Failover Analysis

claude> Can I safely failover right now?

▶ Calling tools...
→ pg_failover_readiness {}

Failover Score: 92/100 — Status: READY
✓ can_failover: true

Primary: db-primary-1

Replicas:
db-primary-2
Lag: 524 KB (0.2s) | Sync: streaming | Eligible: ✓

MCP Protocol (Programmatic)

● ● ● MCP Protocol — Failover Analysis

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_failover_readiness", {});

// Response:
{
"score": 92,
"status": "READY",
"primary": "db-primary-1",
"replicas": [
    {
      "name": "db-primary-2",
      "lag_bytes": 524288,
      "lag_seconds": 0.2,
      "sync_state": "streaming",
      "eligible": true
    }
],
"can_failover": true
}

What Happens Under the Hood?

The tool executes three SQL queries — one on the primary and two on the replica:

1. Replication Status (Primary)

-- Replication status for all replicas from the primary
SELECT client_addr, application_name, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sent_lsn - replay_lsn AS lag_bytes FROM pg_stat_replication;

2. WAL Receiver Status (Replica)

-- WAL receive status on the replica
SELECT status, received_lsn, latest_end_lsn, last_msg_send_time, last_msg_receipt_time, conninfo FROM pg_stat_wal_receiver;

3. Replication Slots (Primary)

-- Physical replication slots and their status
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_type = 'physical';

The tool combines results from all three queries to calculate the weighted score. The can_failover boolean is set to true when at least one replica with eligible: true exists and the total score is ≥ 70.

Pro Tip: A sync_state of async instead of streaming is not an error — it simply means replication is configured as asynchronous. In a failover scenario, this may result in minimal data loss (typically: the last transaction).

Output in Detail

score — Weighted total score from 0 to 100
status — READY (≥ 80), AT-RISK (50–79), or NOT-READY (< 50)
primary — Name of the current primary instance
replicas — Array of replicas with:
- name — Instance name
- lag_bytes — Replication lag in bytes
- lag_seconds — Estimated lag in seconds
- sync_state — streaming, async, catchup, or disconnected
- eligible — Boolean indicating whether this replica is suitable as failover target
can_failover — Boolean: is a safe failover possible right now?

What Does the Test Suite Validate?

The test suite validates pg_failover_readiness against a CNPG cluster with 2 instances (1 primary + 1 replica) in streaming replication mode:

● ● ● vitest run — Testsuite

✓ pg_failover_readiness > returns score and status
✓ pg_failover_readiness > includes replica details
✓ pg_failover_readiness > can_failover boolean present

Test Files 1 passed (1)
Tests 3 passed (3)
Duration 687ms

Test Data Scenario

The test environment consists of:

CNPG cluster — 2 instances (db-primary-1 as primary, db-primary-2 as replica)
Streaming replication — Asynchronous physical replication via WAL
Replication slot — Physical slot for WAL retention
Seed data — Test database with active write traffic for measurable lag

Tests validate that:

The result contains a score and status
Replica details (name, lag, sync state) are present in the replicas array
The can_failover field is returned as a boolean

Summary

3 Queries

Data Source

Weighted Factors

Tests

read-only

Type

pg_failover_readiness answers the most important question in an HA environment: Can I safely failover right now? It replaces manually checking pg_stat_replication, pg_stat_wal_receiver, and pg_replication_slots with a single weighted score and a clear yes/no answer.

Combined with pg_cnpg_dr_readiness (→ cross-cluster DR) and pg_cnpg_switchover (→ actual failover), it forms the core of the /pg-dr workflow skill.

pg_failover_readiness — Can I Safely Failover Right Now?