Horton

Thread

Commits

  1. Doc: warn that parallel pg_restore may fail if --no-schema was used.

  1. docs: warn about post-data-only schema dumps with parallel restore.

    vaibhave postgres <postgresvaibhave@gmail.com> — 2026-01-26T05:23:19Z

    Hi hackers,

Following up on the discussion in [1] about pg_restore failing to restore
post-data items due to circular foreign key deadlocks.

I’m attaching a doc patch that adds a warning about using post-data-only
schema dumps together with parallel restore.

Thanks.

[1] Re: pg_restore: fails to restore post-data items due to circular FK
deadlock - Mailing list pgsql-bugs : Postgres Professional
<https://postgrespro.com/list/id/3908.1717093769@sss.pgh.pa.us>

  2. Re: docs: warn about post-data-only schema dumps with parallel restore.

    David G. Johnston <david.g.johnston@gmail.com> — 2026-01-27T16:51:58Z

    On Sun, Jan 25, 2026 at 10:23 PM vaibhave postgres <
postgresvaibhave@gmail.com> wrote:

> Hi hackers,
>
> Following up on the discussion in [1] about pg_restore failing to restore
> post-data items due to circular foreign key deadlocks.
>
> I’m attaching a doc patch that adds a warning about using post-data-only
> schema dumps together with parallel restore.
>
> Thanks.
>
> [1] Re: pg_restore: fails to restore post-data items due to circular FK
> deadlock - Mailing list pgsql-bugs : Postgres Professional
> <https://postgrespro.com/list/id/3908.1717093769@sss.pgh.pa.us>
>

The note element would align with the sibling para element.

Not a fan of the patch overall though.  I'd want to add something to
pg_restore noting that use of --jobs for constraint restoration needs
schema information to compute the restoration order.

There is also just a lot of detail here when something like:

<para>Consider always combining pre-data and post-data in the same command
so that parallel restores have the necessary dependency information to
create constraints in parallel.</para>

Any other content related to this probably belongs in the Notes section.

David J.

  3. Re: docs: warn about post-data-only schema dumps with parallel restore.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-29T18:33:44Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sun, Jan 25, 2026 at 10:23 PM vaibhave postgres <
> postgresvaibhave@gmail.com> wrote:
>> Following up on the discussion in [1] about pg_restore failing to restore
>> post-data items due to circular foreign key deadlocks.
>> I’m attaching a doc patch that adds a warning about using post-data-only
>> schema dumps together with parallel restore.

> Not a fan of the patch overall though.  I'd want to add something to
> pg_restore noting that use of --jobs for constraint restoration needs
> schema information to compute the restoration order.

Yeah, dropping this into the list of options is bad.  We put caveats
like that into the Notes section usually.

I also tend to think that it'd be better to document this under
pg_restore: when people run into this type of failure, they are going
to go to the pg_restore docs not the pg_dump docs to understand it.
I guess there could be a case for repeating the info in both the
pg_dump and pg_restore pages, but that feels a bit verbose.

So maybe like the attached?

			regards, tom lane

  4. Re: docs: warn about post-data-only schema dumps with parallel restore.

    David G. Johnston <david.g.johnston@gmail.com> — 2026-03-30T03:20:20Z

    On Sun, Mar 29, 2026 at 11:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Sun, Jan 25, 2026 at 10:23 PM vaibhave postgres <
> > postgresvaibhave@gmail.com> wrote:
> >> Following up on the discussion in [1] about pg_restore failing to
> restore
> >> post-data items due to circular foreign key deadlocks.
> >> I’m attaching a doc patch that adds a warning about using post-data-only
> >> schema dumps together with parallel restore.
>
> > Not a fan of the patch overall though.  I'd want to add something to
> > pg_restore noting that use of --jobs for constraint restoration needs
> > schema information to compute the restoration order.
>
> Yeah, dropping this into the list of options is bad.  We put caveats
> like that into the Notes section usually.
>
> I also tend to think that it'd be better to document this under
> pg_restore: when people run into this type of failure, they are going
> to go to the pg_restore docs not the pg_dump docs to understand it.
> I guess there could be a case for repeating the info in both the
> pg_dump and pg_restore pages, but that feels a bit verbose.
>
> So maybe like the attached?
>
>
Works for me.

But how about adding something like the following to the pg_dump notes?  We
already have the corresponding link going to pg_dump in the pg_restore
notes.

"If producing a non-plaint-text format output see also the pg_restore
documentation for details on how the restore process uses the different
sections."

David J.

  5. Re: docs: warn about post-data-only schema dumps with parallel restore.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-31T18:31:20Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> But how about adding something like the following to the pg_dump notes?  We
> already have the corresponding link going to pg_dump in the pg_restore
> notes.

