Subject: Non-text mode for pg_dumpall

From: Andrew Dunstan <[email-masked]>
Date: Mon, 10 Jun 2024 08:58:49 -0400

Tom and Nathan opined recently that providing for non-text mode for 
pg_dumpall would be a Good Thing (TM). Not having it has been a 
long-standing complaint, so I've decided to give it a go.

I think we would need to restrict it to directory mode, at least to 
begin with. I would have a toc.dat with a different magic block (say 
"PGGLO" instead of "PGDMP") containing the global entries (roles, 
tablespaces, databases). Then for each database there would be a 
subdirectory (named for its toc entry) with a standard directory mode 
dump for that database. These could be generated in parallel (possibly 
by pg_dumpall calling pg_dump for each database). pg_restore on 
detecting a global type toc.data would restore the globals and then each 
of the databases (again possibly in parallel).

I'm sure there are many wrinkles I haven't thought of, but I don't see 
any insurmountable obstacles, just a significant amount of code.

Barring the unforeseen my main is to have a preliminary patch by the 
September CF.

Following that I would turn my attention to using it in pg_upgrade.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 10 Jun 2024 09:14:08 -0500

> long-standing complaint, so I've decided to give it a go.

Thank you!

> I think we would need to restrict it to directory mode, at least to begin
> with. I would have a toc.dat with a different magic block (say "PGGLO"
> instead of "PGDMP") containing the global entries (roles, tablespaces,
> databases). Then for each database there would be a subdirectory (named for
> its toc entry) with a standard directory mode dump for that database. These
> could be generated in parallel (possibly by pg_dumpall calling pg_dump for
> each database). pg_restore on detecting a global type toc.data would restore
> the globals and then each of the databases (again possibly in parallel).

I'm curious why we couldn't also support the "custom" format.

> Following that I would turn my attention to using it in pg_upgrade.

+1

-- 
nathan

---

From: Andrew Dunstan <[email-masked]>
Date: Mon, 10 Jun 2024 10:51:42 -0400

> I'm curious why we couldn't also support the "custom" format.

We could, but the housekeeping would be a bit harder. We'd need to keep 
pointers to the offsets of the per-database TOCs (I don't want to have a 
single per-cluster TOC). And we can't produce it in parallel, so I'd 
rather start with something we can produce in parallel.

cheers

andrew

---

From: Magnus Hagander <[email-masked]>
Date: Mon, 10 Jun 2024 16:52:06 +0200

> > long-standing complaint, so I've decided to give it a go.

Indeed, this has been quite annoying!

> I'm curious why we couldn't also support the "custom" format.

Or maybe even a combo - a directory of custom format files? Plus that one
special file being globals? I'd say that's what most use cases I've seen
would prefer.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 10 Jun 2024 09:52:33 -0500

On Mon, Jun 10, 2024 at 10:51:42AM -0400, Andrew Dunstan wrote:
> single per-cluster TOC). And we can't produce it in parallel, so I'd rather
> start with something we can produce in parallel.

Got it.

-- 
nathan

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 10 Jun 2024 10:03:05 -0500

> special file being globals? I'd say that's what most use cases I've seen

Is there a particular advantage to that approach as opposed to just using
"directory" mode for everything?  I know pg_upgrade uses "custom" mode for
each of the databases, so a combo approach would be a closer match to the
existing behavior, but that doesn't strike me as an especially strong
reason to keep doing it that way.

-- 
nathan

---

From: Magnus Hagander <[email-masked]>
Date: Mon, 10 Jun 2024 17:45:19 +0200

On Mon, Jun 10, 2024 at 5:03 PM Nathan Bossart <[email-masked]>
wrote:

> > On Mon, Jun 10, 2024 at 4:14 PM Nathan Bossart <[email-masked]
> >
> > wrote:

A gazillion files to deal with? Much easier to work with individual custom
files if you're moving databases around and things like that.
Much easier to monitor eg sizes/dates if you're using it for backups.

It's not things that are make-it-or-break-it or anything, but there are
some smaller things that definitely can be useful.

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 10 Jun 2024 11:20:18 -0500

> some smaller things that definitely can be useful.

Makes sense, thanks for elaborating.

-- 
nathan

---

From: Tom Lane <[email-masked]>
Date: Mon, 10 Jun 2024 12:21:03 -0400

Magnus Hagander <[email-masked]> writes:
>> "directory" mode for everything?

> Much easier to monitor eg sizes/dates if you're using it for backups.

You can always tar up the directory tree after-the-fact if you want
one file.  Sure, that step's not parallelized, but I think we'd need
some non-parallelized copying to create such a file anyway.

			regards, tom lane

---

From: Andrew Dunstan <[email-masked]>
Date: Mon, 10 Jun 2024 13:27:27 -0400

> some non-parallelized copying to create such a file anyway.

Yeah.

I think I can probably allow for Magnus' suggestion fairly easily, but 
if I have to choose I'm going to go for the format that can be produced 
with the maximum parallelism.

