pg_dumpall

Summary: In this tutorial, you’ll learn how to use the pg_dumpall tool to export the entire database cluster, including all databases, roles, and other global objects.

Introduction to pg_dumpall tool #

pg_dumpall is a command-line tool that allows you to export an entire PostgreSQL database cluster into a file.

Unlike the pg_dump tool, which exports a single database without roles and other global objects, the pg_dumpall exports all databases with global objects, including database roles, tablespaces, and privilege grants for configuration parameters. However, pg_dumpall only supports plain SQL format.

To use the pg_dumpall tool, you follow these steps:

1. First, open your terminal.
2. Second, execute the pg_dumpall command:

pg_dumpall -h db_host -p db_port -U usernameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• db_host is the database host you want to connect to. It defaults to the local PostgreSQL server.
• db_port is the port on which the PostgreSQL listens. It defaults to the port 5432. Since pg_dumpall reads all databases, you must connect to the PostgreSQL server with a superuser.

If you have multiple databases, pg_dumpall will prompt for a password each time it connects to each database if you use password authentication.

Therefore, using a PostgreSQL password file for using the pg_dumpall to automatically connect to a PostgreSQL server is much more convenient.

After having a dump file, you can restore it using the psql tool.

pg_dumpall examples #

The following command exports all databases on the local PostgreSQL server to a file:

pg_dumpall -U postgres > D:\backup\all.sqlCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can replace the path to the backup file with your actual one.

To dump only global objects, you use the -g flag:

pg_dumpall -g > D:\backup\globals.sqlCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To restore the dump file to a PostgreSQL server, you use the psql tool:

psql -U postgres -f all.sqlCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Why Use pg_dumpall? #

• Cluster-wide Backup: pg_dumpall can back up all databases in a PostgreSQL instance.
• Backup Roles and Tablespaces: Besides databases, pg_dumpall backs up the user roles and tablespaces.
• Simplifies Server Migration: pg_dumpall can help transfer entire PostgreSQL clusters between servers.

Summary #

• Use the pg_dumpall tool to export all databases, including user roles and tablespaces.