Re: Does postgresql have a diff tool?

On 14 Sep 2026, Graham Leggett wrote:
> I have a piece of code that modifies a postgresql database, and I need
> to know precisely what changes this piece of code has made to the
> database. The code contains an ORM tool so it is a mystery what the
> tool is doing.
>
> Does there exist an equivalent of pg_dump that is capable of dumping a
> database in a deterministic order so that a standard diff will make
> sense?

Hi Graham,

pg_dump's object ordering is already deterministic — two other things
get in the way of a plain diff, and neither is the ordering.

1. pg_dump wraps its output in psql's \restrict <key> / \unrestrict
<key>, and the key is regenerated on every run, so two dumps of an
unchanged database always differ in two lines. Pin it:

pg_dump --schema-only --restrict-key=whatever
Checked just now on 19beta2 and 18.4: two consecutive dumps with a
fixed key are byte-identical, and with the default random key they
differ in exactly those two lines and nothing else.

2. The data is dumped in heap order, not key order — an UPDATE
physically relocates the row. In a 3-row test table, after UPDATE ...
WHERE id = 1 the COPY block came out in the order 2, 1, 4. Nothing in
pg_dump sorts it, and --inserts doesn't change that. For a diffable
data dump you have to sort per table yourself: COPY (SELECT * FROM t
ORDER BY 1) TO ....

That gives you a diff that means something. But for your actual
question it's worth saying: a diff only shows the net result. It can't
show you an INSERT that was later deleted, or the order in which
things happened. Two features answer that exactly.

Data changes — create a logical slot before running the code
(wal_level = logical):

SELECT * FROM pg_create_logical_replication_slot('orm_watch', 'test_decoding');
-- run the code --
SELECT data FROM pg_logical_slot_get_changes('orm_watch', NULL, NULL);

With ALTER TABLE ... REPLICA IDENTITY FULL you get before-images as well:

table public.clientes: UPDATE: old-key: id:1 plan:'free' new-tuple:
id:1 plan:'pro'

That is read from WAL, in commit order — what actually happened, not
what happens to be left at the end.

Schema changes — logical decoding doesn't decode DDL, but an event
trigger does: ON ddl_command_end calling
pg_event_trigger_ddl_commands(), storing current_query() for the
literal statement. Add an sql_drop trigger for drops. Useful side
effect: because the trigger writes rows, the DDL also shows up in the
logical stream, interleaved with the DML in the right order.

If you'd rather compare two live databases, migra and pg-schema-diff
emit a DDL delta instead of a text diff.

And the cheapest option, if you only want to see what SQL the ORM
emits: log_statement = 'all' (settable per role or database with ALTER
ROLE ... SET), or pgaudit.

Regards,
Manu

El lun, 14 sept 2026 a las 9:38, Daniel Gustafsson (<daniel(at)yesql(dot)se>) escribió:
>
> > On 14 Sep 2026, at 14:33, Graham Leggett <minfrin(at)sharp(dot)fm> wrote:
>
> > I have a piece of code that modifies a postgresql database, and I need to know precisely what changes this piece of code has made to the database. The code contains an ORM tool so it is a mystery what the tool is doing.
> >
> > Does there exist an equivalent of pg_dump that is capable of dumping a database in a deterministic order so that a standard diff will make sense?
>
> This is a question better suited for the pgsql-general@ mailinglist. There are
> a number of schema diff tools available, but I cannot comment on the merits of
> them as I've never tried them myself.
>
> --
> Daniel Gustafsson
>
>
>

--
Saludos cordiales,

Manuel Reyes