(opens in a new tab)

pg_dumpall: Back Up All Postgres Databases

November 8, 2026 by Chat2DB Team

pg_dump backs up one database. It does not include the roles that own the tables, the passwords those roles log in with, or the tablespaces the data lives in. Those objects belong to the whole cluster, not to any single database, and pg_dumpall is the tool that exports them.

This guide shows what pg_dumpall writes, how to restore it into a new server, and how to combine it with pg_dump for a backup that is faster to restore. It also covers the errors you will see on managed services and during restores. Every command and output below comes from PostgreSQL 18.6.

What pg_dumpall Exports

Run it with an output file:

pg_dumpall -f all.sql

The result is one plain SQL script, in three parts:

1. Roles, with their attributes, passwords, memberships and per-role settings.
2. Tablespaces, if you created any beyond the default ones.
3. Every database: a CREATE DATABASE statement, then a \connect to it, then the same content pg_dump would write for that database.

A look at the start of the file shows the structure:

-- Roles
CREATE ROLE app_user;
ALTER ROLE app_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
...
-- User Configurations
ALTER ROLE webapp SET statement_timeout TO '30s';
...
GRANT owner_role TO dev WITH INHERIT FALSE GRANTED BY postgres;
...
-- Databases
CREATE DATABASE shop WITH TEMPLATE = template0 ...;
\connect shop

Role passwords are included as stored hashes, not as plain text:

ALTER ROLE webapp WITH ... LOGIN ... PASSWORD 'SCRAM-SHA-256$4096:otwu...';

Restoring that statement recreates the same password without anyone knowing it. Still, treat the file as a secret. Anyone who has it can try to crack the hashes offline, and it contains all your data.

The Options You Will Actually Use

pg_dumpall shares many options with pg_dump, such as --schema-only, --data-only, --no-owner and --inserts. Five options are specific to the cluster-wide job:

The -t option is a common trap. In pg_dump, -t means "this table". In pg_dumpall, it means tablespaces only. Running pg_dumpall -t orders does not dump a table called orders.

--exclude-database takes the same patterns as psql, and you can repeat it:

pg_dumpall --exclude-database='tmp_*' --exclude-database=analytics -f all.sql

In our test cluster with databases dst, own, r6, r7, r8 and shop, excluding r* and own left exactly dst and shop in the output.

The Recommended Pattern: Globals Plus Per-Database Dumps

pg_dumpall writes only plain SQL. It has no custom format, so you cannot restore it in parallel or pick single tables out of it. For anything beyond a small cluster, the usual approach combines the two tools:

