Back Up and Restore a PostgreSQL Cluster with pg_dumpall

pg_dumpall is the only Debian tool that saves every database plus the roles and tablespaces a restore actually needs. Plain pg_dump misses that global layer, which is exactly the gap that turns a routine restore into a morning of recreating logins from memory. This guide creates one SQL file with a cluster's databases and shared objects, checks it, and restores it with psql. It uses the installed PostgreSQL 16.15 client. Allow a few minutes for a small cluster; a busy or large cluster may take much longer and needs enough free space for the dump.

Before you start

You need a running PostgreSQL server, the pg_dumpall and psql client programs, and a destination directory with enough free space. A complete dump normally needs a database superuser, or an authenticated user that can switch to a suitably privileged role with --role. The restore needs enough privilege to create roles, databases, tablespaces, and ownerships.

Use a password file rather than putting a password in a shell command. A ~/.pgpass entry must be readable only by its owner, for example with chmod 600 ~/.pgpass. The utility connects several times, once for each database, so relying on an interactive prompt is inconvenient for a full dump.

1. Confirm the client version

Check the installed version and available options before you rely on either.

pg_dumpall --version
pg_dumpall --help | sed -n '1,18p'

On this system the first command reports pg_dumpall (PostgreSQL) 16.15. The command writes SQL to standard output unless you use --file or shell redirection.

2. Create a complete cluster dump

Choose a new output path and run the dump as the PostgreSQL account permitted to read the cluster.

pg_dumpall \
  --host=127.0.0.1 \
  --port=5432 \
  --username=postgres \
  --file=/var/backups/postgresql/cluster-$(date +%F).sql

The equivalent short form is pg_dumpall > cluster.sql. The command includes every database plus global objects such as roles, tablespaces, and privilege grants. It calls pg_dump for each database, so a diagnostic can mention pg_dump even though you started pg_dumpall.

For a scheduled job, add --no-password so a missing credential fails promptly instead of waiting for input. Do not add --no-sync to a production backup: it skips waiting for the operating system to safely write the file, which can leave it corrupt after a crash.

3. Verify the file before relying on it

Check that the file exists, is non-empty, and contains the expected SQL markers.

dump=/var/backups/postgresql/cluster-2026-09-26.sql
test -s "$dump" && echo "dump is non-empty"
grep -m 1 -E '^CREATE DATABASE|^CREATE ROLE' "$dump"
tail -n 5 "$dump"

The exact output varies with the cluster. A non-empty file alone is not proof of a successful backup: read the command's exit status and its error output, and check that expected databases and roles appear. Keep a copy outside the database host if the host itself is part of the failure you are planning for.

4. Use scoped dumps when you do not need everything

You can produce smaller files when a full cluster dump is not the goal. These still use the same connection and privilege rules:

• --globals-only writes roles and tablespaces without databases.
• --roles-only writes roles without databases or tablespaces.
• --tablespaces-only writes tablespaces without databases or roles.
• --schema-only writes object definitions without table data.
• --data-only writes data without schema definitions.
• --exclude-database=PATTERN omits matching databases. Quote patterns containing shell wildcard characters.

For a cross-major-version restore, consider --quote-all-identifiers. It makes the SQL harder to read but avoids differences in which words are reserved between PostgreSQL versions. Do not use --binary-upgrade for an ordinary backup; it is intended for in-place upgrade tools.

5. Restore into a destination cluster

Inspect the SQL and prepare the destination first, including every directory required by source tablespaces.

Then run the script through psql while connected to a database that will not be dropped.

psql -X \
  --host=127.0.0.1 \
  --port=5432 \
  --username=postgres \
  --file=/var/backups/postgresql/cluster-2026-09-26.sql \
  --dbname=postgres

The -X option ignores the user's psql startup file, making the restore less dependent on local configuration. The starting database name is not used for the whole import: the script contains commands to create and connect to the saved databases. When the dump was made with --clean, start with postgres, because the script may immediately try to drop other databases.

Some errors can be expected. A bootstrap role may already exist, and a clean restore can report that an object did not exist. Review every error rather than assuming all errors are harmless. A missing tablespace directory, insufficient privilege, failed connection, or incompatible SQL requires action.

6. Check the restore

Check that the databases and roles are present.

psql -X --username=postgres --dbname=postgres -c '\l'
psql -X --username=postgres --dbname=postgres -c '\du'

Connect to each restored database and run ANALYZE, or use vacuumdb -a -z if that is appropriate for your maintenance window. This rebuilds planner statistics after loading data.

If you need to undo a test restore, remove and recreate the disposable destination cluster using your normal PostgreSQL administration procedure. There is no inverse command for a restore into a live cluster, which is why a pre-restore backup and an isolated test matter.