name: aws-rds-database description: Deploy and manage relational databases using RDS with Multi-AZ, read replicas, backups, and encryption. Use for PostgreSQL, MySQL, MariaDB, and Oracle.

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

aws-rds-database

SKILL.md