aws-rds-database

仓库: aj-geddes/useful-ai-prompts
安装量: 128
排名: #6749

安装

npx skills add https://github.com/aj-geddes/useful-ai-prompts --skill aws-rds-database

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]'

  1. 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" }

  1. 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

← 返回排行榜