Re: Order of tables dumped by pg_dump

Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> 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.

That's not supposed to happen (and we have regression tests that rely
on it not happening). Are you sure that the databases are really
identical? One thing I'd check is if the database locales are the
same. The primary sort key inside pg_dump is usually object name,
so you could conceivably get an ordering that depends on locale,
especially if there's any non-ASCII letters in the object names.

> 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.

See sortDumpableObjectsByTypeName (the initial name-based sort)
and sortDumpableObjects (fixes up any dependency problems).

regards, tom lane