Physical Backups: The Speed Demons

What Is a Physical Backup?

Imagine instead of photocopying a book word by word, you just made a perfect photocopy of the entire book's physical pages — binary images of every page, exactly as they are stored. That's a physical backup. It doesn't care what the words mean; it just copies the raw storage.

A Physical Backup copies the actual binary files from the database's data directory on disk. The database engine stores all its data in a structured set of files (pages, segments, WAL logs). A physical backup captures those files directly.

Common tools:
- pgBackRest — PostgreSQL (production-grade, supports compression, encryption, parallel transfer)
- WAL-G — PostgreSQL and MySQL (cloud-native, streams directly to S3/GCS/Azure)
- Percona XtraBackup — MySQL/InnoDB (hot backup without locking)

How It Works: Base Backup + WAL Archiving

Physical backup systems work in two parts:

1. Base Backup — A periodic snapshot of the entire data directory. Think of this as the "starting point." A tool like WAL-G takes this snapshot and pushes it straight to object storage (S3, GCS, Azure).

2. WAL Archiving — Continuous streaming of Write-Ahead Log (WAL) files. WAL is PostgreSQL's transaction journal: every change to the database is first written to WAL before being applied to the data files. By configuring PostgreSQL's archive_mode and pointing archive_command at a tool like WAL-G, every completed WAL segment is continuously pushed offsite, capturing every single transaction.

To restore, you combine the base backup with the WAL stream, replaying transactions up to any desired point.

Pros

• Fast Restore: You are copying binary files, not replaying SQL. For a 5TB database, file copy throughput is orders of magnitude faster than re-executing INSERT statements.
• Point-in-Time Recovery (PITR): The killer feature. By replaying WAL logs, you can restore to any specific second within your retention window.
• Consistent Snapshots: The backup captures the database in a transactionally consistent state without locking tables.

Cons

• Not Portable Across Versions: You cannot restore a physical backup to a different PostgreSQL major version or a different OS architecture. The binary format is version-specific.
• All or Nothing: You generally restore the entire database cluster. Extracting a single table requires restoring the full cluster first, then dumping the table logically.
• Operational Complexity: Tools like pgBackRest require configuration, a dedicated backup server or cloud storage, and ongoing maintenance.