Build a Production Database Backup and Disaster Recovery System on AWS

Automate PostgreSQL RDS backups with cross-region copying via Lambda, implement point-in-time recovery testing, and build a validated DR runbook pipeline.

Related Concepts & TermsProcess CloudWatch Alarm GitHub Actions Lambda Function Bash Environment

Domains & Technologies

Domains

POSTGRESQLCI-CDAWS-CLOUD-ENGINEERING

Technologies

AWSTERRAFORM

Blueprint Walkthrough

Architecture Overview

This project builds the system that every production database needs but most teams only implement after a disaster. You will set up automated RDS snapshot backups, a Lambda function that copies snapshots cross-region every 6 hours, a weekly automated DR test that restores a snapshot to a test instance and runs validation queries, and a GitHub Actions workflow that proves your recovery works before you ever need it in a real incident.

This is the exact backup architecture that Zerodha uses for their trading database — a failure in ap-south-1 (Mumbai) can be recovered from a snapshot in ap-southeast-1 (Singapore) within minutes.

SQL

    RDS Primary (Mumbai)
    (ap-south-1)
          |
    Automated Snapshots
    (every 5 minutes - transaction logs)
    (daily full snapshots)
          |
          v
    Lambda Function
    (copies snapshot cross-region)
    (every 6 hours)
          |
          v
    Snapshot Copy
    (Singapore - ap-southeast-1)
          |
    EventBridge Schedule
    (weekly DR test)
          |
          v
    Test RDS Instance
    (restored from backup)
          |
          v
    Validation Lambda
    (runs test queries)
    (verifies data integrity)

Problem Solved

Most teams discover their backups do not work during a real disaster. The backup was running but the restore process was never tested. The snapshot exists but in the wrong region. The Lambda function was failing silently for 3 months. By the time the disaster happens, it is too late to find out.

This project builds a system where backup failure is impossible to miss, restore procedure is tested automatically every week, and recovery time is measured and documented. When a real disaster happens, your team runs a proven playbook with a known recovery time — not a frantic improvisation.

Step-by-Step Implementation Guide

Step 1: Deploy RDS with Optimised Backup Configuration

Bash

mkdir rds-backup-dr && cd rds-backup-dr

Create main.tf:

HCL

terraform {
  required_version = ">= 1.7.0"
  backend "s3" {
    bucket = "rds-backup-state-YOUR_ACCOUNT_ID"
    key    = "production/terraform.tfstate"
    region = "ap-south-1"
  }
}
 
provider "aws" {
  alias  = "primary"
  region = "ap-south-1"  # Mumbai — primary region
}
 
provider "aws" {
  alias  = "dr"
  region = "ap-southeast-1"  # Singapore — DR region
}
 
## Primary RDS instance with comprehensive backup configuration
resource "aws_db_instance" "primary" {
  provider              = aws.primary
  identifier            = "production-postgres"
  engine                = "postgres"
  engine_version        = "15.4"
  instance_class        = "db.t3.medium"
  allocated_storage     = 100
  storage_type          = "gp3"
  storage_encrypted     = true  # Always encrypt production data
 
  db_name  = "appdb"
  username = "postgres"
  password = var.db_password  # Never hardcode — use variables
 
  # Backup configuration
  backup_retention_period = 30      # Keep 30 days of automated backups
  backup_window           = "02:00-03:00"  # 2-3 AM UTC (7:30-8:30 AM IST)
  maintenance_window      = "Mon:03:00-Mon:04:00"
  copy_tags_to_snapshot   = true
 
  # Point-in-time recovery — logs every 5 minutes
  # This means you can restore to any 5-minute window in the last 30 days
  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]
 
  # Protection against accidental deletion
  deletion_protection = true
  skip_final_snapshot = false
  final_snapshot_identifier = "production-postgres-final-snapshot"
 
  # Performance insights for query analysis
  performance_insights_enabled          = true
  performance_insights_retention_period = 7  # days
 
  # Enhanced monitoring every 60 seconds
  monitoring_interval = 60
  monitoring_role_arn = aws_iam_role.rds_monitoring.arn
 
  tags = {
    Environment = "production"
    BackupLevel = "critical"
    ManagedBy   = "terraform"
  }
 
  lifecycle {
    prevent_destroy = true  # Block terraform destroy on production database
  }
}
 
