Build a Production Database Backup and Disaster Recovery System on AWS

Replicate encrypted RDS PostgreSQL backups to a second region with Terraform, rehearse point-in-time recovery, and auto-test restores every week.

Architecture Overview & Problem Statement

This project builds a backup and disaster recovery system for an encrypted Amazon RDS PostgreSQL database with Terraform. Automated backups are replicated to a second region, you rehearse point-in-time recovery by hand, and a scheduled CodeBuild job restores the replicated backup into the second region every week, runs validation queries, records how long the restore took and emails you if anything fails. You finish with measured recovery numbers and a runbook, not just a hope that backups work.

The problem this solves

Most teams find out their backups do not work during a real incident. The backup job ran, but nobody ever restored it. The copy exists, but in the wrong region, or encrypted with a key the recovery region cannot use. A scheduled task has been failing quietly for months.

Picture a payments company like Razorpay or Zerodha with a primary database in Mumbai. Two different disasters matter. A bad deployment deletes rows, which needs point-in-time recovery (PITR): restoring to the minute before the mistake. A regional outage needs cross-region recovery: bringing the data back in another region. This project covers both and proves each one works.

Two terms to keep straight. RPO (recovery point objective) is how much data you can afford to lose, measured as the age of the newest restorable point. RTO (recovery time objective) is how long recovery takes. You will measure both for your own setup instead of assuming a number.

What you will build

flowchart LR subgraph M["Mumbai, ap-south-1"] DB["RDS PostgreSQL"] end subgraph S["Singapore, ap-southeast-1"] BK["Replicated backups"] CB["CodeBuild restore test"] T["Temporary test DB"] CW["CloudWatch metrics"] SNS["Alert email"] end DB -->|replicates| BK BK --> T CB -->|restores and queries| T CB --> CW CW --> SNS classDef aws fill:#F97316,color:#FFFFFF classDef dr fill:#326CE5,color:#FFFFFF classDef alert fill:#EF4444,color:#FFFFFF class DB aws class BK,CB,T dr class CW,SNS alert

Piece	What it does
Encrypted RDS PostgreSQL	The production-style primary database with 14 days of automated backups
Automated backup replication	Copies snapshots and transaction logs to Singapore, so PITR works there too
KMS keys in both regions	Encryption keys are regional, so each region needs its own
Replicated secret	The test job reads the database password from Singapore, not Mumbai
DR network	A pre-built VPC in Singapore, because you cannot create networking in the middle of an outage
CodeBuild restore test	Restores, validates, measures and deletes the test database every week
Alerts	Emails for backup failures, low storage, failed tests and tests that stop running

What you will have at the end

A Terraform repository that builds the whole system and tears it down
A PITR drill with your measured restore time
A weekly automated restore test with a RestoreSeconds metric you can chart
A RUNBOOK.md for a regional outage, with your measured RTO and RPO filled in

What this build leaves out

This protects the data layer only. A full recovery plan also needs the application tier, DNS failover and a decision process for when to fail over. This lab uses a single-AZ database and the default VPC in Mumbai to stay cheap, so production would add Multi-AZ and private subnets.

Prerequisites and cost

You need an AWS account with administrator access, Terraform 1.10 or later, AWS CLI v2 and the psql client (postgresql-client). Billable pieces are a small RDS instance, replicated backup storage, a NAT gateway, two KMS keys, a secret, and CodeBuild and test-instance minutes during each test. Budget a few dollars for a weekend and run the destroy steps at the end.

Milestone 1: Set Up Terraform, Keys and the Database Secret

This milestone creates the project, the two regional providers, an encryption key in each region and a generated database password that is replicated to Singapore.

KMS keys never leave their region. A backup encrypted with the Mumbai key cannot be read in Singapore, so replication needs a second key in Singapore. The password is generated by Terraform and stored in Secrets Manager with a replica in Singapore, so the recovery job can read it even if Mumbai is unreachable.

Bash

mkdir rds-backup-dr && cd rds-backup-dr && git init
printf ".terraform/\n*.tfstate\n*.tfstate.*\n*.tfplan\nterraform.tfvars\n" > .gitignore

Create versions.tf:

HCL

terraform {
  required_version = ">= 1.10"
 
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 6.0"
    }
    random = {
      source  = "hashicorp/random"
      version = "~> 3.6"
    }
  }
}
 
provider "aws" {
  alias  = "primary"
  region = var.primary_region
 
  default_tags {
    tags = { Project = "rds-dr", ManagedBy = "terraform" }
  }
}
 
provider "aws" {
  alias  = "dr"
  region = var.dr_region
 
  default_tags {
    tags = { Project = "rds-dr", ManagedBy = "terraform" }
  }
}

Create variables.tf:

HCL

variable "primary_region" {
  type    = string
  default = "ap-south-1"
}
 
variable "dr_region" {
  type    = string
  default = "ap-southeast-1"
}
 
variable "db_identifier" {
  type    = string
  default = "production-postgres"
}
 
variable "db_instance_class" {
  type    = string
  default = "db.t4g.micro"
}
 
variable "backup_retention_days" {
  description = "Must be 1 or more for backups, PITR and replication to exist"
  type        = number
  default     = 14
}
 
