---
title: "Compare two PostgreSQL schemas: pg_dump and diff, pgAdmin Schema Diff and a GUI compare"
description: "Two PostgreSQL databases with 21 known differences, compared with pg_dump and diff, pgAdmin Schema Diff and Jam SQL Studio: what each found and missed."
url: "https://jamsql.com/blog/2026-11-20-compare-two-postgresql-schemas/"
html_url: "https://jamsql.com/blog/2026-11-20-compare-two-postgresql-schemas/"
generated: "2026-11-17T22:27:10.989Z"
---

Published: 2026-11-20

# Compare two PostgreSQL schemas: pg\_dump and diff, pgAdmin Schema Diff and a GUI compare

There are three common ways to compare the schemas of two PostgreSQL databases: dump both with `pg_dump --schema-only` and compare the files with `diff`, open **Tools → Schema Diff** in pgAdmin 4, or use a schema compare tool with a results tree and a generated script, such as Jam SQL Studio. To see what each one finds, we built two databases that differ in 21 known ways (columns, indexes, constraints, a function, a view, an enum, a trigger, a sequence, a comment, a grant, an owner and column order) and ran all three against them on November 10, 2026. The dump diff showed all 21 differences but wrote no SQL. pgAdmin 4 9.19 found 20, and after its script only the column order still differed. Jam SQL Studio found 17 and scripted 15, on version 1.5.11; version 1.5.10 itself found 16, scripted 14 and misreported one index, which is [described below](#jam-sql-studio). The table below has the result for each difference.

## The two databases

`shop_b` plays production: customers, products, orders, order items, addresses, an audit log, an `order_status` enum, an invoice-number sequence, two functions and one view. `shop_a` plays the development copy: the same schema with the 21 changes below. Both were created from `template0` on a PostgreSQL 16.4 server, so nothing else differs. In every comparison `shop_a` is the source and `shop_b` the target, so a generated script changes `shop_b` to match `shop_a`.

#

Object

`shop_b` (target)

`shop_a` (source)

1

`customers.phone`

absent

`phone text`

2

`customers.fax`

`fax text`

absent

3

`products.name` type

`varchar(100)`

`varchar(200)`

4

`products.stock` default

`DEFAULT 0`

`DEFAULT 1`

5

`customers.email`

nullable

`NOT NULL`

6

`orders` column renamed

`created_at`

`placed_at`

7

Index `orders_customer_id_idx`

absent

`(customer_id)`

8

Index `products_name_idx`

`(name)`

absent

9

Index `customers_email_idx`

`(email)`

`UNIQUE (lower(email))`

10

`products_price_check`

`CHECK (price >= 0)`

`CHECK (price > 0)`

11

`order_items.product_id`

no foreign key

`REFERENCES products (id)`

12

Function `order_total`

sums all items

adds `AND qty > 0`

13

View `customer_order_counts`

3 columns

adds `c.email`

14

Enum `order_status`

pending, paid, shipped

adds `'refunded'`

15

Trigger `orders_touch_updated_at`

absent

`BEFORE UPDATE`, calls `touch_updated_at()`

16

Sequence `invoice_no_seq`

`INCREMENT 1`

`INCREMENT 10`

17

Comment on table `orders`

none

`'One row per checkout'`

18

Privilege on `products`

none

`GRANT SELECT` to a reporting role

19

Owner of `audit_log`

the connecting user

another role

20

Table `wishlists`

absent

3 columns, primary key, 2 foreign keys

21

`addresses` column order

`city, postcode`

`postcode, city`

## Results by difference

“Found” means the tool listed the difference; the SQL after it is what the tool’s generated script did about it. `pg_dump` and `diff` generate no SQL, so that column only says whether the difference appears in the diff. pgAdmin ran with its default options (none of the four *Ignore* options ticked) and Jam SQL Studio at its default **Standard** depth.

#

Difference

`pg_dump -s` + `diff`

pgAdmin 4 9.19 Schema Diff

Jam SQL Studio Schema Compare (version 1.5.11)

1

Column added

Shown

Found; `ADD COLUMN`

Found; `ADD COLUMN`

2

Column dropped

Shown

Found; `DROP COLUMN IF EXISTS`

Found; `DROP COLUMN`

3

Column type changed

Shown

Found; `ALTER COLUMN … TYPE`

Found; `ALTER COLUMN … TYPE`

4

Default changed

Shown

Found; `SET DEFAULT`

Found; `SET DEFAULT`

5

`NOT NULL` added

Shown

Found; `SET NOT NULL`

Found; `SET NOT NULL`

6

Column renamed

Shown as one line out, one line in, inside a block that moved

Found as a drop and an add; drops the old column

Found as a drop and an add; drops the old column

7

Index added

Shown

Found; `CREATE INDEX`

Found; `CREATE INDEX`

8

Index dropped

Shown

Found; `DROP INDEX`

Found; `DROP INDEX`

9

Index definition changed (to a unique expression index)

Shown

Found; `DROP INDEX`, then `CREATE UNIQUE INDEX`

Found as modified (unique, key columns); `DROP INDEX`, then `CREATE UNIQUE INDEX`

10

`CHECK` constraint changed

Shown

Found; `DROP CONSTRAINT`, `ADD CONSTRAINT`

Found; `DROP CONSTRAINT`, `ADD CONSTRAINT`

11

Foreign key added

Shown

Found; `ADD CONSTRAINT … FOREIGN KEY`

Found; `ADD CONSTRAINT … FOREIGN KEY`

12

Function body changed

Shown

Found; `CREATE OR REPLACE FUNCTION`

Found; `CREATE OR REPLACE FUNCTION`

13

View definition changed

Shown, split into two places in the diff

Found; `CREATE OR REPLACE VIEW`

Found; `CREATE OR REPLACE VIEW`

14

Enum value added

Shown

Found; `ALTER TYPE … ADD VALUE 'refunded' AFTER 'shipped'`

**Not compared** (no PostgreSQL types)

15

Trigger added

Shown

Found; `CREATE OR REPLACE TRIGGER`

Found; **not in the script**

16

Sequence option changed

Shown

Found; `ALTER SEQUENCE … INCREMENT 10`

Found; `ALTER SEQUENCE` with every option restated

17

Table comment added

Shown (not with `--no-comments`)

Found; `COMMENT ON TABLE`

Found; **not in the script**

18

Privilege granted

Shown (not with `-x`)

Found; `GRANT SELECT`, plus a `GRANT ALL` to the owner

**Not compared**

19

Owner changed

Shown (not with `-O`)

Found on the table and its identity sequence; `OWNER TO`

**Not compared**

20

Table only in the source

Shown

Found; `CREATE TABLE` with its keys

Found; `CREATE TABLE`, then two `ADD CONSTRAINT`

21

Column order

Shown

**Not reported**

**Not reported**

Found

21 of 21 (19 with `-O -x`)

20 of 21

17 of 21

In the generated script

No script

20 of 21; ran without errors

15 of 21; ran without errors

Same tool, run again after the script

—

“No difference found”

1 table modified (`orders`: trigger and comment)

The rename (6) counts as found for all three because each one showed the change, but none of them recognised it as a rename. See [Renamed columns](#renames) before running any generated script that drops a column.

The Jam SQL Studio column is from version 1.5.11. Version 1.5.10 differs in one row: it reported difference 9 as an index removed, and its script dropped `customers_email_idx` without creating the new one, so it found 16 and scripted 14. The [Jam SQL Studio section](#jam-sql-studio) has the details.

## Method 1: `pg_dump --schema-only` and `diff`

Dump the definitions of both databases into files and compare the files. This needs nothing but the PostgreSQL client tools, works over SSH and in CI, and shows every difference the dump contains. Our first attempt:

```bash
pg_dump --schema-only -d shop_a -f a.sql
pg_dump --schema-only -d shop_b -f b.sql
diff -u b.sql a.sql
```

The first hunk of the output was not a schema difference at all:

```
-\restrict J2yhVRMJqrhbJKOHwcnyizAogmPTihtzgGtg820D62VdtRntVXXdvvjVZeb6qof
+\restrict 4K3gVB5KurTJdDgfBbaSZTJsmxHZi5ToeMvonu0CFNMwVKNTwCPTKU1vhSUpXrK
```

pg\_dump releases since August 14, 2025 (PostgreSQL 18, 17.6, 16.10 and the other minor releases of that day) start every plain-text dump with a `\restrict` line and end it with `\unrestrict`, each with a random key, to [stop a malicious server from running psql meta-commands](https://www.postgresql.org/docs/release/17.6/) on the machine that restores the dump. Two dumps therefore always differ on those lines. The [pg\_dump reference](https://www.postgresql.org/docs/current/app-pgdump.html) describes `--restrict-key` as “primarily intended for testing purposes and other scenarios that require repeatable output (e.g., comparing dump files)” and advises against it for general use, so use a fixed key only for dumps you compare, never for dumps you restore. The `pg_dump` 16.4 inside our server container predates the change and wrote no such lines.

Owner and privilege lines are the other routine noise: every object carries an `ALTER … OWNER TO` line and an `Owner:` comment header, which differ between environments that use different roles. The flags that made the diff readable:

```bash
pg_dump --schema-only --no-owner --no-privileges --restrict-key=compare -d shop_a -f a.sql
pg_dump --schema-only --no-owner --no-privileges --restrict-key=compare -d shop_b -f b.sql
diff -u b.sql a.sql
```

That took the diff from 15 hunks to 11, and it also hid differences 18 and 19, which is what `-x` and `-O` are for. Drop those two flags when owners and grants matter to you. Use the same `pg_dump` binary for both dumps, because the output format changes between versions; our 18.6 client dumped the 16.4 server without trouble.

Most changes show up as small, readable hunks (output trimmed):

```
@@ -26,7 +26,8 @@
 CREATE TYPE public.order_status AS ENUM (
     'pending',
     'paid',
-    'shipped'
+    'shipped',
+    'refunded'
 );
@@ -169,7 +156,7 @@
 CREATE SEQUENCE public.invoice_no_seq
     START WITH 1000
-    INCREMENT BY 1
+    INCREMENT BY 10
@@ -208,10 +215,10 @@
 CREATE TABLE public.products (
     id integer NOT NULL,
     sku text NOT NULL,
-    name character varying(100) NOT NULL,
+    name character varying(200) NOT NULL,
     price numeric(10,2) NOT NULL,
-    stock integer DEFAULT 0 NOT NULL,
-    CONSTRAINT products_price_check CHECK ((price >= (0)::numeric))
+    stock integer DEFAULT 1 NOT NULL,
+    CONSTRAINT products_price_check CHECK ((price > (0)::numeric))
 );
```

The harder part is that pg\_dump orders objects by their dependencies, so one change can move unrelated objects. Our new view selects `c.email` while grouping by `c.id`, which PostgreSQL allows only because `c.id` is the primary key. The view now depends on the primary-key constraint, so pg\_dump wrote a placeholder view early in the file (`SELECT NULL::integer AS customer_id, …`) and the real definition near the end, after the constraints. The `orders` table, which used to be dumped just before the view, moved further down. `diff` then paired the old `orders` block with the new `customers` block (trimmed):

```
--- Name: orders; Type: TABLE; Schema: public; Owner: -
+-- Name: customers; Type: TABLE; Schema: public; Owner: -
 --

-CREATE TABLE public.orders (
+CREATE TABLE public.customers (
     id integer NOT NULL,
-    customer_id integer NOT NULL,
-    status public.order_status DEFAULT 'pending'::public.order_status NOT NULL,
+    email text NOT NULL,
+    full_name text NOT NULL,
     created_at timestamp with time zone DEFAULT now() NOT NULL,
-    updated_at timestamp with time zone
+    phone text
 );
```

Read literally, that says `customers` lost `customer_id` and `status`. The whole `orders` table reappears further down as an added block, with the renamed column inside it. With a few hundred tables this kind of shift is common, and the diff takes real reading. It is also only text: you still write the `ALTER` statements yourself. Where it beats both GUI tools in our test is coverage: it was the only method that showed the column order change (21), and with default flags it shows owners and grants too.

## Method 2: pgAdmin 4 Schema Diff

pgAdmin describes itself as “an Open Source administration and management tool for the PostgreSQL database” and is released under the PostgreSQL licence (how it compares with other PostgreSQL clients: [pgAdmin alternatives](/alternatives/pgadmin/)). We ran the official `dpage/pgadmin4` container (pgAdmin 4 9.19) next to the database server and drove its web interface from a script. **Tools → Schema Diff** opens a tab with a source and a target row: pick a server and a database on each, leave the schema empty for a *Database Compare*, and click **Compare**. Per the [Schema Diff documentation](https://www.pgadmin.org/docs/pgadmin4/latest/schema_diff.html), “the source and target database servers must be of the same major version,” and when you compare two schemas rather than two databases, “dependencies won’t be resolved.”

The result is a grid grouped by object type, with counts per group. For our pair:

```
public     Different: 10  Source Only: 1  Target Only: 0
Functions  order_total(p_order_id integer)       Different
Sequences  audit_log_id_seq, invoice_no_seq      Different
Tables     audit_log, customers, order_items,
           orders, products                      Different
           wishlists                             Source Only
Types      order_status                          Different
Views      customer_order_counts                 Different
```

Clicking an object shows three panes: the source DDL, the target DDL and the SQL that would turn the target into the source. For `customers` the third pane read:

```sql
ALTER TABLE IF EXISTS public.customers DROP COLUMN IF EXISTS fax;

ALTER TABLE IF EXISTS public.customers
    ALTER COLUMN email SET NOT NULL;

ALTER TABLE IF EXISTS public.customers
    ADD COLUMN phone text COLLATE pg_catalog."default";

DROP INDEX IF EXISTS public.customers_email_idx;

CREATE UNIQUE INDEX IF NOT EXISTS customers_email_idx
    ON public.customers USING btree
    (lower(email) COLLATE pg_catalog."default" ASC NULLS LAST)
;
```

Tick objects (or the header box for all of them) and **Generate Script** opens the combined SQL in the Query Tool, inside `BEGIN;` and `END;`. The script starts with a warning: “Due to circular dependencies, the order in which Schema Diff writes objects may not be optimal and manual changes to the script may be required to ensure changes are applied in the correct order.” Ours ran in one go with `psql -v ON_ERROR_STOP=1` against a fresh copy of `shop_b`, and a second Schema Diff against that copy said **No difference found**. Things to know:

-   **The owner difference appears twice**, on the table and on its identity sequence `audit_log_id_seq`, which is why *Sequences* shows two differences although only one sequence’s options changed.
-   **The grant comes with an extra line.** Next to `GRANT SELECT ON TABLE public.products TO <role>` the script also had `GRANT ALL ON TABLE public.products TO demo`, the owner’s own privileges, which were implicit on the target. It is harmless but worth reading.
-   **Column order is not reported**, so `addresses` did not appear as different.
-   **The options are off by default.** The arrow next to Compare offers *Ignore Owner*, *Ignore Whitespace*, *Ignore Tablespace* and *Ignore Grant/Revoke*; tick the first and last when environments use different roles on purpose.

## Method 3: Jam SQL Studio Schema Compare

In Jam SQL Studio, **More → Schema Compare** opens a setup dialog: pick the connection and database for the source and the target, pick a depth (**Quick**, **Standard** or **Deep**) and click **Compare**. We ran it against the same two databases through the app’s own test harness, at all three depths: version 1.5.10 on November 10, and a build of version 1.5.11, which has the index changes, on November 11. The results below are from 1.5.11; where 1.5.10 differed, it says so.

At Standard depth the Overview counted 1 added, 7 modified, 0 removed and 3 identical objects. The tree listed `customers`, `order_items`, `orders` and `products` as modified, `wishlists` as only in the source, and the view, the `order_total` function and the sequence as modified. Each table card named what changed, for example `email NOT NULL → NULL`, `+ phone`, `− fax` and `1 idx` on `customers`, or `name character varying(200) → character varying(100)` and `stock default changed` on `products` (source value first). Selecting an object opens a side-by-side diff of its DDL, with the source on the left.

![Jam SQL Studio Schema Compare of shop_a against shop_b on PostgreSQL 16: the tree lists five changed tables, one view, one function and one sequence; the Overview shows 1 added, 7 modified, 0 removed and 3 identical objects and a card per changed table, such as customers with one column added, one removed, one changed and one index](/images/docs/schema-compare-postgresql-overview.png)
*The Overview for our two databases at Standard depth.*

**Select → Select All** and **Generate Script** produced a 69-line script inside `BEGIN;` and `COMMIT;`, with a header explaining that PostgreSQL DDL is transactional, so a failure anywhere rolls the whole script back. It ran without errors on a fresh copy of `shop_b`. An excerpt:

```sql
ALTER TABLE "public"."customers" ALTER COLUMN "email" SET NOT NULL;
ALTER TABLE "public"."customers" ADD COLUMN "phone" TEXT NULL;
ALTER TABLE "public"."customers" DROP COLUMN "fax";
DROP INDEX IF EXISTS "public"."customers_email_idx";
CREATE UNIQUE INDEX "customers_email_idx" ON "public"."customers" USING btree (lower(email));
ALTER TABLE "public"."orders" ADD COLUMN "placed_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now();
ALTER TABLE "public"."orders" DROP COLUMN "created_at";
CREATE INDEX "orders_customer_id_idx" ON "public"."orders" USING btree (customer_id);
ALTER TABLE "public"."products" DROP CONSTRAINT "products_price_check";
ALTER TABLE "public"."products" ALTER COLUMN "name" TYPE CHARACTER VARYING(200);
ALTER TABLE "public"."products" ALTER COLUMN "stock" SET DEFAULT 1;
ALTER TABLE "public"."products" ADD CONSTRAINT "products_price_check" CHECK ((price > (0)::numeric));
```

**The changed index** (difference 9) was reported as modified, with the two attributes that differ: `unique` and `key columns` (screenshot below). The script dropped it and created the unique index on `lower(email)`; the `CREATE INDEX` statement is PostgreSQL’s own definition of the source index (`pg_get_indexdef`), addressed to the target table. A second, smaller test with five index changes on one table gave the same result: a changed `WHERE` clause on a partial index, a change from `btree` to `hash`, a change to `DESC` order, an added `INCLUDE (b)` column and a new index on an expression were all reported, each with the attribute that changed, and the script recreated each index. After that script ran, `pg_get_indexdef` returned the same text for every index in both databases.

**Version 1.5.10 and earlier get indexes wrong.** They read only an index’s name, uniqueness and plain key columns, and do not read an index on an expression at all. In the same test, 1.5.10 reported `customers_email_idx` as only in the target, its script dropped the old index and created nothing in its place, and a second comparison then called `customers` identical even though the copy had no unique index on email. In the smaller test it did not report the changed `WHERE` clause, the change to `hash`, the change to `DESC` or the new expression index, and it scripted the added `INCLUDE (b)` column as a second key column. If you synchronised PostgreSQL indexes with 1.5.10 or earlier, compare again with version 1.5.11 or later, or check the indexes with the `pg_dump` diff.

What it did not do, in this test:

-   **The enum was not compared.** PostgreSQL enums, domains and composite types are outside the comparison (user-defined types are compared on SQL Server only), so the new `'refunded'` value did not appear anywhere.
-   **The trigger and the table comment were reported but not scripted.** Both showed in the `orders` diff, but the script contained no `CREATE TRIGGER` and no `COMMENT ON TABLE`, and said nothing about leaving them out. The [Schema Compare docs](/docs/schema-compare/#member-selection) say trigger changes are not scripted and have to be ported by hand; the comment was left out in the same way. The second comparison against the synced copy listed exactly these two as remaining.
-   **Owners and privileges are not compared**, by design; the docs point to the Security Manager for roles and permissions.
-   **Column order is not reported**, the same as pgAdmin.
-   **Index storage parameters are not compared.** In a separate check, two indexes that differed only in `WITH (fillfactor = 70)` compared as identical.

![Jam SQL Studio Schema Compare detail for public.customers: email changed from NOT NULL to NULL, phone only in source, fax only in target, and the index customers_email_idx marked modified, with unique true on the source and false on the target and key columns lower(email) on the source and email on the target](/images/docs/schema-compare-postgresql-table-detail.png)
*The `customers` detail: the three column changes, and `customers_email_idx` as modified with the two attributes that differ (source value first).*

**Depth changed the result.** Quick compares columns only: it found the column changes (1–6), the function, the view, the sequence and the new table, and showed a banner saying that constraints, indexes, triggers, table options and partition definitions were not compared on either side. Standard and Deep gave identical results here, because the only property Standard ignores on PostgreSQL is `fillfactor`, which our tables do not set.

Personal mode, which is free for personal, non-commercial use, compares schemas of up to 200 objects per side; work use needs Pro, which removes the limit ([pricing](/pricing/)). The [Schema Compare docs](/docs/schema-compare/) cover the setup options, per-column selection, script direction and the other engines.

## Renamed columns: every tool shows a drop and an add

PostgreSQL’s catalog does not record that `created_at` became `placed_at`, so none of the three methods could tell a rename from a dropped column and a new one. Both generated scripts added `placed_at` and dropped `created_at`. On a table with data, that deletes every value in `created_at`, and the new column fills with `now()` from its default. Before running either script, replace those two statements with:

```sql
ALTER TABLE public.orders RENAME COLUMN created_at TO placed_at;
```

The same applies to renamed tables, which show up as one table only in the source and one only in the target. The `SET NOT NULL` for `customers.email` is the other statement to check against real data: it fails if any row has a `NULL` email, and because both scripts run in one transaction, the whole script then rolls back.

## Check a sync with pg\_dump

A tool that reports no differences after its own script has only checked what it compares. A `pg_dump` diff of the source against the synced copy shows the rest. After pgAdmin’s script, the only lines left were column order: `addresses`, and `orders`, where `placed_at` now sits after `updated_at` because it was added rather than renamed. After Jam SQL Studio’s script the diff also showed the missing enum value, owner, table comment, trigger and grant (trimmed):

```
-    'shipped'
+    'shipped',
+    'refunded'
-ALTER TABLE public.audit_log OWNER TO demo;
+ALTER TABLE public.audit_log OWNER TO b2lab_owner;
+COMMENT ON TABLE public.orders IS 'One row per checkout';
+CREATE TRIGGER orders_touch_updated_at BEFORE UPDATE ON public.orders FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
+GRANT SELECT ON TABLE public.products TO b2lab_reporting;
```

This is the most useful job for the `pg_dump` diff even if you use a GUI for the comparison itself: run it after a sync, and an empty diff (apart from column order) means the two schemas match.

## Which one to use

-   **A one-off check over SSH or in CI: `pg_dump` and `diff`.** Nothing to install beyond the client, the output can be stored next to a migration, and it covers everything in the dump, including owners, grants and column order. It writes no SQL, and object moves make large diffs hard to read.
-   **You already use pgAdmin: Schema Diff.** It is free, it found 20 of our 21 differences, including the enum value, trigger, comment, owner and grant, and its script left nothing but column order. It needs both servers on the same major version.
-   **A reviewable, selectable diff with a generated script: a GUI compare.** Jam SQL Studio shows every change in a tree and side by side, lets you pick which objects (and which columns, constraints and indexes of a table) go into the script, and also compares SQL Server, MySQL, Oracle and SQLite. On PostgreSQL, check its gaps from this test before you rely on the script: enums and other types, triggers, comments, owners and grants. Use version 1.5.11 or later, which is the first to read expression indexes and to compare an index’s predicate, access method and sort order.

For a comparison of schema compare tools across SQL Server, MySQL, PostgreSQL and Oracle, including commercial ones, see [Best Schema Compare Tools](/alternatives/best-schema-compare-tool/).

## Quick Answers

### Q: How do I compare the schemas of two PostgreSQL databases?

A: Three common ways: dump both with pg\_dump --schema-only and compare the files with diff; use Tools > Schema Diff in pgAdmin 4, which lists the objects that differ and generates a script; or use a GUI schema compare tool such as Jam SQL Studio's Schema Compare. In our test with 21 known differences, the dump diff showed all 21 but wrote no SQL, pgAdmin 4 9.19 found 20 and scripted them, and Jam SQL Studio found 17 and scripted 15 on version 1.5.11 (version 1.5.10 found 16, scripted 14 and misreported one index).

### Q: Can pg\_dump compare two databases?

A: Not by itself, but pg\_dump --schema-only --no-owner --no-privileges writes each database's definitions to a file, and diff -u shows what changed. Use the same pg\_dump version for both dumps. The diff shows every textual difference, including column order, but it is not SQL you can run, and objects that change position in the dump show up as large removed and added blocks.

### Q: Why does a diff of two pg\_dump files show a difference on the restrict line?

A: pg\_dump releases from August 14, 2025 on (PostgreSQL 18, 17.6, 16.10 and the other minor releases of that day) write a \\restrict line with a random key at the top of every plain-text dump and an \\unrestrict line at the end, so two dumps always differ there. For dumps you only compare, pass the same --restrict-key value to both, which the pg\_dump documentation describes as intended for comparing dump files, or filter those two lines out before running diff.

### Q: Does pgAdmin have a schema compare tool?

A: Yes. Tools > Schema Diff in pgAdmin 4 compares two databases, or two schemas, on servers of the same major version, lists each object as identical, different, source only or target only, and shows the source DDL, target DDL and the SQL to apply. Generate Script opens the SQL for the selected objects in the Query Tool. Options can ignore owner, whitespace, tablespace and grants.

### Q: Do schema compare tools detect a renamed column?

A: Not in our test. pg\_dump and diff, pgAdmin Schema Diff and Jam SQL Studio all showed the renamed column as one column removed and another added, and both generated scripts dropped the old column, which deletes its data. Replace those two statements with ALTER TABLE ... RENAME COLUMN before running the script.

### Q: What does Jam SQL Studio's Schema Compare not compare on PostgreSQL?

A: It does not compare enum and other custom types, owners, privileges, column order or an index's storage parameters such as fillfactor. It reports a new or changed trigger and a table comment but leaves them out of the generated script. Versions up to 1.5.10 also did not read an index on an expression (lower(email)), so they showed a changed one as removed and their script dropped it without creating the new one, and they did not report a changed WHERE clause on a partial index, a changed index access method or a changed sort order. Version 1.5.11 and later report and script all of these.

## How we tested

Everything ran on November 10, 2026 on an x86-64 Linux machine. The server was PostgreSQL 16.4 in Docker (`postgis/postgis:16-3.4`; the two databases were created from `template0`, so no extension objects were involved). The dumps used `pg_dump` 18.6 from the Ubuntu packages and, for comparison, the server container’s own `pg_dump` 16.4; the diffs used GNU `diff` 3.12. pgAdmin was the official `dpage/pgadmin4` image, version 9.19, on the same Docker network, driven through its web interface with Playwright. Jam SQL Studio ran in the app’s end-to-end test harness against the same server, twice: version 1.5.10 on November 10, and on November 11 a build with the index changes that follow 1.5.10 (no version number had been assigned to it when this was written). The Jam SQL Studio results in the table and text are from the second run. Each generated script was run with `psql -v ON_ERROR_STOP=1` against its own fresh copy of `shop_b`. The smaller index test used a second pair of databases with one table whose five indexes differed (partial-index predicate, access method, sort order, an `INCLUDE` column and a new expression index); we compared it in Jam SQL Studio at Deep depth and in pgAdmin. With the later Jam SQL Studio build we ran the generated script with `psql` and compared again; we did not run the 1.5.10 script or pgAdmin’s. The fillfactor check used one more pair of tables on the same server. The roles `b2lab_owner` and `b2lab_reporting` in the output are the two test roles we created. We did not run other command-line schema diff tools for this post.

Last verified against postgresql.org and pgadmin.org documentation, and by running every command and comparison above, on 2026-11-10; the Jam SQL Studio comparison was run again on 2026-11-11.

### Related

-   [Schema Compare Docs](/docs/schema-compare/)
-   [Best Schema Compare Tools](/alternatives/best-schema-compare-tool/)
-   [Data Compare Docs](/docs/data-compare/)
-   [Jam SQL Studio for PostgreSQL](/databases/postgresql/)
-   [Migrate SQL Server to PostgreSQL](/blog/2026-06-13-migrate-sql-server-to-postgresql/)

## Compare PostgreSQL schemas in Jam SQL Studio

**Schema Compare** lists the tables, views, functions and sequences that differ between two PostgreSQL databases, shows each change side by side and generates a sync script that you read before you run it. It also compares SQL Server, MySQL, Oracle and SQLite databases. Free for personal use for schemas up to 200 objects per side.

[Download Jam SQL Studio Free](/download/) [Schema Compare docs](/docs/schema-compare/)

Free for personal use • No account required • Mac, Windows, Linux