A physical backup is a database backup that copies the actual files the database engine stores on disk, such as data files, transaction log segments and control files, rather than exporting the data as SQL statements. In backup & recovery it is the standard method for large databases, because restoring files is far faster than replaying millions of inserts and because it forms the base for point-in-time recovery.
The result is tied to the engine, its major version and platform: a physical backup of PostgreSQL 16 restores only to PostgreSQL 16, and an InnoDB copy restores only to a compatible MySQL or MariaDB server. That constraint is the main trade-off against a logical backup, which is portable but much slower to restore.
How a physical backup captures a consistent copy
Simply copying files from a running database produces an inconsistent image, because pages change while the copy is in progress. Physical backup tools solve this in one of two ways: they stop the database first, creating a cold backup, or they copy files while it runs and also capture the transaction log generated during the copy, creating a hot backup.
In a hot backup, the restored files are first brought to a consistent state by replaying the captured log, the same mechanism the engine uses for crash recovery. PostgreSQL marks the start and end of the copy and requires the WAL in between; Percona XtraBackup and MySQL Enterprise Backup copy InnoDB files and the redo log, then run a prepare phase before the data is usable.
The table below lists the main ways to take a physical backup and their characteristics.
| Method | Database availability | Consistency mechanism | Example tools |
|---|---|---|---|
| Cold file copy | Offline during the copy | Clean shutdown | Operating system copy tools |
| Online base backup | Online | Log captured and replayed | pg_basebackup, pgBackRest, Barman |
| Hot InnoDB backup | Online | Redo log applied in prepare phase | Percona XtraBackup, MySQL Enterprise Backup |
| Storage snapshot | Online, brief freeze | Crash-consistent image of volumes | LVM, ZFS, cloud volume snapshots |
| Engine-native backup | Online | Built-in coordination with logs | Oracle RMAN, SQL Server BACKUP DATABASE |
Full, incremental and differential physical backups
Because a physical backup works with pages or files, tools can detect which blocks changed since a previous backup and copy only those. This reduces backup time and storage, at the price of a longer restore chain.
| Type | What is copied | Restore requires | Trade-off |
|---|---|---|---|
| Full | All data files | That backup plus logs | Largest and slowest to create |
| Differential | Changes since the last full | Full plus latest differential | Grows each day until the next full |
| Incremental | Changes since the last backup of any type | Full plus every increment in order | Smallest, longest restore chain |
| Continuous log archiving | Every transaction log segment | A base backup plus the archived logs | Enables recovery to any moment |
Combining a weekly or daily full base backup with continuous archiving of the transaction log is the common design. It gives a recovery point objective of seconds to minutes, limited only by how often log segments are shipped off the server.
Physical backup compared with logical backup
A logical backup, produced by tools such as pg_dump or mysqldump, exports schema and rows as SQL or a portable archive. The two approaches complement each other, and many teams run both.
| Aspect | Physical backup | Logical backup |
|---|---|---|
| Content | Engine files and logs | SQL statements or portable dump |
| Backup speed on large data | Close to disk throughput | Limited by query and serialization |
| Restore speed | Fast, files are copied back | Slow, indexes are rebuilt |
| Point-in-time recovery | Yes, with archived logs | No, only the dump moment |
| Portability | Same engine version and platform | Across versions and often engines |
| Granularity | Usually the whole cluster | Single tables or schemas easily |
| Detects logical corruption | No, copies it faithfully | Partly, reading rows can reveal it |
For a database of several terabytes, a logical restore can take many hours or days because every index must be rebuilt, while a physical restore is bounded mainly by storage and network speed. That difference is why recovery time objectives for large systems are almost always met with physical backups.
Designing a physical backup strategy
A strategy starts from two targets: the recovery point objective (RPO), how much data loss is acceptable, and the recovery time objective (RTO), how long a restore may take. Physical backups influence both through their frequency, type and storage location.
- Store copies off the database host and ideally in a second location, following the familiar 3-2-1 principle.
- Make at least one copy immutable or offline so that ransomware or an administrator mistake cannot delete it.
- Define a backup retention policy that keeps enough full backups and logs to reach the oldest required recovery point.
- Encrypt backups at rest, because they contain every row and every password hash in the database.
- Verify checksums during backup, since some tools can detect corrupted pages while copying.
The most important rule is to test restores regularly. A restore drill measures the real RTO, proves that logs are complete, and catches missing configuration files or encryption keys before an incident does.
Pitfalls of physical backups
Physical backups fail in quiet ways that only show up during a restore. The most common are a gap in the archived logs, which breaks point-in-time recovery beyond that point; a snapshot taken across several volumes without coordination, which yields inconsistent files; and copying data files without the matching log segments, which leaves the database unable to start.
A physical backup also faithfully preserves corruption and accidental deletes. If a table was dropped at noon, only point-in-time recovery to a moment before noon, or a separate logical export, brings it back. Replication is no substitute either: a replica applies the same mistake within seconds.
In everyday language
A physical backup copies the database’s own files, the way you might copy an entire folder from one computer to another. Restoring it is quick, because the files simply go back into place.
The catch is that the copy only works with the same kind and version of database. A logical backup is more like a printed list of all the records. Anyone can read it, but retyping everything takes much longer.
Seen in the real world
A logistics company runs a 3 TB PostgreSQL database for shipment tracking. Its policy requires no more than five minutes of data loss and a restore within two hours. A nightly logical dump took over six hours to restore in a drill, so it could not meet the target.
The team switches to pgBackRest with a weekly full backup, daily differential backups and continuous WAL archiving to object storage in another region. In the next drill, the database is restored and rolled forward to a chosen minute in about 70 minutes. When an import script later deletes thousands of shipments by mistake, the team restores a copy to the moment before the script ran and recovers the missing rows the same afternoon.