aws-rds

What this skill does

# AWS RDS

Deploy and manage Amazon RDS relational databases with production-grade backups, replication, monitoring, and security.

## When to Use This Skill

- Provisioning a managed PostgreSQL, MySQL, MariaDB, Oracle, or SQL Server database
- Setting up Multi-AZ deployments for high availability
- Creating read replicas for horizontal read scaling
- Configuring automated backups, snapshots, and point-in-time recovery
- Tuning database parameters for performance
- Migrating from self-managed databases to RDS
- Monitoring database performance and setting up alarms

## Prerequisites

- AWS CLI v2 installed and configured
- IAM permissions: `rds:*`, `ec2:DescribeSecurityGroups`, `ec2:DescribeSubnets`, `kms:*`, `cloudwatch:*`
- A VPC with at least two subnets in different AZs (for subnet group)
- Security group allowing database port access from application subnets only

## Create a DB Subnet Group

```bash
# Create a subnet group spanning two AZs
aws rds create-db-subnet-group \
  --db-subnet-group-name production-db-subnets \
  --db-subnet-group-description "Production database subnets" \
  --subnet-ids subnet-private-a subnet-private-b

# List subnet groups
aws rds describe-db-subnet-groups \
  --query "DBSubnetGroups[].{Name:DBSubnetGroupName,VPC:VpcId,Status:SubnetGroupStatus}" \
  --output table
```

## Create a Production Database

```bash
# Create a PostgreSQL 16 Multi-AZ instance
aws rds create-db-instance \
  --db-instance-identifier production-api-db \
  --db-instance-class db.r6g.large \
  --engine postgres \
  --engine-version 16.4 \
  --master-username appadmin \
  --manage-master-user-password \
  --allocated-storage 100 \
  --max-allocated-storage 500 \
  --storage-type gp3 \
  --storage-encrypted \
  --kms-key-id alias/rds-key \
  --vpc-security-group-ids sg-db-access \
  --db-subnet-group-name production-db-subnets \
  --db-name appdb \
  --backup-retention-period 14 \
  --preferred-backup-window "03:00-04:00" \
  --preferred-maintenance-window "sun:05:00-sun:06:00" \
  --multi-az \
  --auto-minor-version-upgrade \
  --deletion-protection \
  --copy-tags-to-snapshot \
  --monitoring-interval 60 \
  --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --enable-cloudwatch-logs-exports '["postgresql","upgrade"]' \
  --tags '[
    {"Key":"Environment","Value":"production"},
    {"Key":"Team","Value":"backend"},
    {"Key":"Backup","Value":"daily"}
  ]'

# Wait for instance to become available
aws rds wait db-instance-available --db-instance-identifier production-api-db

# Get connection endpoint
aws rds describe-db-instances \
  --db-instance-identifier production-api-db \
  --query "DBInstances[0].Endpoint.{Address:Address,Port:Port}" \
  --output table
```

## Retrieve Master Password from Secrets Manager

```bash
# When using --manage-master-user-password, RDS stores the password in Secrets Manager
aws rds describe-db-instances \
  --db-instance-identifier production-api-db \
  --query "DBInstances[0].MasterUserSecret.SecretArn" \
  --output text

# Retrieve the secret value
aws secretsmanager get-secret-value \
  --secret-id arn:aws:secretsmanager:us-east-1:123456789012:secret:rds-db-secret-abc123 \
  --query SecretString --output text
```

## Parameter Groups

