November 6, 20268 min read

How to Compare Two PostgreSQL Databases with Ptah

Compare two PostgreSQL schemas, such as staging and production, with one Ptah command. Read every difference as SQL and review it before anything runs.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A release passes on staging and fails on production with column "currency" does not exist. The two databases were meant to match. Before you fix that one column, you want the full list of differences, including the ones that have not failed yet.

Staging has the new release. Production has hotfixes that staging never received. The end state has to keep both, so you cannot make production a copy of staging.

Ptah compares two live PostgreSQL databases with one command and prints the difference as SQL. It needs only the two connection URLs, so neither database has to use Ptah already.

We reproduced this with Ptah 0.23.0 and two disposable PostgreSQL 18.6 servers. The comparison found the missing column, the rest of the release, and the hotfixes. After a reviewed reconciliation, both directions reported no differences and every production row was intact.

What each database holds

Object	Staging	Production
`orders.currency` column	Yes	No
Customer name column	`display_name`	`full_name`
Rest of the release: an enum value, a check constraint, a view column, an index, a grant	Yes	No
`customers.legacy_ref` column and `orders_pending_idx` index	No	Yes, as hotfixes
`ops.audit_events` table	No	Yes, owned by another process

This table comes from what we know about the two databases. Ptah reads only their catalogs. It cannot tell that full_name became display_name, or that a hotfix is worth keeping. Those are review decisions, and the output below does not make them for you.

Compare the databases and read the result

--from is the database the changes are calculated for. --to is the state it is compared with. The printed SQL describes how to turn the first schema into the second, and the command does not apply it.

That direction decides how you read each line:

An ADD, CREATE, or GRANT line is something the --from database lacks.
A DROP line is something only the --from database has.

We wanted to know what production lacked, so production is --from:

ptah schema diff --from "$PRODUCTION_URL" --to "$STAGING_URL"

The command exited 0 and printed 11 statements. The excerpts below show the decisions they raise. They are selected lines from that output, not a script to run.

A missing part of the release. The currency column is the cause of the failure, and production needs it.

ALTER TABLE "orders" ADD COLUMN "currency" text CONSTRAINT "orders_currency_not_null" NOT NULL DEFAULT 'EUR'::text;

A rename that prints as two changes. A catalog records what exists, not how it got there. Staging renamed full_name to display_name, and the comparison sees one column gone and another one new. You have to recognize the rename.

ALTER TABLE "customers" ADD COLUMN "display_name" text;
ALTER TABLE "customers" DROP COLUMN "full_name";

Hotfixes that print as removals. Production has these and staging does not, so the output drops them. You have to decide whether production keeps them.

DROP INDEX "orders_pending_idx";
ALTER TABLE "customers" DROP COLUMN "legacy_ref";

The full output

ALTER TYPE "order_status" ADD VALUE 'refunded';
ALTER TABLE "customers" ADD COLUMN "display_name" text;
ALTER TABLE "orders" ADD COLUMN "currency" text CONSTRAINT "orders_currency_not_null" NOT NULL DEFAULT 'EUR'::text;
CREATE OR REPLACE VIEW "customer_totals" AS
SELECT customer_id,
    sum(total_cents) AS total_cents,
    count(*) AS order_count
   FROM orders
  GROUP BY customer_id;
GRANT UPDATE ON TABLE "orders" TO "app_service";
CREATE INDEX "orders_customer_created_idx" ON "orders" ("customer_id", "created_at" DESC);
ALTER TABLE "orders" ADD CONSTRAINT "orders_currency_check" CHECK ((currency = ANY (ARRAY['EUR'::text, 'USD'::text])));
DROP INDEX "orders_pending_idx";
ALTER TABLE "customers" DROP COLUMN "full_name";
ALTER TABLE "customers" DROP COLUMN "legacy_ref";
DROP TABLE "ops"."audit_events";

Swap the flags to ask the opposite question, and you get what staging would need to match production.

The full output ends by dropping ops.audit_events, which another process owns. Select only the schema you manage:

ptah schema diff --from "$PRODUCTION_URL" --to "$STAGING_URL" --schemas public

Read any result with these limits in mind:

The comparison covers structure. It does not compare rows or column order.
A result with no differences applies only to the schemas you selected.
The command does not apply the generated SQL, but it can try to create temporary helper objects while it reads. PostgreSQL refuses them in a read-only session.

The connection needs no privilege on your tables. The role we used is described below.

Why the printed SQL needs review

SQL that runs without errors can still be the wrong migration. To see the cost, we ran the --schemas public output unchanged on a copy of production. Every statement succeeded. The rows stayed, and the customer names were gone:

 id |       email       | display_name
----+-------------------+--------------
  1 | ana@example.test  |
  2 | ben@example.test  |
  3 | chen@example.test |
(3 rows)

ADD COLUMN created an empty column, and DROP COLUMN removed the old values. A rename keeps them:

ALTER TABLE customers RENAME COLUMN full_name TO display_name;

We ran that rename in the reviewed migration below. On production, the names survived, and so did the hotfix values in legacy_ref:

 id | display_name | legacy_ref
----+--------------+------------
  1 | Ana Silva    | crm-1
  2 | Ben Okafor   | crm-2
  3 | Chen Wei     | crm-3
(3 rows)

The printed SQL also drops legacy_ref and orders_pending_idx. Production still needs both, so we kept them there and added them to staging.

Reconcile both databases

Our reviewed migration differs from the printed SQL in these ways:

It renames full_name to display_name, so the names stay.
It leaves out the DROP statements for legacy_ref and orders_pending_idx, so the hotfixes stay.
It builds the new index with CONCURRENTLY, so writes keep running.

