Re: Order of tables dumped by pg_dump

On Fri, 15 May 2026 at 15:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Thanks for the confirmation.

I checked that none of my table names match [^a-z0-9_] and I always use
UTF-8 encoding with C locale.

However, I didn't think to mention that the database is running 14.22 while
the client pg_dump is 18.1.

I tried to run a test in which I restored the two dumps, then dumped the
result, to see if the order of the table definitions in the dumps mattered.
Unfortunately I found what seems to be a bug in pg_dump relating to the
dumping of inheritance hierarchies (see below).

I did verify that the dumps were identical except for the order of the 3
affected tables, so whatever is causing the difference is not something
that appears in the dumped objects; it only affects their order. This is
why I suspected dependencies from another schema not included in the dump
but there are none. However, I noticed that there are dependencies from the
same tables that trigger the bug in pg_dump. So I suspect the re-ordering
of being another symptom of the bug.

The bug is that inheritance hierarchies can be dumped wrong.

I created a test case:

odyssey=> begin read write;
BEGIN
odyssey=*> create table test_parent (f integer);
CREATE TABLE
odyssey=*> create table test_child (primary key (f)) inherits (test_parent);
CREATE TABLE
odyssey=*> commit;
COMMIT

This dumps as follows:

[ omit header stuff ]

CREATE TABLE ijmorlan.test_parent (
f integer
);

[ omit ownership ]

CREATE TABLE ijmorlan.test_child (
NOT NULL f
)
INHERITS (ijmorlan.test_parent);

[ omit more stuff ]

The CREATE TABLE for test_child is not syntactically valid.

Note that there is no primary key for the parent. There are also no rows in
that table. So it's a bit unusual.

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

Thanks, I will take a look.