Add --extra-dependencies and immediate data dumping for pg_dump/pg_upgrade

On Wed, 24 Dec 2025 at 12:45, Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Hello Hackers,

We have identified a dependency issue—most notably observed with the PostGIS extension—where a table's column definition relies on data existing in another table's catalog at restore time. Because pg_dump typically separates schema and data into distinct sections, these implicit data-level dependencies are not captured, leading to failures during pg_upgrade or pg_restore.

Jakub Wartak previously reported a detailed example of this issue here: /messages/by-id/CAKZiRmwWyh-yGM8Hrvuuo04JiYFy8S4TLM-3Mn-zi9Rfqc744Q@mail.gmail.com

Ah, yes, that does sound like an issue.

Following a discussion with Alvaro Herrera, we have developed a patch based on his suggestions.

The Problem

In certain extension-heavy schemas, an object's schema definition cannot be created unless another table's data is already populated. Current pg_dump logic handles schema-to-schema dependencies via pg_depend, but it lacks a mechanism to:

Enforce a specific order for dependencies not recorded in pg_depend.
Interleave data loading with schema creation for specific tables.

Is there something that prevents PostGIS from recording this kind of
dependency in pg_depend, and by doing so force the right order in
pg_dump? It seems to me that pg_depend's model is generic enough to
enable that kind of dependency; so is the issue that pg_dump doesn't
currently track and resolve that type of dependency in a satisfactory
manner?

I'm personally not a big fan of new pg_dump and pg_upgrade options to
solve this, as they require a user input to register a dependency that
should've been stored in the catalog; it should've been handled
natively. So, if we could make it work using pg_depend instead of
expecting user input here, then that'd be very much appreciated.

Kind regards,

Matthias van de Meent

On Thu, Dec 25, 2025 at 2:22 AM Matthias van de Meent <
boekewurm+postgres@gmail.com> wrote:

On Wed, 24 Dec 2025 at 12:45, Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Hello Hackers,

We have identified a dependency issue—most notably observed with the

PostGIS extension—where a table's column definition relies on data existing
in another table's catalog at restore time. Because pg_dump typically
separates schema and data into distinct sections, these implicit data-level
dependencies are not captured, leading to failures during pg_upgrade or
pg_restore.

Jakub Wartak previously reported a detailed example of this issue here:

/messages/by-id/CAKZiRmwWyh-yGM8Hrvuuo04JiYFy8S4TLM-3Mn-zi9Rfqc744Q@mail.gmail.com

Ah, yes, that does sound like an issue.

Following a discussion with Alvaro Herrera, we have developed a patch

based on his suggestions.

The Problem

In certain extension-heavy schemas, an object's schema definition cannot

be created unless another table's data is already populated. Current
pg_dump logic handles schema-to-schema dependencies via pg_depend, but it
lacks a mechanism to:

Enforce a specific order for dependencies not recorded in pg_depend.
Interleave data loading with schema creation for specific tables.

Is there something that prevents PostGIS from recording this kind of
dependency in pg_depend, and by doing so force the right order in
pg_dump? It seems to me that pg_depend's model is generic enough to
enable that kind of dependency; so is the issue that pg_dump doesn't
currently track and resolve that type of dependency in a satisfactory
manner?

I'm personally not a big fan of new pg_dump and pg_upgrade options to
solve this, as they require a user input to register a dependency that
should've been stored in the catalog; it should've been handled
natively. So, if we could make it work using pg_depend instead of
expecting user input here, then that'd be very much appreciated.

Thanks for the feedback, Matthias; I agree with your assessment. Currently,
Postgres lacks a native mechanism for tracking dependencies between a table
and the specific rows of another table. While certain extensions like
PostGIS introduce these patterns, they remain non-standard edge cases.
Implementing a fix in the core backend seems like overkill for this
scenario. Since the failure is specific to the upgrade path, targeting
pg_dump and pg_upgrade is a significantly less invasive approach. Notably,
this patch triggers an immediate dump of the referenced table data -- an
unconventional behavior that is better handled in the client binaries than
in the backend. Consequently, this approach would require new options for
these binaries to explicitly inject those dependency details.

Kind regards,

Matthias van de Meent

Regards,

--
*Jeevan Chalke*
*Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com&gt;

On 01.01.26 14:43, Jeevan Chalke wrote:

Thanks for the feedback, Matthias; I agree with your assessment.
Currently, Postgres lacks a native mechanism for tracking dependencies
between a table and the specific rows of another table. While certain
extensions like PostGIS introduce these patterns, they remain non-
standard edge cases.

Implementing a fix in the core backend seems like overkill for this
scenario. Since the failure is specific to the upgrade path, targeting |
pg_dump| and |pg_upgrade| is a significantly less invasive approach.
Notably, this patch triggers an immediate dump of the referenced table
data -- an unconventional behavior that is better handled in the client
binaries than in the backend. Consequently, this approach would require
new options for these binaries to explicitly inject those dependency
details.

How about this: postgis should define its table spatial_ref_sys as
user_catalog_table, and we change pg_dump to dump the contents of user
catalog tables before other DDL.

There is still some work to do here, but at least this sounds like a
more principled approach.