Repairing production schema drift without making it worse

Schema drift often announces itself through an application error that looks local.

A column is missing. An enum value is unexpected. A table exists in the migration history but not in production. The tempting response is to connect to the database and add whatever the error mentions.

That can make the immediate exception disappear while moving the database further away from any state the repository knows how to reproduce.

When application code and production schema disagree, I treat the database as evidence before I treat it as something to edit.

First identify all four versions

"What version is production?" is not one question.

I want to identify:

1. The source revision that defined the expected schema.
2. The migration head in that source revision.
3. The migration revision recorded by the production database.
4. The physical tables, columns, constraints, and types that actually exist.

The migration table can lie by omission. A manual change may exist without a migration. A failed migration may have modified part of the schema before the recorded revision changed. A restored database may contain objects from a different release.

History and physical state both matter.

Stop changing things while diagnosing

Automatic deploys, workers, and application restarts can add noise while the schema is being inspected. If the system permits it, I freeze deployments and reduce writes through the affected path.

I also record a precise start time for the investigation. That creates a useful boundary for logs and metrics. "Recent errors" is much less useful than "errors after 14:32 UTC, when the affected service was restarted."

Take a backup and test the backup artifact

Before a repair, I want a database-native backup appropriate for the recovery plan. For PostgreSQL, a custom-format dump is often useful because it supports inspection and selective restoration.

Creating the file is not the end of the step. I inspect its catalog with the restore tooling and record its checksum, size, database identity, and creation time.

That still does not prove full recoverability, but it catches the embarrassing case where the emergency backup is empty, truncated, or not actually a database dump.

The backup should be stored away from the path I am about to modify.

Reconstruct the divergence

I compare migration history with the physical schema.

Useful questions include:

• Which expected revisions never ran?
• Did a later revision assume an object that is missing?
• Are there objects that no migration creates?
• Did a type or constraint drift while the column itself remained present?
• Is the running application newer than the migration job?
• Did two deployment paths apply migrations differently?

The goal is not merely to find the missing column. It is to explain how the system reached this state. Otherwise the next deployment may recreate the problem.

Prefer the repository's migration path

If the recorded revision is genuinely behind and the migration chain is valid, applying the normal upgrade path is safer than transcribing schema changes by hand.

The migration code captures ordering, data transforms, indexes, constraints, and compatibility decisions that a quick ALTER TABLE can miss.

Manual repair is justified only when evidence shows the migration path cannot correctly reconcile the current physical state. Even then, I want the smallest repair that returns the database to a state represented by the migration history.

I avoid broad cleanup during an incident. An unrelated old index or oddly named constraint can wait.

Validate at three levels

After the repair, I do not stop at "the migration command succeeded."

Schema validation

Confirm the expected revision and inspect the specific tables, columns, types, indexes, and constraints involved.

Application validation

Restart only the services that need the new schema. Run focused reads and writes through the affected path. Inspect logs after the restart boundary.

Journey validation

Exercise the user journey that originally failed. A schema can match the model while the application still fails because a background worker, cached statement, provider callback, or old container is out of date.

Fix the deployment path

An emergency repair is incomplete if the deployment process can create the same divergence again.

I want one durable path that:

text
identifies exact source and image
  -> checks current database revision
  -> applies migrations once
  -> verifies the expected head
  -> starts or refreshes dependent services
  -> confirms the exact image is healthy

If migrations are applied by an operator sometimes, an application startup hook sometimes, and a CI job at other times, production has three competing authorities.

What I record afterward

The useful incident note contains facts rather than drama:

• Expected and observed revisions.
• Physical drift found.
• Backup artifact and validation.
• Commands or migrations applied.
• Services refreshed.
• Verification performed.
• Root cause in the delivery path.
• Follow-up that prevents recurrence.

Schema drift is frightening because state carries history. The safest repair respects that history, changes as little as possible, and leaves the system in a state the next deployment can understand.