AWS RDS

Amazon Relational Database Service (RDS) takes care of provisioning, patching, backups, and failover for relational databases. Supports MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, and Amazon Aurora.

Core Concepts

• DB Instance — an isolated database environment in the cloud
• Multi-AZ — synchronous standby replica in another AZ for high availability
• Read Replica — asynchronous copy for read scaling
• Parameter Group — engine configuration settings
• Subnet Group — defines which subnets RDS can use
• Aurora — AWS-native engine, MySQL/PostgreSQL compatible, 5x faster

Provisioning a Database

# Create a DB subnet group
aws rds create-db-subnet-group \
  --db-subnet-group-name app-db-subnets \
  --db-subnet-group-description "Private subnets for RDS" \
  --subnet-ids subnet-aaa subnet-bbb subnet-ccc

# Launch a PostgreSQL instance with Multi-AZ
aws rds create-db-instance \
  --db-instance-identifier app-db-prod \
  --db-instance-class db.r6g.large \
  --engine postgres \
  --engine-version 15.4 \
  --master-username appuser \
  --master-user-password "$(aws secretsmanager get-random-password --password-length 32 --query RandomPassword --output text)" \
  --allocated-storage 100 \
  --storage-type gp3 \
  --multi-az \
  --db-subnet-group-name app-db-subnets \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --backup-retention-period 14 \
  --preferred-backup-window "03:00-04:00" \
  --preferred-maintenance-window "sun:05:00-sun:06:00" \
  --storage-encrypted \
  --tags Key=Env,Value=prod

Aurora Cluster

# Create an Aurora PostgreSQL cluster
aws rds create-db-cluster \
  --db-cluster-identifier app-aurora-prod \
  --engine aurora-postgresql \
  --engine-version 15.4 \
  --master-username appuser \
  --master-user-password "$DB_PASSWORD" \
  --db-subnet-group-name app-db-subnets \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --backup-retention-period 14 \
  --storage-encrypted \
  --serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=16

# Add writer and reader instances to the cluster
aws rds create-db-instance \
  --db-instance-identifier app-aurora-writer \
  --db-instance-class db.serverless \
  --engine aurora-postgresql \
  --db-cluster-identifier app-aurora-prod

aws rds create-db-instance \
  --db-instance-identifier app-aurora-reader \
  --db-instance-class db.serverless \
  --engine aurora-postgresql \
  --db-cluster-identifier app-aurora-prod

Read Replicas

# Create a read replica in same region
aws rds create-db-instance-read-replica \
  --db-instance-identifier app-db-read-1 \
  --source-db-instance-identifier app-db-prod \
  --db-instance-class db.r6g.large

# Create cross-region read replica for DR
aws rds create-db-instance-read-replica \
  --db-instance-identifier app-db-read-eu \
  --source-db-instance-identifier arn:aws:rds:us-east-1:123456789:db:app-db-prod \
  --db-instance-class db.r6g.large \
  --region eu-west-1

# Promote replica to standalone (for migration or DR)
aws rds promote-read-replica --db-instance-identifier app-db-read-eu

Snapshots and Recovery

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

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

# Point-in-time recovery
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier app-db-prod \
  --target-db-instance-identifier app-db-pitr \
  --restore-time "2024-01-15T10:30:00Z" \
  --db-instance-class db.r6g.large

# List automated snapshots
aws rds describe-db-snapshots \
  --db-instance-identifier app-db-prod \
  --snapshot-type automated \
  --query 'DBSnapshots[].[DBSnapshotIdentifier,SnapshotCreateTime,Status]' \
  --output table

Parameter Groups

# Create custom parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name app-postgres15 \
  --db-parameter-group-family postgres15 \
  --description "Custom params for app"

# Tune parameters for performance
aws rds modify-db-parameter-group \
  --db-parameter-group-name app-postgres15 \
  --parameters \
    "ParameterName=shared_buffers,ParameterValue={DBInstanceClassMemory/4},ApplyMethod=pending-reboot" \
    "ParameterName=max_connections,ParameterValue=200,ApplyMethod=pending-reboot" \
    "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate"

Monitoring

# Enable Performance Insights
aws rds modify-db-instance \
  --db-instance-identifier app-db-prod \
  --enable-performance-insights \
  --performance-insights-retention-period 7

# Check instance status and endpoints
aws rds describe-db-instances \
  --db-instance-identifier app-db-prod \
  --query 'DBInstances[0].[Endpoint.Address,DBInstanceStatus,AllocatedStorage,MultiAZ]' \
  --output table

Best Practices

• Always enable Multi-AZ for production databases
• Use Aurora Serverless v2 for unpredictable workloads
• Store credentials in AWS Secrets Manager with automatic rotation
• Set backup retention to at least 7 days; test restores periodically
• Use read replicas to offload reporting and analytics queries
• Enable Performance Insights and Enhanced Monitoring
• Use parameter groups to tune engine settings per workload
• Place RDS in private subnets; never expose to the public internet