What Is a Logical Backup vs a Physical Backup Step by Step

"Back up the database" can mean two different things, and they differ in what you can do with the result. Choosing one without understanding the other leaves a gap: dumps that take eight hours to restore, or file copies that cannot move to a newer version. The choice depends on database size, recovery goals and flexibility. It extends pg_dump backups and dumping databases in Docker. The commands assume PostgreSQL 17 and MySQL 8.4.

What each one is

Logical   a description of the data: CREATE TABLE and INSERT statements, or COPY data in a dump format
          tools: pg_dump, mysqldump, mysqlpump, mongodump, sqlite3 .dump
Physical  the bytes of the data files (plus WAL/redo logs) as they are on disk
          tools: pg_basebackup, xtrabackup / mariabackup, LVM or cloud snapshots, rsync of a stopped data dir

A logical dump is rebuilt by the database engine on restore: it re-inserts every row and rebuilds every index, which is why it is slow for large data. A physical backup is the files themselves, so restoring means putting them back and starting the server, with crash recovery replaying the logs.

Examples

# logical
pg_dump -Fc -f appdb.dump appdb
mysqldump --single-transaction --routines appdb > appdb.sql

# physical
pg_basebackup -h db -U replicator -D /backups/base -Ft -z -X stream -P
xtrabackup --backup --target-dir=/backups/xtra

pg_dump runs in a transaction that sees a consistent snapshot, so it can run while the database is in use. pg_basebackup copies the cluster's files from a running server and streams the WAL generated during the copy, which makes the copy consistent after recovery. Copying a live data directory with cp or rsync without that mechanism produces files from different moments, which will not start cleanly.

Comparing them

                      Logical             Physical
Backup speed          slower (reads all)  fast (file copy)
Restore speed         slow (rebuild)      fast (copy + recover)
Size                  often smaller*      full size of files, incl. bloat and indexes
Cross-version         yes (newer target)  no, same major version
Cross-architecture    yes                 no (usually)
Restore one table     yes                 hard (restore whole, then extract)
Point-in-time         no                  yes, with archived WAL/binlogs
Consistency           snapshot per dump   needs the correct procedure
* A dump excludes indexes and dead tuples; compression helps both.

The table is a summary of typical behaviour. A dump of a 200 GB database may take hours to restore because indexes must be rebuilt, where a physical restore of the same data is limited by disk speed. In the other direction, you can upgrade PostgreSQL 15 to 17 by dumping and restoring, and cannot by copying files without pg_upgrade.

Which for what

For a small database (a few gigabytes), logical dumps are simple, portable and enough, and restore in minutes. As the database grows, restore time and the data-loss window matter more: add physical backups with continuous archiving so you can restore quickly and to a chosen moment, as in point in time recovery. Keep occasional logical dumps even then, since they are the format you can read, move to a new version, restore a single table from and use in a test environment.

Snapshots

sudo lvcreate --snapshot --size 10G --name dbsnap /dev/vg0/pgdata
sudo mount -o ro /dev/vg0/dbsnap /mnt/dbsnap
rsync -a /mnt/dbsnap/ /backups/pgdata-$(date +%F)/

Storage snapshots (LVM, ZFS, cloud volume snapshots) are physical backups taken at one instant. For a crash-consistent snapshot, the database recovers as it would after a power cut, which is safe for databases with a proper write-ahead log if the snapshot covers data and log together atomically. Check the database's documentation about quiescing. Cloud providers' managed snapshots follow this rule.

Test whichever you use

Both kinds fail in different ways: a logical dump may be incomplete if the process died midway, a physical one may be missing its logs. Restore both regularly, as in a restore drill, and monitor the age and size of backups, and encrypt them if they leave the host, see compressing and encrypting dumps.

Recovery targets decide the method

Two numbers drive the choice: how much data you can afford to lose and how long you can afford to be down. The first is the recovery point objective, the second the recovery time objective. A nightly logical dump gives a loss window of up to a day, which is fine for a hobby site and unacceptable for a shop taking orders. Continuous archiving of the write-ahead log brings the window to seconds, and a physical base backup keeps the restore time bounded by disk speed. Write the two numbers down before choosing tools, and test whether the chosen setup meets them with a measured restore, not an estimate.

Think about the failure you are protecting against. A dropped table or a bad deployment needs a point-in-time restore to a moment shortly before the mistake, which requires physical backups and logs. A destroyed server needs a fast whole-system restore. A need to move to a new major version or another architecture needs a logical export. Most real systems end up with both kinds, each covering a different failure.

Consistency and what the backup captures

A backup is only useful if it is consistent. Logical dumps achieve it by reading inside a single transaction, with a snapshot isolation level, so every table reflects the same instant. A physical copy of files taken while the database runs is inconsistent on its own, and relies on the log to repair it during recovery; that is why tools pair the file copy with the log generated during the copy. A plain file copy of a running data directory without that coordination produces something that may not start, or worse, starts and silently contains corruption.

Global objects need attention with logical backups. Roles, tablespaces and some settings live outside individual databases, and a per-database dump does not include them. Dump them separately, for instance with pg_dumpall --globals-only, and store the result with the rest. A restore that lacks the roles fails in confusing ways, with ownership errors scattered through the output.

Size, bloat and cost

Physical backups include everything in the files, including dead rows and bloated indexes, so they can be several times larger than the logical dump of the same data. Compression and incremental modes reduce the transfer: many tools can copy only changed blocks after a full base. Logical dumps compress well, since they contain text. Estimate storage for the retention period you need, including the log archive, and include the price of moving data out of cloud storage, because a restore may incur egress charges you did not plan for.

Verification differs for each kind

A logical dump is verified by restoring it into an empty database and comparing counts. A physical backup is verified by starting a server on the restored files and replaying logs to the target time. Both are cheap to automate in a container, and both should run on a schedule, so a corrupted backup is found within a day and not during an emergency. Record the restore time in each test; it is the number that tells you whether the method still meets the objective as the data grows.

Where this does not apply

SQLite is a single file; use .backup or VACUUM INTO for a consistent copy, which is a physical copy done safely. Managed databases do physical backups and point-in-time recovery for you; add a logical dump if you want an exit path. NoSQL stores have their own tools and consistency models. See the PostgreSQL backup chapter and the MySQL backup types.