Multiple database backup using pg_dump

Question

Is it possible to backup multiple databases with a single pg_dump command, instead of running a pg_dump command for each database

Answer 1

It looks like it is possible using the pg_dumpall command: https://www.postgresql.org/docs/9.6/static/app-pg-dumpall.html.

From the documentation:

Description

pg_dumpall is a utility for writing out ("dumping") all PostgreSQL databases of a cluster into one script file. The script file contains SQL commands that can be used as input to psql to restore the databases. It does this by calling pg_dump for each database in a cluster. pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole.

Since pg_dumpall reads tables from all databases you will most likely have to connect as a database superuser in order to produce a complete dump. Also you will need superuser privileges to execute the saved script in order to be allowed to add users and groups, and to create databases.

The SQL script will be written to the standard output. Use the [-f|file] option or shell operators to redirect it into a file.