[PATCH v1] Allow excluded tables to remain in the pg_restore data-loading path for directory-format dumps
Hackers,
This patch adds a directory-format pg_dump option that preserves TABLE DATA
restore entries for excluded tables by writing empty COPY placeholder
files, allowing those files to be replaced before pg_restore.
While over 500 lines changed. Only 79 Lines of code were added, 5 removed!
Almost half is tests for various edge cases!
Motivation
Our pg_dumps of critical data finishes in under 20 minutes. We exclude 6
very large tables. But this dump is then partially useful. For Staging,
we then have a manual process to pull in limited amounts of data on those 6
tables, and re-index. It's painful. With this patch, we can run 6 simple
COPY commands of those tables (limiting to recent data), replacing the
NNN.dat files and voila, a normal pg_restore and we are finished!
With this option, excluded tables still appear in the archive as TABLE DATA
entries with the usual COPY statement, and a per-table data file is created
containing only the COPY end marker (\.). Before restore, those placeholder
files can be replaced with externally generated COPY data. pg_restore then
loads the tables in the normal order, including loading data before
dependent index creation, without extra post-restore steps.
The patch
The new option is --create-table-data-placeholders. It requires
--exclude-table-data or --exclude-table-data-and-children, and is supported
only for directory-format dumps (-Fd) that use COPY data (the default). It
is rejected for --inserts, --column-inserts, --rows-per-insert,
--schema-only, and --no-data.
For excluded ordinary tables, pg_dump now emits the TABLE DATA TOC entry
and creates the numbered .dat file, but does not copy table rows. Sequences
and materialized views are unchanged: they keep the existing exclusion
behavior, since they are not restored from replaceable table data files in
this workflow.
Documentation and a TAP test (src/bin/pg_dump/t/
012_pg_dump_empty_excluded_data.pl) are included.
There are 28 individual TAP assertions in one new test file!
Testing
The new TAP test covers option validation, placeholder file contents,
restore of both included and excluded tables, and confirmation that
excluded sequences and materialized views are not affected by the flag.
Comments welcome. I am happy to adjust naming, documentation, or scope
based on feedback.
Kirk
Attachments:
v1-0001-pg-dump-table-data-placeholders.patchapplication/octet-stream; name=v1-0001-pg-dump-table-data-placeholders.patchDownload+358-7
On 23 Jun 2026, at 03:17, Kirk Wolak <wolakk@gmail.com> wrote:
Comments welcome. I am happy to adjust naming, documentation, or scope based on feedback.
Hi Kirk,
+1 on the problem. Excluding a few huge tables to keep the dump fast,
but still wanting a loadable slot so a recent slice can be dropped in
and restored in the normal order (data before indexes/FKs, no manual
reindex) is a real, recurring need. I'd be glad to see it solved.
For the archive: the broader "filter table data on dump" idea has come
up many times, and it may help to frame this patch against that history:
- 2008, Simon Riggs: pg_dump -w for sampling [0]/messages/by-id/1216999182.3894.930.camel@ebony.2ndQuadrant. Redirected -
"why not COPY (SELECT ...) TO STDOUT".
- 2018, Carter Thaxton: --include-table-data-where, then --where [1]/messages/by-id/CAGiT_HNav5B=OfCdfyFoqTa+oe5W1vG=PXkTETCxXg4kcUTktA@mail.gmail.com.
Euler Taveira's concerns: a filtered subset "won't restore" (FKs),
and whether pg_dump is the right place for an ETL-ish parameter;
plus table:clause quoting/colon parsing. Returned with feedback.
- 2020, Surafel Temesgen: --where revival [2]/messages/by-id/CALAY4q8o00Sg5nemi2AuqNuLvmGLaR6br+YrvM41ZuSPjM2Qvg@mail.gmail.com. Daniel Gustafsson
flagged arbitrary user SQL as a search_path / CVE-2018-1058-class
hazard; no tests. Returned with feedback.
- 2022, Nikita Starovoitov: partial data dumps [3]/messages/by-id/CAC5EiNMthnPAKugDj0D8-kfn21hPQnOuvDAMOHa4TDmzGYon4w@mail.gmail.com. Stalled on
referential integrity - by the author's own account he could not
solve it within the dump.
- 2025, your own --filter-data PoC [4]/messages/by-id/CACLU5mS07WGPpq6=m8aC5tUMusNpL8FhBPnYem86iSawEgcavw@mail.gmail.com. Still open; Tom suggested
partitioning, Greg suggested dumping a view (-t foo=view:fooslice).
Two objections recur across those: "just use COPY (SELECT ...) TO",
and "a raw row filter produces a dump that won't restore (FKs)". The
placeholder approach sits entirely clear of both. There is no user
WHERE clause, so no ETL surface, no search_path hazard, and no
consistency promise: as Carter noted in 2018, --where=table:false
already behaves exactly like --exclude-table-data, and the placeholder
is precisely that corner - identical restore-risk, it just keeps a
loadable empty TABLE DATA entry instead of dropping it. And the reason
this belongs in pg_dump rather than a hand-rolled \copy is the point
David G. Johnston made in 2018: it lets you lean on pg_dump's
dependency resolution - which is exactly what the placeholder buys, the
(externally produced) data lands on the normal restore path, ahead of
indexes and FKs, no post-restore reindex. So I'd pitch it as "reserve a
loadable slot", not "lightweight --where".
On the interface, a few options to discuss:
1. As posted: --create-table-data-placeholders as a global modifier
to --exclude-table-data. Works, but "create" already means CREATE
DATABASE in pg_dump, it's all-or-nothing across excluded tables,
and it couples two options.
2. A positive pattern option, e.g. --empty-table-data=PATTERN (plus
--empty-table-data-and-children), mirroring the --exclude-table-data
family. It folds the two current flags into one, reads as "include
the table, empty", and gives per-table control. Slight preference
here.
3. A matching action for the table_data object type in the --filter
file, for people (like you) who keep the big-table list in a file.
Heavier - it's a grammar change to a shared file format - so I'd
see it as an addition to 2, not a replacement.
WDYT?
Thank you!
Best regards, Andrey Borodin.
[0]: /messages/by-id/1216999182.3894.930.camel@ebony.2ndQuadrant
[1]: /messages/by-id/CAGiT_HNav5B=OfCdfyFoqTa+oe5W1vG=PXkTETCxXg4kcUTktA@mail.gmail.com
[2]: /messages/by-id/CALAY4q8o00Sg5nemi2AuqNuLvmGLaR6br+YrvM41ZuSPjM2Qvg@mail.gmail.com
[3]: /messages/by-id/CAC5EiNMthnPAKugDj0D8-kfn21hPQnOuvDAMOHa4TDmzGYon4w@mail.gmail.com
[4]: /messages/by-id/CACLU5mS07WGPpq6=m8aC5tUMusNpL8FhBPnYem86iSawEgcavw@mail.gmail.com