MCP server for Postgres: test failover consistency, not only availability
A PostgreSQL MCP server can remain available during failover and still return the wrong answer.
The connection is retried against a replica. The replica is seconds behind. A conversation combines a pre-failover result with a post-failover follow-up. A pool keeps stale sessions alive. The answer looks complete because every individual query succeeded.
Production testing must verify consistency, freshness, and identity—not only reconnection.
Define the answer consistency contract
Classify each workflow before testing:
- eventual: a bounded lag is acceptable and disclosed;
- monotonic: one conversation must not move backward in observed state;
- read-your-writes: a user must observe a completed approved change;
- point-in-time: multiple queries must describe one declared snapshot;
- primary-only: the decision cannot tolerate replica delay.
Without a contract, “the database answered” becomes the accidental standard.
Make source identity and freshness part of the result
Return a source role, server or cluster identity, transaction or snapshot marker where available, schema version, observed timestamp, and freshness status with material answers.
The model should not guess whether two results came from the same state. If lag cannot be measured or freshness exceeds the workflow budget, return a structured stale or unavailable status.
Pair this with freshness windows for AI database answers.
Build a deterministic failover fixture
Use records whose transitions are easy to identify: order 100 moves from pending to paid, invoice 200 moves from open to settled, and an aggregate count changes from 40 to 41.
Record the commit marker and expected results before, during, and after failover. Include a schema migration that adds a compatible field and a policy change that removes access to one record.
The fixture should detect stale rows, mixed snapshots, missing policy changes, duplicate operations, and silent fallback to a differently configured source.
Exercise pool and DNS behavior
Failover does not replace every connection at once. Test idle pooled connections, active transactions, prepared statements, DNS caches, connection-string target attributes, health checks, and pool recycling.
Verify that a connection accepted as healthy is also connected to an allowed role and schema version. A reachable standby with the wrong migration or policy state is not ready for the tool.
For capacity behavior, see MCP database connection pool sizing.
Test in-flight transaction boundaries
Interrupt a single query, a multi-query answer, a read-only transaction, and a long-running aggregate. Determine which operations are safe to retry and which must restart from a new declared snapshot.
Do not concatenate partial rows from the old primary with rows from the new primary. If an answer cannot prove one consistency boundary, discard the partial result and return a retryable structured failure.
Keep retries from changing meaning
Bound retries by deadline, attempt count, and failure class. Record each attempt under one logical operation ID, including source identity and outcome.
For reads, a retry may observe newer state. That can be correct, but it must not be presented as the same snapshot. For mutations, use a separate approved path with idempotency and durable receipts; a connection error does not prove that the database did nothing.
Test conversation continuity
Ask for a revenue total, trigger failover, then ask “which accounts explain that increase?” The follow-up depends on the first result's source and snapshot.
The server should either pin the supported snapshot, recompute both parts under the new state, or explain that the earlier result is no longer comparable. Quietly mixing states creates a coherent story from incompatible evidence.
Include schema and authorization drift
A promoted node can be available before every schema, extension, role, or policy invariant is present. Assert required migration versions, exposed objects, grants, RLS state, function definitions, and semantic-context version before declaring readiness.
Use Postgres MCP schema-change readiness for the migration test set.
Observe lag as a workflow budget
Track replay position or an application heartbeat, not only a generic “replica healthy” flag. Translate lag into the decision: “data current through 10:02:14 UTC; workflow budget is 30 seconds.”
Test byte backlog, time since last replayed transaction, paused replay, network partition, burst catch-up, and clock skew. A low time-lag number can be misleading when no new writes exist.
Concrete example: incident triage
An operator asks which deployments correlate with a spike in failed jobs. The first query runs on the primary at snapshot A. During failover, the deployment table is current on the promoted node but job events lag.
The MCP server detects that the two sources cannot satisfy the point-in-time contract. It refuses to correlate them, reports the freshness gap, and retries only after both datasets pass the declared marker. The useful outcome is a delayed trustworthy answer, not an immediate false cause.
Failover test checklist
- Assign a consistency and freshness contract to each workflow.
- Return source, schema, snapshot, and freshness evidence.
- Test idle pools, active transactions, DNS, and prepared statements.
- Interrupt single- and multi-query answers.
- Bind retry attempts to one logical operation.
- Continue a conversation across failover.
- Assert schema, extension, role, and policy readiness.
- Measure replay progress against the workflow budget.
- Test failback and repeated failover, not only one promotion.
Where Conexor fits
Conexor provides MCP infrastructure for connecting AI clients to PostgreSQL and other data sources through governed tools. Production operation should keep source identity, query controls, freshness, schema context, and result evidence explicit when the database topology changes.