Check if two databases have same structure

Question

I have a Postgres database that was developed over time, adding tables and keys when needed.

Now I'm using a tool (Enterprise Architect) to document it. The tool allows to create automatically the SQL needed to re-create the database.

I want to know if the database that I create and the original one have the same structure (same tables, same primary keys, same foreign keys etc). I'm not interested on data contained in database, only in structure.

Is there a way to check if two different databases have the same structure?

Answer 1

PostgreSQL has a comprehensive set of system catalogs. These hold the metadata for a database i.e. table names, columns, index definitions and so forth. By comparing the content of the original database's catalog with that of the DB generated from the model you can check completeness. Turns out, there's an app for that.

Answer 2

The easiest way is to just take schema-only dump with pg_dump -s for both databases and compare that texts by any diff utility.

pg_dump -s db1 --file db1_schema.sql
pg_dump -s db2 --file db2_schema.sql
diff -u db1_schema.sql db2_schema.sql

You can also use pg_dump --no-acl --no-owner parameters to ignore possible difference of owners and access rights.