Table of Contents

Introduction

Amazon RDS simplifies database management by handling backups, patching, scaling, and replication. This guide covers deploying a production-grade PostgreSQL database on RDS using Terraform with Multi-AZ failover, encrypted storage, automated backups, and custom parameter groups.

Network Setup

resource "aws_db_subnet_group" "main" {
  name       = "main-db-subnet-group"
  subnet_ids = var.private_subnet_ids

  tags = {
    Name = "Main DB Subnet Group"
  }
}

resource "aws_security_group" "rds" {
  name   = "rds-postgresql-sg"
  vpc_id = var.vpc_id

  ingress {
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = [var.app_security_group_id]
    description     = "PostgreSQL from app servers"
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

Parameter Group

resource "aws_db_parameter_group" "postgresql" {
  name   = "custom-postgresql16"
  family = "postgres16"

  parameter {
    name  = "log_connections"
    value = "1"
  }

  parameter {
    name  = "log_disconnections"
    value = "1"
  }

  parameter {
    name  = "log_duration"
    value = "1"
  }

  parameter {
    name  = "shared_preload_libraries"
    value = "pg_stat_statements"
  }

  parameter {
    name  = "pg_stat_statements.track"
    value = "all"
  }

  parameter {
    name         = "max_connections"
    value        = "200"
    apply_method = "pending-reboot"
  }

  lifecycle {
    create_before_destroy = true
  }
}

RDS Instance

resource "aws_db_instance" "postgresql" {
  identifier = "production-postgresql"

  engine         = "postgres"
  engine_version = "16.3"
  instance_class = "db.r6g.large"

  allocated_storage     = 100
  max_allocated_storage = 500
  storage_type          = "gp3"
  storage_encrypted     = true
  kms_key_id            = var.kms_key_arn

  db_name  = "appdb"
  username = "dbadmin"
  password = var.db_password

  multi_az               = true
  db_subnet_group_name   = aws_db_subnet_group.main.name
  vpc_security_group_ids = [aws_security_group.rds.id]
  parameter_group_name   = aws_db_parameter_group.postgresql.name
  publicly_accessible    = false

  backup_retention_period = 30
  backup_window           = "03:00-04:00"
  maintenance_window      = "Mon:04:00-Mon:05:00"

  deletion_protection       = true
  skip_final_snapshot       = false
  final_snapshot_identifier = "production-final-snapshot"
  copy_tags_to_snapshot     = true

  performance_insights_enabled          = true
  performance_insights_retention_period = 7

  enabled_cloudwatch_logs_exports = [
    "postgresql",
    "upgrade"
  ]

  auto_minor_version_upgrade = true

  tags = {
    Environment = "production"
    ManagedBy   = "terraform"
  }
}

Read Replica

resource "aws_db_instance" "read_replica" {
  identifier          = "production-postgresql-replica"
  replicate_source_db = aws_db_instance.postgresql.identifier

  instance_class = "db.r6g.large"
  multi_az       = false

  storage_encrypted = true
  kms_key_id        = var.kms_key_arn

  vpc_security_group_ids = [aws_security_group.rds.id]
  parameter_group_name   = aws_db_parameter_group.postgresql.name
  publicly_accessible    = false

  performance_insights_enabled = true
  skip_final_snapshot          = true

  tags = {
    Environment = "production"
    Role        = "read-replica"
  }
}

Secrets Manager Integration

resource "aws_secretsmanager_secret" "db_credentials" {
  name = "production/postgresql/credentials"
  recovery_window_in_days = 7
}

resource "aws_secretsmanager_secret_version" "db_credentials" {
  secret_id = aws_secretsmanager_secret.db_credentials.id
  secret_string = jsonencode({
    username = aws_db_instance.postgresql.username
    password = var.db_password
    host     = aws_db_instance.postgresql.address
    port     = aws_db_instance.postgresql.port
    dbname   = aws_db_instance.postgresql.db_name
    engine   = "postgres"
  })
}

Monitoring

resource "aws_cloudwatch_metric_alarm" "cpu" {
  alarm_name          = "rds-high-cpu"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 3
  metric_name         = "CPUUtilization"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 80
  alarm_description   = "RDS CPU utilization exceeds 80%"
  alarm_actions       = [var.sns_topic_arn]

  dimensions = {
    DBInstanceIdentifier = aws_db_instance.postgresql.identifier
  }
}

resource "aws_cloudwatch_metric_alarm" "storage" {
  alarm_name          = "rds-low-storage"
  comparison_operator = "LessThanThreshold"
  evaluation_periods  = 1
  metric_name         = "FreeStorageSpace"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 10737418240  # 10 GB
  alarm_description   = "RDS free storage below 10GB"
  alarm_actions       = [var.sns_topic_arn]

  dimensions = {
    DBInstanceIdentifier = aws_db_instance.postgresql.identifier
  }
}

Best Practices

  1. Never store passwords in Terraform state — use var.db_password from environment or Vault
  2. Enable Multi-AZ for production — automatic failover in ~60 seconds
  3. Use gp3 storage — better price-performance than gp2
  4. Set max_allocated_storage for auto-scaling storage
  5. Enable Performance Insights — free for 7 days retention
  6. Keep 30-day backup retention for production
  7. Use deletion_protection to prevent accidental destruction

Conclusion

A properly configured RDS PostgreSQL instance with Terraform gives you a reliable, encrypted, and monitored database that handles failover automatically, scales storage on demand, and maintains 30 days of point-in-time recovery backups.