How to Detect Schema Drift Between Migrations and a Production PostgreSQL Database

A PostgreSQL schema is the database’s structure: its tables, indexes, and other objects. Migrations are ordered changes that describe how that structure should change. Schema drift happens when production’s objects no longer match the structure defined by the migrations. A manual hotfix, an incomplete deployment, or an unrecorded change can cause this mismatch.

To detect drift accurately, compare production with the schema defined by the latest migration production has applied. The newest migration files may include changes that have not reached production. Comparing production with those files would incorrectly flag those changes as drift. A migration history table shows which changes the migration tool recorded as applied. It does not prove that production’s tables, indexes, and other objects still match those changes.

Compare production with its applied migration revision

A drift check compares production’s current objects with the schema defined by all migrations through the latest one production has applied. The database’s migration history records which migration version it has completed. Later migration files are pending changes, so leave them out of the expected schema.

A migration-aware tool can find the latest migration production has applied, build the corresponding schema in a development database, inspect production, and report differences. For versioned migrations, Atlas’s drift detection works this way. Atlas documents that the atlas migrate drift command leaves the database unchanged. It returns exit code 0 when the schemas match and 1 when it finds drift. Atlas lists the command as an Atlas Pro feature.

For example:

atlas migrate drift \
  --url "$PROD_DATABASE_URL" \
  --dir "file://migrations" \
  --dev-url "docker://postgres/17/dev"

Use a development database with the same PostgreSQL version and relevant configuration as the project. Run the check periodically and before applying migrations. Before changing production, the migration runner can flag or block unexpected differences found by a pre-deployment check. When you add a check to an established database, review the existing differences first. Some objects may be managed outside the migration directory on purpose.

Set the objects and schemas the check covers

A schema comparison finds differences only in the objects it checks. Decide which database schemas production’s application owns, and identify objects managed by another service or operator. Tables, columns, indexes, and constraints are common schema objects. Check views, functions, triggers, and sequences explicitly, or include them in a separate check. Apply the same approach to extensions, grants, ownership, and database-wide roles.

The objects a tool checks depend on the command and its configuration. For example, Atlas says its declarative schema diff command checks schemas and tables by default, including their indexes and constraints. Checking several other object types requires Atlas Pro. Check what your specific command covers; one tool setting does not necessarily include every PostgreSQL object.

For a targeted column inspection, query PostgreSQL’s information_schema.columns:

SELECT table_schema, table_name, column_name, data_type,
       is_nullable, column_default
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name, ordinal_position;

Use this query to investigate differences in columns. It does not compare the entire schema. PostgreSQL’s information schema documentation says these views show only objects the current user can access. An account with limited privileges can therefore return incomplete results. Some PostgreSQL-specific details require system catalogs. These catalogs store database metadata and are specific to PostgreSQL’s implementation.

Use schema dumps when a migration-aware diff is unavailable

A schema-only dump gives you files to compare. Create a separate database from the migrations, then dump its schema and production’s schema:

pg_dump --schema-only "$PROD_DATABASE_URL" > production.sql
pg_dump --schema-only "$EXPECTED_DATABASE_URL" > expected.sql
diff -u expected.sql production.sql

PostgreSQL’s pg_dump --schema-only option exports database definitions without table data. The text diff helps with review, but it does not determine whether two schemas mean the same thing. You need to interpret the differences, and both databases must use consistent scopes and options. For example, --no-owner omits ownership statements, while --no-privileges omits grant and revoke statements.

A database dump does not include every object shared across a PostgreSQL cluster. PostgreSQL documents pg_dumpall for global objects such as roles and tablespaces. Use compatible dump-client versions: PostgreSQL’s current pg_dump documentation says a client cannot dump from a server with a newer major version.

Separate migration-history checks from schema checks

Migration history checks and schema comparisons answer different questions. A history check shows which migrations the migration tool recorded and whether the migration files still match the stored metadata. A schema diff checks whether the objects it covers match the expected schema.

For example, Flyway’s validate command compares migration names, types, and checksums with its schema history. This check can find migration files that were edited, removed, or changed. It does not confirm that every object in production matches the expected schema. Use history validation and schema comparison together.

When a diff reports a difference, identify what it means before changing production. An unexpected object could be an unrecorded hotfix or something another service manages. A missing object could result from a manual deletion or an incomplete deployment. Inspect the specific column, index, constraint, or other object whose definition changed. Investigate a migration-history mismatch separately from a difference between database objects.

For unintended drift, make the correction through the migration workflow so someone can review and reproduce the change. Do not apply generated DDL without review. A diff shows structural differences, but it cannot tell you whether a change is safe for production data or whether the migration owner should make it. Record intentional exceptions, then exclude them explicitly or compare them in a separate scope. Repairing migration history does not fix live database objects. Flyway describes repairing schema history as a metadata operation for certain validation problems.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com