pg_dump --exclude-table loses FOR ALL TABLES EXCEPT publications
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.
On Fri, Nov 6, 2026 at 9:28 AM Zsolt Parragi <zsolt.parragi@percona.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]https://www.postgresql.org/docs/current/app-pgdump.html 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
On Fri, Nov 6, 2026 at 4:35 AM Peter Smith <smithpb2250@gmail.com> wrote:
On Fri, Nov 6, 2026 at 9:28 AM Zsolt Parragi <zsolt.parragi@percona.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
On Fri, Nov 6, 2026 at 9:08 AM shveta malik <shveta.malik@gmail.com> wrote:
On Fri, Nov 6, 2026 at 4:35 AM Peter Smith <smithpb2250@gmail.com> wrote:
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.
+1. I also think the current behavior is expected. I see an argument
of adding this behaviour in pg-dump docs but it is not clear to me if
doing so really adds value. OTOH, if others think that will be
helpful, I won't mind adding it.
--
With Regards,
Amit Kapila.
"When -t is specified, pg_dump makes no attempt to dump any other
database objects that the selected table(s) might depend upon.
That note is actually my problem: because it documents the opposite
case: it says that when we exclude some tables, we do not dump any
other database object depending on it. That the quoted limitation is
for -e, -n, -t, while the issue I reported is about -N, -T and
--exclude-table-and-children.
And also note the inconsistency: with -e, -n and -t, we do what is
documented, resulting in a consistent restore that produces no errors
and no publications.
With the other options, it is the opposite: we dump publications that
depend on excluded database objects. Consequently, the restore ends
without the publication and prints errors.
So while I am also leaning towards simply documenting this, as I think
the current behavior is better than the alternative, we are
documenting an inconsistency. (And that's also the reason why I think
it is important to document this)
The comment for selectDumpablePublicationObject is also similarly incorrect:
A publication can have schemas and tables which have schemas, but those are
ignored in decision making, because publications are only dumped when we are
dumping everything.
When we exclude some tables, we clearly do not dump everything, but we
still dump publications.
As I wrote earlier, I think that keeping the current behavior is
correct: there's a reasonable chance that the user restores the
missing excluded table before the main dump, in which case we want to
have these publications there for completeness, excluding them would
be the wrong choice.
OTOH, if others think that will be
helpful, I won't mind adding it.
So I think expanding the note about this, and adjusting the code
comment would be an improvement, see attached patch. But if everyone
else disagrees, I am also fine with leaving it as-is.