A PostgreSQL MCP server can remain available during failover and still return the wrong answer.
The connection retries against a replica.
The replica is behind.
A conversation combines a pre-failover result with a post-failover follow-up.
Every query succeeds, so the final answer looks complete.
Before production, give each workflow a consistency contract:
- eventual with a disclosed lag budget
- monotonic within one conversation
- read-your-writes
- point-in-time across multiple queries
- primary-only
Then make source identity, schema version, snapshot marker, observed time, and freshness part of material results.
Test more than promotion:
- idle pooled connections
- active transactions
- prepared statements and DNS caches
- interrupted multi-query answers
- bounded retries
- conversation continuity
- schema and policy versions
- paused replay and catch-up
- failback
Never concatenate partial rows from the old primary with rows from the new one. If the answer cannot prove one consistency boundary, discard the partial result and return a structured retryable failure.
Availability is a transport property.
Trustworthy answers need a data contract.
Full test guide: MCP server for Postgres: test failover consistency, not only availability
Top comments (1)
"Availability is a transport property. Trustworthy answers need a data contract." That split is the one I keep re-learning at progressively deeper levels, and your failover case is a level past where I currently sit.
My progression: my health check originally verified that a page returned its expected status code, which caught a dead server and nothing else — a shell that answers while the database behind it is gone looks identical to health. So I added an endpoint that runs a real query. Then I realised the watcher was reading that endpoint's own JSON, trusting a self-reported "database: ok," so I made the watcher run its own query directly and flag any disagreement between the two as its own alarm. Felt thorough at the time.
Your post shows the next gap and I don't have it covered: my direct query proves a database answered, not which one, and not whether it was current. Under your scenario the watcher's own independent query would come back clean from a lagging replica and I'd have two agreeing green signals over stale data — worse than one, because agreement reads as corroboration. Making source identity part of the answer rather than an ambient property of the connection is the fix I'm missing. That's going on the list, and it's the rare case where the failure mode is invisible precisely because every layer succeeded.