Thread: pg_dumpall with individual output files per database?

Hi everyone,

I need to backup multiple host with multiple Postgres databases each.
In all of those cases I'm interested to backup all databases, which
makes pg_dumpall a natural choice at first glance. Though, from my
understanding of the docs that is only capable of storing all
databases per host into one single file.

That's not a too good approach for my databases, because some of those
store files with few GiB in size for legacy reasons. I would like to
avoid creating one large file only which then mixes those
file-databases with others containing less or different types of data.

So, is there some option I'm missing telling pg_dumpall to dump into
individual files, simply named after e.g. the dumped databases?

If not, was a feature like that discussed already or what's the
reasons to not do that? There are a lot of search results how to dump
all databases with lots of different scripting approaches. Many of
those could simply be avoided with pg_dumpall supporting that
already.

Tools like BorgMatic making use of pg_dumpall might benefit of such a
feature as well:

https://projects.torsion.org/witten/borgmatic/issues/393

Thanks!

Mit freundlichen Grüßen

Thorsten Schöning

--
AM-SoFT IT-Service - Bitstore Hameln GmbH
Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK

E-Mail: Thorsten.Schoening@AM-SoFT.de
Web:    http://www.AM-SoFT.de/

Tel:   05151-  9468- 0
Tel:   05151-  9468-55
Fax:   05151-  9468-88
Mobil:  0178-8 9468-04

AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska


Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung.

Mit freundlichen Grüßen

Thorsten Schöning


Tel: 05151 9468 0
Fax: 05151 9468 88
Mobil:
Webseite: https://www.am-soft.de

AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister
fürIT und TK 

AM-Soft IT-Service - Bitstore Hameln GmbH i.G.
Brandenburger Str. 7c
31789 Hameln
Tel: 05151 9468 0

Bitstore IT-Consulting GmbH
Zentrale - Berlin Lichtenberg
Frankfurter Allee 285
10317 Berlin
Tel: 030 453 087 80

CBS IT-Service - Bitstore Kaulsdorf UG
Tel: 030 453 087 880 1

Büro Dallgow-Döberitz
Tel: 03322 507 020

Büro Kloster Lehnin
Tel: 033207 566 530

PCE IT-Service - Bitstore Darmstadt UG
Darmstadt
Tel: 06151 392 973 0

Büro Neuruppin
Tel: 033932 606 090

ACI EDV Systemhaus - Bitstore Dresden GmbH
Dresden
Tel: 0351 254 410

Das Systemhaus - Bitstore Magdeburg GmbH
Magdeburg
Tel: 0391 636 651 0

Allerdata.IT - Bitstore Wittenberg GmbH
Wittenberg
Tel: 03491 876 735 7

Büro Liebenwalde
Tel: 033054 810 00

HSA - das Büro - Bitstore Altenburg UG
Altenburg
Tel: 0344 784 390 97

Bitstore IT – Consulting GmbH
NL Piesteritz
Piesteritz
Tel: 03491 644 868 6

Solltec IT-Services - Bitstore Braunschweig UG
Braunschweig
Tel: 0531 206 068 0

MF Computer Service - Bitstore Gütersloh GmbH
Gütersloh
Tel: 05245 920 809 3

Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln
Geschäftsführer Janine Galonska

Hi Thorsten:

On Thu, Jul 15, 2021 at 6:30 PM Thorsten Schöning <tschoening@am-soft.de> wrote:

> I need to backup multiple host with multiple Postgres databases each.
> In all of those cases I'm interested to backup all databases, which
> makes pg_dumpall a natural choice at first glance. Though, from my
> understanding of the docs that is only capable of storing all
> databases per host into one single file.

I think what you are naming host is better named
server/cluster/instance ( a host may be running several postgres
installations, pg_dumpall dos not dump the wole host just the instance
you point it too ).

...
> So, is there some option I'm missing telling pg_dumpall to dump into
> individual files, simply named after e.g. the dumped databases?
> If not, was a feature like that discussed already or what's the
> reasons to not do that? There are a lot of search results how to dump
> all databases with lots of different scripting approaches. Many of
> those could simply be avoided with pg_dumpall supporting that
> already.

It would probably complicate it, and dumping a whole cluster using
something like pg_dumpall -g for the globals plus a loop over the
databases using something like pg_dump -Fc ( which I would always
recommend over plain sql format ) is just a ( little complex ) one
liner or a 10 line script, probably not worth the scarce developer /
maintainer brain cycles.

> Tools like BorgMatic making use of pg_dumpall might benefit of such a
> feature as well:

They might, but in a project of the (apparent, have not dug out much )
size of that, I would possibly just include an script, or dump the
databases as individual backup objects ( different retention cycles /
copies per database, skipping of dev/test databases etc). The script
to dump a whole cluster is just one line for the pg_dumpall, one psql
line to grab the database names and another for a loop pg_dumping all
of them. And from them on you can improve it a bit for special client
software purposes, it does not seem to hold its weight.

FOS

On Thu, Jul 15, 2021 at 7:27 PM Francisco Olarte <folarte@peoplecall.com> wrote:
> It would probably complicate it, and dumping a whole cluster using
> something like pg_dumpall -g for the globals plus a loop over the
> databases using something like pg_dump -Fc ( which I would always
> recommend over plain sql format ) is just a ( little complex ) one
> liner or a 10 line script, probably not worth the scarce developer /
> maintainer brain cycles.

I always find the 502.pgsql FreeBSD backup script illuminating in this
sense: <https://svnweb.freebsd.org/ports/head/databases/postgresql10-server/files/502.pgsql.in?view=markup#l44>.
I used it as a base for my "loop and backup" scripts.

Luca