Recovery

Implement comprehensive disaster recovery, point-in-time recovery (PITR), and automated failover strategies for production database systems with automated backup verification and recovery testing.

When to Use This Command

Use /recovery when you need to:

• Set up disaster recovery infrastructure for production databases
• Implement point-in-time recovery (PITR) capabilities
• Automate backup validation and recovery testing
• Design multi-region failover strategies
• Recover from data corruption or accidental deletions
• Meet compliance requirements for backup retention and recovery time objectives (RTO)

DON'T use this when:

• Only need basic database backups (use backup automator instead)
• Working with development databases without recovery requirements
• Database system doesn't support WAL/binary log replication
• Compliance doesn't require tested recovery procedures

Design Decisions

This command implements comprehensive disaster recovery with PITR because:

• Point-in-time recovery prevents data loss from user errors or corruption
• Automated failover ensures minimal downtime (RTO < 5 minutes)
• Regular recovery testing validates backup integrity before disasters
• Multi-region replication provides geographic redundancy
• WAL archiving enables recovery to any point in last 30 days

Alternative considered: Snapshot-only backups

• Simpler to implement and restore
• No point-in-time recovery capability
• Recovery point objective (RPO) limited to snapshot frequency
• Recommended only for non-critical databases

Alternative considered: Manual recovery procedures

• No automation or testing
• Prone to human error during incidents
• Longer recovery times (RTO hours vs minutes)
• Recommended only for development environments

Prerequisites

Before running this command:

1. Database with WAL/binary logging enabled
2. Object storage for backup retention (S3, GCS, Azure Blob)
3. Monitoring infrastructure for backup validation
4. Understanding of RTO (Recovery Time Objective) and RPO (Recovery Point Objective) requirements
5. Separate recovery environment for testing

Implementation Process

Step 1: Configure WAL Archiving and Continuous Backup

Enable write-ahead logging (WAL) archiving for point-in-time recovery capabilities.

Step 2: Implement Automated Base Backup System

Set up scheduled base backups with compression and encryption to object storage.

Step 3: Design Failover and High Availability Architecture

Configure streaming replication with automated failover for zero-downtime recovery.

Step 4: Build Recovery Testing Framework

Automate recovery validation by restoring backups to test environments regularly.

Step 5: Document and Drill Recovery Procedures

Create runbooks and conduct disaster recovery drills quarterly.

Output Format

The command generates:

• config/recovery.conf - PostgreSQL recovery configuration
• scripts/pitr-restore.sh - Point-in-time recovery automation script
• monitoring/backup-validator.py - Automated backup verification
• failover/replication-monitor.py - Streaming replication health monitoring
• docs/recovery-runbook.md - Step-by-step recovery procedures

Code Examples

Example 1: PostgreSQL PITR with WAL Archiving

# postgresql.conf - Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-db-backups/wal-archive/%f --region us-east-1'
archive_timeout = 300  # Force segment switch every 5 minutes
max_wal_senders = 10
wal_keep_size = 1GB

# Continuous archiving with monitoring
restore_command = 'aws s3 cp s3://my-db-backups/wal-archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'

#!/bin/bash
# scripts/pitr-restore.sh - Point-in-Time Recovery Script

set -euo pipefail

# Configuration
BACKUP_BUCKET="s3://my-db-backups"
PGDATA="/var/lib/postgresql/14/main"
TARGET_TIME="${1:-latest}"  # Format: '2024-10-15 14:30:00 UTC'
RECOVERY_TARGET="${2:-immediate}"  # immediate, time, xid, name

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

log() {
…

# monitoring/backup-validator.py - Automated Backup Verification
import subprocess
import boto3
import psycopg2
import logging
import json
from datetime import datetime, timedelta
from typing import Dict, List, Optional
from dataclasses import dataclass, asdict

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@dataclass
class BackupValidationResult:
    """Results from backup validation."""
    backup_name: str
    backup_date: datetime
…