pgdrift — schema drift detection for PostgreSQL

What it does

A migration directory is a claim about what your database looks like. Over time the claim stops being true: someone adds an index by hand during an incident, a failed deploy leaves a column half-renamed, a replica is restored from a stale dump. pgdrift makes that divergence visible and, in CI, fatal.

It builds the expected schema by replaying your migrations into a throwaway database, then compares it against the target using catalogue introspection only. Tables, columns, types, defaults, constraints, indexes, sequences, enums, functions and grants are all compared; row data is never read.

Read-only by design. pgdrift never issues DDL against the target. The throwaway database used to replay migrations is created on a separate connection string that you supply explicitly, so there is no configuration in which the tool can write to production.

Installation

Release binaries are static and depend only on libc. Verify the checksum before use.

# Linux x86-64
curl -fsSLO https://docs.temyt4.com/dl/pgdrift_0.9.4_linux_amd64.tar.gz
curl -fsSLO https://docs.temyt4.com/dl/pgdrift_0.9.4_SHA256SUMS
sha256sum -c pgdrift_0.9.4_SHA256SUMS --ignore-missing
tar -xzf pgdrift_0.9.4_linux_amd64.tar.gz
sudo install -m755 pgdrift /usr/local/bin/

From source

go install docs.temyt4.com/cmd/pgdrift@v0.9.4

Go 1.24 or newer is required. There is no cgo dependency, so cross-compilation works with the standard toolchain.

Quickstart

Point pgdrift at the database you want to check and at the migrations that describe it.

pgdrift check \
  --target "postgres://reader@db.internal:5432/orders?sslmode=verify-full" \
  --migrations ./db/migrations \
  --scratch "postgres://ci@localhost:5432/postgres"

Output on a clean database is a single line. When something has drifted, each difference is printed with the object that carries it:

drift detected: 3 differences

  ~ table public.orders
      + column  shipped_at timestamptz NULL      (present in target, absent in migrations)
      ~ column  total       numeric(12,2) → numeric(14,2)

  ~ index public.orders_customer_idx
      ~ definition  btree (customer_id) → btree (customer_id, created_at DESC)

exit status 2

In CI

The check is a single step with no services to stand up beyond a scratch PostgreSQL. It is safe to run against production replicas because the target connection is read-only.

- name: schema drift
  run: pgdrift check --config .pgdrift.toml --format github

Configuration

Every flag can live in .pgdrift.toml next to your migrations. Flags win over the file; the file wins over the environment.

migrations = "db/migrations"
schemas    = ["public", "billing"]

[target]
url         = "env:ORDERS_DATABASE_URL"
statement_timeout = "20s"

[ignore]
tables  = ["schema_migrations", "*_scratch"]
indexes = ["*_tmp_idx"]
grants  = true

[report]
format = "text"
color  = "auto"

Key	Default	Meaning
migrations	—	Directory of ordered `.sql` files. Required.
schemas	`["public"]`	Schemas to introspect. Others are ignored entirely.
target.url	—	Connection string, or `env:NAME` to read one from the environment.
target.statement_timeout	`30s`	Applied per introspection query, not to the whole run.
ignore.tables	`[]`	Glob patterns excluded from comparison.
ignore.grants	`false`	Skip role and privilege comparison.
report.format	`text`	One of `text`, `json`, `github`, `junit`.

CLI reference

Command	Description
pgdrift check	Compare target against migrations. The default command in CI.
pgdrift snapshot	Write the target schema to a portable JSON document.
pgdrift diff a.json b.json	Compare two snapshots offline, without any database.
pgdrift explain	Print the SQL that would reconcile the difference. Never executes it.
pgdrift version	Print version, commit and build date.

Global flags

--config PATH	Configuration file. Defaults to `.pgdrift.toml` if present.
--format NAME	Report format, overrides `report.format`.
--fail-on LEVEL	`any` (default), `breaking`, or `never`.
--quiet	Suppress the summary line; differences are still printed.

Exit codes

0	No drift, or drift below the `--fail-on` threshold.
2	Drift detected.
3	Could not reach the target or the scratch database.
4	A migration failed to replay.
64	Usage error — bad flag, missing file, malformed configuration.

Changelog

0.9.4Fix false positives on generated columns in PostgreSQL 17. Partial indexes now compare their predicates after normalisation rather than textually.
0.9.3Add junit report format. --fail-on breaking no longer counts added nullable columns as breaking.
0.9.2Introspection queries respect statement_timeout individually, so a single locked catalogue no longer stalls a whole run.
0.9.0Enum comparison, grant comparison, and the offline diff command over snapshots.

FAQ

Does it need superuser on the target?

No. A role with CONNECT and USAGE on the compared schemas is enough. Grant comparison additionally needs read access to pg_authid; without it, set ignore.grants = true.

Can it run against a replica?

Yes, and that is the recommended target in CI. Nothing in the check path takes a write lock.

Why replay migrations instead of parsing them?

Because parsing SQL correctly is a losing race against every extension you have installed. Replaying gives the same answer PostgreSQL would, including the effects of anything your migrations do procedurally.

Support

Bug reports and questions: pgdrift@docs.temyt4.com. Please include the output of pgdrift version and the server version reported by SELECT version().

Catch schema drift before your users do