pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes postgresql have a diff tool?
4+ messages / 3 participants
[nested] [flat]
* Does postgresql have a diff tool?
@ 2026-09-14 12:33 Graham Leggett <minfrin@sharp.fm>
0 siblings, 1 reply; 4+ messages in thread
From: Graham Leggett @ 2026-09-14 12:33 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
Hi all,
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?
Regards,
Graham
--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does postgresql have a diff tool?
@ 2026-09-14 12:37 Daniel Gustafsson <daniel@yesql.se>
parent: Graham Leggett <minfrin@sharp.fm>
0 siblings, 1 reply; 4+ messages in thread
From: Daniel Gustafsson @ 2026-09-14 12:37 UTC (permalink / raw)
To: Graham Leggett <minfrin@sharp.fm>; +Cc: pgsql-hackers@lists.postgresql.org
> On 14 Sep 2026, at 14:33, Graham Leggett <minfrin@sharp.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does postgresql have a diff tool?
@ 2026-09-14 12:56 Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 4+ messages in thread
From: Manuel Reyes Bravo @ 2026-09-14 12:56 UTC (permalink / raw)
To: Graham Leggett <minfrin@sharp.fm>; +Cc: Daniel Gustafsson <daniel@yesql.se>; pgsql-general@lists.postgresql.org
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@yesql.se>) escribió:
>
> > On 14 Sep 2026, at 14:33, Graham Leggett <minfrin@sharp.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does postgresql have a diff tool?
@ 2026-09-14 15:25 Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
parent: Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Manuel Reyes Bravo @ 2026-09-14 15:25 UTC (permalink / raw)
To: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; +Cc: Graham Leggett <minfrin@sharp.fm>; Daniel Gustafsson <daniel@yesql.se>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.Faerber@stmuk.bayern.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@gmail.com>
> Gesendet: Montag, 14. September 2026 14:56
> An: Graham Leggett <minfrin@sharp.fm>
> Cc: Daniel Gustafsson <daniel@yesql.se>; pgsql-general@lists.postgresql.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@yesql.se>) escribió:
> >
> > > On 14 Sep 2026, at 14:33, Graham Leggett <minfrin@sharp.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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-09-14 15:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-14 12:33 Does postgresql have a diff tool? Graham Leggett <minfrin@sharp.fm>
2026-09-14 12:37 ` Daniel Gustafsson <daniel@yesql.se>
2026-09-14 12:56 ` Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
2026-09-14 15:25 ` Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox