Re: pg_dump --exclude-table loses FOR ALL TABLES EXCEPT publications

On Fri, Nov 6, 2026 at 4:35 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Fri, Nov 6, 2026 at 9:28 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
> >
> > Hello
> >
> > pg_dump writes the EXCEPT list of a FOR ALL TABLES publication inline
> > into CREATE PUBLICATION. If an excluded table is not part of the dump,
> > restoring it into an empty database loses the whole publication:
> >
> > CREATE TABLE keep (a int);
> > CREATE TABLE secret (a int);
> > CREATE PUBLICATION p_all FOR ALL TABLES EXCEPT (TABLE secret);
> >
> > $ pg_dump -T secret postgres > dump.sql
> > $ psql -d restored -f dump.sql
> > ERROR: relation "public.secret" does not exist
> > ERROR: publication "p_all" does not exist
> >
> > -N, --exclude-table-and-children and --filter behave the same way.
> >
> > I am not sure if there is a good fix for this - for example skipping
> > the except clause would make the create publication work correctly in
> > the dump, but then it can result in accidetanlly publishing the table
> > later. So maybe this should be just documented as a pg_dump
> > limitation?
> >
> > There are also a few similar corner cases on 18+ that result in
> > errors: -T keeps "ALTER PUBLICATION ... ADD TABLE ONLY <excluded
> > table>" and -N keeps "ADD TABLES IN SCHEMA <excluded schema>", and
> > both fail on restore, but in these cases all source tables are missing
> > from the restored database.
> >
>
> I don't really see this as a "limitation" -- if the user omits some
> expected objects, then it's unsurprising something might break
> later...

+1. We had internally discussed this case while the implmentation of
EXCEPT, and since the other publication related commands mentioned in
the reported email also behaves the same, we planned to leave EXCEPT
behaviour as is.

> There are already notes [1] about this kind of thing for "-t" and "-n". e.g.
> "When -t is specified, pg_dump makes no attempt to dump any other
> database objects that the selected table(s) might depend upon.
> Therefore, there is no guarantee that the results of a specific-table
> dump can be successfully restored by themselves into a clean
> database."
>
> ~
>
> IMO, the "fix" (if deemed necessary) for this is just to add similar
> notes for "-T", "-N", and "--filter". Or some generic note about them
> in the "Notes" section at the bottom.
>

I think we are good with the current notes, but let's see what others
have to say on this.

thanks
Shveta