variable "admin_cidr" {
  description = "Your IP in CIDR form. Only this address can reach the lab database"
  type        = string
}
 
variable "alert_email" {
  type = string
}
 
variable "protect_database" {
  description = "Deletion protection and a final snapshot. Set false before destroying the lab"
  type        = bool
  default     = true
}

Create security.tf:

HCL

resource "aws_kms_key" "primary" {
  provider                = aws.primary
  description             = "RDS encryption key, primary region"
  enable_key_rotation     = true
  deletion_window_in_days = 7
}
 
resource "aws_kms_key" "dr" {
  provider                = aws.dr
  description             = "RDS encryption key, DR region"
  enable_key_rotation     = true
  deletion_window_in_days = 7
}
 
resource "random_password" "db" {
  length  = 24
  special = false # RDS rejects some special characters and shells dislike others
}
 
resource "aws_secretsmanager_secret" "db" {
  provider                = aws.primary
  name                    = "${var.db_identifier}/master-password"
  kms_key_id              = aws_kms_key.primary.arn
  recovery_window_in_days = 0 # lab setting: delete immediately on destroy
 
  replica {
    region     = var.dr_region
    kms_key_id = aws_kms_key.dr.arn
  }
}
 
resource "aws_secretsmanager_secret_version" "db" {
  provider      = aws.primary
  secret_id     = aws_secretsmanager_secret.db.id
  secret_string = random_password.db.result
}

Provide your values and create these pieces:

Bash

cat > terraform.tfvars <<EOF
admin_cidr  = "$(curl -s https://checkip.amazonaws.com | tr -d '\n')/32"
alert_email = "you@example.com"
EOF
 
terraform init
terraform fmt && terraform validate
terraform apply
# Expected: 2 KMS keys, 1 secret with a replica, 1 secret version

Security
The generated password is stored in Terraform state. Keep state private, and in a team setting use a remote encrypted backend like the one in the EKS project.

Common Mistake
Reusing one KMS key ARN for both regions. KMS keys are regional, so the replication or restore fails with a key-not-found error. Create one key per region.

Milestone 2: Deploy the Primary Database and Seed It

This milestone creates an encrypted PostgreSQL instance with automated backups and loads sample data you can check after every restore.

The setting that makes everything else possible is backup_retention_period. Above zero, RDS takes a daily snapshot and uploads transaction logs about every five minutes, which is what allows PITR. At zero there are no automated backups, no PITR and nothing to replicate.

Create database.tf:

HCL

data "aws_vpc" "default" {
  provider = aws.primary
  default  = true
}
 
resource "aws_security_group" "db" {
  provider    = aws.primary
  name        = "${var.db_identifier}-sg"
  description = "PostgreSQL from the admin IP only"
  vpc_id      = data.aws_vpc.default.id
 
  ingress {
    description = "Admin access for seeding and drills"
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = [var.admin_cidr]
  }
}
 
resource "aws_db_instance" "primary" {
  provider   = aws.primary
  identifier = var.db_identifier
 
  engine         = "postgres"
  engine_version = "16"
  instance_class = var.db_instance_class
 
  allocated_storage = 20
  storage_type      = "gp3"
  storage_encrypted = true
  kms_key_id        = aws_kms_key.primary.arn
 
  db_name  = "appdb"
  username = "postgres"
  password = random_password.db.result
 
  vpc_security_group_ids = [aws_security_group.db.id]
  publicly_accessible    = true # lab only, so you can seed from your laptop
 
  # Backups and point-in-time recovery
  backup_retention_period = var.backup_retention_days
  backup_window           = "20:30-21:30" # 02:00 to 03:00 IST
  copy_tags_to_snapshot   = true
 
  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]
  auto_minor_version_upgrade      = true
  apply_immediately               = true
 
  deletion_protection       = var.protect_database
  skip_final_snapshot       = !var.protect_database
  final_snapshot_identifier = var.protect_database ? "${var.db_identifier}-final" : null
 
  lifecycle {
    ignore_changes = [engine_version] # minor upgrades happen automatically
  }
}

Create outputs.tf:

HCL

output "db_endpoint" {
  value = aws_db_instance.primary.address
}
 
output "db_sg_id" {
  value = aws_security_group.db.id
}
 
output "secret_name" {
  value = aws_secretsmanager_secret.db.name
}

Bash

terraform apply
# Takes about 10 minutes. Expected: the instance is created and available

Create seed.sql:

SQL

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT now()
);
 
CREATE TABLE orders (
  id serial PRIMARY KEY,
  user_id int NOT NULL REFERENCES users(id),
  amount_cents int NOT NULL,
  created_at timestamptz DEFAULT now()
);
 
INSERT INTO users (email)
SELECT 'user' || g || '@example.com' FROM generate_series(1, 1000) g;
 
INSERT INTO orders (user_id, amount_cents)
SELECT (random() * 999 + 1)::int, (random() * 50000)::int FROM generate_series(1, 5000);

Load it, reading the password from Secrets Manager so it never appears in a file:

Bash

export AWS_REGION=ap-south-1
HOST=$(terraform output -raw db_endpoint)
export PGPASSWORD=$(aws secretsmanager get-secret-value \
  --secret-id production-postgres/master-password \
  --query SecretString --output text)
 
