Software and Versions Used
This explanation refers to:
- Concepts: Distribution-independent backup principles
- Storage media: HDD, SSD, NAS, cloud storage
- Encryption: gpg 2.2+, age 1.0+ (concepts, not complete tutorials)
- Context: Self-hosting infrastructures, digital sovereignty
Hard drive failures destroy databases within seconds. Ransomware encrypts entire servers, including local backups. Fire or water damage destroys hardware and all connected storage media at the same time. A single backup stored on the same drive as the original data protects against none of these scenarios.
The 3-2-1 rule defines the minimum backup requirements for serious data protection: three copies of your data on at least two different types of storage media, with one copy stored at a geographically separate location. This strategy survives hardware failures, local disasters, and most attack scenarios.
The 3-2-1 Rule in Practice
Three Copies Means Primary Data Plus Two Backups
A production database runs on a server SSD (primary data). The first backup is stored on the same machine in a separate directory for fast recovery from software corruption or accidental deletion. The second backup is written to an external HDD or NAS to protect against SSD failure. The third backup is stored on a remote server or in cloud storage to survive fire, theft, or ransomware.
Example directory structure:
/var/lib/postgresql/data/ Primary database on SSD
/backup/local/daily/ First copy: Local SSD
/mnt/nas/backups/postgresql/ Second copy: NAS or external HDD
user@remote:/backups/server1/ Third copy: External remote server
This structure supports tiered recovery: the local backup for a five-minute recovery, the NAS backup for hardware replacement scenarios, and the external backup for complete site destruction.
Two Types of Storage Media Prevent Correlated Failures
SSDs and HDDs fail for different reasons. SSD flash cells wear out after repeated write cycles, resulting in predictable failure after three to five years of intensive use. HDDs suffer mechanical failures caused by head crashes or motor failures, leading to unpredictable failures regardless of workload. An SSD failure does not correlate with HDD health.
NAS systems with RAID eliminate individual drive failures but do not protect against controller failures, filesystem corruption, or ransomware encryption. Cloud storage completely survives local hardware failures but requires trust in third-party providers and a permanent Internet connection.
One External Copy Survives Local Disasters
Fire, flooding, and burglary affect every device at the same physical location. A server rack containing the primary SSD, backup HDD, and NAS loses all data simultaneously in the event of a fire or water damage. Geographic separation through remote servers or cloud storage ensures data survival even if the entire location is lost.
The minimum distance for an effective off-site backup is a different building, ideally a different city. Home server backups stored on an office server meet this requirement. VPS providers in different data centers or cloud storage in different regions provide maximum physical separation.
Storage Media Characteristics and Selection
SSD Performance for Fast Recovery
SSDs achieve sequential read speeds of 500–3500 MB/s depending on the interface (SATA vs. NVMe). A 100 GB PostgreSQL database can be read completely from an NVMe SSD in 30–60 seconds. This speed makes SSDs ideal for primary backups intended for rapid recovery.
SSD cost per gigabyte: EUR 0.08–0.15 for consumer-grade models and EUR 0.20–0.40 for enterprise models with higher endurance. Backup workloads with infrequent write operations work perfectly well on consumer SSDs. An endurance rating of 600 TBW (Terabytes Written) is sufficient for more than ten years of daily 100 GB backups.
HDD Capacity for Long-Term Archives
HDDs provide capacities of 12–20 TB at EUR 0.015–0.025 per gigabyte—roughly four to eight times less expensive than SSDs. Sequential read speeds of 100–200 MB/s are sufficient for recoveries that are not time-critical. Restoring 100 GB from an HDD takes approximately 8–15 minutes instead of less than one minute from an SSD.
Mechanical hard drives tolerate 300–600 operating hours per year without reliability problems. Continuously running NAS HDDs typically achieve a mean operating life of five to eight years before failure. External HDDs that are powered on only for backups can last more than ten years because of their minimal mechanical wear.
NAS Systems for Centralized Backup Management
Network Attached Storage collects backups from multiple servers in a central storage location. RAID configurations (RAID1, RAID5, RAID6) distribute data across multiple drives to provide fault tolerance. RAID5 survives the simultaneous failure of one HDD, while RAID6 survives the simultaneous failure of two.
NAS network performance: 1 GbE reaches 110–120 MB/s, while 10 GbE reaches 1100–1200 MB/s. Transferring a 100 GB backup requires about 15 minutes over 1 GbE and about 90 seconds over 10 GbE. Multi-Gigabit switches (2.5 GbE, 5 GbE) provide a middle ground for home lab budgets.
Cloud Storage for Geographic Redundancy
S3-compatible storage services (AWS, Backblaze B2, Wasabi) offer usage-based pricing without hardware investment. Backblaze B2 costs USD 0.005 per GB of storage plus USD 0.01 per GB downloaded. A 500 GB backup archive costs USD 2.50 per month, with an additional USD 5 for a complete restore.
Upload bandwidth limits the practicality of cloud backups: a 100 Mbit/s upload requires approximately 2.5 hours for the initial 100 GB backup, followed only by deltas. Typical German DSL connections provide 10–50 Mbit/s upload bandwidth, requiring approximately 5–12 hours for 100 GB. Dedicated server uplinks with 1 Gbit/s complete the same backup in about 15 minutes.
Retention Policies for Balancing Storage Usage and Recovery Options
Daily-Weekly-Monthly Scheme
Daily backups for the last seven days enable recovery from problems discovered within one week. Weekly backups for the last four weeks protect against monthly database corruption. Monthly backups retained for twelve months support year-over-year comparisons and compliance requirements.
Retention cleanup:
/backup/daily - Delete files older than 7 days
/backup/weekly - Delete files older than 28 days
/backup/monthly - Delete files older than 365 days
This policy balances storage consumption against recovery flexibility. A 100 GB database with daily full backups requires: 7 daily (700 GB) + 4 weekly (400 GB) + 12 monthly (1200 GB) = 2.3 TB of total storage for a complete one-year history.
Incremental Backups vs. Full Backups
Full backups copy complete databases during every execution, providing the simplest recovery but requiring the maximum amount of storage. Incremental backups save only the changes since the last full backup, improving storage efficiency but requiring a more complex recovery process consisting of the base backup plus every incremental backup.
PostgreSQL transaction log archiving stores change logs continuously instead of relying exclusively on periodic full backups. One weekly full backup combined with daily transaction log archives enables point-in-time recovery with minimal storage costs. MariaDB binary logs provide identical functionality for MySQL-compatible databases.
Encryption Protects External Backups
Unencrypted backups stored on remote servers or in cloud storage expose sensitive data. Server operators, cloud provider employees, or attackers with access to the storage can directly read database contents. Encrypted backups remain unreadable without the decryption key, even if the storage itself is completely compromised.
gpg (GNU Privacy Guard) encrypts files using public-key cryptography or symmetric passphrases. age (Actually Good Encryption) provides a more modern alternative with a simpler syntax. Both tools generate encrypted data streams suitable for integration into backup pipelines.
Encryption examples:
Database dump → Compression → gpg encryption → backup.sql.gz.gpg
Database dump → Compression → age encryption → backup.sql.gz.age
Key management determines the security level. Symmetric passphrases are simple but require secure passphrase storage. Public-key encryption separates the encryption key (on the backup server) from the decryption key (only on the recovery workstation), providing better operational security.
Compression Reduces Storage Requirements and Transfer Time
Text-based backup formats (SQL dumps, CSV exports) typically compress by 80–95%. A 10 GB PostgreSQL database becomes a 500–2000 MB .gz file depending on the characteristics of the data. Binary formats (PostgreSQL custom format, MariaDB physical backups) compress by 30–70% because their internal data structures are already relatively compact.
gzip (standard compression) achieves 5–20 MB/s single-threaded. xz (better compression) achieves 1–5 MB/s. zstd (a modern compromise) achieves 100–500 MB/s using multiple threads while delivering compression ratios comparable to gzip. The optimal compressor depends on the trade-off between CPU overhead and reduced transfer time.
Already compressed data (videos, images, compressed database formats) gains little benefit from additional compression. Encrypted data cannot be compressed because encryption produces uniformly distributed byte streams without compressible patterns. Compression must occur before encryption to achieve effective storage savings.
Test Disaster Recovery Scenarios Regularly
Untested backups are worthless. Recovery procedures can fail because of incorrect permissions, missing dependencies, or incompatible software versions. Regular recovery tests verify backup integrity and document working recovery workflows.
The minimum testing frequency is one complete disaster recovery simulation per quarter on separate hardware. Test recovery from all three backup locations (local, NAS, and external) to cover different failure modes. Document the recovery time to support realistic recovery time objective planning.
Partial recovery tests validate specific recovery scenarios: restore an individual table from a PostgreSQL dump, reconstruct a specific point in time from a transaction log archive, or replace a corrupted database page from a physical backup. These tests identify gaps in backup coverage before real disasters occur.
Automation and Testing: The next article covers scheduled execution, rotation mechanisms, and verification workflows.
