November 5, 20264 min read

How to Detect PostgreSQL Schema Drift with Ptah

Detect PostgreSQL schema drift with Ptah: compare a desired schema to a live database, catch an unreviewed column in CI, and choose a safe response.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

The migration directory and database both say version 1 is current. Then someone adds a column directly to PostgreSQL. The next release still expects the schema in Git, while the live table has changed outside review. Ptah detects that difference with a read-only check that can fail CI.

We reproduced this with Ptah 0.21.0 and PostgreSQL 18.6. migrations status exited 0 after the manual change because the recorded migration was intact. schema drift exited 1 and classified the extra column as a destructive reconciliation. The runnable files and complete output are beside this post.

Start with a schema and a clean migration history

The desired schema declares one orders table, its validated amount check, and an index:

CREATE TABLE public.orders (
    id bigint PRIMARY KEY,
    status text NOT NULL DEFAULT 'pending',
    total_cents integer NOT NULL CHECK (total_cents >= 0)
);

CREATE INDEX orders_status_idx ON public.orders (status);

We applied the matching versioned migration and inserted two orders. The migration directory’s ptah.sum was hashed before application. At this point, both checks exited 0:

ptah migrations status --db-url "$DB_URL" --migrations-dir migrations --verify-sum --exit-code
ptah schema drift --schema-file schema.sql --db-url "$DB_URL" --schemas public

migrations status checks the recorded revision and directory integrity. schema drift compares the desired schema with the live PostgreSQL catalog. Both are useful because they answer different questions.

Detect an out-of-band PostgreSQL change

We ran this SQL outside the migration workflow:

ALTER TABLE public.orders ADD COLUMN support_note text;

The existing migration file, checksum, and revision row did not change. migrations status --verify-sum --exit-code still exited 0:

Current Version: 1
Total Migrations: 1
Applied Migrations: 1
Pending Migrations: 0
Out-of-order Migrations: 0
Status: ✅ Database is up to date

Now run the schema check that belongs in CI:

ptah schema drift --schema-file schema.sql --db-url "$DB_URL" --schemas public

It exited 1 and reported:

Schema drift detected (highest severity: destructive).
Failure threshold: all. Failing: true.
Database: postgres://postgres:***@127.0.0.1:55491/drift_lab?sslmode=disable

Findings:
- columns_removed: 1 (destructive)

columns_removed describes what reconciliation would do: the live database has support_note, but the desired schema does not. Removing it could discard data. The command did not remove anything. A separate ptah schema compare --exit-code named public.orders and printed the proposed SQL; its full output is available for review.

For a CI step, use the same schema drift command and its exit status. Ptah returns 0 when the checked schema matches, 1 when drift crosses the selected threshold, and 2 when the check cannot run. We verified all three cases, including an unreachable database for exit 2. Automation should treat 2 as a failed check, never as proof of a matching schema. Ptah can also write a JSON report when CI needs structured findings. The compare and drift guide covers more output formats and thresholds.

Investigate before changing the database

We inspected the unexpected column and checked whether it held values:

 orders | notes_with_values
--------+-------------------
      2 |                 0
(1 row)

This synthetic change was known to be unreviewed, and its new column was empty. After review, we ran the revert SQL without CASCADE. schema drift then exited 0 with No schema drift detected. The two original order rows were unchanged.

If an unexpected column contains useful data, preserve it while deciding whether to adopt it. Update the desired schema and arrange a reviewed migration that can reproduce that state in other environments. Do not execute the printed reconciliation merely because the drift check failed. The PostgreSQL DROP COLUMN documentation explains its effect on stored values and dependent objects.

Exclude only a deliberately unmanaged table

We also created an audit_events table to represent an object managed by another process. Ptah reported it as drift. After explicitly deciding it was outside this schema’s ownership, we scoped the check:

ptah schema drift --schema-file schema.sql --db-url "$DB_URL" --schemas public --ignore tables=audit_events

That command exited 0 with No schema drift detected. The recorded reports show the difference before and after the exclusion. Keep the ignore rule narrow: it removes that table from the check.

The gate checks the declared public.orders objects while leaving audit_events to its owner. PostgreSQL reported the sample amount check as validated (convalidated = t). A constraint added with NOT VALID raises a different question about existing rows: Ptah can plan VALIDATE CONSTRAINT when the desired schema requires it, as the PostgreSQL support guide describes. Use the PostgreSQL object guide to choose the scope your team owns.

For a new Ptah evaluation, start with the existing-database trial. The visual drift guide shows the same idea in a local SQLite viewer. This PostgreSQL example gives you the command and exit codes to put the check into a deployment workflow today.

Put Ptah to work

Example files

← All articles