## CloudWatch alarm — alert if backup fails
resource "aws_cloudwatch_metric_alarm" "backup_storage" {
  provider            = aws.primary
  alarm_name          = "rds-backup-storage-low"
  comparison_operator = "LessThanThreshold"
  evaluation_periods  = 1
  metric_name         = "FreeStorageSpace"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 10737418240  # Alert when less than 10GB free
 
  dimensions = {
    DBInstanceIdentifier = aws_db_instance.primary.id
  }
 
  alarm_description = "RDS free storage is critically low — backups may fail"
  alarm_actions     = [aws_sns_topic.alerts.arn]
}

Step 2: Lambda Function for Cross-Region Snapshot Copying

Bash

## Create the Lambda function code
mkdir -p lambda/snapshot-copy
cat > lambda/snapshot-copy/handler.py << 'EOF'
import boto3
import json
from datetime import datetime, timezone
 
DR_REGION = 'ap-southeast-1'  # Singapore
PRIMARY_REGION = 'ap-south-1'  # Mumbai
DB_IDENTIFIER = 'production-postgres'
RETENTION_DAYS = 30
 
def lambda_handler(event, context):
    """Copy the latest RDS snapshot to the DR region."""
 
    primary_rds = boto3.client('rds', region_name=PRIMARY_REGION)
    dr_rds = boto3.client('rds', region_name=DR_REGION)
 
    # Get the most recent automated snapshot from primary region
    snapshots = primary_rds.describe_db_snapshots(
        DBInstanceIdentifier=DB_IDENTIFIER,
        SnapshotType='automated',
    )['DBSnapshots']
 
    if not snapshots:
        raise Exception(f"No automated snapshots found for {DB_IDENTIFIER}")
 
    # Sort by creation time and take the most recent
    latest = sorted(
        snapshots,
        key=lambda s: s['SnapshotCreateTime'],
        reverse=True
    )[0]
 
    # Generate a unique name for the DR copy
    timestamp = datetime.now(timezone.utc).strftime('%Y%m%d-%H%M')
    copy_id = f"{DB_IDENTIFIER}-dr-copy-{timestamp}"
 
    # Copy the snapshot to the DR region
    dr_rds.copy_db_snapshot(
        SourceDBSnapshotIdentifier=latest['DBSnapshotArn'],
        TargetDBSnapshotIdentifier=copy_id,
        SourceRegion=PRIMARY_REGION,
        CopyTags=True,
        Tags=[
            {'Key': 'CopiedFrom', 'Value': PRIMARY_REGION},
            {'Key': 'OriginalSnapshot', 'Value': latest['DBSnapshotIdentifier']},
            {'Key': 'CopiedAt', 'Value': timestamp},
        ]
    )
 
    print(f"SUCCESS: Copied {latest['DBSnapshotIdentifier']} to {DR_REGION} as {copy_id}")
 
    # Clean up old DR copies (keep only last RETENTION_DAYS)
    cleanup_old_snapshots(dr_rds, copy_id)
 
    return {'status': 'success', 'copy_id': copy_id}
 
 
