dataiotech Labs
dbaguidedbaguideDBAdataxtoolsdataopsphere
dbaguide.com
Home/Fundamentals/backup-stratejisi
RPO · RTO · PITR · İzolasyon
8 min read

Physical vs Logical Backup Philosophy: RPO/RTO, Snapshotting, and PITR Architecture

Zero Data Loss Enterprise Backup Strategy via pgBackRest, Percona XtraBackup, and Storage Snapshots

Core Architecture Principles
Logical dump vs physical binary backup structural trade-offs
RPO and RTO mathematical recovery boundaries
WAL and Binlog streaming pipelines for sub-second PITR
3-2-1-1-0 immutable backup and automated verification standard

▸1. Why Logical Dumps Fail in Production

Relying on pg_dump or mysqldump cronjobs for production databases beyond 50GB represents a critical operational vulnerability:

  1. 1RTO Catastrophe (Recovery Time): Restoring a 500GB SQL dump requires re-executing millions of individual INSERT statements and rebuilding every secondary index from scratch. This can easily consume 18 to 36 hours of sustained downtime!
  2. 2RPO Degradation (Data Loss Window): A dump executed at 02:00 means an infrastructure outage at 16:30 incurs a 14.5-hour permanent data loss (RPO = 14.5 hours).
  3. 3Consistency & Bloat Stalls: Logical dumps mandate long-running snapshot locks or prolonged MVCC transaction snapshots, triggering massive table bloat and WAL retention backlogs.

▸2. Block-Level Physical Backups

Enterprise physical backup systems (pgBackRest, Percona XtraBackup, mariabackup) stream raw database blocks directly without invoking query parsing layers:

  • •PostgreSQL (pgBackRest): Reads raw 8KB database pages directly. Copies only modified blocks during Differential or Incremental passes. With multi-core compression and async I/O, a 1TB database backs up in under 20 minutes.
  • •MySQL (Percona XtraBackup): Copies InnoDB physical tablespace pages while transactions are actively committing. Concurrently reads the active Redo Log stream to reconcile crash-inconsistent blocks during the prepare recovery phase.

▸3. Point-in-Time Recovery (PITR): Sub-Second Precision

The true strength of physical backups emerges when paired with continuous write-ahead log shipping (PostgreSQL WAL or MySQL Binary Logs).

Mimari Akış & Topoloji Şeması (Mermaid)
Mermaid şeması çiziliyor...
  1. 1Continuous Journal Archiving: Every generated WAL file (16MB) or Binlog is immediately pushed to secure remote object storage (AWS S3, MinIO, Ceph).
  2. 2Designating Recovery Targets: In the event of a catastrophic command like DROP TABLE users;, log inspectors determine the precise second prior to execution:
sql
-- postgresql.auto.conf (or recovery.signal) recovery_target_time = '2026-10-27 14:32:09 UTC' recovery_target_action = 'promote'
  1. 1The restore process extracts the latest base backup, applies subsequent incremental blocks, and replays journals exactly to 14:32:09 UTC, achieving zero data loss.

▸4. The 3-2-1-1-0 Enterprise Backup Standard

The resilience gold standard implemented across tier-1 financial and telecommunications architectures:

  • •3 Copies: 1 primary production copy + at least 2 distinct backup datasets.
  • •2 Media Types: Fast local NVMe arrays + remote object stores (S3).
  • •1 Offsite Location: At least one replica physically situated in a distinct geographic availability zone or data center.
  • •1 Immutable Copy: Ransomware-proof storage locked via S3 Object Lock (WORM - Write Once, Read Many).
  • •0 Errors: Backups continuously verified through automated weekly restore drills and block-level checksum audits.
Back to Fundamentals