DEV Community: Eggletric

How to compare two PostgreSQL schemas

Eggletric — Tue, 28 Jul 2026 12:13:00 +0000

You have two Postgres databases that are supposed to have the same schema — local and staging, staging and production, or two customer deployments — and you need to know whether they actually do.

This post walks through doing it with nothing but pg_dump and standard Unix tools, gets the approach to a genuinely usable state, and is honest about where it stops being enough.

The naive version

pg_dump --schema-only "$DB_A" > a.sql
pg_dump --schema-only "$DB_B" > b.sql
diff -u a.sql b.sql

This works, in the sense that real differences will appear in the output. The problem is what appears alongside them. On any two databases with separate histories, you'll see:

Ownership and privilege lines (ALTER TABLE ... OWNER TO ...) differing because the role names differ
Version-dependent preamble — SET statements that vary with the server and pg_dump version
Object ordering differences. pg_dump orders output by dependency and OID, and OIDs reflect each database's creation history. The same table can appear at a different position in each dump
Comment lines (-- Name: users; Type: TABLE; ...) that shift with the ordering
Representation differences — a column created as serial versus one created as an identity column dumps as structurally different SQL even when the runtime behavior is equivalent

The signal is in there, but you're reading dozens of hunks to find the three that matter. And attention degrades: after a screen of noise, humans start scrolling past real differences too.

Step 1: cut the flags

Two options remove entire noise categories at the source:

pg_dump --schema-only --no-owner --no-privileges "$DB_A" > a.sql
pg_dump --schema-only --no-owner --no-privileges "$DB_B" > b.sql

If you don't intend to compare grants and ownership — and across environments you usually don't, since roles legitimately differ — remove them from the dump rather than filtering them later.

Be clear about what this trades away, though. With these flags, privilege drift is invisible by construction: a GRANT quietly missing in production — or an extra one quietly granted — will never appear in this comparison, and a permissions gap can matter more than a mystery column. If privileges are part of what you need to verify, keep --no-owner, drop --no-privileges, and absorb the legitimate role-name differences with the allow-list from Step 3 instead. (Credit to a commenter on the previous post for calling out that the original version of this article took the noise reduction without stating its cost.)

Step 2: normalize

The remaining big two are comments/preamble and ordering. Both can be handled with a short script:

#!/usr/bin/env bash
# normalize-schema.sh — make pg_dump output diff-friendly
set -euo pipefail

normalize() {
  grep -vE '^(--|SET |SELECT pg_catalog|\\(un)?restrict)' "$1" |
    sed -E 's/[[:space:]]+$//' |
    awk 'BEGIN{blank=0} /^$/{blank++; if(blank>1) next} /./{blank=0} {print}' |
    awk 'BEGIN{RS=""; ORS="\0"} {print}' |
    sort -z |
    tr '\0' '\n'
}

normalize "$1" > "${1%.sql}.normalized.sql"

Line by line: drop comment lines, SET statements, catalog selects, and \restrict/\unrestrict markers (recent pg_dump versions emit these with a random token that differs on every run — a guaranteed false positive if left in); strip trailing whitespace; collapse blank-line runs; then treat each blank-line-separated block as a record and sort the blocks.

./normalize-schema.sh a.sql
./normalize-schema.sh b.sql
diff -u a.normalized.sql b.normalized.sql

The interesting step is the last one. Treating each blank-line-separated block as a record and sorting the blocks means every CREATE TABLE, CREATE INDEX, and ALTER TABLE ... ADD CONSTRAINT statement lands at a deterministic position regardless of the order pg_dump emitted it. Ordering noise disappears entirely; what's left in the diff is actual structural difference.

To put numbers on it: I tested this against two databases with three planted differences (a NOT NULL mismatch, an extra column, a production-only index) plus deliberately different object creation order. The naive diff was 141 lines. Normalized: 71 lines, all three differences clearly visible, and — verified separately with two identically-schemed databases built in different order — zero false positives from ordering. The output is also deterministic: same input, byte-identical output, every run.

This gets you surprisingly far. For a periodic "are these two databases still the same" check, it's most of the way there.

What it still can't see through

Some noise is semantic, and text processing can't normalize it:

serial vs. identity. One database was built with serial, the other migrated to GENERATED BY DEFAULT AS IDENTITY. The dumps differ substantially; the behavior barely does. Whether this counts as drift is a judgment call — it may be a migration in progress.
Default expression spelling. now() vs. CURRENT_TIMESTAMP, '0'::integer vs. 0. Same effect, different text.
Type aliases. timestamp without time zone is stable in dumps, but defaults and check constraints echo whatever spelling they were created with.
Constraint names. Auto-generated names (users_email_key, fk_orders_user_id) can differ between databases even when the constraints are identical, and every dependent line differs with them.

Each of these produces a diff hunk that a human has to look at and classify. Which is fine at low volume — the point of normalization is to get the volume low enough that classification is feasible.

Step 3: make it a drift check

Once the comparison is cheap, run it on a schedule. The most useful variant compares what migrations claim against what production is:

