pg_dump vs pg_dumpall: which PostgreSQL backup do you need?

Use pg_dump when one application database is the recovery unit. It can create a custom-format archive that pg_restore can inspect, filter, reorder, and restore in parallel. Use pg_dumpall when the recovery unit is the PostgreSQL cluster: every database plus global roles, tablespaces, and configuration-parameter grants.

Many teams need both scopes, but not necessarily in one file. A practical setup can use separate custom-format pg_dump archives for application databases and a small pg_dumpall --globals-only export for roles and tablespaces. That keeps flexible per-database restores without forgetting cluster-wide objects.

pg_dump connects to one database. Its output includes that database's schema and data, but not cluster-wide roles or tablespaces. It makes a consistent export while readers and writers continue, although it takes ACCESS SHARE locks and can add meaningful load.

pg_dumpall calls pg_dump for each database and writes one plain SQL script. It also emits the global objects that pg_dump misses. A complete run normally requires a superuser, connects to the server multiple times, and produces a restore script with broad authority.

• One application database and selective restore: pg_dump --format=custom.
• All databases and global objects in one script: pg_dumpall.
• Application archives plus separate globals: pg_dump for each database and pg_dumpall --globals-only.
• Point-in-time recovery: use physical base backups and WAL archiving; neither command supplies it alone.

Keep passwords out of command arguments. PostgreSQL clients can read a .pgpass file with mode 0600, or receive credentials from a service manager for an unattended job. Use a client version that is at least as new as the server you are dumping, then test the target-version restore.

Custom format is a strong default for one database because pg_restore can list its contents and choose objects. pg_dumpall always produces SQL, so it must be restored with psql, not pg_restore.

List the archive before restoring it. This proves pg_restore can parse the file and lets you see extensions, schemas, tables, and ownership metadata. Then restore into a newly created database with error-on-failure enabled.

--no-owner suppresses ownership restoration, but it does not suppress GRANT and REVOKE statements that reference production roles. The scratch example also uses --no-acl so it can run without those grantee roles. A production recovery that must preserve ownership and privileges should restore or create the roles first, then omit both options. Never test --clean against the source database.

A pg_dumpall script can create roles, tablespaces, databases, and their contents. PostgreSQL expects it to be fed to psql while connected to a maintenance database. Run the first rehearsal in a disposable cluster that matches the source major version and has the required tablespace paths.

Read the psql output carefully. An otherwise successful cluster script can report expected conflicts with objects created during target initialization, while a missing role, extension, tablespace directory, or privilege can break later statements. Verify every database, role membership, extension, owner, and application login before cutover.

Rested offers single-database and server-wide logical backup scopes for Docker and network PostgreSQL sources. The selected scope carries through to recovery, where a server-wide restore can create or alter databases and cluster-level objects far beyond one application.

The dashboard keeps that scope visible and records the operation, but it cannot make a broad restore harmless. Use an isolated cluster for rehearsal, and do not mistake either logical mode for physical backup plus WAL-based point-in-time recovery.