Phase 5: Platform Engineering

Backup & recovery: point-in-time restore, snapshots & disaster recovery

Advanced ~4 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you’re building an incredible LEGO castle. You’ve spent hours carefully choosing bricks, creating clever rooms, and setting up all your minifigure knights and dragons. This castle is super important. But what if something goes wrong? What if your little brother accidentally bumps the table, or you realize you made a huge mistake a few minutes ago? You wouldn't want to lose all your hard work! That’s why we have smart ways to save your castle.

One way to save your castle is like taking a super detailed photograph of it. We call these "snapshots." When you take a snapshot, you’re making a perfect copy of your entire castle exactly as it looks at that second. If your castle ever crashes down, you can quickly rebuild the whole thing exactly the way it was when you took the picture. It’s a fast way to get back to a recent, good version, especially if the whole thing got messed up. But remember, it’s just a picture. If you built three new towers after you took the photo, and then the castle crashes, those new towers won't be in the picture, so they'll be gone when you rebuild.

Now, there’s an even cleverer way to save your work, called "Point-in-Time Restore." This is like having a big photo, plus a special notebook that records every single brick you add or move after that photo was taken. "First, I added a red brick here. Then, I moved a knight to the tower." If your castle crashes, you can start with your big photo, then carefully follow every single step you wrote, one by one. This means you can rebuild your castle up to any exact second you want – even right before you accidentally knocked over that tower at 3:15 PM! You can replay all your building steps and stop right before the mistake, so you don't lose recent cool stuff.

So, snapshots are like quick whole-castle photos for big resets, and Point-in-Time Restore is like having a super detailed building diary for fixing tiny mistakes or going back to an exact moment. Both are important tools that help engineers protect your amazing LEGO castles from any disaster, big or small. This means when you build incredible online games or apps, you can be confident that even if something breaks, all the progress and information will be safe and sound.

Database reliability hinges on robust backup and recovery strategies, crucial for handling everything from minor data corruption to catastrophic system failures. At a fundamental level, snapshots provide a point-in-time copy of your entire storage volume or disk. They are fast to create and restore, making them excellent for quick rollbacks of an entire database instance, creating development environments, or as a component of rapid disaster recovery. However, snapshots operate at the block level and lack the granularity to restore individual transactions or specific data states within the snapshot window. You can only restore to the exact moment the snapshot was taken, meaning any transactions after that point are lost.

For fine-grained data recovery, Point-in-Time Restore (PITR) is indispensable. Unlike snapshots, PITR doesn't just capture a disk image; it combines a full base backup with a continuous archive of transaction logs (e.g., PostgreSQL's WAL files, MySQL's binary logs). This continuous logging allows you to replay transactions up to any specific second between your last full backup and the present (or just before a failure). This capability is critical for recovering from logical data corruption, accidental data deletions, or targeted cyberattacks, enabling you to rewind the database state to precisely the moment before the incident occurred, minimizing data loss.

Both snapshots and PITR are vital components within a broader Disaster Recovery (DR) strategy. DR is about ensuring business continuity after major outages, typically involving replicating data and applications to geographically distinct locations. Snapshots can be utilized for fast, localized instance recovery or for quickly seeding new replicas. PITR ensures that even across a failover to a DR site, you can achieve a consistent and highly granular recovery point, meeting strict Recovery Point Objective (RPO) targets. A well-architected DR plan integrates these mechanisms, defining clear Recovery Time Objective (RTO) and RPO goals, and includes regular testing to validate the entire recovery workflow, ensuring resilience against a multitude of failure scenarios.

Key Takeaways

  • Snapshots offer fast, volume-level recovery for quick rollbacks or cloning.
  • PITR provides granular, second-by-second data recovery using base backups and continuous transaction logs.
  • Disaster Recovery is a comprehensive strategy for business continuity, using both snapshots and PITR to meet RTO/RPO.
  • Choose between snapshots and PITR based on the required recovery granularity and speed.
  • Regularly test your backup and recovery procedures to ensure operational readiness.

Code Example

bash
# PostgreSQL example demonstrating core components for PITR

# 1. Archive Command in postgresql.conf (on the primary/source DB)
# This tells PostgreSQL where to save WAL files after they are filled.
# Ensure the archive_mode is 'on' or 'always'.
# archive_mode = always
# archive_command = 'cp %p /path/to/wal/archive/%f'

# 2. Base Backup (e.g., using pg_basebackup)
# Take a full backup of the database cluster. This is your starting point for recovery.
# pg_basebackup -h <host> -p <port> -U <user> -D /path/to/backup/data -Ft -z -P -R

# 3. Restore Command (used during recovery on a new or failed instance)
# This tells PostgreSQL how to retrieve WAL files during recovery.
# Placed in a 'recovery.signal' file (for PG 12+) or 'recovery.conf' (for older PG versions).
# In postgresql.auto.conf (or recovery.conf):
# restore_command = 'cp /path/to/wal/archive/%f %p'

# To perform a PITR to a specific timestamp (e.g., just before an error):
# target_time = '2023-10-27 10:30:00 UTC'

How this code works

This code illustrates the fundamental steps for setting up Point-In-Time Recovery (PITR) in PostgreSQL, enabling a database to be restored to any specific moment in its history – a critical capability for disaster recovery and correcting errors.

The process begins by configuring the primary database with archive_mode = always and an archive_command. This ensures that PostgreSQL continuously copies its "Write-Ahead Log" (WAL) files, which record every database change, to a separate, safe archive location. Next, a pg_basebackup command takes a full snapshot of the entire database cluster. This base backup provides the essential starting point for any recovery; crucially, without a base backup, the archived WAL files are useless on their own for a full restore. Finally, during a recovery operation, a restore_command is used to retrieve those archived WAL files, applying them sequentially to the base backup. By specifying a target_time, PostgreSQL can then precisely replay transactions up to that exact moment, effectively rewinding the database to a desired state.

Related Articles

Hand-picked reads to go deeper on database reliability.

Back to Site Reliability Engineer Roadmap