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.
