What is the difference between logical and physical database backups?

Question

Accepted Answer

Logical and physical backups are two fundamentally different approaches to protecting database data. Each has strengths and weaknesses, and a well-designed backup strategy often uses both.

A logical backup exports the database content as SQL statements or a structured data format that can recreate the database objects and their data. Tools like pg_dump for PostgreSQL, mysqldump for MySQL, and expdp for Oracle produce logical backups. The output is typically a text file containing CREATE TABLE statements, INSERT statements for data, and other DDL and DML commands. Logical backups are human-readable, portable across different database versions and even different hardware architectures, and allow granular restoration of specific tables or schemas. However, they are slow for large databases because every row must be read, formatted, and written. Restoring a large logical backup can also be slow because every INSERT statement is executed individually.

A physical backup copies the actual data files that the database stores on disk. Tools like pg_basebackup for PostgreSQL, Percona XtraBackup for MySQL, and RMAN for Oracle create physical backups. These tools copy the binary data files, which includes all tables, indexes, and internal structures. Physical backups are much faster to create and restore for large databases because they perform file-level copying rather than row-by-row processing. However, they are not human-readable, are generally tied to the same database version and operating system architecture, and typically require restoring the entire database rather than individual objects.

The key differences come down to several factors. Speed favors physical backups significantly for large databases. A terabyte database might take hours to dump with pg_dump but can be physically copied in a fraction of that time. Portability favors logical backups because they can be loaded into different database versions or even different database systems with modifications. Granularity favors logical backups because you can export and import individual tables, schemas, or even filtered subsets of data. Consistency handling differs as well. Physical backups must account for the database being active during the backup by including WAL or redo log files to ensure a consistent restore point.

In PostgreSQL, pg_dump creates logical backups and supports multiple output formats including plain SQL, custom format for parallel restore, and directory format. pg_basebackup creates physical backups by streaming the database files from the server. In MySQL, mysqldump is the standard logical backup tool, while Percona XtraBackup provides hot physical backups without locking tables. In SQL Server, the BACKUP DATABASE command creates physical backups, and SQL Server Management Studio provides export wizards for logical exports.

A best practice is to use physical backups as your primary backup for fast recovery and supplement with periodic logical backups for portability and granular restore capabilities. Logical backups also serve as a cross-check: if a physical backup is corrupted, a recent logical backup provides an alternative recovery path.

As a junior DBA, become proficient with both types for your primary database system. Practice both backup and restore procedures so you can execute them confidently during an actual emergency.