How to compare two PostgreSQL schemas

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:

1. 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.
2. 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.
3. 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.
4. 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.