Detecting schema drift with pg_dump hashes

Hash pg_dump --schema-only output to detect unauthorized schema changes and verify restores byte-for-byte. Normalization tricks included.

The one-liner

pg_dump "$URL" --schema-only --no-owner --no-privileges \
  | grep -v '^--' | grep -v '^SET ' | grep -v '^SELECT pg_catalog.set_config' \
  | sha256sum

Stable across dump runs of the same schema, changes when the schema does. Store it with each backup and you can answer 'did the schema change this week?' and 'did the restore reproduce the schema exactly?' with a string compare.

Normalization matters

Raw dumps carry noise: comment headers with timestamps, SET lines that vary by version, search_path config. Strip comments, SET/set_config lines, and blank lines before hashing. Object order within a dump is deterministic (dependency order), so no sorting needed — but hash from the same pg_dump major on both sides.

Where this shines

1. Restore verification — hash source schema at backup time, hash the restored schema, compare (Firedrill's schema_hash check).
2. Drift alarms — a nightly job comparing prod's hash against the migrations-generated hash catches hotfix-by-psql before it becomes archaeology.
3. Multi-env parity — staging and prod hashes should differ only when a deploy is mid-flight.