What Does This Skill Do?
/pg-dr is the most impressive skill in the mcp-postgres arsenal. It orchestrates a full disaster recovery drill across two Kubernetes clusters: pre-flight check, replica promotion, verification of the new primary, demote back to the original state, and a final health check. The entire promote/demote cycle completes in under 3.5 seconds.
This is not a toy — it is a validated DR drill that proves your failover works before you need it.
Workflow Phases
- Phase 1 — Pre-Flight:
pg_cnpg_dr_readinesscomputes a readiness score (0–100). The drill only starts if score ≥ 80. - Phase 2 — Replica Status:
pg_cnpg_replica_statuschecks replication lag and WAL position - Phase 3 — WAL Archive:
pg_cnpg_wal_archive_statusverifies WAL archiving is current - Phase 4 — Promote:
pg_cnpg_promotepromotes the replica to new primary - Phase 5 — Verify Promoted:
pg_cnpg_cluster_statusconfirms the new primary - Phase 6 — Demote:
pg_cnpg_demoterestores the original state - Phase 7 — Verify Restored:
pg_cnpg_cluster_statusconfirms restoration - Phase 8 — Final Report: Timing data, result, and total duration
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:
▶ 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)
▶ Calling tools...
→ Phase 1/8: pg_cnpg_dr_readiness { cluster: "hubport-lab" }
Readiness Score: 92 / 100
Primary: db-primary (2 instances), Replica: db-replica (1 instance)
Replication Lag: 0 bytes, WAL Archiving: ✓
→ Score ≥ 80, starting drill
→ Phase 2/8: pg_cnpg_replica_status {}
Replica db-replica-1: streaming, lag 0 bytes
Last received LSN: 0/4A000060, last replayed LSN: 0/4A000060
→ Phase 3/8: pg_cnpg_wal_archive_status {}
Last archived WAL: 00000001000000000000004A
Archive command: OK, Last failure: none
→ Phase 4/8: pg_cnpg_promote { cluster: "db-replica", method: "spec_patch" }
Promoting db-replica...
Promotion complete in 1.2s
db-replica-1 is now primary
→ Phase 5/8: pg_cnpg_cluster_status { cluster: "db-replica" }
Cluster: HEALTHY, Primary: db-replica-1, Instances: 1/1 ready
→ Phase 6/8: pg_cnpg_demote { cluster: "db-replica", method: "spec_patch" }
Demoting db-replica back to replica...
Demote complete in 0.8s
db-replica-1 is replica again
→ Phase 7/8: pg_cnpg_cluster_status { cluster: "db-primary" }
Cluster: HEALTHY, Primary: db-primary-1, Replicas: 2 (incl. db-replica)
→ Phase 8/8: DR Drill Final Report
╔══════════════════════════════════════════════╗
║ DR Drill: SUCCESSFUL ║
╠══════════════════════════════════════════════╣
║ Readiness Score : 92 / 100 ║
║ Promote Duration : 1.2s ║
║ Demote Duration : 0.8s ║
║ Total Duration : 3.4s ║
║ Cluster Status : Restored ║
║ Data Loss : 0 bytes ║
╚══════════════════════════════════════════════╝
MCP Protocol (Skill Definition)
const result = await client.callTool("/pg-dr", { "cluster": "hubport-lab" });
// Response:
{
"skill": "/pg-dr",
"tools": [
"pg_cnpg_dr_readiness",
"pg_cnpg_replica_status",
"pg_cnpg_wal_archive_status",
"pg_cnpg_promote",
"pg_cnpg_cluster_status",
"pg_cnpg_demote",
"pg_cnpg_cluster_status"
],
"phases": 8,
"gate": { "min_readiness_score": 80 },
"output": {
"result": "SUCCESS",
"readiness_score": 92,
"promote_duration_ms": 1200,
"demote_duration_ms": 800,
"total_duration_ms": 3400,
"data_loss_bytes": 0,
"cluster_restored": true
}
}
What Happens Under the Hood?
The skill orchestrates seven MCP tools across two Kubernetes clusters. The key differentiator: it uses the spec_patch method for promote and demote, which leverages the CNPG operator rather than direct PostgreSQL commands.
Phase 1: Pre-Flight (Readiness)
Checks replication lag, WAL archiving, cluster health, and instance availability. Computes a weighted score. If the score is below 80, the drill is aborted — no risk with unstable replication.
Phases 2–3: Replica and WAL Status
Detailed check of streaming replication and WAL archiving. Ensures the replica is fully caught up and all WAL segments are archived.
Phase 4: Promote
Promotes the replica to primary via a spec_patch on the CNPG cluster object. This is the safest method because the CNPG operator orchestrates the entire failover process:
- Streaming connection is severed
- Recovery completed
- Timeline incremented
- New timeline published
Phase 5: Verify Promoted
Confirms the former replica is running as the new primary and write access is available.
Phase 6: Demote
Restores the cluster to its original state — the former replica becomes a replica again, the original primary resumes leadership.
Phases 7–8: Verify + Report
Confirms full restoration and generates a timing report with promote duration, demote duration, and total time.
Why is this impressive? A manual DR drill typically takes 30–60 minutes and requires coordinated actions across terminals, kubectl, and PostgreSQL clients. /pg-dr does the same in 3.5 seconds — fully automated, validated, and reproducible.
What Does the Test Suite Validate?
The test suite runs the full promote/demote cycle against a CNPG lab environment:
→ expects spec_patch method for promote and demote
→ readiness gate: score ≥ 80
→ total cycle: 3.4s
Test Files 1 passed (1)
Tests 1 passed (1)
Duration 4.2s
Test Data Scenario
The lab environment consists of:
- k8s-primary — Kubernetes cluster with CNPG cluster "db-primary" (2 instances)
- k8s-replica — Kubernetes cluster with CNPG cluster "db-replica" (1 instance, replica)
- Streaming Replication — Synchronous replication between clusters
- WAL Archive — S3-compatible archive for point-in-time recovery
- Test gate — The test is skipped if no DR configuration is present
Summary
/pg-dr is proof that disaster recovery does not have to remain just a concept in a runbook. A validated, automated DR drill in under 4 seconds — cross-cluster, with promote, demote, and verification. Run it weekly and sleep better at night.