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] [options]

-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.

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.

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.

pg_dumpall utilizes the standard PostgreSQL environment variables for connection parameters (e.g., PGHOST, PGPORT, PGUSER, PGPASSWORD).

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.

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.

pg_dump(1), psql(1)