pg_dumpall: back up every Postgres database, with examples and restore steps

pg_dumpall writes out an entire PostgreSQL cluster as one SQL script: every database, plus the roles and tablespaces that pg_dump leaves out. The basic command, run as the postgres superuser:

pg_dumpall --file=cluster.sql

Back up with pg_dumpall and restore with psql, not pg_restore:

psql --dbname=postgres --file=cluster.sql

That covers moving a whole self-hosted Postgres server. The rest of this page is the practical detail: the flags you will actually use, compressing the output, the error every restore prints, getting one database back out of the file, and why the same command fails on AWS RDS and other managed hosts. Everything was run on PostgreSQL 17 before publishing.

What postgres pg_dumpall includes

The script starts with the cluster-wide objects: a CREATE ROLE and ALTER ROLE for every role, with its attributes, memberships, and (as a superuser) its password hash, then the tablespaces. After that comes one section per database, headed -- Database "shop" dump, each with a CREATE DATABASE, a \connectinto it, and that database’s schema and rows, the same content pg_dump would produce.

Under the hood, the PostgreSQL pg_dumpall command dumps the globals itself and then runs pg_dump once per database. Each database is internally consistent, but they are not taken at the same instant, so a cluster whose databases refer to each other can come back slightly out of step. It connects to every database in turn, which matters for passwords below.

pg_dumpall examples

How to use pg_dumpall in the situations that come up most. Each pg_dumpall command below can take the same connection flags.

Dump every database to a file

The file holds every row of every database and, run as a superuser, the password hash of every role. Create it readable only by you:

umask 077   # new files: owner read/write only
pg_dumpall --username=postgres --file=cluster-$(date +%F).sql

pg_dumpall from a remote server

For a pg_dumpall remote server backup, give the host, port, and user as flags, or one connection string with --dbname. The pg_dumpall port option is --port (-p):

pg_dumpall --host=db.internal --port=5433 --username=postgres \
  --file=cluster.sql

pg_dumpall --dbname="postgresql://postgres@db.internal:5433/postgres" \
  --file=cluster.sql

pg_dumpall gzip: compress the output

There is no pg_dumpall compression option; pipe the output through a compressor. With set -o pipefail, a failed dump fails the whole command instead of leaving a truncated archive behind a successful exit code:

set -o pipefail
umask 077
pg_dumpall --username=postgres | gzip > cluster.sql.gz

# zstd works the same way
pg_dumpall --username=postgres | zstd > cluster.sql.zst

pg_dumpall globals only: roles and tablespaces

The most useful form in practice. Run it next to per-database pg_dump files, so a restore can recreate the roles first and keep every owner and grant intact:

umask 077   # role password hashes are in here
pg_dumpall --globals-only --file=globals.sql

# only the roles, without tablespaces
pg_dumpall --roles-only --file=roles.sql

pg_dumpall exclude database

--exclude-databasetakes a pattern and can be repeated. It skips the database’s contents, not the roles:

pg_dumpall --exclude-database=analytics \
  --exclude-database='test_*' --file=cluster.sql

Schema only

pg_dumpall --schema-only --file=cluster-schema.sql

pg_dumpall clean: --clean --if-exists

--clean adds a DROP DATABASE and DROP ROLE ahead of each object, for restoring over a server that already has them; --if-exists keeps the drops quiet when the object is missing. Expect one harmless error even so: the script tries to drop the role you are connected as, and PostgreSQL answers current user cannot be dropped.

pg_dumpall --clean --if-exists --file=cluster.sql

pg_dumpall options

The ones worth knowing, from man pg_dumpall (the pg_dumpall man page). Most of pg_dump’s content flags (--inserts, --no-comments and so on) work here too.

pg_dumpall format: plain SQL only

Unlike pg_dump, pg_dumpall has no --format option in PostgreSQL 17 or 18: no custom, directory, or tar output, only a SQL script. That is why it cannot be restored with pg_restore, restored in parallel, or restored one table at a time, and there is no pg_dumpall tar output either. If you need any of those, dump each database with pg_dump --format=custom and take the roles with pg_dumpall --globals-only. To get a single tar file, archive the SQL file afterwards; the tar is only a container.

pg_dumpall password: use .pgpass, not the prompt

Running pg_dumpall with password prompts does not scale: it opens a new connection for every database, so the pg_dumpall password prompt comes back once per database, which also rules out running it from cron. Put the password in a password file instead:

# ~/.pgpass, mode 0600: host:port:database:user:password
# * as database matches every database pg_dumpall connects to
printf 'Password for postgres: '; IFS= read -rs PGPASS_VALUE; echo
touch ~/.pgpass && chmod 600 ~/.pgpass   # appends; existing entries stay
printf 'db.internal:5432:*:postgres:%s\n' \
  "$(printf '%s' "$PGPASS_VALUE" | sed 's/[\\:]/\\&/g')" >> ~/.pgpass
unset PGPASS_VALUE

pg_dumpall --host=db.internal --username=postgres --no-password --file=cluster.sql

--no-password makes it fail at once instead of waiting for a prompt that nobody will answer. On Windows the file is %APPDATA%\postgresql\pgpass.conf. Setting PGPASSWORD also works, but it is visible to other processes on some systems.

Role passwords inside the dump are a separate question: they are written only when you run as a superuser. The --no-role-passwords flag leaves them out, so after a restore every login role needs its password set again.

pg_dumpall vs pg_dump

