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

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

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.

======
[1] https://www.postgresql.org/docs/current/app-pgdump.html

Kind Regards,
Peter Smith.
Fujitsu Australia