Parity Before Promotion: Attesting Database Schema Equivalence Across Recovery Estates

Replication certifies rows, not structure

A recovery estate can pass every data-plane check a technology leader cares to run and still fail the moment an application connects to it. Streaming replication, storage-level mirroring and snapshot shipping all attest that bytes arrived. They do not attest that the destination database will accept the queries, functions, grants and query plans that the application expects. Schema drift is the gap between those two assurances, and as of 2026 it remains one of the more common causes of a failover that succeeds technically and fails operationally.

For UK regulated enterprises, the distinction matters because operational resilience obligations are framed around important business services, not around replication lag. A regulator will not ask whether the standby cluster was in sync at 02:00. It will ask whether the firm could have continued to serve customers, and whether it can evidence that the recovery capability was tested. Schema parity is a precondition for answering both questions honestly.

Why replication hides the problem

Physical replication carries data definition language (DDL) to the standby, so the naive assumption is that structure follows data. In practice, drift arrives through several side doors. Logical replication and change data capture pipelines typically replicate rows and not DDL, so a migration applied to the primary can leave the subscriber behind. Cross-engine recovery estates, increasingly common where firms are consolidating onto PostgreSQL or moving away from commercial engines, cannot rely on DDL propagation at all. Manual hotfixes applied during an incident, extensions installed on one node and not the other, and restore-from-backup rehearsals that use a different baseline image all introduce divergence that no replication monitor will flag.

The result is a familiar pattern in recovery tests: the database starts, the data looks broadly correct, and an application team discovers within minutes that a stored procedure is missing, a collation differs, or a required extension was never installed on the recovery host. The failure is not a data loss event. It is an attestation gap.

The comparison surface

Schema parity is not a single check. It is a set of comparisons that should be defined once, automated, and reported as an evidence pack rather than a green tick.

The core comparison set for most estates includes:

• Engine identity. Vendor, major version, minor version and patch level. A recovery estate running one minor release behind can behave differently on planner behaviour, security patches and extension compatibility.
• Extensions and modules. Name, version and installation schema. PostgreSQL extensions are the obvious case, but the principle applies to Oracle options, SQL Server features and MySQL plugins.
• Collations, character sets and locale. These govern sort order, comparison semantics and index usability. A difference here can change query results without changing a single row.
• Object inventory. Tables, columns, data types, nullability, defaults, identity and sequence definitions, partitioned table structures, indexes and constraints.
• Programmable objects. Functions, procedures, triggers, views and materialised views, compared by definition hash rather than by name.
• Security and policy objects. Roles, grants, row-level security policies, masking rules and ownership. Recovery estates frequently run with broader privileges than production, which is itself a control weakness worth recording.
• Configuration parameters. Time zone, date style, connection limits, work memory and other settings that influence behaviour under load.

Turning comparison into evidence

The most defensible approach is to treat schema as an artefact with a fingerprint. Generate a schema-only dump from each estate, normalise it to remove environment-specific noise such as tablespace paths and ownership IDs, and hash it per object rather than per database. Per-object hashes let you report drift precisely and let an operator see whether a difference is cosmetic or material.

Store the output in the same evidence repository used for other recovery attestations. Each record should carry the timestamp, the operator or automation identity, the source and target estate identifiers, the comparison tool version and the resulting drift register. Connection strings should be redacted; the evidence needs to show what was compared, not how to reach it.

Run the comparison nightly and on every change window that touches the database tier. Attest the result before any failover test, not during it. A drift report produced after the application has already failed is a post-mortem input, not a control.

Where it belongs in the recovery calendar

Schema parity should sit upstream of the failover test, not inside it. A practical cadence for tier-one services is a full comparison four weeks before the scheduled test, a remediation window, and a re-check within twenty-four hours of the test start. Quarterly full attestation is a reasonable baseline for important business services, with monthly spot checks on the objects most frequently changed.

The check should also be triggered by events rather than only by calendar: any production migration, any extension installation, any engine patch and any change to the recovery baseline image. These are the moments when drift is introduced, and they are the moments when it is cheapest to correct.

The exception register and the board view

Not every difference warrants remediation before a test. Some drift is tolerated deliberately, for example where a recovery estate runs a reporting-only schema or where an extension is intentionally absent. The control is not zero drift. The control is a documented decision.

Maintain an exception register with an owner, a rationale, a compensating control and an expiry date. Report the register alongside the parity evidence so that reviewers can see the difference between an estate that is clean and an estate whose divergence has been accepted. That distinction is what turns a technical comparison into a governance artefact that a board or an operational resilience committee can rely on.

A practical first pass

Start with three important business services and the databases behind them. Generate schema-only dumps from production and recovery, normalise and hash them, and publish the drift list to the owning teams. Most firms find the first pass uncomfortable and the second pass routine. The objective is not a perfect match on day one. It is a repeatable, evidenced answer to a question every failover test implicitly asks: when the recovery estate takes over, will it recognise the application that arrives?