psql "host=$HOST dbname=appdb user=postgres sslmode=require" -f seed.sql
psql "host=$HOST dbname=appdb user=postgres sslmode=require" -tA \
  -c "SELECT count(*) FROM users" -c "SELECT count(*) FROM orders"
# Expected: 1000 and 5000

Security
publicly_accessible = true with a single-IP security group is acceptable for a short lab only. Real production databases sit in private subnets with no public endpoint.

Common Mistake
Setting backup_retention_period = 0 to save money. RDS then keeps no automated backups, PITR is impossible and cross-region replication cannot start. Keep it at 1 or more.

Milestone 3: Replicate Backups to the DR Region

This milestone copies the database's automated backups, including transaction logs, to Singapore, encrypted with the Singapore key.

Automated backup replication is an RDS feature that keeps a second copy of your snapshots and logs in another region. Because the logs are copied too, you can do point-in-time recovery in the DR region, not only restore the last snapshot. It also removes the need for a custom snapshot-copy script, which is one less thing to fail silently.

Create replication.tf:

HCL

resource "aws_db_instance_automated_backups_replication" "dr" {
  provider               = aws.dr
  source_db_instance_arn = aws_db_instance.primary.arn
  kms_key_id             = aws_kms_key.dr.arn
  retention_period       = var.backup_retention_days
}

Bash

terraform apply
 
aws rds describe-db-instance-automated-backups --region ap-southeast-1 \
  --query 'DBInstanceAutomatedBackups[*].[DBInstanceIdentifier,Status,RestoreWindow.EarliestTime,RestoreWindow.LatestTime]' \
  --output table
# Expected: production-postgres with Status replicating

The first restore point in Singapore can take from minutes to a few hours to appear, because replication starts from the next automated backup. An empty restore window early on is normal.

Tip
If Terraform reports an error starting replication for an encrypted instance, start it with the CLI instead, which handles the cross-region request for you: aws rds start-db-instance-automated-backups-replication --region ap-southeast-1 --source-db-instance-arn SOURCE_ARN --kms-key-id DR_KEY_ARN --backup-retention-period 14. Then import the result or manage replication outside Terraform.

Common Mistake
Treating the replication resource as proof of recovery. A backup you have never restored is only a belief. The restore test in Milestone 7 is what turns it into evidence.

Milestone 4: Add Alerts for Failures

This milestone makes problems loud: emails for backup failures, low storage and, later, failed restore tests.

You need an SNS topic in each region. EventBridge and CloudWatch can only publish to a topic in their own region, and the restore test lives in Singapore. Each email subscription must be confirmed once from the link AWS sends.

Create alerts.tf:

HCL

resource "aws_sns_topic" "primary" {
  provider = aws.primary
  name     = "rds-dr-alerts"
}
 
resource "aws_sns_topic_subscription" "primary_email" {
  provider  = aws.primary
  topic_arn = aws_sns_topic.primary.arn
  protocol  = "email"
  endpoint  = var.alert_email
}
 
resource "aws_sns_topic" "dr" {
  provider = aws.dr
  name     = "rds-dr-alerts"
}
 
resource "aws_sns_topic_subscription" "dr_email" {
  provider  = aws.dr
  topic_arn = aws_sns_topic.dr.arn
  protocol  = "email"
  endpoint  = var.alert_email
}
 
# Backup failures, low storage and recovery events on the primary database
resource "aws_db_event_subscription" "primary" {
  provider         = aws.primary
  name             = "${var.db_identifier}-events"
  sns_topic        = aws_sns_topic.primary.arn
  source_type      = "db-instance"
  source_ids       = [aws_db_instance.primary.identifier]
  event_categories = ["backup", "failure", "low storage", "recovery"]
}
 
resource "aws_cloudwatch_metric_alarm" "low_storage" {
  provider            = aws.primary
  alarm_name          = "${var.db_identifier}-low-storage"
  alarm_description   = "Free storage is below 2 GB, backups and writes may fail"
  namespace           = "AWS/RDS"
  metric_name         = "FreeStorageSpace"
  statistic           = "Average"
  period              = 300
  evaluation_periods  = 1
  comparison_operator = "LessThanThreshold"
  threshold           = 2147483648
  alarm_actions       = [aws_sns_topic.primary.arn]
 
  dimensions = {
    DBInstanceIdentifier = aws_db_instance.primary.identifier
  }
}

Bash

terraform apply
# Then open both confirmation emails and click Confirm subscription

Common Mistake
Never confirming the SNS subscription emails. The topic exists, the alarms fire, and nobody receives anything. Confirm both subscriptions and check the subscription status shows a real ARN.

Milestone 5: Rehearse Point-in-Time Recovery

This milestone simulates a bad migration and recovers from it, so you have done the most common real recovery before you need it.

A restore never overwrites the running database. RDS always creates a new instance from the chosen moment. The usual pattern is to restore beside production, inspect it, copy the missing rows back, then delete the copy. Switching the application to the restored instance is a last resort, because it changes the endpoint and loses anything written after your chosen time.

Bash

export AWS_REGION=ap-south-1
HOST=$(terraform output -raw db_endpoint)
DB_SG=$(terraform output -raw db_sg_id)
export PGPASSWORD=$(aws secretsmanager get-secret-value \
  --secret-id production-postgres/master-password \
  --query SecretString --output text)