The reviewed migration

SET ROLE shop_owner;

ALTER TYPE order_status ADD VALUE 'refunded';

-- Ptah printed ADD display_name and DROP full_name. This was a rename.
ALTER TABLE customers RENAME COLUMN full_name TO display_name;

ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'EUR';
ALTER TABLE orders ADD CONSTRAINT orders_currency_check CHECK (currency IN ('EUR', 'USD'));

CREATE OR REPLACE VIEW customer_totals AS
SELECT customer_id, sum(total_cents) AS total_cents, count(*) AS order_count
FROM orders
GROUP BY customer_id;

GRANT UPDATE ON orders TO app_service;

-- CONCURRENTLY keeps writes running. It cannot run inside a transaction block.
CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at DESC);

-- Left out: the DROP statements for legacy_ref and orders_pending_idx.
-- Production needs both. Staging adopts them next.

After we ran it on production, the comparison listed only the hotfixes. Staging was now the side that lacked something, so we swapped the flags:

ptah schema diff --from "$STAGING_URL" --to "$PRODUCTION_URL" --schemas public

ALTER TABLE "customers" ADD COLUMN "legacy_ref" text;
CREATE INDEX "orders_pending_idx" ON "orders" ("created_at") WHERE (status = 'pending'::order_status);

We applied those statements to staging.

Verify the schemas and the data

We repeated the comparison in both directions with --schemas public. Both printed the same line:

Schemas are synced, no changes to be made.

That line covers the structure of the public schema. ops.audit_events stays on production, outside the selection.

Rows need their own check, because the comparison does not read them. On production, check-data.sql showed three customers with their names and legacy_ref values, and four orders with their total intact. The release query that failed at the start succeeded.

Turn the comparison into a CI check

Command	What it is for	Exit code
`schema diff`	Get the SQL for the differences	`0` when the comparison ran, with or without differences
`schema drift`	Fail a pipeline when a database differs from a reference	`0` for no drift, `1` for drift at the threshold
Either command, when the connection fails	Not applicable	`2`: the check did not run (`schema diff`, `schema drift`)

To stop a pipeline, snapshot the reference database and check the other one against it:

ptah schema inspect --db-url "$STAGING_URL" --schemas public --format sql > staging.sql
ptah schema drift --schema-file staging.sql --db-url "$PRODUCTION_URL" --schemas public

Before the reconciliation, the second command reported drift and exited 1. After it, with a fresh snapshot, the command exited 0 with No schema drift detected. --severity sets the threshold. These runs used its default, all, which fails on any drift.

The drift report before the reconciliation

Schema drift detected (highest severity: destructive).
Failure threshold: all. Failing: true.
Database: postgres://schema_reader:***@127.0.0.1:55512/shop?sslmode=disable

Findings:
- columns_removed: 2 (destructive)
- columns_added: 2 (warning)
- constraints_added: 1 (warning)
- enum_values_added: 1 (warning)
- indexes_added: 1 (warning)
- indexes_removed: 1 (warning)
- views_modified: 1 (warning)
- grants_added: 1 (safe)

To script against the comparison itself, schema diff --format json returns the statements as an array, empty once the databases match.

A staging snapshot is a useful reference when no schema file exists yet. Once the desired schema lives in Git, check production against that file instead, as in the PostgreSQL drift guide. To bring a database you already run under Ptah, with the reviewed SQL in versioned migrations, start with the existing-database trial.

Details and alternatives

Connect with a read-only role

We connected with this role on both servers. It holds no privilege on any table, and its sessions refuse writes by default:

-- Run as a superuser on both servers.
CREATE ROLE schema_reader LOGIN PASSWORD 'disposable-example';
ALTER ROLE schema_reader SET default_transaction_read_only = on;

Ptah reads the structure from the PostgreSQL system catalogs, so the role does not have to own the objects. Its output was identical to the output of a superuser connection, including the CHECK constraint and the grant. Use Ptah 0.22.0 or later with a role like this one. Earlier versions read information_schema, which hides objects from a role that does not own them.

default_transaction_read_only makes each new transaction read-only, and a session can turn it off. The missing privileges still hold after that: SELECT and UPDATE on customers were refused.

We logged every statement this role sent to production: 71 read queries plus transaction control. It also attempted 10 CREATE statements for the temporary objects Ptah uses to read PostgreSQL’s spelling of types and expressions. PostgreSQL refused them in the read-only session and logged the errors, so expect those lines in your server log. The superuser connection could write, and after it no ptah relation remained in the database.

Column order and `pg_dump`

Before the release changed anything, the two customers tables already looked different. They held phone and locale in opposite order, and their rows were not the same. Ptah reported:

Schemas are synced, no changes to be made.

A text diff of pg_dump --schema-only is the usual alternative. PostgreSQL 18.6’s pg_dump writes a random \restrict key, so two dumps of one database differ until you pass --restrict-key. With that flag, the two servers differed by the column order:

77,78c77,78
<     locale text,
<     phone text
---
>     phone text,
>     locale text

That difference can matter. SELECT * and a COPY without a column list depend on column order, and Ptah does not report it. Use the dump diff when your code relies on that order. For every other change, the dump diff leaves you to write the statements yourself. If you prefer a GUI, pgAdmin’s Schema Diff also lists differences as SQL and generates a synchronization script.

Other ways to select objects

--exclude ops.audit_events removed the same lines as --schemas public. Read the warnings when you use it. An --exclude that matches nothing prints a warning and still exits 0. A selection that would break a dependency is refused: --include public.orders exited 2 because orders references customers.

Put Ptah to work

Example files

← All articles