A logical backup is a database backup that captures the logical content of a database, meaning its schema definitions and rows, as SQL statements or a portable archive format, rather than copying the underlying data files. Tools such as pg_dump for PostgreSQL and mysqldump for MySQL and MariaDB are the best-known examples, and the result is often called a database dump.
In backup & recovery planning, logical backups are valued for portability and selectivity: a dump can be restored on a different server version, operating system or even another database engine with some translation, and individual tables or schemas can be recovered on their own. Their weaknesses are speed on large databases and the lack of built-in point-in-time recovery.
How a logical backup is produced and restored
A logical backup tool connects to the database as a client, reads the catalog to discover tables, indexes, views, functions and permissions, and then queries every table to write out its rows. The output either contains CREATE statements followed by INSERT or COPY commands, or uses an engine-specific archive format that stores the same information more compactly.
Consistency comes from reading all tables inside one snapshot. In PostgreSQL, pg_dump uses a repeatable-read transaction, and in MySQL with InnoDB the --single-transaction option does the same, so the backup reflects one moment even while applications keep writing. Engines without transactional snapshots need table locks during the dump instead.
Restoring means replaying the dump: recreating objects, loading data and rebuilding indexes and constraints. Because indexes are rebuilt from scratch, restore time is often several times longer than dump time on large databases.
The table below lists common output formats and their properties.
| Format | Example tool or option | Selective restore | Parallel restore |
|---|---|---|---|
| Plain SQL script | pg_dump plain, mysqldump | Only by editing the file | No |
| Custom archive | pg_dump custom format | Yes, per object | Yes |
| Directory archive | pg_dump directory format | Yes, per object | Yes, also parallel dump |
| Delimited data files | Per-table CSV or tab files | Yes, per table | Yes, with scripting |
Logical backup compared with physical backup
The central contrast is with a physical backup, which copies data files, pages and transaction logs at the storage level. The two methods answer different recovery questions, and mature backup strategies usually combine them.
| Aspect | Logical backup | Physical backup |
|---|---|---|
| What is copied | Schema and rows as statements or archive | Data files and log segments |
| Speed on large data | Slower dump, much slower restore | Close to disk copy speed |
| Portability | Across versions and platforms | Same engine version and layout |
| Granularity | Single table or schema possible | Usually whole cluster or instance |
| Point-in-time recovery | No, only the dump moment | Yes, with archived logs |
| Corruption detection | Reading every row exposes many errors | May copy corrupted pages silently |
A useful side effect of logical backups is that reading every row forces the engine to touch all data, so certain kinds of page corruption surface during the dump rather than months later during an emergency restore.
Key options and parameters
The behavior of a logical backup depends heavily on tool options, and wrong defaults are a common reason why a dump looks fine but fails to restore.
| Setting | Purpose | Consideration |
|---|---|---|
| Consistent snapshot | One point-in-time view | Essential for transactional engines |
| Routines, triggers and events | Include stored code | Not always included by default |
| Users and privileges | Include roles and grants | Often stored separately, as with pg_dumpall |
| Compression | Reduce archive size | Trades CPU for disk and network |
| Parallel jobs | Dump or restore tables concurrently | Limited by the slowest large table |
| Character set | Preserve text encoding | Mismatches corrupt non-ASCII data |
Global objects such as roles, tablespaces and server settings live outside individual databases in many engines, so a backup plan must capture them explicitly or restored databases will lack their owners and permissions.
When to use a logical backup
Logical backups are the right tool when portability or selectivity matters more than recovery speed. They are rarely sufficient on their own for large production systems, where the time to restore from a dump can exceed acceptable recovery targets.
- Major version upgrades and migrations between servers or cloud providers.
- Restoring a single table or schema after an accidental deletion, without rolling back the whole database.
- Creating development or test copies, often combined with masking of personal data.
- Long-term archives in a format that remains readable after the original engine version is retired.
- Small and medium databases, where dump and restore complete within the agreed recovery time.
For large systems, a common pattern is physical backups with log archiving for disaster recovery and point-in-time recovery, complemented by periodic logical backups for selective restores and an independent second copy.
Limitations and common mistakes
The most serious mistake is never testing the restore. A dump file that exists is not proof of a working backup; only a successful restore into a separate environment, followed by basic checks, confirms that the backup is usable.
| Pitfall | Effect | Prevention |
|---|---|---|
| No consistent snapshot | Related tables out of sync | Use single-transaction or snapshot options |
| Missing global objects | Restore fails on owners and grants | Back up roles and settings separately |
| Untested restore | Failure discovered during an incident | Scheduled restore drills |
| Dump stored on same server | Lost together with the server | Copy to separate storage |
| Restore time underestimated | Recovery target missed | Measure full restore duration |
Long-running dumps also have a side cost on the source: a snapshot held open for hours can delay cleanup of old row versions in MVCC engines, so very large dumps are often taken from a replica instead of the primary.
In everyday language
A logical backup writes down the contents of a database as a set of instructions. These instructions say which tables to create and which rows to put into them.
It is like saving a recipe instead of freezing the finished meal. The recipe works in almost any kitchen, and you can cook just one dish from it. Cooking everything from scratch takes longer, though. The key takeaway is that logical backups are flexible and portable, but restoring a large database from them is slow.
Seen in the real world
An online booking service runs a 60 GB PostgreSQL database and protects it with nightly physical backups plus archived write-ahead logs. In addition, the team creates a weekly logical backup with pg_dump in directory format using four parallel jobs and stores it in separate object storage.
One afternoon, a faulty migration script empties the table that holds discount codes, while all other tables remain correct. Rolling the entire database back would discard hours of new bookings. Instead, the administrator uses pg_restore to load only the discount table from the latest logical backup into a staging schema, compares it with recent changes and copies the missing rows back. The outcome is a full recovery of the lost data in under an hour, with no effect on bookings made that day.