# 1. Build the expected schema: replay all migrations on a scratch database
createdb scratch_expected
migrate -database "$SCRATCH_URL" up   # your migration tool here

# 2. Dump both sides and normalize
pg_dump --schema-only --no-owner --no-privileges "$SCRATCH_URL" > expected.sql
pg_dump --schema-only --no-owner --no-privileges "$PROD_URL"    > actual.sql
./normalize-schema.sh expected.sql
./normalize-schema.sh actual.sql

# 3. Fail loudly on any difference
diff -u expected.normalized.sql actual.normalized.sql

Wire that into CI as a nightly job and you've closed the biggest gap in a migration-based workflow: drift now surfaces in days, not on the next incident.

One thing you will immediately need: an allow-list. Some differences are intentional — the production-only index, the replica-only tuning — and they'll show up every night. Grep them out with a maintained pattern file, and accept the trade-off you've just made: the allow-list is itself a small new convention someone has to maintain. In exchange, your intentional drift is now written down for the first time, which is more than most teams have.

Where DIY stops

Everything above answers detection. If detection is all you need — you review the nightly diff and fix things through your normal migration flow — the scripts on this page are honestly enough, and you should use them.

What they don't give you:

Applying the difference. The diff shows name character varying(255) on one side and NOT NULL on the other. Producing the ALTER TABLE statements — in a form that accounts for what's actually there — is still on you.
Ordering. Creating a table with a foreign key requires the referenced table first. Dropping a column requires dropping dependent indexes and constraints first. With dozens of selected changes, dependency ordering becomes a real graph problem.
Safety. Adding NOT NULL to a column that contains NULLs fails — or worse, a type change succeeds and mangles data. Text diffs know nothing about the data the schema holds.
Semantic equivalence. The serial-vs-identity problem above never fully goes away in text.

In the open-source ecosystem, migra deserves a mention: it compares two live Postgres databases at the catalog level rather than the text level, and emits the ALTER statements for the difference. If you live entirely in Postgres and entirely in the terminal, it solves a good chunk of items 1 and 4.

Items 2 and 3 — and doing any of this across MySQL, SQL Server, or SQLite, or with someone on the team who won't run a CLI — are the point where I stopped scripting and built DiffyPick: a desktop tool (macOS / Windows / Linux) that does the extraction, classification, dependency ordering, and pre-flight data checks, with the diff presented one line per difference. The free tier covers SQLite with no limits if you want to see the approach.

But start with the script. If the nightly diff stays quiet, you may never need anything else — and you'll know within a week either way.

Why schema drift goes undetected

Eggletric — Tue, 28 Jul 2026 12:10:00 +0000

Here is a scenario from a project in its third year of operation. Migrations are managed properly. CI is green. validate passes.

Then an error shows up in production only. The production table has a column that doesn't exist anywhere else. Nobody remembers who added it, when, or why. There is no migration file for it.

The important part is not the mystery column. It's that this divergence passed every check the team had. Nothing was broken. The tooling worked exactly as designed — and, as designed, it does not detect this class of problem.

This post is about why.

Code and databases are not symmetric

Git made deployment reproducible. Check out any commit and you get exactly the same tree, every time. If CI is green, the contents of that commit are settled.

Migrations brought the same idea to databases: record every change as a timestamped file, apply them in order, and every environment converges on the same schema.

There is an asymmetry hiding in that analogy, and it's rarely stated outright.

Replaying history is deterministic for code. The repository's state is fully determined by its history. If someone hand-edits a file on a server, the next deploy overwrites it. The working tree is disposable; the truth lives in the history.

Replaying history is not deterministic for databases. A database is a persistent entity you cannot throw away and rebuild from scratch. And it changes through paths that never touch your migration files:

An emergency ALTER TABLE run directly in a production console during an incident
Changes made by another team or another tool with write access — GUI clients, BI tools, monitoring agents, batch jobs
Implicit behavioral differences between engine versions: default collations, implicit type coercions, index length limits
An index added by hand in production because the data volume demanded it

None of these leave a trace in migration history.

A migration directory is an append-only log of intent. It records what someone meant to change. It is not a representation of what the database currently is.

For code, history is the truth. For a database, the truth is only in the database.

Two questions migrations cannot answer

This asymmetry has practical consequences. There are two questions a migration-based workflow structurally cannot answer.

1. What does the schema look like right now?

Migration files are a sequence of imperative steps, not a declaration. If you want to know the current definition of a table, you'd have to mentally fold hundreds of files in order. Nobody does that. By year three, everyone runs SHOW CREATE TABLE or checks \d in psql instead — because the live database is more trustworthy than the files.

And that's the problem. The moment "the database is more reliable than the migration files" becomes shared team knowledge, the files stop functioning as documentation. Files nobody reads decay. Decayed files get read even less.

2. When two environments differ, which one is correct?

There is no arbiter. "Whatever you get by replaying all migrations against an empty database" sounds like an answer, but it's circular — the whole problem is that this result and production disagree.

`validate` is not looking at your database

A common misconception is worth clearing up precisely.

