A database dump is a logical export of a database into a file that contains the statements or records needed to rebuild its schema and data from scratch. In backup and recovery for databases, a dump is the most portable form of backup: it describes what the data is rather than how it is stored on disk, so it can be restored on another server, another operating system or a newer major version of the same engine.
Typical tools are `mysqldump` and `mysqlpump` for MySQL and MariaDB, `pg_dump` and `pg_dumpall` for PostgreSQL, `mongodump` for MongoDB and the `.dump` command of SQLite. Their output is either plain SQL text or an engine-specific archive format that a matching restore tool reads back.
How a database dump is produced
A dump tool connects to the server as an ordinary client, reads the system catalog to learn the structure, and then reads every table with normal queries. It writes object definitions first (schemas, tables, types, sequences), then the rows, and finally the objects that are cheaper to build after loading, such as indexes, constraints and triggers.
Consistency is the critical property. On engines with multiversion concurrency control, the tool opens a single transaction with a snapshot, so every table reflects the same moment even while applications keep writing. pg_dump does this by default, and mysqldump --single-transaction does it for InnoDB tables. Engines or tables without transactional snapshots, such as MyISAM, need table locks for a consistent result, which blocks writers for the duration of the dump.
Because a dump re-reads data through the query layer, it never contains index pages, free space or physical corruption from the storage files. Restoring it rebuilds every index and reclaims all bloat, which is why dumps are also used to migrate and clean up databases.
The table below shows the common output formats and what each one allows during restore.
| Format | Typical tool and option | Restore method | Selective and parallel restore |
|---|---|---|---|
| Plain SQL script | mysqldump, pg_dump -Fp | Run through mysql or psql | No, the script runs top to bottom |
| Custom archive | pg_dump -Fc | pg_restore | Selective yes, parallel yes |
| Directory archive | pg_dump -Fd -j 4 | pg_restore -j 4 | Selective yes, parallel dump and restore |
| Tar archive | pg_dump -Ft | pg_restore | Selective yes, parallel no |
| BSON files | mongodump | mongorestore | Per collection, parallel collections |
Logical dumps compared with physical backups
A dump is a logical backup, and its main counterpart is the physical backup, which copies the data files themselves. The difference decides speed, portability and whether point-in-time recovery is possible. A database snapshot at the storage level is a third option with its own trade-offs.
| Property | Database dump | Physical backup | Storage snapshot |
|---|---|---|---|
| Unit of copy | SQL statements or records | Data files and pages | Disk blocks at one instant |
| Portability | Across versions and platforms | Same major version and platform | Same engine version and storage |
| Restore speed | Slow, indexes are rebuilt | Fast, files are copied back | Very fast, seconds to minutes |
| Granularity | Single table or schema possible | Usually whole instance | Whole volume |
| Point-in-time recovery | No, fixed moment only | Yes, with replayed logs | Only with replayed logs |
| Size | Compact, no index data | Includes indexes and free space | Full volume, often incremental |
The most important limitation follows from the table: a dump captures one fixed moment. Anything written after the snapshot is lost unless it is recovered from the binary log or another change log. Teams that need a low recovery point objective therefore pair physical backups with log archiving and keep dumps as a portable secondary copy.
Size, duration and restore time
Dump duration grows roughly linearly with data volume, because every row is read and serialized. Restore is usually the slower direction: loading rows is followed by index creation, constraint validation and statistics collection. On a single thread, a restore can easily take two to five times as long as the dump that produced it.
A rough calculation shows why size matters. A 500 GB database dumped at a sustained 100 MB per second needs about 5,000 seconds, or roughly 83 minutes, before compression and index rebuilding are counted. Restoring that same data with index builds at three times the dump duration would take about four hours, which is often longer than the outage a business can accept.
The following parameters have the largest effect on dump and restore performance.
| Parameter | Typical setting | Effect |
|---|---|---|
| Parallel jobs | -j 4 to -j 16 in pg_dump directory format | Dumps and restores several tables at once |
| Compression level | gzip level 1-6 or zstd | Smaller files, more CPU time |
| Snapshot mode | --single-transaction | Consistent dump without blocking writers |
| Extended inserts | Default in mysqldump | Many rows per statement, faster reload |
| Schema-only or data-only | --schema-only, --no-data | Exports structure or rows separately |
When a database dump is the right tool
Dumps are the preferred tool for portability and selectivity, not for fast disaster recovery of large systems. They work best in these situations:
- Major version upgrades and migrations between servers or cloud providers, where file-level copies are incompatible.
- Small and medium databases, typically up to a few hundred gigabytes, where restore time remains acceptable.
- Restoring a single table or schema after an accidental delete, without rolling back the whole instance.
- Seeding development and test environments, often combined with masking of personal data before the file leaves production.
- Long-term archives, because a plain SQL file stays readable even if the original binary formats are no longer supported.
For large production systems, a common policy is physical backups plus log archiving for primary recovery and a weekly or nightly dump as an independent, version-neutral copy.
Common mistakes with dumps
Most dump failures are discovered only during a restore, which is the worst possible moment. The table lists the typical problems and how practitioners prevent them.
| Mistake | Consequence | Prevention |
|---|---|---|
| Dump without a snapshot | Tables reflect different moments, broken references | Use --single-transaction or the engine default |
| Global objects omitted | Roles and tablespaces missing after restore | Add pg_dumpall --globals-only or user grants |
| Restores never tested | Corrupt or incomplete files found too late | Run scheduled test restores |
| Unencrypted files | Full copy of sensitive data exposed | Encrypt at rest and restrict access |
| Character set mismatch | Garbled text after reload | Set encoding explicitly, for example utf8mb4 |
A dump also contains every record in readable form, so it carries the same data protection obligations as the live database. Storing it on a shared drive without encryption effectively creates an unmonitored copy of production.
The idea in brief
A database dump is like writing down a complete recipe for a database instead of photographing the kitchen. The file lists every table and every row, so anyone with the same database software can rebuild it from the recipe, even on a different computer.
The benefit is flexibility: the copy can be moved, inspected and restored in parts. The cost is time, because rebuilding everything step by step is slower than copying files directly, and the recipe only describes the moment it was written.
A practical scenario
A small online retailer runs PostgreSQL 13 with a 60 GB database and plans to move to PostgreSQL 16 on a new server. The data files of version 13 cannot simply be copied to version 16, so the team uses pg_dump -Fd -j 8 during a quiet night window. With eight parallel jobs, the dump finishes in about 25 minutes and compresses to roughly 14 GB.
The team restores the directory archive on the new server with pg_restore -j 8, which loads the rows and rebuilds all indexes in about 70 minutes. Before switching traffic, they compare row counts for the largest tables and run the application test suite. The migration completes within the planned three-hour window, and the archive stays on encrypted storage as a fallback copy for the next month.