AWS RDS Database

Overview

Amazon RDS simplifies relational database deployment and operations. Support multiple database engines with automated backups, replication, encryption, and high availability through Multi-AZ deployments.

When to Use

• PostgreSQL and MySQL applications
• Transactional databases and OLTP
• Oracle and Microsoft SQL Server workloads
• Read-heavy applications with replicas
• Development and staging environments
• Data requiring ACID compliance
• Applications needing automatic backups
• Disaster recovery scenarios

Implementation Examples

1. RDS Instance Creation with AWS CLI

# Create DB subnet group
aws rds create-db-subnet-group \
  --db-subnet-group-name app-db-subnet \
  --db-subnet-group-description "App database subnet" \
  --subnet-ids subnet-12345 subnet-67890

# Create security group for RDS
aws ec2 create-security-group \
  --group-name rds-sg \
  --description "RDS security group" \
  --vpc-id vpc-12345

# Allow inbound PostgreSQL
aws ec2 authorize-security-group-ingress \
  --group-id sg-rds123 \
  --protocol tcp \
  --port 5432 \
  --source-security-group-id sg-app123

# Create RDS instance
aws rds create-db-instance \
  --db-instance-identifier myapp-db \
  --db-instance-class db.t3.micro \
  --engine postgres \
  --engine-version 15.2 \
  --master-username admin \
  --master-user-password MySecurePassword123! \
  --allocated-storage 100 \
  --storage-type gp3 \
  --db-subnet-group-name app-db-subnet \
  --vpc-security-group-ids sg-rds123 \
  --multi-az \
  --storage-encrypted \
  --kms-key-id arn:aws:kms:region:account:key/id \
  --backup-retention-period 30 \
  --preferred-backup-window "03:00-04:00" \
  --preferred-maintenance-window "mon:04:00-mon:05:00" \
  --enable-clouwatch-logs-exports postgresql \
  --enable-iam-database-authentication

# Create read replica
aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-db-read \
  --source-db-instance-identifier myapp-db

# Take manual snapshot
aws rds create-db-snapshot \
  --db-snapshot-identifier myapp-db-backup-2024 \
  --db-instance-identifier myapp-db

# Describe RDS instance
aws rds describe-db-instances \
  --db-instance-identifier myapp-db \
  --query 'DBInstances[0].[DBInstanceIdentifier,DBInstanceStatus,Endpoint.Address]'

2. Terraform RDS Configuration

# rds.tf
terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.0"
    }
  }
}

provider "aws" {
  region = "us-east-1"
}

# DB subnet group
resource "aws_db_subnet_group" "app" {
  name       = "app-db-subnet"
  subnet_ids = [aws_subnet.private1.id, aws_subnet.private2.id]

  tags = { Name = "app-db-subnet" }
}