pg_dump vs pg_dumpall comes down to scope and format. The difference between pg_dump and pg_dumpall, in one table:

For backups of a self-hosted server, the combination usually beats either alone: pg_dumpall --globals-only for the roles, plus a pg_dump --format=custom per database for everything else. You keep selective and parallel restores and still get the roles back. For the bigger picture, including pg_basebackup and point-in-time recovery, see the PostgreSQL backup overview.

How to restore pg_dumpall backups

To restore pg_dumpall output, run the script with psql, connected to any existing database on the target server as a superuser; the script creates the other databases and connects to each one itself:

psql --username=postgres --dbname=postgres \
  --file=cluster.sql 2> restore-errors.log

# compressed
gunzip -c cluster.sql.gz \
  | psql --username=postgres --dbname=postgres 2> restore-errors.log

Then read restore-errors.log. On a new server it will contain one line you can ignore:

psql:cluster.sql:18: ERROR:  role "postgres" already exists

The script recreates every role, including the superuser the target server was initialised with, and that one always exists. This is also why the usual advice to add --set ON_ERROR_STOP=1 backfires here: psql stops at that first harmless error, before a single database is created. In our test psql exited with status 3 and not one database had been restored. Restore without it, then check the log contains nothing else.

If the log instead starts with invalid command \restrict, your psql is older than the tools that made the dump. Since the August 2025 security releases (17.6, 16.10, 15.14, 14.19, 13.22), pg_dump and pg_dumpall wrap each section in \restrict and \unrestrict lines that older psql does not understand. Restore with a psql from one of those releases or newer.

Restore a single database from a pg_dumpall file

Each database has its own section in the file, headed -- Database "shop" dump, and cutting one out with sed or awk is the usual advice. We tested it and would not rely on it: a row of table data or a comment in a function that happens to look like a section header ends the cut early, and the restore then succeeds with rows silently missing. The safe route is to restore the whole file into a throwaway server and take the one database out of that with pg_dump:

# the ( ) subshell keeps set -e and the cleanup trap out of your terminal
(
set -euo pipefail
# restore the whole file into a throwaway server, then dump only shop from it
docker run --detach --name pgdumpall-extract --network none \
  --env POSTGRES_PASSWORD=throwaway postgres:17 > /dev/null
# remove the container (and the restored data in it) however the script ends
trap 'docker rm --force --volumes pgdumpall-extract > /dev/null 2>&1' EXIT
until docker exec pgdumpall-extract psql -h 127.0.0.1 -U postgres -c 'select 1' > /dev/null 2>&1; do
  sleep 1
done
docker cp cluster.sql pgdumpall-extract:/tmp/cluster.sql
docker exec pgdumpall-extract psql -h 127.0.0.1 -U postgres --dbname=postgres \
  --quiet --file=/tmp/cluster.sql > /dev/null 2> extract-errors.log
docker exec pgdumpall-extract pg_dump -h 127.0.0.1 -U postgres \
  --format=custom --dbname=shop > shop.dump
)

shop.dump is now an ordinary custom-format archive: restore it into the real server with pg_restore, one table at a time if you like, as the pg_restore guide describes. Use the image of the PostgreSQL version the dump came from, and check extract-errors.log holds only the role "postgres" already exists line. We tested this against a dump with a decoy header inside a table: the sed-style cut lost rows, this route brought back every one.

pg_dumpall on AWS RDS, Supabase, and Neon

An AWS RDS pg_dumpall run as the master user stops immediately:

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid

Role passwords live in pg_authid, which only a real superuser can read, and managed hosts such as RDS, Aurora, Supabase, and Neon do not give you one. We reproduced exactly this error with an ordinary role on a local server. Adding --no-role-passwords gets past it by reading roles from pg_roles instead; dumping the databases then also needs read access to every table in them, or it fails on the first one it cannot read.

# roles without passwords: works as a non-superuser
pg_dumpall --dbname="$RDS_URL" --globals-only --no-role-passwords \
  --file=globals.sql

The pg_dumpall RDS workaround ends there. On a managed host, the practical setup is that globals file plus a pg_dump per database, not a full pg_dumpall. The host’s own snapshots cover the physical side. The pg_dump to S3 guide has a script for the per-database dumps and connection notes for RDS, Neon, Railway, and Supabase.

pg_dumpall on Windows, and where to install it

pg_dumpall ships with the PostgreSQL client tools, always next to pg_dump; there is no separate pg_dumpall install. For pg_dumpall Windows setups, it is pg_dumpall.exe in the installation’s bin folder, for example C:\Program Files\PostgreSQL\17\bin, which is not on PATH by default:

& "C:\Program Files\PostgreSQL\17\bin\pg_dumpall.exe" `
  --username=postgres --file=cluster.sql

On Debian and Ubuntu it comes with postgresql-client-17 from the PostgreSQL apt repository; on macOS with brew install libpq. Use a version at least as new as the server you dump (pg_dumpall refuses newer servers) and no newer than the server you restore into: output from a newer pg_dumpall is not guaranteed to load into an older server. There is no psql dumpall or PostgreSQL dumpall command; pg_dumpall is its own program, installed alongside psql.

Check what came back

psql finishing is not proof. After a restore, list the databases and roles, and compare row counts per table with the source; the pg_restore guide has a query that counts every table exactly, which works just as well after a psql restore. Doing that on a schedule, against the latest backup in a throwaway server, is what turns a backup file into a backup you know works.

FAQ