cheers

andrew

---

From: Magnus Hagander <[email-masked]>
Date: Mon, 10 Jun 2024 21:36:37 +0200

On Mon, Jun 10, 2024 at 6:21 PM Tom Lane <[email-masked]> wrote:

> > On Mon, Jun 10, 2024 at 5:03 PM Nathan Bossart <[email-masked]
> >
> > wrote:

That would require double the disk space.

But you can also just run pg_dump manually on each database and a
pg_dumpall -g like people are doing today -- I thought this whole thing was
about making it more convenient :)

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Tue, 31 Dec 2024 23:53:40 +0530

Hi all,
With the help of Andrew and Dilip Kumar, I made a poc patch to dump all the
databases in archive format and then restore them using pg_restore.

Brief about the patch:
new option to pg_dumpall:
-F, --format=d|p (directory|plain) output file format (directory, plain
text (default))

Ex: ./pg_dumpall --format=directory --file=dumpDirName

dumps are as:
global.dat ::: global sql commands in simple plain format
map.dat.   ::: dboid dbname ---entries for all databases in simple text form
databases. :::
      subdir     dboid1  -> toc.dat and data files in archive format
      subdir     dboid2. -> toc.dat and data files in archive format
              etc
---------------------------------------------------------------------------

new options to pg_restore:
-g, --globals-only           restore only global objects, no databases
--exclude-database=PATTERN   exclude databases whose name matches PATTERN

When we give -g/--globals-only option, then only restore globals, no db
restoring.

*Design*:
When --format=directory is specified and there is no toc.dat file in the
main directory, then check
for global.dat and map.dat to restore all databases. If both files exist in
a directory,
then first restore all globals from global.dat and then restore all
databases one by one
from map.dat list.
While restoring, skip the databases that are given with exclude-database.

---------------------------------------------------------------------------
NOTE:
if needed, restore single db by particular subdir

Ex: ./pg_restore --format=directory -d postgres dumpDirName/databases/5
   -- here, 5 is the dboid of postgres db
   -- to get dboid, refer dbname in map.file
--------------------------------------------------------------------------

Please let me know feedback for the attached patch.

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

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Thu, 2 Jan 2025 02:05:13 +0530

Here, I am attaching an updated patch. I fixed some bugs of v01 patch and
did some code cleanup also.

TODO WIP 1: after excluding databases, we have paths of all the databases
that are needed to
restore so we can launch parallel workers for each database. I am studying
for this part.

TODO WIP 2: exclude-database=NAME, for pg_restore, I am using NAME as of
now, I will try to make it PATTERN. PATTERN
should be matched from map.dat file.

Please have a look over the patch and let me know feedback.

On Tue, 31 Dec 2024 at 23:53, Mahendra Singh Thalor <[email-masked]>
wrote:

> Hi all,
> With the help of Andrew and Dilip Kumar, I made a poc patch to dump all
> the databases in archive format and then restore them using pg_restore.
>
> Brief about the patch:
> for global.dat and map.dat to restore all databases. If both files exist
> in a directory,

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 6 Jan 2025 11:35:54 -0600

> did some code cleanup also.

Thank you for picking this up!  I started to review it, but the
documentation changes didn't build, and a few tests in check-world are
failing.  Would you mind resolving those issues?  Also, if you haven't
already, please add an entry to the next commitfest [0] to ensure that 1)
this feature is tracked and 2) the automated tests will run.

+	if (dbfile)
+	{
+		printfPQExpBuffer(&cmd, "\"%s\" %s %s", pg_dump_bin,
+						  dbfile, create_opts);
+		appendPQExpBufferStr(&cmd, " -F d ");
+	}

Have you given any thought to allowing a directory of custom format files,
as discussed upthread [1]?  Perhaps that is better handled as a follow-up
patch, but it'd be good to understand the plan, anyway.

[0] https://commitfest.postgresql.org
[1] https://postgr.es/m/CABUevExoQ26jo%2BaQ9QZq%2BUMA1aD6gfpm9xBnh_t5e0DhaCeRYA%40mail.gmail.com

-- 
nathan

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Wed, 8 Jan 2025 00:34:47 +0530

> this feature is tracked and 2) the automated tests will run.

Thanks Nathan for the quick response.

I fixed bugs of documentation changes and check-world in the latest patch.
Now docs are building and check-world is passing.

I added entry into commitfest for this patch.[0]
<https://commitfest.postgresql.org/52/5495/>

> patch, but it'd be good to understand the plan, anyway.

I will make these changes and will test. I will update my findings after
doing some testing.

Apart from these bugs, I added code to handle --exclude-database= PATTERN.
Earlier I was using NAME only to skip databases for restore.

*TODO: .pl test cases for new added options.*

Here, I am attaching an updated patch for review and feedback.

> [1]
https://postgr.es/m/CABUevExoQ26jo%2BaQ9QZq%2BUMA1aD6gfpm9xBnh_t5e0DhaCeRYA%40mail.gmail.com