Detect Schema Drift Between Environments with migra

Compare two live Postgres schemas with migra, review the generated SQL diff, and push the drift back into versioned migrations.

~25 mindatabasepostgresci

Task

Diff staging and production schemas, confirm which differences are drift versus pending migrations, and reconcile them into the migration history.

When to use this

A hotfix added a column directly in production. Months later the migration history and reality disagree, and a fresh staging restore behaves inexplicably.

Prerequisites

migra installed, typically via pipx or Docker
Read access to both database schemas
The migration history of the project at hand

Steps

1. Run the diff between the two live schemas.

mgra postgresql://user@staging/db postgresql://user@prod/db 2>/dev/null || migra postgresql://user@staging/db postgresql://user@prod/db

Expected output: migra prints the SQL that would transform the first schema into the second, making every difference concrete.

2. Classify each hunk as pending migration or drift.

migra "$STAGING_URL" "$PROD_URL" | grep -E "^(ALTER|CREATE|DROP)" | head -30

Expected output: A column that exists in prod but in no migration file is hand-applied drift; a column missing from prod only is an unapplied migration.

3. Convert confirmed drift into a real migration.

-- migrations/2026_10_22_0930_reconcile_prod_drift.sql
ALTER TABLE invoices
    ADD COLUMN IF NOT EXISTS currency_code text NOT NULL DEFAULT 'USD';

Expected output: The reconciliation migration is idempotent with IF NOT EXISTS, so it applies cleanly on drifted and clean databases alike.

4. Verify environments converge after applying it.

migra "$STAGING_URL" "$PROD_URL" | wc -l

Expected output: The diff length drops to the noise floor, and both schemas now match the migration history.

5. Block future drift with a CI check.

- name: Schema drift gate
  run: |
    migra "$CI_STAGING_URL" "$PROD_URL" --schema public > drift.sql || true
    test ! -s drift.sql || { cat drift.sql; exit 1; }

Expected output: The pipeline fails when live schemas diverge, so the next hotfix applied by hand fails loudly the next morning.

Troubleshooting

Problem

Diff is full of noise from extensions or system objects

Solution

Scope migra to the schemas you own with the schema option, and filter out objects created by monitoring tools into dedicated schemas.

Problem

Drift exists only in permissions, not structure

Solution

migra compares structure; grant drift needs a separate dump of ACLs or an infrastructure-as-code review of roles.

Problem

Cannot point migra at production safely

Solution

migra only reads catalogs and never writes; if policy still forbids it, snapshot production to a scratch clone and diff against that.

Limitations

migra output is directional SQL, not a migration plan; review and edit before applying anywhere.
The diff compares structure only; data-level drift such as divergent seed rows is invisible to it.

The history lies, the database does not

Migration files describe an intended history; the live schema is the truth. Every hotfix applied by hand in an emergency widens the gap until staging and production quietly disagree about columns and constraints. migra reads both catalogs and prints the SQL that would reconcile them, turning an invisible liability into a reviewable diff that ends as a proper migration file.

# What SQL would make staging equal production?
migra "$STAGING_URL" "$PROD_URL"

# Keep the gate green
migra "$CI_STAGING_URL" "$PROD_URL" > drift.sql || true
test ! -s drift.sql || { cat drift.sql; exit 1; }

Reconcile into history, not over it

The temptation is to apply the migra output directly to the lagging environment and call it fixed. That reproduces the original sin at scale. Instead, write the reconciliation as an idempotent migration, run it everywhere, and let the history and reality converge permanently, with the migration file as the record of what the hotfix did.

Make drift a build failure

A one-time diff finds yesterday drift; a CI gate prevents tomorrow. Comparing the live schemas on a schedule, and failing when the diff is non-empty, converts hand-applied changes into next-morning alerts instead of quarter-later mysteries. The gate needs read-only credentials only, so it is cheap to authorize.