Pg_dump vs pg_basebackup: Key Differences and Usage Guide

pg_dump creates logical backups by exporting database contents as SQL scripts or archive files, while pg_basebackup makes physical backups by copying the entire database cluster files. Use pg_dump for flexible, selective backups and pg_basebackup for fast, full cluster backups suitable for replication.

Quick Comparison

This table summarizes the main differences between pg_dump and pg_basebackup.

Feature	pg_dump	pg_basebackup
Backup Type	Logical (SQL scripts or archive)	Physical (file system level)
Backup Scope	Individual databases or objects	Entire database cluster
Backup Speed	Slower, depends on data size	Faster, copies files directly
Restore Method	Restore via SQL commands	Restore by copying files back
Use Case	Selective backups, migrations, upgrades	Full cluster backup, replication setup
Consistency	Consistent snapshot per database	Consistent physical snapshot

Key Differences

pg_dump creates a logical backup by exporting database contents as SQL statements or custom archive formats. This means it extracts data and schema in a readable form, allowing selective restoration of tables or schemas. It works on a per-database basis and is slower because it processes data row by row.

In contrast, pg_basebackup performs a physical backup by copying the entire PostgreSQL data directory at the file system level. It captures the exact state of the database cluster, including all databases, configurations, and transaction logs. This method is faster and ideal for setting up streaming replication or full disaster recovery.

Restoring from pg_dump involves running SQL scripts to recreate database objects and data, which can be selective and flexible. Restoring from pg_basebackup requires replacing the data directory with the backup files, making it an all-or-nothing approach but very fast to recover.

Code Comparison

Here is how you create a backup of a single database using pg_dump:

bash

pg_dump -U postgres -F c -b -v -f mydb_backup.dump mydb

Output

Output shows progress of dumping database 'mydb' into a custom format file named 'mydb_backup.dump'.

pg_basebackup Equivalent

Here is how you create a full physical backup of the entire PostgreSQL cluster using pg_basebackup:

bash

pg_basebackup -U postgres -D /var/lib/postgresql/backup -F tar -z -P

Output

Output shows progress of copying the entire data directory into a compressed tar archive in '/var/lib/postgresql/backup'.

When to Use Which

Choose pg_dump when you need flexible backups of individual databases or specific objects, such as for migrations, upgrades, or partial restores. It is best when you want human-readable backups or need to move data between different PostgreSQL versions.

Choose pg_basebackup when you want a fast, consistent physical copy of the entire database cluster, especially for setting up replication or full disaster recovery. It is ideal when you need to restore the entire cluster quickly without selective data recovery.

Key Takeaways

pg_dump creates logical backups suitable for selective restores and migrations.

pg_basebackup creates physical backups ideal for full cluster recovery and replication.

pg_dump works per database; pg_basebackup backs up the entire cluster.

Restoring from pg_dump involves SQL scripts; from pg_basebackup involves file replacement.

Use pg_dump for flexibility, pg_basebackup for speed and completeness.