intro
Let’s learn how to dump all PostgreSQL databases in a cluster using the pg_dumpall command-line utility!
pg_dumpall is a powerful PostgreSQL command-line tool used to export all databases in a cluster. That is the ideal solution to create complete database backups with a single command.
In this guide supported by detailed examples, you will see how to effectively use it and master PostgreSQL data management.
Let’s dive in!
What Is pg_dumpall?
pg_dumpall is a PostgreSQL command-line utility used to export ("dump") all databases within a PostgreSQL cluster into a single script. In this context, a “cluster” refers to a collection of databases managed by a single instance of a PostgreSQL server.
The output of pg_dumpall is a plain-text script containing SQL commands that can be fed into psql to recreate all the databases and their contents. Under the hood, pg_dumpall achieves this by internally calling pg_dump for each individual database in the cluster. Find out more in our complete guide on pg_dump.
In detail, pg_dumpall also exports global objects that are common across all databases. These include roles (users and groups), tablespaces, and any privilege grants related to global configuration parameters.
PostgreSQL pg_dumpall Command: Syntax and Options
The basic syntax of the PostgreSQL pg_dumpall command is:
1
pg_dumpall [options]
Where options include the flags to customize the behavior of the dump all logic, such as authentication details, output file format, compression, and more.
Notes:
pg_dumpall Options
Below is a list of the most commonly used and important options for the PostgreSQL pg_dumpall command:
For a complete list of pg_dumpall options, refer to the official documentation.
Note: Keep in mind that passing sensitive options via the command line can be insecure, as they may be visible to anyone with access to your shell history or process list.
pg_dumpall Environment Variables
These are the pg_dumpall environment variables:
pg_dumpall Example List
Now that you know how the PostgreSQL cluster dumping utility works, get ready to explore a pg_dumpall example list!
Note: In the following examples, the user will always be named admin. Adjust this based on your specific user and database.
Dump All Databases into an SQL Script Format
To dump all databases in a PostgreSQL cluster into an SQL script format, use:
1
pg_dumpall -U admin -f all_databases_backup.sql
This will generate an all_databases_backup.sql file containing dumps for all databases.
Restore All Databases from a SQL Script
To restore the backup, run the following command:
1
psql -U admin -f all_databases_backup.sql
This command restores all the databases from the backup script.
Export Only Data
If you want to dump only the data (excluding schema), use the --data-only flag:
1
pg_dumpall -U admin --data-only -f all_databases_data.sql
This will export only the data in a format that contains COPY statements.
Export Only Schema
To export just the schema (structure of the databases), use the --schema-only flag:
1
pg_dumpall -U admin --schema-only -f all_databases_schema.sql
This will generate a SQL script containing only the CREATE and ALTER statements.
Dump a Remote Database Cluster Over SSH
To dump a PostgreSQL cluster remotely, you can use SSH tunneling to securely dump the entire cluster:
1
ssh user@remote_host "pg_dumpall -U admin" > remote_all_databases_backup.sql
Replace user@remote_host with the correct SSH user and host. This command will create a backup of the remote database cluster and save it locally.
Conclusion
In this guide, you learned what pg_dumpall is and the options it offers. This powerful PostgreSQL command-line utility enables you to back up all databases in a cluster. It provides many options, and here we highlighted the most important ones along with examples to help you get started.
Creating effective cluster dumps can be complex, and you might prefer a more advanced solution like DbVisualizer. Beyond offering the features of a robust database client, this tool allows you to export databases with just a few clicks.
DbVisualizer also includes advanced query optimization capabilities and can generate ERD-like schemas for better visualization of your database structure. Try DbVisualizer for free today!
FAQ
Where is the output of pg_dumpall saved?
By default, the output of pg_dumpall is printed to the standard output (i.e., the terminal or command prompt). However, you can redirect it to a file using the -f or --file option followed by a filename. That option enables you to save the dump to a file, which can then be used for backups or restoration purposes.
What is the path to pg_dumpall.exe?
On Windows, pg_dumpall.exe is typically located in the bin directory of your PostgreSQL installation. The path will generally look like this:
1
C:\Program Files\PostgreSQL\<version>\bin\pg_dumpall.exe
Simply replace <version> with the version number of your PostgreSQL server.
Does pg_dumpall require superuser privileges?
Since pg_dumpall reads tables from all databases, you will most likely need to connect as a database superuser to create a complete dump. Additionally, superuser privileges are required to execute the saved script, as you will need permission to add roles and create databases.
Is it possible to run pg_dumpall on a remote server?
First, make sure the PostgreSQL server is configured to allow remote connections. Then, run pg_dumpall from a remote server by following these steps:
What is pg_dump and what is the difference with pg_dumpall?
pg_dump is a PostgreSQL utility used to create backups of individual databases. On the other hand, pg_dumpall backs up all databases in a PostgreSQL cluster, including global objects like roles and tablespaces.
