AW: Does postgresql have a diff tool?

Hi Graham,

concerning the non-deterministic COPY blocks inside the Dump:

I use the following bash function / awk script to get a deterministic order:

function sort_pg_copy_statements() {
local AWKSCRIPT='
BEGIN {
# effectively disable field separator:
FS="\n"
PROCINFO["sorted_in"] = "@ind_str_asc"
}
/^\\\./ {
in_copy_stmt = 0
for (i in a)
print a[i]
delete a
}
in_copy_stmt {
a[$0]=$0
}
!in_copy_stmt {
print $0
}
/^COPY / {
in_copy_stmt = 1
}'

cat | awk "$AWKSCRIPT"
}

Use it like this:

pg_dump --your --params | sort_pg_copy_statements

Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Manuel Reyes Bravo <manuelreyesbravo(at)gmail(dot)com>
Gesendet: Montag, 14. September 2026 14:56
An: Graham Leggett <minfrin(at)sharp(dot)fm>
Cc: Daniel Gustafsson <daniel(at)yesql(dot)se>; pgsql-general(at)lists(dot)postgresql(dot)org
Betreff: 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