def cleanup_old_snapshots(dr_rds, exclude_id):
    """Delete DR snapshots older than retention period."""
    snapshots = dr_rds.describe_db_snapshots(
        SnapshotType='manual',
        Filters=[{'Name': 'db-instance-id', 'Values': [DB_IDENTIFIER]}]
    )['DBSnapshots']
 
    now = datetime.now(timezone.utc)
    for snapshot in snapshots:
        if snapshot['DBSnapshotIdentifier'] == exclude_id:
            continue
        age_days = (now - snapshot['SnapshotCreateTime']).days
        if age_days > RETENTION_DAYS:
            dr_rds.delete_db_snapshot(
                DBSnapshotIdentifier=snapshot['DBSnapshotIdentifier']
            )
            print(f"CLEANUP: Deleted old DR snapshot {snapshot['DBSnapshotIdentifier']} ({age_days} days old)")
EOF
 
## Create the Terraform resource for Lambda
cat >> main.tf << 'EOF'
 
## Package the Lambda function
data "archive_file" "snapshot_copy" {
  type        = "zip"
  source_dir  = "${path.module}/lambda/snapshot-copy"
  output_path = "${path.module}/dist/snapshot-copy.zip"
}
 
resource "aws_lambda_function" "snapshot_copy" {
  provider         = aws.primary
  filename         = data.archive_file.snapshot_copy.output_path
  source_code_hash = data.archive_file.snapshot_copy.output_base64sha256
  function_name    = "rds-snapshot-cross-region-copy"
  role             = aws_iam_role.lambda_snapshot.arn
  handler          = "handler.lambda_handler"
  runtime          = "python3.12"
  timeout          = 300  # 5 minutes — snapshot copy initiation is fast
 
  environment {
    variables = {
      DR_REGION    = "ap-southeast-1"
      DB_IDENTIFIER = "production-postgres"
    }
  }
}
 
## EventBridge rule — run every 6 hours
resource "aws_cloudwatch_event_rule" "snapshot_copy" {
  provider            = aws.primary
  name                = "rds-snapshot-copy-schedule"
  description         = "Copy RDS snapshot to DR region every 6 hours"
  schedule_expression = "rate(6 hours)"
}
 
resource "aws_cloudwatch_event_target" "snapshot_copy" {
  provider  = aws.primary
  rule      = aws_cloudwatch_event_rule.snapshot_copy.name
  target_id = "rds-snapshot-copy"
  arn       = aws_lambda_function.snapshot_copy.arn
}
EOF

Step 3: Automated DR Test Pipeline

Bash

## Create the DR validation Lambda
cat > lambda/dr-test/handler.py << 'EOF'
import boto3
import time
import psycopg2
 
DR_REGION = 'ap-southeast-1'
DB_IDENTIFIER = 'production-postgres'
TEST_INSTANCE_ID = 'dr-test-instance'
 
