Email Us |TEL: 050-1720-0641 | LinkedIn | Daily Posts

Mintarc
  Mintarc Forge   Contact Us   News Letter   Blog   Partners
Collaboration Questions? Monthly Letter Monthly Blog Our Partners

Database Backup Strategies

Data is the stuff that makes applications valuable, and unlike static configuration files or compiled binaries, database state is constantly changing. Every second brings new transactions, updated records, and deleted rows, making the task of capturing a reliable, point-in-time snapshot a challenge. A naive file-level copy of a running database directory almost always results in corrupted files, torn pages, and broken relational constraints because the database engine holds dirty buffers in memory that have not yet been flushed to disk. To protect data without grinding production traffic to a halt, database admins rely on tools designed to guarantee transactional consistency. The architectures behind logical and physical backups specifically through pg_dump, WAL-G, and mydumper shows you how each tool solves the consistency puzzle and when to deploy each in production environments.

Logical vs. Physical Backups

It is important to know the difference between logical and physical backup methods, as this fundamental divide dictates recovery speed, storage overhead, and flexibility. Logical backups extract the schema and data from a database as human-readable SQL statements or structured binary representations of rows and tables. Because logical backups operate through standard database connections and queries, they are portable; you can restore a logical backup across different operating systems, CPU architectures, or even major database engine versions. However, this portability comes at the cost of speed during restoration, because the database engine must parse SQL, rebuild indexes from scratch, and validate constraints for every single row being reinserted.

Physical backups, operate at the filesystem and storage block level by copying the exact binary data files, page structures, and transaction logs that the database engine manages on disk. Because a physical restore places pre-built data pages and index trees back onto the storage volume, recovery times are dramatically faster for large datasets, and bounded only by disk I/O and network throughput. The trade-off is rigidity: a physical backup is coupled to the exact major version and hardware architecture of the source server, making it unsuitable for migrating between different platforms or selectively restoring a single corrupted table without spinning up an entirely separate recovery instance.

PostgreSQL Logical Backups with pg_dump

For PostgreSQL environments, pg_dump is the utility for generating consistent logical backups. When pg_dump initiates a backup, it opens a database connection and begins a transaction using the REPEATABLE READ isolation level. Uising PostgreSQL’s Multi-Version Concurrency Control, the tool captures a frozen snapshot of the entire database as it existed at the exact moment the transaction started. Concurrent users can continue inserting, updating, and deleting records without blocking pg_dump, and pg_dump will never see partial or uncommitted changes, guaranteeing complete referential integrity across all tables without requiring heavy table locks.

pg_dump supports plain-text SQL output, production workflows almost always utilize its custom format (-Fc) or directory format (-Fd). The directory format is good because it enables parallel dumping via the -j flag, allowing multiple worker processes to extract different tables concurrently and compress them on the fly. You should use pg_dump for small-to-medium databases (typically under a few hundred gigabytes), when migrating data between different PostgreSQL major versions, when moving workloads into or out of managed cloud services where filesystem access is restricted, or when you need the granular ability to extract and restore a single schema or table using pg_restore.

Continuous Archiving and PITR with WAL-G

When databases grow into the multi-terabyte range or when business requirements demand minimal data loss, logical dumps alone become insufficient due to lengthy restore times and wide recovery point objectives. This is where physical backups combined with continuous Write-Ahead Log (WAL) archiving shine, and WAL-G can be used as the standard for executing this strategy. WAL-G is a high-performance archival and restoration tool written in Go, designed as a cloud-native successor to WAL-E. It works by taking periodic full physical base backups of the PostgreSQL data directory streamed directly to object storage like Amazon S3, Google Cloud Storage, or Azure Blob Storage and simultaneously intercepting and compressing every WAL segment generated by the database as transactions occur.

The value of WAL-G is Point-In-Time Recovery (PITR). Because every single change made to the database is recorded in the archived WAL stream, an administrator can restore the most recent physical base backup and instruct PostgreSQL to replay the WAL segments up to a precise timestamp or transaction ID. If an engineer accidentally runs an unfiltered DELETE statement at 2:14 PM, WAL-G allows you to rewind the database to 2:13:59 PM, losing virtually zero legitimate data. WAL-G supports delta backups, which inspect data pages and upload only the blocks that have changed since the previous backup, reducing network bandwidth and cloud storage costs. You should deploy WAL-G for large production databases, mission-critical systems requiring strict Recovery Point Objectives measured in seconds, and environments needing fast disaster recovery

Parallel Dumps with mydumper

In the MySQL and MariaDB ecosystem, the native mysqldump utility has historically suffered from a major architectural bottleneck: it operates as a single-threaded process. For large databases, a single thread reading tables sequentially and writing to a single massive SQL file creates backup and restore delays. To overcome this limitation, mydumper (paired with its restoration counterpart, myloader) was made to bring multi-threaded parallelism to MySQL logical backups.

When mydumper begins, it acquires a brief global read lock (FLUSH TABLES WITH READ LOCK) just long enough to synchronize all worker threads to the exact same transactional snapshot and record the current binary log coordinates. Once each worker thread starts its own consistent transaction (START TRANSACTION WITH CONSISTENT SNAPSHOT), the global lock is immediately released, allowing production writes to resume within milliseconds. The workers then dump different tables in parallel and can even split massive individual tables into smaller primary-key chunks writing each table's schema and data into separate, compressed files. When disaster strikes, myloader reads these separate files concurrently across multiple CPU cores, accelerating restoration speeds by an order of magnitude compared to mysqldump. You should choose mydumper for large MySQL or MariaDB instances where logical portability is required, when upgrading across major MySQL versions, or when seeding staging environments quickly from production data.

Choosing the Right Tool for Your Architecture

Selecting the corr3ct backup strategy requires balancing database size, acceptable downtime, and complexity. For smaller PostgreSQL deployments, cross-version upgrades, and routine developer exports, pg_dump offers simplicity and zero external infrastructure dependencies. When your PostgreSQL footprint scales up or requires near-zero data loss through Point-In-Time Recovery, pairing periodic physical base backups with continuous WAL-G archiving provides enterprise-grade resilience. And, for MySQL and MariaDB environments that have outgrown single-threaded dumps, mydumper bridges the gap by delivering multi-core speed preserving the flexibility of logical backups. The most resilient production architectures will combine both approaches relying on continuous physical archiving for disaster recovery while maintaining scheduled logical dumps for long-term archival and granular table-level restores.