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 <[email protected]>
Gesendet: Montag, 14. September 2026 14:56
An: Graham Leggett <[email protected]>
Cc: Daniel Gustafsson <[email protected]>; [email protected]
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 (<[email protected]>) escribió:
>
> > On 14 Sep 2026, at 14:33, Graham Leggett <[email protected]> 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
