Re: Does postgresql have a diff tool?

Hi Franz-Josef,

Nice trick — the rule ordering that prints \. after the flush is neat.
Two things I hit when I ran it, both of which fail silently, which
matters given what Graham is after:

1. PROCINFO["sorted_in"] is a gawk extension. On Debian/Ubuntu
/usr/bin/awk is mawk, which ignores it without a word. Same script,
same input:

gawk: 1 a / 10 j / 2 b / 3 c
mawk: 2 b / 10 j / 1 a / 3 c (mawk 1.3.4, Ubuntu 22.04)

So on the most common server distro it produces an arbitrary order and
still looks like it worked. Calling gawk explicitly fixes it.

2. The rows are stored as array keys (a[$0]=$0), so duplicate rows
collapse. My test block had 5 data rows, two of them identical, and
both awks emitted 4. In a table without a unique constraint that
silently hides a real difference — exactly the kind of change Graham
is trying to find. One fix that keeps the rest of the script as is:

a[$0 SUBSEP (++n)] = $0

SUBSEP sorts below the printable range, so identical prefixes still
order correctly and duplicates survive.

Regards,
Manu

El lun, 14 sept 2026 a las 10:43, Färber, Franz-Josef (StMUK)
(<Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>) escribió:
>
> 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
>
>

--
Saludos cordiales,

Manuel Reyes