# Security group
resource "aws_security_group" "rds" {
  name_prefix = "rds-"
  vpc_id      = aws_vpc.main.id

  ingress {
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = [aws_security_group.app.id]
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

# KMS key for encryption
resource "aws_kms_key" "rds" {
  description             = "RDS encryption key"
  deletion_window_in_days = 10
  enable_key_rotation     = true
}

resource "aws_kms_alias" "rds" {
  name          = "alias/rds-key"
  target_key_id = aws_kms_key.rds.key_id
}

# RDS instance
resource "aws_db_instance" "app" {
  identifier            = "myapp-db"
  engine               = "postgres"
  engine_version       = "15.2"
  instance_class       = "db.t3.micro"
  allocated_storage    = 100
  storage_type         = "gp3"
  storage_encrypted    = true
  kms_key_id           = aws_kms_key.rds.arn

  db_name  = "appdb"
  username = "admin"
  password = random_password.db_password.result

  db_subnet_group_name   = aws_db_subnet_group.app.name
  vpc_security_group_ids = [aws_security_group.rds.id]

  multi_az               = true
  publicly_accessible    = false

  backup_retention_period = 30
  backup_window           = "03:00-04:00"
  maintenance_window      = "mon:04:00-mon:05:00"
  copy_tags_to_snapshot   = true

  enabled_cloudwatch_logs_exports = ["postgresql"]

  enable_iam_database_authentication = true

  deletion_protection = true

  skip_final_snapshot       = false
  final_snapshot_identifier = "myapp-db-final-snapshot-${formatdate("YYYY-MM-DD-hhmm", timestamp())}"

  tags = {
    Name = "myapp-db"
  }
}

# Generate random password
resource "random_password" "db_password" {
  length  = 16
  special = true
}

# Store password in Secrets Manager
resource "aws_secretsmanager_secret" "db_password" {
  name_prefix             = "rds/myapp/"
  recovery_window_in_days = 7
}

resource "aws_secretsmanager_secret_version" "db_password" {
  secret_id = aws_secretsmanager_secret.db_password.id
  secret_string = jsonencode({
    username = aws_db_instance.app.username
    password = random_password.db_password.result
    engine   = "postgres"
    host     = aws_db_instance.app.address
    port     = aws_db_instance.app.port
    dbname   = aws_db_instance.app.db_name
  })
}

# Read replica
resource "aws_db_instance" "read_replica" {
  identifier     = "myapp-db-read"
  replicate_source_db = aws_db_instance.app.identifier
  instance_class      = "db.t3.micro"
  publicly_accessible = false

  tags = {
    Name = "myapp-db-read"
  }
}

# Enhanced monitoring role
resource "aws_iam_role" "rds_monitoring" {
  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Action = "sts:AssumeRole"
      Effect = "Allow"
      Principal = {
        Service = "monitoring.rds.amazonaws.com"
      }
    }]
  })
}

resource "aws_iam_role_policy_attachment" "rds_monitoring" {
  role       = aws_iam_role.rds_monitoring.name
  policy_arn = "arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole"
}

# CloudWatch alarms
resource "aws_cloudwatch_metric_alarm" "db_cpu" {
  alarm_name          = "rds-high-cpu"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 2
  metric_name         = "CPUUtilization"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 80
  alarm_description   = "Alert when RDS CPU exceeds 80%"

  dimensions = {
    DBInstanceIdentifier = aws_db_instance.app.id
  }
}

resource "aws_cloudwatch_metric_alarm" "db_connections" {
  alarm_name          = "rds-high-connections"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 1
  metric_name         = "DatabaseConnections"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 80
  alarm_description   = "Alert when database connections exceed 80"

  dimensions = {
    DBInstanceIdentifier = aws_db_instance.app.id
  }
}

# Outputs
output "db_endpoint" {
  value       = aws_db_instance.app.endpoint
  description = "RDS endpoint address"
}

output "db_password_secret" {
  value       = aws_secretsmanager_secret.db_password.arn
  description = "Secret Manager ARN for database credentials"
}

3. Database Connection and Configuration

# Connect to RDS instance
psql -h myapp-db.xxxx.us-east-1.rds.amazonaws.com \
     -U admin \
     -d appdb \
     -p 5432

# Create database user with IAM authentication
psql -h myapp-db.xxxx.us-east-1.rds.amazonaws.com \
     -U admin \
     -d appdb << EOF
CREATE USER app_user;
GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER ROLE app_user WITH PASSWORD 'MySecurePassword123!';
EOF

# Export database
pg_dump -h myapp-db.xxxx.us-east-1.rds.amazonaws.com \
        -U admin \
        appdb > backup.sql

# Import database
psql -h myapp-db.xxxx.us-east-1.rds.amazonaws.com \
     -U admin \
     appdb < backup.sql

Best Practices

✅ DO

• Use Multi-AZ for production
• Enable automated backups
• Use encryption at rest and in transit
• Implement IAM database authentication
• Create read replicas for scaling
• Monitor performance metrics
• Set up CloudWatch alarms
• Store credentials in Secrets Manager
• Use parameter groups for configuration

❌ DON'T

• Store passwords in code
• Disable encryption
• Use public accessibility in production
• Ignore backup retention
• Skip automated backups
• Create databases without Multi-AZ

Monitoring

• CloudWatch metrics (CPU, connections, storage)
• Enhanced Monitoring with OS metrics
• RDS Performance Insights
• AWS CloudTrail for API logging
• Custom CloudWatch Logs from applications

Resources

• AWS RDS Documentation
• RDS Best Practices
• PostgreSQL on RDS