Re: Non-text mode for pg_dumpall

On Thu, 18 Jun 2026 at 19:13, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 2026-06-17 We 5:59 PM, Noah Misch wrote:
>
> On Mon, Jun 15, 2026 at 04:11:16PM -0400, Andrew Dunstan wrote:
>
> On 2026-06-06 Sa 8:02 PM, Noah Misch wrote:
>
> On Thu, Feb 26, 2026 at 09:02:48AM -0500, Andrew Dunstan wrote:
>
> pushed with a slight tweak.
>
> Having now reviewed commit 763aaa0, I don't think it's ready to remain part of
> v19. While some points from my v18 review are now resolved, other points
> still seem unresolved. I didn't find discussion of the unresolved points. I
> also see new issues.
>
> OK, here's a reversion path. It's a bit messy, and I didn't touch the
> release notes, but apart from that I think it does the right thing.
>
> I checked "git diff 763aaa06f03^ src/bin/pg_dump/{*.c,*.h,t}" and I agree this
> reverts everything. Thanks for working through the revert.
>
>
> Thanks for checking. Pushed with the release notes update.
>
> cheers
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com

Thanks Noah and Andrew for the feedback. Based on review comments, I
re-implemented these patches.

Add non-text output formats to pg_dumpall

Teach pg_dumpall to write custom, directory, and tar archive output,
in addition to its existing plain-text SQL script format, and teach
pg_restore to restore a whole cluster from such an archive.

Design
------

pg_dumpall gains a --format/-F option (plain, custom, directory, tar;
plain remains the default). For a non-plain format, --file names a
directory rather than a file. That directory holds:

- toc.glo, a custom-format archive of global objects: roles,
tablespaces, and the DROP statements needed to remove databases,
roles, and tablespaces that already exist on the target before
recreating them.
- map.dat, a plain-text list mapping each database's OID to its
name, so pg_restore knows what to restore and in what order.
Lines can be commented out with '#' to skip a database.
- a "databases" subdirectory holding one regular pg_dump archive per
database, named by OID, produced by invoking pg_dump as a
subprocess for each one.

Each global object gets a single archive entry built the same way
pg_dump.c builds its own entries: the SQL is assembled once and
handed to ArchiveEntry() with both a .createStmt and a .dropStmt, and
the archiver's normal restore-time drop pass (gated on --clean)
decides whether the drop actually runs. Roles, tablespaces, and the
per-database drop entries are cluster-wide objects with no owning
database or schema, so the archiver's selective-restore logic
(_tocEntryRequired) and its createDB-mode drop restriction both carry
a small carve-out recognizing them, ensuring they survive filters
like --schema and --no-tablespaces and are not gated on -C the way
an ordinary DATABASE entry is.

pg_restore gains matching support for this layout: -g/--globals-only
to restore only the global objects, --no-globals to skip them, and
--exclude-database=PATTERN to skip specific databases, whether
restoring live (-d) or to a script file. Restoring a full archive
requires -C/--create, since each database needs to be created before
its own contents are restored into it; databases are restored one
per pg_dump sub-archive, optionally in parallel via -j, while global
objects are always restored first and serially.

Other implementation notes: map.dat is fsynced like the rest of the
archive; the per-database archive's file name is shell-quoted with
appendShellString() rather than hand-rolled quoting; and COMMENT and
SECURITY LABEL entries for roles and tablespaces record a dependency
on their parent object, as pg_dump.c's own entries do.

Note: v01-0001 to v01-0003 fix reverted patches; v01-0004 includes all
changes. Please review v01-0004.

--
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com