def lambda_handler(event, context):
    """Restore latest DR snapshot and validate data integrity."""
    dr_rds = boto3.client('rds', region_name=DR_REGION)
 
    # Find the most recent DR snapshot
    snapshots = dr_rds.describe_db_snapshots(
        SnapshotType='manual',
        Filters=[{'Name': 'db-instance-id', 'Values': [DB_IDENTIFIER]}]
    )['DBSnapshots']
 
    latest = sorted(snapshots, key=lambda s: s['SnapshotCreateTime'], reverse=True)[0]
    print(f"Restoring from snapshot: {latest['DBSnapshotIdentifier']}")
    print(f"Snapshot age: {latest['SnapshotCreateTime']}")
 
    # Delete existing test instance if it exists
    try:
        dr_rds.delete_db_instance(
            DBInstanceIdentifier=TEST_INSTANCE_ID,
            SkipFinalSnapshot=True
        )
        waiter = dr_rds.get_waiter('db_instance_deleted')
        waiter.wait(DBInstanceIdentifier=TEST_INSTANCE_ID)
        print("Previous test instance deleted")
    except dr_rds.exceptions.DBInstanceNotFoundFault:
        pass  # No existing test instance — that is fine
 
    # Restore the snapshot to a test instance
    dr_rds.restore_db_instance_from_db_snapshot(
        DBInstanceIdentifier=TEST_INSTANCE_ID,
        DBSnapshotIdentifier=latest['DBSnapshotIdentifier'],
        DBInstanceClass='db.t3.micro',  # Small instance for testing
        MultiAZ=False,
        PubliclyAccessible=False,
        AutoMinorVersionUpgrade=False,
    )
 
    # Wait for the instance to be available (typically 5-15 minutes)
    print("Waiting for test instance to become available...")
    start_time = time.time()
    waiter = dr_rds.get_waiter('db_instance_available')
    waiter.wait(
        DBInstanceIdentifier=TEST_INSTANCE_ID,
        WaiterConfig={'Delay': 30, 'MaxAttempts': 40}
    )
 
    recovery_time_minutes = (time.time() - start_time) / 60
    print(f"Instance available in {recovery_time_minutes:.1f} minutes")
 
    # Get connection details
    instance = dr_rds.describe_db_instances(
        DBInstanceIdentifier=TEST_INSTANCE_ID
    )['DBInstances'][0]
    endpoint = instance['Endpoint']['Address']
 
    # Run validation queries
    results = run_validation_queries(endpoint)
 
    # Publish metrics to CloudWatch
    cloudwatch = boto3.client('cloudwatch', region_name=DR_REGION)
    cloudwatch.put_metric_data(
        Namespace='DisasterRecovery',
        MetricData=[
            {
                'MetricName': 'RecoveryTimeMinutes',
                'Value': recovery_time_minutes,
                'Unit': 'Count'
            },
            {
                'MetricName': 'ValidationPassed',
                'Value': 1 if results['passed'] else 0,
                'Unit': 'Count'
            }
        ]
    )
 
    # Clean up the test instance
    dr_rds.delete_db_instance(
        DBInstanceIdentifier=TEST_INSTANCE_ID,
        SkipFinalSnapshot=True
    )
 
    print(f"DR Test Results: {results}")
    return results
 
 
def run_validation_queries(endpoint):
    """Run data integrity checks on the restored database."""
    try:
        conn = psycopg2.connect(
            host=endpoint,
            database='appdb',
            user='postgres',
            password=DR_TEST_PASSWORD,
            connect_timeout=30
        )
        cursor = conn.cursor()
 
        checks = {}
 
        # Check 1: Can we connect and query
        cursor.execute('SELECT COUNT(*) FROM users')
        checks['user_count'] = cursor.fetchone()[0]
 
        # Check 2: Recent records exist (not a stale backup)
        cursor.execute("""
            SELECT COUNT(*) FROM orders
            WHERE created_at > NOW() - INTERVAL '24 hours'
        """)
        checks['recent_orders'] = cursor.fetchone()[0]
 
        # Check 3: Foreign key integrity
        cursor.execute("""
            SELECT COUNT(*) FROM orders o
            LEFT JOIN users u ON o.user_id = u.id
            WHERE u.id IS NULL
        """)
        orphaned_orders = cursor.fetchone()[0]
        checks['orphaned_orders'] = orphaned_orders
 
        conn.close()
 
        return {
            'passed': orphaned_orders == 0 and checks['user_count'] > 0,
            'checks': checks
        }
    except Exception as e:
        return {'passed': False, 'error': str(e)}
EOF

Step 4: Weekly DR Test via GitHub Actions

Create .github/workflows/dr-test.yml:

YAML

name: Weekly Disaster Recovery Test
 
on:
  schedule:
    - cron: '0 1 * * 0'  # Every Sunday at 1 AM UTC (6:30 AM IST)
  workflow_dispatch:  # Allow manual trigger from GitHub UI
 
