pg_dumpall
Back up all PostgreSQL databases
TLDR
Dump all databases
Dump all databases using a specific username
Same as above, customize host and port
Dump only database data into an SQL-script file
Dump only schema (data definitions) into an SQL-script file
SYNOPSIS
pg_dumpall [connection-options] [options]
PARAMETERS
-c, --clean
Include commands to clean (drop) databases and global objects before recreating them.
-C, --create
Include commands to create the databases themselves before restoring the data.
-e, --echo
Show the commands pg_dumpall generates internally.
-f, --file=FILENAME
Output file name. The default is standard output.
-g, --globals-only
Dump only global objects, not databases.
-l, --load-role=ROLENAME
Role name to use when loading the dump.
-o, --no-owner
Omit commands to set ownership of objects.
-O, --no-privileges
Do not dump privileges (grant/revoke commands). Also prevents the creation of the dump as superuser by default.
-p, --port=PORT
Database server port.
-r, --roles-only
Dump only roles, not databases.
-s, --schema-only
Dump only the schema (data definitions), not data.
-t, --tablespaces-only
Dump only tablespaces, not databases or roles.
-U, --username=NAME
Database user name.
-w, --no-password
Never issue a password prompt.
-W, --password
Force a password prompt.
-x, --no-role-passwords
Do not dump passwords for roles.
-h, --host=HOSTNAME
Database server host or socket directory.
-?
Show help about pg_dumpall command line arguments, and exit.
--version
Output PostgreSQL version, then exit.
DESCRIPTION
pg_dumpall is a utility for backing up an entire PostgreSQL database cluster. It dumps all databases within the cluster and global objects that are common to all databases, such as roles and tablespaces, into a single script file.
Unlike pg_dump, which only dumps a single database, pg_dumpall does not operate on a specific database. It connects to the PostgreSQL server and reads information about all databases, roles, and tablespaces to create the backup script. The resulting script can then be used to restore the entire cluster using the psql command.
This command is essential for creating complete backups of your PostgreSQL environment, ensuring that all data and configuration settings are preserved. It's particularly useful for disaster recovery and migrating PostgreSQL clusters to new servers.
CAVEATS
pg_dumpall does not dump database-specific local objects (e.g., tables, indexes, views); use pg_dump for those. Restoring a cluster backup created by pg_dumpall requires superuser privileges.
ENVIRONMENT VARIABLES
pg_dumpall utilizes the standard PostgreSQL environment variables for connection parameters (e.g., PGHOST, PGPORT, PGUSER, PGPASSWORD).
SECURITY CONSIDERATIONS
The output of pg_dumpall can contain sensitive information, such as database passwords (if -x is not used). Securely store and transport the resulting backup file.
Restoring from a dump file generated by pg_dumpall requires superuser privileges, so restrict access to those operations.
HISTORY
pg_dumpall has been a part of PostgreSQL since its early versions. It was created to provide a simple way to back up and restore entire database clusters, simplifying administrative tasks.