Comparing Two Database Schemas

A deploy to staging failed, and nobody knows what is different there. To compare two database schemas, read both catalogs and diff what they return. A column query is quick but shallow. A schema-only dump also shows keys, indexes and types. DbSchema compares a model with a database and writes the migration SQL for the differences you pick.

We ran each method on PostgreSQL 16 against two copies of a 14-table demo schema, on 25 October 2026. The staging copy, dbschema_demo_stg, has 12 deliberate drifts. Here is what each method found:

Neither method writes the SQL that brings one side in line. That is the job of schema synchronization in DbSchema, shown further down.

Table of contents

• What comparing two schemas actually compares
• Compare two schemas with a catalog query
• Diff a schema-only dump to catch keys, indexes and types
• Compare two schemas in DbSchema and generate the script
• Check the generated script before it runs
• Keep the model in Git as the baseline
• Frequently asked questions
• Sources

What comparing two schemas actually compares

A schema is the set of objects in a database: tables, columns, keys, indexes, views and types. Comparing two schemas means comparing those definitions, not the rows inside the tables.

What a schema is depends on the engine. In PostgreSQL, "a database contains one or more named schemas[1], which in turn contain tables". In Oracle, "a schema is owned by a database user[2] and shares the same name as the user". So you compare a user's objects in Oracle, and a named schema in PostgreSQL.

Before you compare, check that you have the right schema on each side. In psql, \\dn lists the schemas. From any client, this query does the same:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg\_%'
  AND schema_name <> 'information_schema'
ORDER BY schema_name;

The demo database has one user schema:

For more ways to list them, see list all schemas in PostgreSQL.

Compare two schemas with a catalog query

The quickest comparison runs one catalog query on each database and diffs the two results. Here is the query we ran on both PostgreSQL databases:

SELECT table_name, column_name, data_type,
       character_maximum_length AS len,
       is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, column_name;

We saved each result to a file and compared the files with diff. Dev returned 105 rows and staging 102. Part of the output:

31c36
< projects|key|character varying|10|NO|
---
> projects|key|character varying|20|NO|
45c50
< tasks|completed_at|timestamp with time zone||YES|
---
> tasks|blocked_reason|character varying|200|YES|
53c58
< tasks|priority|USER-DEFINED||NO|'medium'::task_priority
---
> tasks|priority|USER-DEFINED||YES|'medium'::task_priority

Lines with \< come from dev, lines with \> from staging. Staging widened projects.key to 20 characters. It has blocked_reason instead of completed_at. It also lets priority be null.

In total the diff found 8 of the 12 drifts. It also caught a table that exists only on staging, a table missing there, a changed default and a view that lost two columns. It missed the other four: a dropped foreign key, two indexes and a new enum value. None of them live in information_schema.columns.

The same query works on SQL Server, where INFORMATION_SCHEMA.COLUMNS[3] "returns one row for each column that can be accessed by the current user". On Oracle, query ALL_TAB_COLUMNS[4] and filter on owner. On every engine, the views only show what your user can access. Run the query as the same user on both sides, or the diff will report access differences as drift.

Diff a schema-only dump to catch keys, indexes and types

A schema-only dump writes every definition, including the ones the column query missed. In PostgreSQL, pg_dump with --schema-only (-s) will "dump only the object definitions[5] (schema), not data".

pg_dump -s -d dbschema_demo     > dev_schema.sql
pg_dump -s -d dbschema_demo_stg > stg_schema.sql
diff -u dev_schema.sql stg_schema.sql > schema.diff

This diff found all 12 drifts. Among them are the four the column query missed:

+    'blocked'
-CREATE INDEX idx_activity_payload_gin ON public.activity_log USING gin (payload);
+CREATE INDEX idx_users_email_lower ON public.users USING btree (lower((email)::text));
-ALTER TABLE ONLY public.comments
-    ADD CONSTRAINT comments_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL;

The cost is reading. The diff ran to 488 lines for 12 changes, because a dump prints each sequence, key and index as its own statement with a comment header. It also tells you what differs, not what to run. Turning it into a migration script is still manual work.

Compare two schemas in DbSchema and generate the script

DbSchema compares a design model with a database and writes the SQL for you. The model is DbSchema's own copy of the schema, saved in a .dbs file. Schema synchronization compares that model with the database and lists every difference.

