snippetsqlMinor
How to dump multiple databases of the same cluster in single execution?
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
Is there any way to do something like this (pseudocode):
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.backupSolution
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, includingglobal objects (roles, tablespaces, ...).
Postgres 12 adds the switch
--exclude-database. The manual:--exclude-database=patternDo not dump databases whose name matches pattern. [...]
A specific DB name in the connection string is ignored. The manual:
-d connstr--dbname=connstrSpecifies 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 clientapplications, 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 theinitial 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.