Overview
Regular backups are the foundation of database reliability. PostgreSQL provides two command-line tools for logical backups: pg_dump for a single database and pg_dumpall for an entire cluster. This tutorial covers both backup and restore workflows with practical examples.
Logical vs Physical Backups
| Type | Tool | Use case |
|---|---|---|
| Logical | pg_dump, pg_dumpall |
Portable dumps, version upgrades, selective restore |
| Physical | pg_basebackup |
Full cluster replication, point-in-time recovery |
This tutorial focuses on logical backups, which are the most common starting point.
Back Up a Single Database
Plain SQL Format
pg_dump -U postgres -h localhost mydb > mydb_backup.sql
The output is a plain SQL file that can be restored with psql. It is human-readable and easy to inspect.
Custom Format (Recommended)
pg_dump -U postgres -h localhost -Fc mydb > mydb_backup.dump
The custom format (-Fc) is compressed and supports parallel restore and selective table restore. This is the preferred format for production backups.
Compressed Plain Format
pg_dump -U postgres -h localhost mydb | gzip > mydb_backup.sql.gz
Back Up All Databases
pg_dumpall -U postgres -h localhost > all_databases.sql
pg_dumpall includes global objects such as roles and tablespaces that pg_dump does not capture.
Back Up Only the Schema
pg_dump -U postgres -h localhost -s mydb > mydb_schema.sql
Use -a instead of -s to dump only data without schema.
Restore from a Plain SQL Backup
createdb -U postgres -h localhost mydb_restored
psql -U postgres -h localhost -d mydb_restored < mydb_backup.sql
Restore from a Custom Format Backup
pg_restore -U postgres -h localhost -d mydb_restored mydb_backup.dump
To restore only specific tables:
pg_restore -U postgres -h localhost -d mydb_restored -t users mydb_backup.dump
To use parallel restore for faster performance:
pg_restore -U postgres -h localhost -d mydb_restored -j 4 mydb_backup.dump
Backup Command Reference
| Command | Purpose |
|---|---|
pg_dump -Fc db > db.dump |
Custom format, compressed |
pg_dump -Fp db > db.sql |
Plain SQL format |
pg_dump -s db > schema.sql |
Schema only |
pg_dump -a db > data.sql |
Data only |
pg_dump -t users db > users.sql |
Single table |
pg_dumpall > all.sql |
All databases and roles |
Automating Backups with Cron
0 2 * * * pg_dump -U postgres -h localhost -Fc mydb > /backups/mydb_$(date +\%Y\%m\%d).dump
This runs a compressed backup every day at 2:00 AM, with the date appended to the filename.
Restore Troubleshooting
| Error | Solution |
|---|---|
role "xxx" does not exist |
Create the role first, or use --no-owner |
permission denied for schema public |
Grant privileges or restore as superuser |
relation already exists |
Drop the target database and recreate it before restoring |
