Backup & Recovery in SQL

The Disaster Recovery Engine: Backup & Recovery No matter how optimized your queries are or how secure your roles are, production database infrastructure is always vulnerable to real-world failures. Hardware crashes, corrupted storage drives, data-center power outages, or accidental human errors can wipe out critical systems instantly. Backup & Recovery is the collection of architectural strategies, automated tools, and data replication pipelines designed to preserve data snapshots and restore systems smoothly to prevent data loss. 1. Preserving Data: Backup Methodologies A production backup strategy must balance backup speed, storage costs, and the system resource overhead required to generate the files. THE BACKUP ARCHITECTURE VECTOR │ ┌──────────────────────────────┴──────────────────────────────┐ ▼ ▼ Logical Backups Physical Backups • Generates raw plain-text SQL files. • Copies raw binary files and disk blocks directly. • Output formats: `.sql`, `.tar`. • Output formats: Exact byte-by-byte snapshots. • Ideal for migrations and small tables. • Ideal for rapid multi-terabyte recovery paths. A. Logical Backups Logical backups scan your database tables and export them into plain-text script files containing the exact CREATE TABLE and INSERT statements needed to rebuild the database from scratch. The Tools: PostgreSQL uses pg_dump (single table/database) and pg_dumpall (entire cluster setup); MySQL uses mysqldump . Production Standard: Excellent for moving data across different hardware setups, testing schemas on local dev environments, or backing up smaller production databases. However, because rebuilding data via thousands of INSERT statements is slow, logical backups are inefficient for massive, multi-terabyte production systems. Bash # Executing a live compressed logical database backup from a terminal shell pg_dump -U application_backend_user -F c -b -v -f production_snapshot.backup iiu_db B. Physical Backups Physical backups bypass SQL commands entirely, creating a direct, byte-by-byte copy of the actual binary files, data directories, and physical disk storage blocks used by the database engine. The Tools: PostgreSQL uses pg_basebackup ; MySQL uses Percona XtraBackup . Production Standard: Because it reads and writes raw binary chunks directly, this method is significantly faster than a logical dump. It is the industry standard for backing up massive, high-volume production databases. 2. Restoring Infrastructure: Database Reconciliation A backup file is only useful if it can be successfully restored during an outage. The restore execution path must match the original backup's format. A. Restoring Logical SQL Dumps Plain-text SQL dump files are parsed and re-executed using standard database shell connections or tools like pg_restore (for custom compressed archive layouts). Bash # Rebuilding a logical database cluster footprint from a compressed backup file pg_restore -U administrative_superuser -d test_db_recovery -v production_snapshot.backup B. Restoring Physical Snapshots To restore a physical backup, you stop the database engine software, clear out the corrupted database storage directory on the server, swap in the backup binary files, and restart the database service. 3. Surgical Time-Travel: Point-in-Time Recovery (PITR) Standard daily backups have a major limitation: if your system creates a backup every night at 12:00 AM, and an application bug accidentally corrupts your data the next afternoon at 3:00 PM, running a standard restore will wipe out 15 hours of valid business data. Point-in-Time Recovery (PITR) solves this problem. It allows you to roll your database back to an exact microsecond in the past (e.g., exactly 2:59:59 PM, right before the corruption occurred). The Two Core Components of PITR The Base Physical Snapshot: A baseline binary backup file generated periodically (e.g., once a week). The Transaction Log Stream: A continuous, real-time log of every single change made to the database, written before the changes hit the table blocks. This log is known as the Write-Ahead Log (WAL) in PostgreSQL, or the Redo Log / Binlog in MySQL. The PITR Recovery Process Timeline When you trigger a Point-in-Time Recovery, the database engine executes a specific multi-step timeline: It loads your oldest baseline physical snapshot, restoring the entire database to its historical starting point state. It opens the sequential timeline of archived transaction logs (WAL/Binlog files). The Roll-Forward Phase: The engine replays every single insertion, update, and deletion log sequentially, fast-forwarding through time row-by-row. The moment it hits your designated stop timestamp parameter, the playback engine halts immediately, drops any uncommitted transactions, and opens the database up for production connections again safely. 4. Scaling Availability: Replication Basics Relying on a single database server means your system has a Single Point of Failure (SPOF) . If that single server goes offline, your entire application goes down with it. Database Replication solves this by continuously copying your data across multiple servers in real time. PRIMARY-REPLICA ARCHITECTURE BACKEND APPLICATION PRIMARY DATABASE SERVER REPLICA SERVER (Read-Only) ┌───────────────────────┐ ┌─────────────────────────┐ ┌─────────────────────────┐ │ Read/Write Operations │ ──────► │ Writes data to disk │ ────► │ Reads WAL stream │ │ (Inserts & Updates) │ │ Streams WAL binary text │ │ Replays edits inline │ └───────────┬───────────┘ └─────────────────────────┘ └─────────────────────────┘ │ ▲ └─────────────────────── Read-Only Analytical Queries ───────┘ A. Primary-Replica Topology (Active-Passive Architecture) The Primary Node: Serves as the central entry point for your application. It handles all write operations ( INSERT , UPDATE , DELETE ) and generates the stream of transaction logs (WAL). The Replica Nodes: Read-only shadow copies that continuously monitor and replay the primary server's WAL stream, updating their own local disk storage to match. Production Read-Scaling Benefit: You can point heavy, slow analytical read queries directly to your read-only replica servers, taking the processing load off your primary node so your core application stay fast. B. Synchronous vs. Asynchronous Replication When the primary node receives a write operation, it can replicate that change to the replica servers using two different timing methods: Asynchronous Replication (Default Standard): The primary server writes the change to its own disk, confirms the success to your application immediately, and sends the change log to the replicas in the background. Pros: Ultra-fast write performance. Cons: Risk of minimal data loss. If the primary server crashes completely before a background change log reaches the replicas, that data is lost. Synchronous Replication: The primary server receives the write operation but waits to send a success confirmation to your application until both the primary disk and the replica servers have confirmed they have written the change safely. Pros: Zero data loss. If the primary server fails, your replicas are guaranteed to be perfectly up to date. Cons: Slower write speeds. Your application must wait for network round-trips to every replica server before a write can finish. Disaster Recovery Strategy Reference Matrix Operational Continuity Tool Primary RPO/RTO Impact Data Currency Fidelity Primary Production Safeguard Role Logical Backup ( pg_dump ) High RPO/RTO (Slow restoration lag). Stale snapshot from the exact moment the export file was generated. Managing dev migrations, staging database cloning, and archiving smaller lookup tables safely. Physical Backup Moderate RPO/RTO (Fast binary data block copy). Snapshot from the exact moment the data folder was copied. Providing a baseline storage snapshot for high-capacity multi-terabyte transactional setups. Point-in-Time Recovery (PITR) Ultra-Low RPO. Allows near-zero data loss targets. Real-Time. Rebuilds data up to the exact microsecond before an incident occurred. Recovering from catastrophic user errors, application script logic bugs, or ransomware attacks. Asynchronous Replication Near-instant failover capabilities (Low RTO). Minor replication lag delay behind the active primary master node. Scaling application read performance across read-only nodes and providing a backup failover target if a server goes down. Synchronous Replication Absolute Zero RPO. Guarantees no data loss. Perfect Real-Time Sync. Matches the primary master node perfectly across all operations. Powering mission-critical financial ledger frameworks and core ledger books where data consistency is vital.