Flyway's validate checks that the checksums of applied migrations still match the files on disk. It answers the question "has anyone edited a migration file after it was applied?" It does not answer "does the actual schema match what the migrations describe?"

Run a manual ALTER in production and validate stays green. The files didn't change, so from its point of view, nothing happened. This is correct behavior — for what the tool is scoped to do.

To be fair: tooling for the other question does exist. Liquibase ships diff and diffChangeLog in its open-source tier. Flyway offers drift detection in its paid editions. But the former assumes a changelog-centric CLI workflow with real setup cost, and the latter sits behind a license. The practical result across most teams is the same: the capability exists somewhere, and nobody runs it routinely. "Technically possible" and "actually happening every day" are very different states.

While we're being honest about tooling: most codebases carry down migrations that have never been executed even once. Untested code doesn't work. "We can roll back if we need to" is, in most shops, a comforting fiction that costs writing effort on every change.

The discipline problem

Everything so far was mechanical. The deeper problem is organizational.

A migration workflow looks like a technical mechanism, but what actually holds it together is a rule: every schema change goes through a migration file. No exceptions, no direct changes, everyone, always.

Rules like this have three properties that determine their fate:

They can be broken.
Things keep working when they're broken.
Breaking them is not detected.

Any process with all three properties degrades. It's only a question of when.

And this particular rule has an extra cost: it must be transmitted to every future participant for the lifetime of the project. It doesn't matter that the current team understands it perfectly. The engineer who joins in six months, the team that inherits the system in two years, the contractor called in for a 2 a.m. incident five years from now — the rule binds all of them, and any one of them can silently break it.

The higher your staff turnover, the faster the history rots.

This is the trade migrations quietly make: less friction today, paid for with a discipline debt assigned to people who weren't in the room.

The assumption that all environments are identical

There's one more assumption baked into migration workflows, and it's worth dragging into the light: every environment should have the same schema. Apply the same files in the same order, get the same result. Any divergence is an accident.

Except some divergence is deliberate.

An index that exists only in production, because production has 400× the data. Locally it would just slow the tests down.
Partitioning applied only in production.
Read-optimized indexes that exist only on replicas.
Collation or encoding differences mid-migration.
Debug views and seed tables that exist only in staging.
Per-customer schema variations in on-premise or packaged deployments.
A foreign key constraint deliberately dropped in production for operational reasons.

Try to express these in migrations and you get conditional branches keyed on environment variables — which makes the files even harder to read, which means they get read even less.

So there is a category of real, legitimate schema state that migrations cannot express even in principle.

To be clear, none of this is an argument against migrations. Versioned migration history is the correct practice. It just has a smaller jurisdiction than we usually assume.

Only a human can classify a difference — and we've given humans nothing to work with

Once you accept that intentional drift exists, a task becomes unavoidable: when a difference between environments surfaces, someone has to decide — is this intentional, or is it an accident?

That decision cannot be automated. Intent lives in people's heads. No tool can know whether an extra index is a performance fix someone deliberately applied to production, or something an engineer added during an investigation and forgot to remove.

I think it's important to say this plainly, because it marks the hard boundary of every "fully automated schema management" pitch: classification requires human judgment, full stop.

The real problem is not that humans have to look. It's that we've made looking nearly impossible.

Today your options are roughly:

Open two database clients side by side. Works for three tables. Does not work for eighty. This is a limitation of human attention, not effort.

Dump both schemas and diff the text.

pg_dump --schema-only -h host-a mydb > a.sql
pg_dump --schema-only -h host-b mydb > b.sql
diff a.sql b.sql

Reasonable in theory. In practice, most of what comes out is noise: object ordering differences, AUTO_INCREMENT counters, formatting variations in defaults, type spelling differences (serial vs. identity columns), ownership lines, version-dependent SET statements. You end up hunting for the one meaningful line buried in dozens of irrelevant ones — and humans who scan noisy output for long enough start missing the meaningful lines too.

That's the structural gap: the judgment can only be made by a human, and the output was never designed for human judgment.

Here's the part that makes it tractable. Intentional drift is stable. That production-only index shows up as the same difference every single time you compare. If the comparison output is structured and noise-free, stable differences become recognizable patterns — after the third look, it's "ah, that one again," and the real anomalies stand out against a familiar background.

People don't miss differences because there are too many. They miss them because the same difference looks different every time.

Where this leaves us

Migration history records intent. It does not describe the present. The present exists only in the database itself.
The verification built into migration tools checks files, not databases. Nothing in the default workflow detects drift.
The workflow's integrity rests on a convention that is breakable, silently breakable, and must be re-taught forever.
Some drift is intentional, which means classification is unavoidable, which means human eyes are unavoidable.
So the leverage point is not more automation of intent — it's better material for judging results: a clean, structured, repeatable comparison of what your databases actually are.

Manage history with migrations. Verify the present with diffs. These are different jobs, and conflating them is how a column ends up in production that nobody can explain.

I build DiffyPick, a desktop tool for comparing and syncing database schemas — it exists because of everything above.