PSQL="psql host=$HOST dbname=appdb user=postgres sslmode=require -tA"
 
# 1. Note a good moment, then wait a minute
GOOD_TIME=$(date -u +%Y-%m-%dT%H:%M:%SZ)
echo "Good time: $GOOD_TIME"
sleep 60
 
# 2. The "bad migration"
$PSQL -c "DELETE FROM orders WHERE amount_cents < 25000"
$PSQL -c "SELECT count(*) FROM orders"
# Expected: roughly half of the 5000 orders are gone

Wait until the delete is part of the restorable window, then restore to the moment before it:

Bash

aws rds describe-db-instances --db-instance-identifier production-postgres \
  --query 'DBInstances[0].LatestRestorableTime' --output text
# Wait until this time is later than the delete (usually about 5 minutes)
 
START=$(date +%s)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier production-postgres \
  --target-db-instance-identifier pitr-drill \
  --restore-time "$GOOD_TIME" \
  --db-instance-class db.t4g.micro \
  --vpc-security-group-ids "$DB_SG" \
  --publicly-accessible
 
aws rds wait db-instance-available --db-instance-identifier pitr-drill
echo "Restore took $(( $(date +%s) - START )) seconds"
 
PITR_HOST=$(aws rds describe-db-instances --db-instance-identifier pitr-drill \
  --query 'DBInstances[0].Endpoint.Address' --output text)
psql "host=$PITR_HOST dbname=appdb user=postgres sslmode=require" -tA \
  -c "SELECT count(*) FROM orders"
# Expected: 5000, the rows from before the bad delete

Record your restore time, because it is your first real RTO measurement. Then remove the drill instance:

Bash

aws rds delete-db-instance --db-instance-identifier pitr-drill \
  --skip-final-snapshot --delete-automated-backups

Tip
Restoring beside production and copying rows back (for example with pg_dump of just the affected table) keeps new writes safe. Use a restored instance as the new primary only when the original is unrecoverable.

Common Mistake
Choosing a restore time later than LatestRestorableTime and getting an invalid restore time error. Check LatestRestorableTime first. It usually trails real time by about five minutes.

Milestone 6: Build the Network in the DR Region

This milestone creates a VPC in Singapore that is ready before any disaster, with private subnets for restored databases and a NAT gateway so the test runner can reach AWS APIs.

You cannot calmly design networking in the middle of an outage, so the DR region needs its network built and tested in advance. The restored database goes in private subnets and accepts connections only from the test runner's security group.

Create dr-network.tf:

HCL

data "aws_availability_zones" "dr" {
  provider = aws.dr
  state    = "available"
}
 
resource "aws_vpc" "dr" {
  provider             = aws.dr
  cidr_block           = "10.20.0.0/16"
  enable_dns_support   = true
  enable_dns_hostnames = true
  tags                 = { Name = "dr-vpc" }
}
 
resource "aws_internet_gateway" "dr" {
  provider = aws.dr
  vpc_id   = aws_vpc.dr.id
  tags     = { Name = "dr-igw" }
}
 
resource "aws_subnet" "dr_public" {
  provider          = aws.dr
  vpc_id            = aws_vpc.dr.id
  cidr_block        = cidrsubnet(aws_vpc.dr.cidr_block, 8, 0)
  availability_zone = data.aws_availability_zones.dr.names[0]
  tags              = { Name = "dr-public" }
}
 
resource "aws_subnet" "dr_private" {
  count             = 2
  provider          = aws.dr
  vpc_id            = aws_vpc.dr.id
  cidr_block        = cidrsubnet(aws_vpc.dr.cidr_block, 8, count.index + 10)
  availability_zone = data.aws_availability_zones.dr.names[count.index]
  tags              = { Name = "dr-private-${count.index}" }
}
 
resource "aws_eip" "dr_nat" {
  provider = aws.dr
  domain   = "vpc"
}
 
resource "aws_nat_gateway" "dr" {
  provider      = aws.dr
  allocation_id = aws_eip.dr_nat.id
  subnet_id     = aws_subnet.dr_public.id
  depends_on    = [aws_internet_gateway.dr]
  tags          = { Name = "dr-nat" }
}
 
resource "aws_route_table" "dr_public" {
  provider = aws.dr
  vpc_id   = aws_vpc.dr.id
 
  route {
    cidr_block = "0.0.0.0/0"
    gateway_id = aws_internet_gateway.dr.id
  }
}
 
resource "aws_route_table_association" "dr_public" {
  provider       = aws.dr
  subnet_id      = aws_subnet.dr_public.id
  route_table_id = aws_route_table.dr_public.id
}
 
resource "aws_route_table" "dr_private" {
  provider = aws.dr
  vpc_id   = aws_vpc.dr.id
 
  route {
    cidr_block     = "0.0.0.0/0"
    nat_gateway_id = aws_nat_gateway.dr.id
  }
}
 
resource "aws_route_table_association" "dr_private" {
  count          = 2
  provider       = aws.dr
  subnet_id      = aws_subnet.dr_private[count.index].id
  route_table_id = aws_route_table.dr_private.id
}
 