You can start from either side. To check a database against your design, connect the model to it and choose Compare. The Synchronize Model and Database dialog then shows one row per difference. The DbSchema Model column holds the model's side and the Database column the database's side.

In the Action column, you pick the side that wins for each row. The left button writes the difference into the model, Ignore leaves it, and the right button writes it into the database. Select a row to see the SQL in the lower pane.

The buttons at the bottom decide what changes:

• Apply to the model changes only the model.
• Commit in the database runs the rows set to the database, one statement at a time.
• Generate SQL script writes the statements without running them.

To compare two databases, reverse-engineer each one into its own model file. Then open one model and choose Compare Model with Other Model From File. The same dialog opens with the other file on the right, and nothing runs in a database. Generate SQL script still writes the statements. Export Report saves the differences as HTML or Markdown to keep with a release.

DbSchema also tracks renames. When you rename a column in the model, DbSchema remembers its previous name. The script then uses RENAME instead of a drop and a create, so the column keeps its data. Schema synchronization needs DbSchema Pro.

Check the generated script before it runs

Read every generated script before it touches a shared database. Whichever method produced it, three kinds of statement need a closer look.

The first is a new NOT NULL column without a default. We added one to a temporary copy of five tasks rows, and PostgreSQL 16 refused it:

ALTER TABLE tasks_copy ADD COLUMN reviewed_by integer NOT NULL;

ERROR:  column "reviewed_by" of relation "tasks_copy" contains null values

With a default, the same change works, and every existing row gets the value:

ALTER TABLE tasks_copy ADD COLUMN review_state varchar(20) NOT NULL DEFAULT 'pending';
SELECT count(*) AS rows, count(review_state) AS filled FROM tasks_copy;

The second is a drop. A DROP COLUMN or DROP TABLE deletes data, so confirm that the object is really meant to go. In DbSchema, a drop and a create for one column means DbSchema has no previous name for it. Set the previous name under Renamed Items, and the script renames the column instead.

The third is a changed view. A changed view is dropped and created again. The DbSchema documentation shows PostgreSQL 17 refusing a plain DROP VIEW when another view depends on it. In that case, send the script to the SQL Editor and drop the dependent views first.

Then run the script on staging before production. For that comparison, see comparing production and staging schemas.

Keep the model in Git as the baseline

A comparison is easier when one side is a known good version. DbSchema saves the whole design in one .dbs file, and the Git documentation notes that DbSchema "has Git built in". Commit the model after each release, and you can compare any database with the version that shipped.

The file is plain XML, so a change reads well in a diff. We put a model that DbSchema saved for the demo database into a Git repository. Then we added one column element by hand, the same element DbSchema writes for a column:

@@ -232,2 +232,3 @@
 			<column name="completed_at" type="timestamptz" />
+			<column name="blocked_reason" type="varchar" length="200" />
 			<index name="tasks_pkey" unique="PRIMARY_KEY" >

Git reported one file changed with one insertion. A reviewer sees the new column in a pull request without opening DbSchema.

Keep the two steps apart. Git changes only the model file. To bring a change into the database, synchronize the model with the database as shown above. Saving the model to a file needs DbSchema Pro. For the full workflow, see putting a database schema in Git.

Download DbSchema, connect it to the database you trust, and reverse-engineer it into a model. Then compare that model with the other database, or with a model of it, and generate the script. The free Community Edition connects and reverse-engineers. Comparing, generating the migration script and saving the model to a file are in DbSchema Pro.

Frequently asked questions

How do I list all schemas in PostgreSQL?

In psql, run \dn. From any client, select schema_name from information_schema.schemata and filter out the pg_ system schemas.

Can I compare two schemas with SQL alone?

Yes, to find the differences. Run the same catalog query on both databases and diff the results. For keys, indexes and types, diff two schema-only dumps instead. Neither method writes the migration script, so that part stays manual.

Does comparing schemas compare the data?

No. A schema comparison reads only definitions such as tables, columns, keys and indexes. The rows in the tables are not compared.

Sources

1. postgresql.org
2. docs.oracle.com
3. learn.microsoft.com
4. docs.oracle.com
5. postgresql.org