How to Use pg_dumpall in PostgreSQL for Full Database Backup

Use pg_dumpall to back up all PostgreSQL databases and global objects like roles and tablespaces in one step. Run pg_dumpall > backup.sql to create a full SQL dump file that can be restored later with psql.

Syntax

The basic syntax of pg_dumpall is simple and includes options to customize the backup:

• pg_dumpall [options] - runs the dump of all databases and global objects.
• -U username - specifies the PostgreSQL user to connect as.
• -h hostname - specifies the server host.
• -p port - specifies the server port.
• > filename.sql - redirects the output to a file.

bash

pg_dumpall -U postgres > all_databases_backup.sql

Example

This example shows how to back up all databases and global objects to a file named full_backup.sql. You can later restore this file using psql.

bash

pg_dumpall -U postgres > full_backup.sql

# To restore:
psql -U postgres -f full_backup.sql

Common Pitfalls

Common mistakes when using pg_dumpall include:

• Not specifying the correct user with -U, causing authentication errors.
• Running the command without sufficient permissions to read all databases.
• Confusing pg_dumpall with pg_dump, which only dumps one database.
• Not redirecting output to a file, which causes the dump to print to the terminal.

bash

Wrong way:
pg_dumpall

Right way:
pg_dumpall -U postgres > backup.sql

Quick Reference

Option	Description
-U username	Connect as specified user
-h hostname	Connect to specified host
-p port	Connect to specified port
> filename.sql	Redirect output to file
--clean	Include commands to drop databases before recreating
--globals-only	Dump only global objects like roles and tablespaces

Key Takeaways

Use pg_dumpall to back up all PostgreSQL databases and global objects in one file.

Always specify the user with -U to avoid authentication issues.

Redirect output to a file to save the backup instead of printing to the terminal.

pg_dumpall differs from pg_dump by dumping all databases, not just one.

Restore backups with psql using the saved SQL file.