resource "aws_db_subnet_group" "dr" {
  provider   = aws.dr
  name       = "dr-restore"
  subnet_ids = aws_subnet.dr_private[*].id
}
 
resource "aws_security_group" "dr_runner" {
  provider    = aws.dr
  name        = "dr-test-runner"
  description = "CodeBuild restore test runner"
  vpc_id      = aws_vpc.dr.id
 
  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}
 
resource "aws_security_group" "dr_db" {
  provider    = aws.dr
  name        = "dr-restored-db"
  description = "PostgreSQL from the test runner only"
  vpc_id      = aws_vpc.dr.id
 
  ingress {
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = [aws_security_group.dr_runner.id]
  }
}

Bash

terraform apply
# Expected: VPC, subnets, NAT gateway, subnet group and two security groups created

Tip
The NAT gateway is the priciest part of this lab by the hour. A real DR network often keeps one running, and you can destroy the lab at the end to stop the charge.

Common Mistake
Putting the test runner in a private subnet with no NAT or VPC endpoints. The build then hangs because it cannot reach the RDS, Secrets Manager or CloudWatch APIs. Give private subnets a route out.

Milestone 7: Automate the Weekly Restore Test

This milestone creates a CodeBuild job that restores the replicated backup in Singapore, queries the data, records the restore time and deletes the test database. A schedule runs it every Sunday.

CodeBuild fits because a restore takes longer than a Lambda function can run, and because it can run inside your VPC to reach a private database. The test passes only if the restored database answers real queries. A restore that merely reaches "available" proves little.

Create buildspec-dr-test.yml:

YAML

version: 0.2
 
env:
  shell: bash
 
phases:
  build:
    commands:
      - |
        set -euo pipefail
        dnf install -y postgresql15 > /dev/null 2>&1 || dnf install -y postgresql16 > /dev/null
        psql --version
        TEST_ID="dr-test-$(date -u +%Y%m%d-%H%M)"
        cleanup() {
          aws rds delete-db-instance --db-instance-identifier "$TEST_ID" \
            --skip-final-snapshot --delete-automated-backups \
            --region "$DR_REGION" > /dev/null 2>&1 || true
        }
        trap cleanup EXIT
 
        BACKUP_ARN=$(aws rds describe-db-instance-automated-backups --region "$DR_REGION" \
          --query "DBInstanceAutomatedBackups[?DBInstanceIdentifier=='$DB_ID'] | [0].DBInstanceAutomatedBackupsArn" \
          --output text)
        if [ -z "$BACKUP_ARN" ] || [ "$BACKUP_ARN" = "None" ]; then
          echo "No replicated backup found for $DB_ID"
          exit 1
        fi
 
        LATEST=$(aws rds describe-db-instance-automated-backups --region "$DR_REGION" \
          --query "DBInstanceAutomatedBackups[?DBInstanceIdentifier=='$DB_ID'] | [0].RestoreWindow.LatestTime" \
          --output text)
        echo "Latest restorable time in DR region: $LATEST"
 
        START=$(date +%s)
        aws rds restore-db-instance-to-point-in-time \
          --region "$DR_REGION" \
          --source-db-instance-automated-backups-arn "$BACKUP_ARN" \
          --target-db-instance-identifier "$TEST_ID" \
          --use-latest-restorable-time \
          --db-instance-class "$TEST_CLASS" \
          --db-subnet-group-name "$SUBNET_GROUP" \
          --vpc-security-group-ids "$DB_SG_ID" \
          --kms-key-id "$KMS_KEY_ARN" \
          --no-publicly-accessible \
          --no-multi-az \
          --tags Key=Purpose,Value=dr-test > /dev/null
 
        aws rds wait db-instance-available --db-instance-identifier "$TEST_ID" --region "$DR_REGION"
        ELAPSED=$(( $(date +%s) - START ))
        echo "Restore reached available in $ELAPSED seconds"
 
        ENDPOINT=$(aws rds describe-db-instances --db-instance-identifier "$TEST_ID" \
          --region "$DR_REGION" --query 'DBInstances[0].Endpoint.Address' --output text)
        export PGPASSWORD=$(aws secretsmanager get-secret-value --secret-id "$SECRET_NAME" \
          --region "$DR_REGION" --query SecretString --output text)
 
        Q() { psql "host=$ENDPOINT dbname=appdb user=postgres sslmode=require connect_timeout=30" -tA -c "$1"; }
        USERS=$(Q "SELECT count(*) FROM users")
        ORDERS=$(Q "SELECT count(*) FROM orders")
        ORPHANS=$(Q "SELECT count(*) FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL")
        echo "users=$USERS orders=$ORDERS orphaned_orders=$ORPHANS"
 
        PASSED=0
        if [ "$USERS" -gt 0 ] && [ "$ORDERS" -gt 0 ] && [ "$ORPHANS" -eq 0 ]; then PASSED=1; fi
 
        aws cloudwatch put-metric-data --namespace DisasterRecovery \
          --metric-name RestoreSeconds --value "$ELAPSED" --unit Seconds --region "$DR_REGION"
        aws cloudwatch put-metric-data --namespace DisasterRecovery \
          --metric-name ValidationPassed --value "$PASSED" --unit Count --region "$DR_REGION"
 
        [ "$PASSED" -eq 1 ]

