pg_dumpall

Dump all databases

$ pg_dumpall > [path/to/file.sql]

$ pg_dumpall [[-U|--username]] [username] > [path/to/file.sql]

$ pg_dumpall [[-h|--host]] [host] [[-p|--port]] [port] > [output_file.sql]

$ pg_dumpall [[-a|--data-only]] > [path/to/file.sql]

$ pg_dumpall [[-s|--schema-only]] > [output_file.sql]

pg_dumpall [connection-options] [option...]

-a, --data-only
    Dump only the data, not the schema (definitions).

-c, --clean
    Include DROP commands for all dumped objects, ensuring they are removed before recreation during restore.

-g, --globals-only
    Dump only global objects (roles, tablespaces), no individual databases.

-s, --schema-only
    Dump only the schema (definitions), no data.

-f, --file=FILENAME
    Send output to the specified file. If omitted, output goes to standard output.

-O, --no-owner
    Do not output commands to set object ownership. All objects will be owned by the user running the restore.

-x, --no-privileges, --no-acl
    Prevent dumping of access privileges (GRANT/REVOKE commands).

--if-exists
    Add IF EXISTS clauses to DROP commands (implies --clean). Useful to prevent errors if objects don't exist during restore.

-v, --verbose
    Enable verbose mode, showing progress and detailed object information.

-V, --version
    Print the pg_dumpall version and exit.

-h, --host=HOSTNAME
    Database server host name or IP address to connect to.

-p, --port=PORT
    Database server port number to connect to.

-U, --username=USERNAME
    User name to connect as. Requires superuser privileges for a full dump.

-w, --no-password
    Never prompt for password. If a password is required and not available, the connection attempt will fail.

-W, --password
    Force pg_dumpall to prompt for a password before connecting to a database.

-o, --oids
    Include Object Identifiers (OIDs) in the dump. Rarely used for general backups.

The pg_dumpall utility is used to extract a complete PostgreSQL database cluster into a script file. This dump includes definitions and data for all databases within the cluster, as well as global objects like database roles (users), tablespaces, and object access privileges (ACLs). The output of pg_dumpall is a SQL script that can be directly used with the psql command-line tool to recreate the entire cluster on another server or to restore it after a catastrophic failure. It connects to the postgres database as a superuser by default to gather global object information before iterating through each database to dump its contents using pg_dump internally. This command is crucial for full cluster backups, migrations, and disaster recovery planning.

• 
  pg_dumpall requires superuser privileges on the database cluster to dump all objects and databases effectively.
• 
  The output file must be restored using psql. It is not compatible with pg_restore.
• 
  Does not dump configuration files like pg_hba.conf, postgresql.conf, or certificate files. These must be backed up separately.
• 
  Each database is dumped sequentially. While each individual database dump is transactionally consistent, global consistency across all databases at a single point in time is not guaranteed during a live cluster dump.
• 
  For very large clusters, the output file can be extremely large; ensure sufficient disk space.

To perform a complete cluster backup and redirect the output to a file:
pg_dumpall > all_databases.sql

To restore the cluster from the generated SQL script (typically on a freshly initialized cluster or after dropping all existing databases/roles):
psql -f all_databases.sql postgres
It's important to connect to the postgres database when restoring a full dump, as pg_dumpall handles the creation of databases and roles, which usually cannot be done from within another user database.

Since pg_dumpall requires superuser access to the database cluster, it is critical to run it in a secure environment. For automation, consider using password files (e.g., ~/.pgpass) or environment variables (e.g., PGUSER, PGPASSWORD) for credentials instead of embedding them directly in scripts for enhanced security.

pg_dumpall has been an integral part of the PostgreSQL utility set for many years, serving as the primary method for performing full cluster backups. Its evolution has focused on ensuring compatibility across various PostgreSQL versions and improving its ability to handle complex cluster configurations, including global objects like roles and tablespaces. It remains a cornerstone utility for disaster recovery and migration strategies within the PostgreSQL ecosystem.

pg_dump(1), psql(1), pg_restore(1), initdb(1)