> "If producing a non-plaint-text format output see also the pg_restore
> documentation for details on how the restore process uses the different
> sections."

Hmm, I think we could be a bit more definite than that.  What do you
think of this advice:

  <para>
   When creating an archive (non-text) output file, it is advisable not to
   restrict the set of database objects dumped, but instead plan to apply
   any desired object filtering when reading the archive
   with <application>pg_restore</application>.  This will preserve
   flexibility and possibly avoid problems at restore time; for details
   see the <xref linkend="app-pgrestore"/> documentation.  However,
   omitting table data (<option>--no-data</option>) or large objects
   (<option>--no-large-objects</option>) does not have any surprising
   consequences.
  </para>

			regards, tom lane

  6. Re: docs: warn about post-data-only schema dumps with parallel restore.

    David G. Johnston <david.g.johnston@gmail.com> — 2026-03-31T19:48:31Z

    On Tuesday, March 31, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > But how about adding something like the following to the pg_dump notes?
> We
> > already have the corresponding link going to pg_dump in the pg_restore
> > notes.
>
> > "If producing a non-plaint-text format output see also the pg_restore
> > documentation for details on how the restore process uses the different
> > sections."
>
> Hmm, I think we could be a bit more definite than that.  What do you
> think of this advice:
>
>   <para>
>    When creating an archive (non-text) output file, it is advisable not to
>    restrict the set of database objects dumped, but instead plan to apply
>    any desired object filtering when reading the archive
>    with <application>pg_restore</application>.  This will preserve
>    flexibility and possibly avoid problems at restore time; for details
>    see the <xref linkend="app-pgrestore"/> documentation.  However,
>    omitting table data (<option>--no-data</option>) or large objects
>    (<option>--no-large-objects</option>) does not have any surprising
>    consequences.
>   </para>
>
>
I’m against including that final sentence.  The rest seems ok but I’
suggest going with an explicit mention that “—no-schema is risky” (or
otherwise omitting the entire section)

I have a nagging suspicion we could be a bit more precise; e.g., it’s
advisable to include the schema objects for the data that is being exported
only, not the entire schema always.  But we already mention that dealing in
subsets can introduce dependency issues so people have already been given
the alert there.  But data/no-schema seems like it should just work and
this just needs to warn them that it may not.

David J.

  7. Re: docs: warn about post-data-only schema dumps with parallel restore.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-31T20:00:31Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, March 31, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm, I think we could be a bit more definite than that.  What do you
>> think of this advice:
>> 
>> <para>
>> When creating an archive (non-text) output file, it is advisable not to
>> restrict the set of database objects dumped, but instead plan to apply
>> any desired object filtering when reading the archive
>> with <application>pg_restore</application>.  This will preserve
>> flexibility and possibly avoid problems at restore time; for details
>> see the <xref linkend="app-pgrestore"/> documentation.  However,
>> omitting table data (<option>--no-data</option>) or large objects
>> (<option>--no-large-objects</option>) does not have any surprising
>> consequences.
>> </para>

> I’m against including that final sentence.  The rest seems ok but I’
> suggest going with an explicit mention that “—no-schema is risky” (or
> otherwise omitting the entire section)

How about replacing that sentence with "In particular, dumping table
data without the corresponding table definition (via --no-schema and
related options) is not recommended."

			regards, tom lane

  8. Re: docs: warn about post-data-only schema dumps with parallel restore.

    David G. Johnston <david.g.johnston@gmail.com> — 2026-03-31T20:04:21Z

    On Tuesday, March 31, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Tuesday, March 31, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Hmm, I think we could be a bit more definite than that.  What do you
> >> think of this advice:
> >>
> >> <para>
> >> When creating an archive (non-text) output file, it is advisable not to
> >> restrict the set of database objects dumped, but instead plan to apply
> >> any desired object filtering when reading the archive
> >> with <application>pg_restore</application>.  This will preserve
> >> flexibility and possibly avoid problems at restore time; for details
> >> see the <xref linkend="app-pgrestore"/> documentation.  However,
> >> omitting table data (<option>--no-data</option>) or large objects
> >> (<option>--no-large-objects</option>) does not have any surprising
> >> consequences.
> >> </para>
>
> > I’m against including that final sentence.  The rest seems ok but I’
> > suggest going with an explicit mention that “—no-schema is risky” (or
> > otherwise omitting the entire section)
>
> How about replacing that sentence with "In particular, dumping table
> data without the corresponding table definition (via --no-schema and
> related options) is not recommended."
>
>
That should work.

David J.

  9. Re: docs: warn about post-data-only schema dumps with parallel restore.

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-31T20:36:32Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, March 31, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> How about replacing that sentence with "In particular, dumping table
>> data without the corresponding table definition (via --no-schema and
>> related options) is not recommended."

> That should work.

Done that way, then.

			regards, tom lane