Skip to content

Instantly share code, notes, and snippets.

@flbuddymooreiv

flbuddymooreiv/compareschema.md

Last active August 21, 2017 10:03
Show Gist options
  • Save flbuddymooreiv/491d389aec7052ae9af9 to your computer and use it in GitHub Desktop.
Save flbuddymooreiv/491d389aec7052ae9af9 to your computer and use it in GitHub Desktop.
Download ZIP
Testing PostgreSQL Schema compliance
Raw
compareschema.md

Assuming a database mydb, and a canonical database file defined in a file tables.sql, here is a way to tell if an operational schema differs from the canonical one:

pg_dump -U postgres -a mydb > mydbdata.sql
psql -U postgres -c "drop database mydbcanonical"
psql -U postgres -c "create database mydbcanonical"
psql -U postgres -d mydbcanonical -f tables.sql
psql -U postgres -d mydbcanonical -f mydbdata.sql

The gist is to see if the operational database's data will load cleanly into the canonical schema. These commands depend on the admin to visually parse the output of the last command where the data is loaded into the test database for errors. If there are no errors of concern, the schema is compliant.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment