--- 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** ```bash # 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** ```hcl # 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** ```bash # 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](https://docs.aws.amazon.com/rds/) - [RDS Best Practices](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_BestPractices.html) - [PostgreSQL on RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html)