Order of tables dumped by pg_dump

From: Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Order of tables dumped by pg_dump
Date: 2026-05-15 18:36:58
Message-ID: CAMsGm5ctgdCpiCAVPVR5wDN9YXGB3tNdYwjtEyszq9FTOybLDg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Can somebody help me understand the order in which pg_dump produces tables?

I sometimes write migration scripts to apply schema changes. Part of
validating these scripts involves running my schema file (a file or files
which create the schema from scratch, with CREATE TABLE and other
statements), then dumping the schema. Next apply the draft migration script
to a copy of production, then dump the schema again.

Ideally, the schema dump obtained by applying the migration script to
production and the one obtained by running the migration script on a copy
of production should be identical.

In the past I used to have problems with things being out of order,
sometimes leading to large diffs that were really just re-orderings. More
recent Postgres versions have been better. Sometimes small "real" diffs
result in large text diffs, but I think this is due to dependency changes
and probably hard to avoid.

Right now however I'm back to a situation where 3 tables are generated
differently in the 2 dumps. In one, all 3 are together while in the other
they are in 3 different locations. As a result I have 8 diffs: 4 for the
table definitions, and 4 for the permissions. I have verified that the
differences are only an ordering difference by diffing the output of \d+
for the 3 tables and by diffing the two schemas after sorting their
respective lines.

So I have 2 identical schemas that produce different dumps. This makes me
wonder if the pg_dump order is not entirely deterministic and maybe some
ORDER BYs are missing somewhere, resulting in the order of the data in the
system tables affecting the output.

If somebody can point me at the part of the pg_dump code that determines
the table order, I'd be interested in taking a look and trying to make the
order more deterministic. Any other hints welcome as well. I am working
with Postgres 18.1.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Sami Imseih 2026-05-15 19:36:22 Re: Vacuumlo improvements
Previous Message Ayush Tiwari 2026-05-15 18:33:35 Re: [PATCH] refint: Avoid reusing cascade UPDATE plans.