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

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Subject: pg_dump --exclude-table loses FOR ALL TABLES EXCEPT publications
Date: 2026-11-05 22:28:07
Message-ID: CAN4CZFPCu+6mMQqLEFuU8t0yGct-VFdtsanC2aSier5eGYskAw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Chao Li 2026-11-05 22:38:00 Re: Remove obsolete locking comment from AddBufferToRing()
Previous Message surya poondla 2026-11-05 22:24:14 Re: Reporting WAL replay progress during pre-consistency standby reovery