Logical Backups: The Portable Approach

What Is a Logical Backup?

Imagine photocopying every page of a book, word by word, so that someone else could retype the entire book from scratch. That's a logical backup: it captures the meaning of the data (the rows, tables, and relationships) as a series of human-readable instructions.

A Logical Backup exports the data as SQL commands (CREATE TABLE, INSERT INTO) or a generic format like CSV or JSON. To restore, the database engine reads these instructions and re-executes them, rebuilding the database from scratch.

Common tools:
- pg_dump / pg_restore — PostgreSQL
- mysqldump / mysqlpump — MySQL/MariaDB

How It Works in Practice

pg_dump exports a database to a compressed custom-format file (-Fc), and pg_restore reads it back into a newly created target database. The -Fc custom binary format is faster to restore than plain SQL and, unlike plain SQL dumps, supports parallel restore — passing -j 4 to pg_restore splits the rebuild across 4 workers, dramatically speeding up restores of large schemas with many independent tables and indexes.

Pros

• Human Readable (plain SQL format): You can open the file and inspect the data directly.
• Portable: You can restore a PostgreSQL 13 backup into a PostgreSQL 15 database. This makes logical backups the standard tool for major version upgrades.
• Granular: You can back up a single table, a single schema, or exclude specific tables entirely.

Cons

• Slow Restore: The database must re-execute millions of INSERT statements and rebuild every index from scratch. For terabyte-scale data, this can take days.
• No Point-in-Time Recovery: A logical backup is a snapshot. You can only restore to the exact moment the dump was taken — not to an arbitrary second between backups.
• Performance Impact: Running pg_dump performs a full sequential scan of all data, which can evict hot rows from the shared buffer cache and degrade live query performance.