Back Up and Restore a PostgreSQL Cluster with pg_dumpall

You will create one SQL file containing a PostgreSQL cluster's databases and shared objects, inspect it, and restore it with psql. This guide 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.

Checkpoint: confirm the client

1. Check the installed version and available options.

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.

Create a complete cluster dump

1. 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.

Checkpoint: verify the file before relying on it

1. 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.

Useful scoped dumps

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.

Restore into a destination cluster

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

2. 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.

After the restore

1. 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.