pg_dumpall --globals-only -f globals.sql
for db in $(psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn"); do
  pg_dump -Fc -f "$db.dump" "$db"
done

To restore, load the globals first, so the roles exist before any ALTER ... OWNER TO runs. Then restore each database:

psql -d postgres -f globals.sql
createdb shop
pg_restore -d shop -j 4 shop.dump

This gives you parallel restores, compressed files, and the option to restore one table later. Our guide to restoring a single table with pg_restore shows how. The per-database dumps also carry each database's own settings when you add --create to pg_dump, so you can recreate the database exactly.

Restoring a Full pg_dumpall File

A complete pg_dumpall file is restored with psql, connected to any existing database, usually postgres:

psql -d postgres -f all.sql

We restored our file into a freshly initialized 18 cluster. All databases came back, and the test table had its 5,000 rows. One error appeared, and you will see it too:

psql:all.sql:24: ERROR:  role "postgres" already exists

The dump creates every role, including the bootstrap superuser, which a new cluster already has. The error is harmless. The ALTER ROLE on the next line still applies that role's attributes. Do not run the restore with ON_ERROR_STOP unless you remove that line first.

To restore over an existing cluster, add --clean --if-exists when you dump. The script then starts with DROP DATABASE IF EXISTS and DROP ROLE IF EXISTS for every object:

pg_dumpall --clean --if-exists -f all-clean.sql

One DROP always fails, because you cannot drop the role you are connected as:

ERROR:  current user cannot be dropped

That is also expected. The restore continues, and the existing role gets its attributes reset.

Compress and Inspect the File

pg_dumpall has no compression option of its own, because it only writes plain text. Pipe it through a compressor instead, and decompress while you restore:

pg_dumpall | gzip > all.sql.gz
gunzip -c all.sql.gz | psql -d postgres

Plain SQL compresses well. In our test the compressed file was less than half the size of the uncompressed one, and larger databases with repetitive data usually compress better still.

Before a restore, check which databases the file contains. Every database section starts with a CREATE DATABASE statement followed by a \connect:

gunzip -c all.sql.gz | grep -E '^(CREATE DATABASE|\\connect)'

\connect template1
CREATE DATABASE dst WITH TEMPLATE = template0 ...
\connect dst
CREATE DATABASE own WITH TEMPLATE = template0 ...
\connect own
\connect postgres
CREATE DATABASE shop WITH TEMPLATE = template0 ...
\connect shop

template1 and postgres appear only as \connect lines, because they exist in every cluster. pg_dumpall restores their contents but does not create them. This listing is also a quick way to confirm that --exclude-database left out what you meant it to.

pg_dumpall on Managed Services

On Amazon RDS, Cloud SQL, Azure and similar services, you do not get a real superuser. pg_dumpall then fails while reading roles, because password hashes live in pg_authid, which only superusers can read. We reproduced this with an ordinary role:

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
pg_dumpall: detail: Query was: SELECT oid, rolname, rolsuper, ... FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2

Add --no-role-passwords. pg_dumpall then reads the pg_roles view instead, which any role may read:

pg_dumpall --globals-only --no-role-passwords -f globals.sql

The roles come out without PASSWORD clauses, so set passwords again after the restore. Per-role settings such as ALTER ROLE webapp SET statement_timeout are still included. Some providers also have roles you cannot create on another server, such as their own admin roles. Expect a few errors when you restore those globals elsewhere, and check them one by one.

Version Rules

pg_dumpall follows the same version rule as pg_dump. The client must be the same major version as the server or newer:

pg_dumpall: error: server version: 18.6; pg_dumpall version: 14.24
pg_dumpall: error: aborting because of server version mismatch

Use the newest client you have. A pg_dumpall 18 works against servers back to 9.2. If you are taking this backup because you are moving off PostgreSQL 14, which reaches end of life on November 12, 2026, run the version 18 client against the old server and restore into the new one. Our guide to the pg_dump server version mismatch covers installing the right client on each platform.

Plain scripts from current releases begin with a \restrict line, a security measure added in 2025. Run them with a current psql. Older psql builds do not know that command.

Restoring One Database From a Full Dump

pg_restore cannot read a pg_dumpall file, so there is no command to pull out one database. You can cut the section out of the script by hand, because each database begins at its CREATE DATABASE and \connect lines, but on a file of many gigabytes that is slow and error-prone. If you think you may ever need to restore a single database, take per-database pg_dump -Fc files as shown above. That design costs nothing extra at backup time and saves a lot of work at restore time.

When to Use pg_dumpall, and When Not To

pg_dumpall is the right tool when you need:

• A simple, complete logical copy of a small cluster, for example a development server.
• The global objects, roles and tablespaces, to go with per-database pg_dump files.
• A migration to a different major version or platform where a physical copy will not work.

It is the wrong tool for large production clusters as the only backup. A logical dump of hundreds of gigabytes takes hours to restore, and it only captures one moment. For those, use physical backups with point-in-time recovery, such as pg_basebackup with WAL archiving or a tool like pgBackRest. Keep pg_dumpall --globals-only alongside them as a quick way to rebuild roles. Our Postgres backup and restore guide compares these approaches.

After any restore, check the result before you rely on it: compare the list of databases, the number of roles, and row counts in a few key tables. Chat2DB (opens in a new tab) can connect to the source and the restored server at the same time, which makes those side-by-side checks quick. For building the per-database commands, our pg_dump command generator (opens in a new tab) and pg_restore command generator (opens in a new tab) give you the right flags for each case.