```bash
# Create a custom parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name production-pg16 \
  --db-parameter-group-family postgres16 \
  --description "Production PostgreSQL 16 parameters"

# Set performance parameters
aws rds modify-db-parameter-group \
  --db-parameter-group-name production-pg16 \
  --parameters \
    "ParameterName=max_connections,ParameterValue=200,ApplyMethod=pending-reboot" \
    "ParameterName=shared_buffers,ParameterValue={DBInstanceClassMemory/4},ApplyMethod=pending-reboot" \
    "ParameterName=effective_cache_size,ParameterValue={DBInstanceClassMemory*3/4},ApplyMethod=pending-reboot" \
    "ParameterName=work_mem,ParameterValue=65536,ApplyMethod=immediate" \
    "ParameterName=maintenance_work_mem,ParameterValue=524288,ApplyMethod=immediate" \
    "ParameterName=random_page_cost,ParameterValue=1.1,ApplyMethod=immediate" \
    "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate" \
    "ParameterName=log_statement,ParameterValue=ddl,ApplyMethod=immediate" \
    "ParameterName=idle_in_transaction_session_timeout,ParameterValue=60000,ApplyMethod=immediate"

# Apply parameter group to the instance
aws rds modify-db-instance \
  --db-instance-identifier production-api-db \
  --db-parameter-group-name production-pg16 \
  --apply-immediately
```

## Read Replicas

```bash
# Create a read replica in the same region
aws rds create-db-instance-read-replica \
  --db-instance-identifier production-api-db-read1 \
  --source-db-instance-identifier production-api-db \
  --db-instance-class db.r6g.large \
  --availability-zone us-east-1b \
  --enable-performance-insights \
  --monitoring-interval 60 \
  --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role

# Create a cross-region read replica for DR
aws rds create-db-instance-read-replica \
  --db-instance-identifier dr-api-db-read \
  --source-db-instance-identifier arn:aws:rds:us-east-1:123456789012:db:production-api-db \
  --db-instance-class db.r6g.large \
  --region us-west-2 \
  --storage-encrypted \
  --kms-key-id alias/rds-dr-key

# Promote a read replica to standalone (for DR failover)
aws rds promote-read-replica \
  --db-instance-identifier dr-api-db-read

# Check replication lag
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name ReplicaLag \
  --dimensions Name=DBInstanceIdentifier,Value=production-api-db-read1 \
  --start-time "$(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ)" \
  --end-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \
  --period 300 \
  --statistics Average \
  --output table
```

## Snapshots and Point-in-Time Recovery

```bash
# Create a manual snapshot
aws rds create-db-snapshot \
  --db-instance-identifier production-api-db \
  --db-snapshot-identifier production-api-db-pre-migration-$(date +%Y%m%d)

# Wait for snapshot to complete
aws rds wait db-snapshot-available \
  --db-snapshot-identifier production-api-db-pre-migration-20260324

# Restore from snapshot (creates a new instance)
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier production-api-db-restored \
  --db-snapshot-identifier production-api-db-pre-migration-20260324 \
  --db-instance-class db.r6g.large \
  --db-subnet-group-name production-db-subnets \
  --vpc-security-group-ids sg-db-access

# Point-in-time recovery (restore to a specific second)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier production-api-db \
  --target-db-instance-identifier production-api-db-pitr \
  --restore-time "2026-03-24T10:30:00Z" \
  --db-instance-class db.r6g.large \
  --db-subnet-group-name production-db-subnets

# Copy snapshot to another region
aws rds copy-db-snapshot \
  --source-db-snapshot-identifier arn:aws:rds:us-east-1:123456789012:snapshot:production-api-db-pre-migration-20260324 \
  --target-db-snapshot-identifier production-api-db-dr-copy \
  --region us-west-2 \
  --kms-key-id alias/rds-dr-key

# Delete old snapshots
aws rds delete-db-snapshot --db-snapshot-identifier old-snapshot-name
```

## Monitoring and Alarms

```bash
# Set CPU utilization alarm
aws cloudwatch put-metric-alarm \
  --alarm-name rds-production-cpu-high \
  --alarm-description "RDS CPU > 80% for 5 minutes" \
  --metric-name CPUUtilization \
  --namespace AWS/RDS \
  --dimensions Name=DBInstanceIdentifier,Value=production-api-db \
  --statistic Average \
  --period 300 \
  --threshold 80 \
  --comparison-operator GreaterThanThreshold \
  --evaluation-periods 1 \
  --alarm-actions arn:aws:sns:us-east-1:123456789012:db-alerts

# Set free storage space alarm (alert below 10 GB)
aws cloudwatch put-metri