jobs:
  dr-test:
    runs-on: ubuntu-latest
    timeout-minutes: 60
 
    steps:
    - name: Configure AWS credentials
      uses: aws-actions/configure-aws-credentials@v4
      with:
        role-to-assume: arn:aws:iam::ACCOUNT_ID:role/dr-test-role
        aws-region: ap-southeast-1
 
    - name: Trigger DR test Lambda
      id: dr-test
      run: |
        RESULT=$(aws lambda invoke \
          --function-name dr-validation-test \
          --region ap-southeast-1 \
          --payload '{}' \
          --cli-binary-format raw-in-base64-out \
          response.json)
        cat response.json
        PASSED=$(cat response.json | python3 -c "import sys,json; print(json.load(sys.stdin).get('passed', False))")
        echo "passed=$PASSED" >> $GITHUB_OUTPUT
    - name: Fail pipeline if DR test failed
      if: steps.dr-test.outputs.passed != 'True'
      run: |
        echo "DR TEST FAILED — backup restore or validation did not pass"
        echo "Check CloudWatch logs for details"
        exit 1
    - name: Post result to Slack
      if: always()
      uses: 8398a7/action-slack@v3
      with:
        status: ${{ job.status }}
        text: |
          DR Test Result: ${{ job.status }}
          Region: ap-southeast-1 (Singapore)
          Recovery validation: ${{ steps.dr-test.outputs.passed }}
      env:
        SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}

Validation & Testing

Bash

## 1. Verify automated backups are configured
aws rds describe-db-instances \
  --db-instance-identifier production-postgres \
  --query 'DBInstances[0].[BackupRetentionPeriod,PreferredBackupWindow,StorageEncrypted]'
## Expected: [30, "02:00-03:00", true]
 
## 2. Verify Lambda snapshot copy is working
aws lambda invoke \
  --function-name rds-snapshot-cross-region-copy \
  --region ap-south-1 \
  output.json
cat output.json
## Expected: {"status": "success", "copy_id": "production-postgres-dr-copy-YYYYMMDD-HHMM"}
 
## 3. Verify DR copy arrived in Singapore
aws rds describe-db-snapshots \
  --region ap-southeast-1 \
  --snapshot-type manual \
  --query 'DBSnapshots[*].[DBSnapshotIdentifier,SnapshotCreateTime,Status]' \
  --output table
## Expected: Recent snapshots with status=available
 
## 4. Manually trigger the DR test
aws lambda invoke \
  --function-name dr-validation-test \
  --region ap-southeast-1 \
  --payload '{}' \
  --cli-binary-format raw-in-base64-out \
  dr-result.json
cat dr-result.json
## Expected: {"passed": true, "checks": {"user_count": N, "recent_orders": N, "orphaned_orders": 0}}
 
## 5. Check CloudWatch metrics for recovery time trends
aws cloudwatch get-metric-statistics \
  --namespace DisasterRecovery \
  --metric-name RecoveryTimeMinutes \
  --statistics Average \
  --period 604800 \
  --start-time 2024-01-01T00:00:00Z \
  --end-time 2024-12-31T00:00:00Z \
  --region ap-southeast-1
## Expected: Average recovery time, trending over past DR tests
 
## 6. Test point-in-time recovery (PITR)
## Restore to a specific timestamp (e.g., before a bad migration)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier production-postgres \
  --target-db-instance-identifier pitr-test-instance \
  --restore-time 2024-01-15T10:30:00Z \
  --db-instance-class db.t3.micro \
  --region ap-south-1
## This proves you can recover to any 5-minute window in the last 30 days
echo "Disaster recovery system validated"

Videos & Guides

Video Guide

AWS RDS Backup and Disaster Recovery — Complete Guide

Complete AWS RDS backup strategy tutorial covering automated snapshots, cross-region copying, point-in-time recovery, and building a tested disaster recovery runbook.

View Reference

DOCUMENTATION

AWS Lambda with EventBridge — Scheduled Jobs

Official AWS documentation for scheduling Lambda functions with EventBridge — building automated database backup and cross-region replication workflows.

View Reference

DOCUMENTATION

AWS RDS Automated Backups and Snapshots Documentation

Official AWS documentation for RDS automated backups, manual snapshots, backup retention, point-in-time recovery, and cross-region snapshot copying.

View Reference