4

I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t.

Is there any way to do something like this (pseudocode):

pg_dump -d db1 -d db2 -d db3 -Fc > dbs_backup.backup
1
  • No, not possible. pg_dump can only dump data from a single database
    – user1822
    Commented Nov 30, 2018 at 12:34

2 Answers 2

4

pg_dump dumps a single DB. The manual:

pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall.

pg_dumpall dumps the whole DB cluster, including global objects (roles, tablespaces, ...).
Postgres 12 adds the switch --exclude-database. The manual:

--exclude-database=pattern

Do not dump databases whose name matches pattern. [...]

A specific DB name in the connection string is ignored. The manual:

-d connstr
--dbname=connstr

Specifies parameters used to connect to the server, as a connection string. See Section 34.1.1 for more information.

The option is called --dbname for consistency with other client applications, but because pg_dumpall needs to connect to many databases, the database name in the connection string will be ignored. Use the -l option to specify the name of the database used for the initial connection, which will dump global objects and discover what other databases should be dumped.

To dump some DBs but not all, use a shell script, like has been advised. You may want to include commands to create each DB in the dump:

2
  • pg_dumpall can still be used with the --exclude-database flag to dump only some databases Commented Sep 11, 2021 at 10:49
  • Thanks, I added the new option above! Commented Sep 11, 2021 at 14:37
1

Create a txt file with database names, one per line like:

db1
db2
db3

and so on for all db, then from shell:

for a in  `cat filename.txt`; do pg_dump -d $a > /tmp/${a}_backup.out; done

by M.

P.S. I'm not able to write correctly (cannot understand why) but the filename.txt MUST be enclosed by "grave accent" (code U+0060)

1
  • I aware of other alternatives through scripts. Just I wonder is there any new parameter or any function available to achieve the same. Anyway, thanks for your time.
    – RBB
    Commented Nov 30, 2018 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.