1

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
  • No, not possible. pg_dump can only dump data from a single database – a_horse_with_no_name Nov 30 '18 at 12:34
2

pg_dump can only dump 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, on the other hand can only dump all DBs in the cluster, even if you provide a specific DB name in the connection string. 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:

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)

  • 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. – Spike Nov 30 '18 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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