Create dr-test.tf:

HCL

data "aws_caller_identity" "current" {
  provider = aws.primary
}
 
resource "aws_cloudwatch_log_group" "dr_test" {
  provider          = aws.dr
  name              = "/aws/codebuild/rds-dr-restore-test"
  retention_in_days = 30
}
 
data "aws_iam_policy_document" "codebuild_assume" {
  statement {
    actions = ["sts:AssumeRole"]
 
    principals {
      type        = "Service"
      identifiers = ["codebuild.amazonaws.com"]
    }
  }
}
 
resource "aws_iam_role" "dr_test" {
  provider           = aws.dr
  name               = "rds-dr-restore-test"
  assume_role_policy = data.aws_iam_policy_document.codebuild_assume.json
}
 
data "aws_iam_policy_document" "dr_test" {
  statement {
    sid       = "Logs"
    actions   = ["logs:CreateLogGroup", "logs:CreateLogStream", "logs:PutLogEvents"]
    resources = ["${aws_cloudwatch_log_group.dr_test.arn}:*"]
  }
 
  statement {
    sid = "RestoreAndCleanUp"
    actions = [
      "rds:RestoreDBInstanceToPointInTime",
      "rds:DescribeDBInstances",
      "rds:DescribeDBInstanceAutomatedBackups",
      "rds:DeleteDBInstance",
      "rds:AddTagsToResource",
    ]
    resources = ["*"] # scope this down by ARN in a real account
  }
 
  statement {
    sid       = "ReadReplicatedSecret"
    actions   = ["secretsmanager:GetSecretValue"]
    resources = ["arn:aws:secretsmanager:${var.dr_region}:${data.aws_caller_identity.current.account_id}:secret:${var.db_identifier}/master-password-*"]
  }
 
  statement {
    sid       = "UseDrKey"
    actions   = ["kms:Decrypt", "kms:DescribeKey", "kms:GenerateDataKey*", "kms:CreateGrant", "kms:ReEncrypt*"]
    resources = [aws_kms_key.dr.arn]
  }
 
  statement {
    sid       = "PublishMetrics"
    actions   = ["cloudwatch:PutMetricData"]
    resources = ["*"]
 
    condition {
      test     = "StringEquals"
      variable = "cloudwatch:namespace"
      values   = ["DisasterRecovery"]
    }
  }
 
  statement {
    sid = "VpcNetworking"
    actions = [
      "ec2:CreateNetworkInterface",
      "ec2:DescribeNetworkInterfaces",
      "ec2:DeleteNetworkInterface",
      "ec2:DescribeSubnets",
      "ec2:DescribeSecurityGroups",
      "ec2:DescribeDhcpOptions",
      "ec2:DescribeVpcs",
    ]
    resources = ["*"]
  }
 
  statement {
    sid       = "VpcNetworkingPermission"
    actions   = ["ec2:CreateNetworkInterfacePermission"]
    resources = ["arn:aws:ec2:${var.dr_region}:${data.aws_caller_identity.current.account_id}:network-interface/*"]
 
    condition {
      test     = "StringEquals"
      variable = "ec2:AuthorizedService"
      values   = ["codebuild.amazonaws.com"]
    }
  }
}
 
resource "aws_iam_role_policy" "dr_test" {
  provider = aws.dr
  name     = "restore-test"
  role     = aws_iam_role.dr_test.id
  policy   = data.aws_iam_policy_document.dr_test.json
}
 
resource "aws_codebuild_project" "dr_test" {
  provider      = aws.dr
  name          = "rds-dr-restore-test"
  description   = "Restores the replicated RDS backup, validates the data and deletes the test instance"
  service_role  = aws_iam_role.dr_test.arn
  build_timeout = 90
 
  artifacts {
    type = "NO_ARTIFACTS"
  }
 
  environment {
    compute_type = "BUILD_GENERAL1_SMALL"
    image        = "aws/codebuild/amazonlinux-x86_64-standard:5.0"
    type         = "LINUX_CONTAINER"
 
    environment_variable {
      name  = "DR_REGION"
      value = var.dr_region
    }
    environment_variable {
      name  = "DB_ID"
      value = var.db_identifier
    }
    environment_variable {
      name  = "TEST_CLASS"
      value = var.db_instance_class
    }
    environment_variable {
      name  = "SUBNET_GROUP"
      value = aws_db_subnet_group.dr.name
    }
    environment_variable {
      name  = "DB_SG_ID"
      value = aws_security_group.dr_db.id
    }
    environment_variable {
      name  = "KMS_KEY_ARN"
      value = aws_kms_key.dr.arn
    }
    environment_variable {
      name  = "SECRET_NAME"
      value = aws_secretsmanager_secret.db.name
    }
  }
 
  vpc_config {
    vpc_id             = aws_vpc.dr.id
    subnets            = aws_subnet.dr_private[*].id
    security_group_ids = [aws_security_group.dr_runner.id]
  }
 
  logs_config {
    cloudwatch_logs {
      group_name = aws_cloudwatch_log_group.dr_test.name
    }
  }
 
  source {
    type      = "NO_SOURCE"
    buildspec = file("${path.module}/buildspec-dr-test.yml")
  }
 
  depends_on = [aws_route_table_association.dr_private, aws_iam_role_policy.dr_test]
}
 
