On Fri, Nov 6, 2026 at 9:28 AM Zsolt Parragi <[email protected]> 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
