MCP server for Postgres: test schema-change readiness before production
A PostgreSQL MCP server can work perfectly on Friday and become confidently wrong after Monday's migration.
The database is available. The tool still connects. SQL may even execute. But a renamed column, changed view grain, new enum value, revised permission, or stale cached schema can change the meaning of the answer.
Before production, test schema-change readiness as a full lifecycle—not a one-time discovery feature.
Inventory every versioned dependency
Record the PostgreSQL version, schema migration version, exposed schemas and views, database role, row-level policies, extensions, tool catalog, tool input/output schemas, semantic descriptions, metric definitions, and MCP server build.
A test result is valid only for the versions it exercised. “Schema discovery passed” is incomplete if the discovered context cannot be tied to the source state that produced it.
Start with schema context for MCP database agents.
Define the change contract
For each migration, describe more than the DDL. Identify changed objects, compatibility window, old and new consumers, business-meaning changes, permission changes, expected context refresh, validation queries, rollback or roll-forward path, and the point when old context becomes invalid.
A nullable column addition may be backward-compatible for SQL but still change the meaning of “complete” records. A view can preserve its column names while changing from one row per account to one row per subscription.
Test renamed and removed objects
Rename a column used by a tool, remove a deprecated view, and move an object to another schema. Verify that the server detects the mismatch before presenting an answer.
The safe behavior may be to refresh context, select a versioned replacement, or return a structured stale-schema error. Silently falling back to a similarly named object is not recovery.
Test quoted identifiers, search path changes, aliases, and old prepared statements as well as direct object names.
Test changes that preserve SQL but alter meaning
The dangerous changes are often executable:
- a reporting view changes grain;
- a status gains a new value;
- a timestamp changes business interpretation;
- a soft-delete rule moves to another table;
- a current-price column replaces a historical captured price;
- a join becomes one-to-many and multiplies totals.
Use metric contracts and deterministic fixtures to verify the answer, not only the query. Return the source and definition version with material results.
Make schema caches observable
If the MCP server caches schema context, expose the source version, discovery timestamp, refresh reason, and cache status. Define maximum age and invalidation triggers.
Test event-driven invalidation, scheduled refresh, manual refresh, process restart, multiple server replicas, and a missed migration event. Verify that replicas do not serve different tool descriptions for the same source version.
When the cache cannot prove freshness, fail closed for affected tools instead of labeling old context as current.
Test permission drift separately
A schema refresh does not guarantee authorization stayed correct. Add a column containing sensitive data, grant access to a new view, revoke a role, change an RLS policy, and alter a function's security mode.
The exposed tool surface should remain bound to approved objects and fields. A new database grant should not automatically become model-visible merely because discovery found it.
Review the connection baseline at connect PostgreSQL to Claude and keep the runtime identity scoped to the intended surface.
Use expand-and-contract for tool contracts
Database migrations often use an expand-and-contract sequence. Apply the same idea to MCP tools.
- Add the new object or versioned view.
- Expose a new tool or result field without removing the old one.
- Run old and new contracts against the same fixtures.
- Move clients to the new contract deliberately.
- Observe usage and error rates during the compatibility window.
- Remove the old contract only after consumers and approvals are clear.
Do not change a field's meaning in place while preserving its name. Version the contract when callers need to make a different decision.
Test query plans and workload after the change
Schema changes can preserve correctness while damaging source reliability. A new filter, join, type cast, or view layer may produce a plan that scans far more rows.
Run representative bind values and compare plan shape, rows, buffers, duration, and result bytes. Keep statement timeout, concurrency, and queue limits active during the test.
Use the MCP server for Postgres query-plan regression budget for a deeper performance gate.
Test in-flight conversations
A user may begin a conversation under schema version 41 and continue after version 42 is deployed. Decide whether old tool calls can finish, whether follow-ups require a refresh, and how the client learns that prior context is stale.
Bind tool results to schema and semantic versions. If a follow-up depends on an older result, either preserve a supported compatibility path or require a new query under the current contract.
Exercise failure and rollback states
Interrupt the migration between expand and backfill, after the new view is created, during context refresh, and after one MCP replica updates. Test failed rollback, roll-forward, and a database failover to a node with different migration state.
The server should not infer readiness from connection success. Readiness must include required object versions, permissions, context freshness, and validated invariants.
Concrete example: revenue view v4 to v5
A team replaces recognized_revenue_v4 with v5 to handle chargebacks and a new currency source. The column names remain similar, but the business rule changes.
The migration adds v5 without removing v4. The MCP server discovers it but does not expose it automatically. Contract tests compare seeded paid, refunded, chargeback, test, deleted, and multi-currency records. The new tool returns definition v5 and source freshness. Clients move during a compatibility window. Only then is v4 removed and its cached context invalidated.
The useful gate is not “the new view exists.” It is “the new meaning is tested, versioned, authorized, observable, and reversible.”
Schema-change test checklist
- Bind tools and tests to database, schema, policy, semantic, and server versions.
- Rename and remove an exposed object.
- Change view grain without changing its visible shape.
- Add an enum value and a sensitive column.
- Change grants, RLS, and function security.
- Miss a cache-invalidation event on one replica.
- Compare plans with representative parameters.
- Continue an in-flight conversation across the deployment.
- Interrupt, roll forward, and retire the old contract.
Where Conexor fits
Conexor provides MCP infrastructure for connecting AI clients to PostgreSQL and other data sources through governed tools. Production readiness includes versioned schema context, scoped exposure, query controls, and evidence that tool contracts still match the source after change.