# Weekly schedule: Sunday 01:00 UTC
data "aws_iam_policy_document" "events_assume" {
  statement {
    actions = ["sts:AssumeRole"]
 
    principals {
      type        = "Service"
      identifiers = ["events.amazonaws.com"]
    }
  }
}
 
resource "aws_iam_role" "events_start_build" {
  provider           = aws.dr
  name               = "rds-dr-test-scheduler"
  assume_role_policy = data.aws_iam_policy_document.events_assume.json
}
 
resource "aws_iam_role_policy" "events_start_build" {
  provider = aws.dr
  name     = "start-build"
  role     = aws_iam_role.events_start_build.id
 
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Effect   = "Allow"
      Action   = "codebuild:StartBuild"
      Resource = aws_codebuild_project.dr_test.arn
    }]
  })
}
 
resource "aws_cloudwatch_event_rule" "weekly_test" {
  provider            = aws.dr
  name                = "weekly-dr-restore-test"
  schedule_expression = "cron(0 1 ? * SUN *)"
}
 
resource "aws_cloudwatch_event_target" "weekly_test" {
  provider = aws.dr
  rule     = aws_cloudwatch_event_rule.weekly_test.name
  arn      = aws_codebuild_project.dr_test.arn
  role_arn = aws_iam_role.events_start_build.arn
}
 
# Alert when a test run fails, times out or is stopped
resource "aws_sns_topic_policy" "dr" {
  provider = aws.dr
  arn      = aws_sns_topic.dr.arn
 
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Sid       = "AllowEventsAndAlarms"
      Effect    = "Allow"
      Principal = { Service = ["events.amazonaws.com", "cloudwatch.amazonaws.com"] }
      Action    = "sns:Publish"
      Resource  = aws_sns_topic.dr.arn
    }]
  })
}
 
resource "aws_cloudwatch_event_rule" "test_failed" {
  provider = aws.dr
  name     = "dr-restore-test-failed"
 
  event_pattern = jsonencode({
    source        = ["aws.codebuild"]
    "detail-type" = ["CodeBuild Build State Change"]
    detail = {
      "build-status" = ["FAILED", "FAULT", "TIMED_OUT", "STOPPED"]
      "project-name" = [aws_codebuild_project.dr_test.name]
    }
  })
}
 
resource "aws_cloudwatch_event_target" "test_failed" {
  provider = aws.dr
  rule     = aws_cloudwatch_event_rule.test_failed.name
  arn      = aws_sns_topic.dr.arn
}
 
# Alert when no passing test has been recorded for 8 days
resource "aws_cloudwatch_metric_alarm" "no_recent_pass" {
  provider            = aws.dr
  alarm_name          = "dr-restore-test-not-passing"
  alarm_description   = "No successful DR restore test in the last 8 days"
  namespace           = "DisasterRecovery"
  metric_name         = "ValidationPassed"
  statistic           = "Sum"
  period              = 86400
  evaluation_periods  = 8
  datapoints_to_alarm = 8
  comparison_operator = "LessThanThreshold"
  threshold           = 1
  treat_missing_data  = "breaching"
  alarm_actions       = [aws_sns_topic.dr.arn]
}

Bash

terraform fmt && terraform validate
terraform apply
# Expected: CodeBuild project, schedule, alert rules and alarm created

Security
The restore job can create and delete RDS instances. This lab grants those actions on * for readability. In a real account, restrict them with resource ARNs and a tag condition so the job can only delete instances tagged Purpose=dr-test.

Common Mistake
Treating "the instance became available" as a passing test. Plenty of restores come up and still hold the wrong or empty data. The job above queries real tables and fails the build when counts or integrity checks are off.

Milestone 8: Write the Runbook and Measure Your Numbers

This milestone turns what you built into a runbook someone could follow at 3 AM, with real numbers instead of guesses.

A runbook is a short list of commands in the order you would run them, with the expected result of each. Create RUNBOOK.md in the repository:

MARKDOWN

# Regional outage runbook: production-postgres
 
Measured RTO (restore until database available): FILL_IN minutes
Measured RPO (age of newest restorable point in DR): FILL_IN minutes
 
## 1. Confirm the primary region is really unavailable
## 2. Find the newest restorable point in Singapore
## 3. Restore into the DR VPC
## 4. Validate the data
## 5. Repoint the application and announce
## 6. Plan failback

Fill in step 2 and step 3 with these commands:

Bash

DR=ap-southeast-1
 
aws rds describe-db-instance-automated-backups --region $DR \
  --query "DBInstanceAutomatedBackups[?DBInstanceIdentifier=='production-postgres'] | [0].[DBInstanceAutomatedBackupsArn,RestoreWindow.LatestTime]" \
  --output text
 
aws rds restore-db-instance-to-point-in-time --region $DR \
  --source-db-instance-automated-backups-arn BACKUP_ARN \
  --target-db-instance-identifier production-postgres-dr \
  --use-latest-restorable-time \
  --db-instance-class db.t4g.micro \
  --db-subnet-group-name dr-restore \
  --vpc-security-group-ids DR_DB_SG_ID \
  --kms-key-id DR_KEY_ARN \
  --no-publicly-accessible

