HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to dump multiple databases of the same cluster in single execution?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dumpsamethedatabasessinglemultiplehowexecutioncluster

Problem

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

Solution

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:

  • How to get a pg_dump -s to include the CREATE DATABASE command?

Context

StackExchange Database Administrators Q#223830, answer score: 4

Revisions (0)

No revisions yet.