Measure the two numbers from real data:

Bash

# RPO: how old is the newest restorable point in the DR region?
LATEST=$(aws rds describe-db-instance-automated-backups --region ap-southeast-1 \
  --query "DBInstanceAutomatedBackups[?DBInstanceIdentifier=='production-postgres'] | [0].RestoreWindow.LatestTime" \
  --output text)
echo "DR restore point is $(( ($(date +%s) - $(date -d "$LATEST" +%s)) / 60 )) minutes old"
 
# RTO: read the RestoreSeconds metric published by the weekly test
aws cloudwatch get-metric-statistics --region ap-southeast-1 \
  --namespace DisasterRecovery --metric-name RestoreSeconds \
  --statistics Maximum --period 604800 \
  --start-time "$(date -u -d '30 days ago' +%Y-%m-%dT%H:%M:%SZ)" \
  --end-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)"

Tip
Treat these as your own measurements for this database size and instance class, not a promise. They grow as the database grows, so re-measure after big data changes.

Common Mistake
Writing a runbook that has never been followed. Run steps 2 and 3 once as a drill, fix whatever was unclear, and only then trust the document.

Validation & Testing

This final step proves each part works, then removes everything. Run the checks in order.

Bash

# 1. Automated backups are on and encrypted. Expected: [14, true]
aws rds describe-db-instances --region ap-south-1 --db-instance-identifier production-postgres \
  --query 'DBInstances[0].[BackupRetentionPeriod,StorageEncrypted]'
 
# 2. Backups are replicated to Singapore. Expected: Status replicating and a restore window
aws rds describe-db-instance-automated-backups --region ap-southeast-1 \
  --query 'DBInstanceAutomatedBackups[*].[DBInstanceIdentifier,Status,RestoreWindow.LatestTime]' \
  --output table
 
# 3. Both alert emails are confirmed. Expected: real subscription ARNs, not PendingConfirmation
aws sns list-subscriptions-by-topic --region ap-south-1 \
  --topic-arn "$(aws sns list-topics --region ap-south-1 --query "Topics[?contains(TopicArn,'rds-dr-alerts')].TopicArn | [0]" --output text)" \
  --query 'Subscriptions[*].SubscriptionArn'
 
# 4. Run the DR restore test now instead of waiting for Sunday
BUILD_ID=$(aws codebuild start-build --region ap-southeast-1 \
  --project-name rds-dr-restore-test --query 'build.id' --output text)
aws logs tail /aws/codebuild/rds-dr-restore-test --region ap-southeast-1 --follow
# Expected: "users=1000 orders=5000 orphaned_orders=0" and a successful build
 
aws codebuild batch-get-builds --region ap-southeast-1 --ids "$BUILD_ID" \
  --query 'builds[0].buildStatus' --output text
# Expected: SUCCEEDED
 
# 5. The metrics were published. Expected: ValidationPassed 1 and a RestoreSeconds value
aws cloudwatch list-metrics --region ap-southeast-1 --namespace DisasterRecovery
 
# 6. The test database cleaned itself up. Expected: no dr-test-* instance
aws rds describe-db-instances --region ap-southeast-1 \
  --query 'DBInstances[?starts_with(DBInstanceIdentifier,`dr-test`)].DBInstanceIdentifier'

Prove the failure path too. Temporarily break the test, for example by changing the users table name in the query in buildspec-dr-test.yml, apply, run the build again, and confirm it fails and you receive the alert email. Then revert the change. An alert you have never seen fire is untested.

Clean up

Delete the replicated backups and instances last, because they cost money while they exist.

Bash

# Allow deletion, then destroy everything Terraform created
terraform apply -var protect_database=false
terraform destroy
 
# Replicated backups can outlive the replication. List and delete any that remain
aws rds describe-db-instance-automated-backups --region ap-southeast-1 \
  --query 'DBInstanceAutomatedBackups[*].[DbiResourceId,Status]' --output text
aws rds delete-db-instance-automated-backups --region ap-southeast-1 --dbi-resource-id RESOURCE_ID

The KMS keys are scheduled for deletion after 7 days, so they stop billing then.

Common Mistake
Running terraform destroy while protect_database is still true. Deletion protection blocks the destroy. Apply with protect_database=false first.

Commit the repository with RUNBOOK.md, a screenshot of the passing restore test and your measured RTO and RPO. A reviewer can see that your backups are replicated, tested and monitored, not just configured.

Found a mistake, an outdated step or a broken link?

Build a Production Database Backup and Disaster Recovery System on AWS

Blueprint Walkthrough

Architecture Overview & Problem Statement

The problem this solves

What you will build

What you will have at the end

What this build leaves out

Prerequisites and cost

Milestone 1: Set Up Terraform, Keys and the Database Secret

Milestone 2: Deploy the Primary Database and Seed It

Milestone 3: Replicate Backups to the DR Region

Milestone 4: Add Alerts for Failures

Milestone 5: Rehearse Point-in-Time Recovery

Milestone 6: Build the Network in the DR Region

Milestone 7: Automate the Weekly Restore Test

Milestone 8: Write the Runbook and Measure Your Numbers

Validation & Testing

Clean up