Database Module - GovTech Multicloud Platform

The database module provisions a managed PostgreSQL database using Amazon RDS with enterprise features including automated backups, encryption at rest, Multi-AZ deployment, and enhanced monitoring.

Overview

This module creates:

RDS PostgreSQL 15.4 instance
Database subnet group in private subnets
Custom parameter group for PostgreSQL configuration
Enhanced monitoring with 60-second granularity
Automated backups with configurable retention
Performance Insights for query analysis

Resources Created

RDS Instance

Resource: aws_db_instance.main
Engine: PostgreSQL 15.4
Storage: gp3 SSD with auto-scaling
Encryption: Enabled with KMS
Accessibility: Private subnets only

Database Subnet Group

Resource: aws_db_subnet_group.main
Groups private subnets from networking module
RDS can fail over to any subnet in the group

Parameter Group

Resource: aws_db_parameter_group.postgres
Family: postgres15
Custom Parameters:
- log_min_duration_statement = 1000 - Log queries slower than 1 second
- log_connections = 1 - Log all connection attempts
- timezone = UTC - Consistent with Kubernetes cluster

IAM Role for Monitoring

Resource: aws_iam_role.rds_monitoring
Allows RDS to send enhanced monitoring metrics to CloudWatch
Attached policy: AmazonRDSEnhancedMonitoringRole

Variables

project_name

string

required

Project name for resource naming and tagging

environment

string

required

Deployment environment: dev, staging, or prodAffects:

Deletion protection (enabled in prod)
Final snapshot (created in prod, skipped in dev)

subnet_ids

list(string)

required

IDs of private subnets where RDS will be deployed. Use private_subnet_ids from networking module.

security_group_id

string

required

Security group ID allowing access from EKS to RDS on port 5432. Use rds_security_group_id from networking module.

db_instance_class

string

default:"db.t3.micro"

RDS instance class determining CPU and memory.Recommendations by environment:

db.t3.micro: Dev (1 vCPU, 1GB RAM) - ~$15/month
db.t3.small: Staging (2 vCPUs, 2GB RAM) - ~$30/month
db.r6g.large: Production (2 vCPUs, 16GB RAM) - ~$120/month

db_allocated_storage

number

default:20

Initial storage allocation in GB. Minimum 20 GB for gp3.

db_max_allocated_storage

number

default:100

Maximum storage for auto-scaling. Set to 0 to disable auto-scaling.RDS automatically increases storage when:

Free space falls below 10%
Low space persists for 5 minutes
6 hours have passed since last modification

db_name

string

default:"govtech"

Name of the initial database created on the instance

db_username

string

default:"govtech_admin"

Master username for PostgreSQL. Avoid using postgres or admin.

db_password

string

required

Master password for PostgreSQL.Security:

Do not hardcode in Terraform files
Use AWS Secrets Manager or environment variables
Minimum 8 characters
Should contain uppercase, lowercase, numbers, and symbols

multi_az

bool

default:false

Enable Multi-AZ deployment for high availability.

true: Synchronous replica in different AZ, automatic failover in 1-2 minutes
false: Single instance (recommended for dev to reduce costs)

Production: Always set to true

backup_retention_days

number

default:7

Number of days to retain automated backups (1-35).Recommendations:

Dev: 1-3 days
Staging: 7 days
Production: 14-30 days

Outputs

db_instance_id

string

RDS instance identifier

db_instance_endpoint

string

Connection endpoint in format hostname:port (e.g., db.abc.us-east-1.rds.amazonaws.com:5432)Sensitive: Contains internal hostname

db_instance_address

string

Hostname only, without port. Use for application configuration.

db_port

number

Database port (default: 5432)

db_name

string

Name of the initial database

db_username

string

Master username

db_subnet_group_name

string

Name of the DB subnet group

db_arn

string

ARN of the RDS instance for IAM policies and monitoring

Usage Example

module "database" {
  source = "./modules/database"

  project_name = "govtech"
  environment  = "prod"

  # Network configuration
  subnet_ids        = module.networking.private_subnet_ids
  security_group_id = module.networking.rds_security_group_id

  # Instance configuration
  db_instance_class      = "db.r6g.large"
  db_allocated_storage   = 50
  db_max_allocated_storage = 500

  # Database credentials
  db_name     = "govtech"
  db_username = "govtech_admin"
  db_password = var.db_password  # From terraform.tfvars or environment

  # High availability and backups
  multi_az               = true
  backup_retention_days  = 14
}

# Store connection info in Kubernetes secret
resource "kubernetes_secret" "database" {
  metadata {
    name      = "database-credentials"
    namespace = "default"
  }

  data = {
    host     = module.database.db_instance_address
    port     = module.database.db_port
    database = module.database.db_name
    username = module.database.db_username
    password = var.db_password
  }
}

Connection Configuration

Application Connection String

PostgreSQL URL format:

postgresql://username:password@hostname:5432/database

Example with outputs:

DATABASE_URL="postgresql://${module.database.db_username}:${var.db_password}@${module.database.db_instance_address}:${module.database.db_port}/${module.database.db_name}"

Using AWS Secrets Manager

Recommended for production:

# Store credentials after RDS creation
aws secretsmanager put-secret-value \
  --secret-id govtech/prod/db-credentials \
  --secret-string '{
    "username": "govtech_admin",
    "password": "your-secure-password",
    "host": "db-instance.abc.us-east-1.rds.amazonaws.com",
    "port": "5432",
    "dbname": "govtech"
  }'

Pods can retrieve credentials using IRSA (IAM Roles for Service Accounts).

Backup and Recovery

Automated Backups

Daily automated backups during maintenance window
Default backup window: 03:00-04:00 UTC
Retention: Configurable via backup_retention_days
Point-in-time recovery to any second within retention period

Manual Snapshots

Create manual snapshot before major changes:

aws rds create-db-snapshot \
  --db-instance-identifier govtech-prod-postgres \
  --db-snapshot-identifier govtech-prod-pre-migration-2024-03-01

Restore from Snapshot

aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier govtech-prod-restored \
  --db-snapshot-identifier govtech-prod-pre-migration-2024-03-01

Performance Monitoring

Enhanced Monitoring

Provides OS-level metrics every 60 seconds:

CPU utilization (detailed)
Memory usage
Disk I/O
Network throughput
Process list

Access in RDS console → Monitoring tab.

Performance Insights

Enabled by default with 7-day retention (free tier). Features:

Real-time query performance analysis
Top SQL statements by execution time
Database load visualization
Wait event analysis

Access in RDS console → Performance Insights tab.

CloudWatch Alarms

Create alarms for critical metrics:

resource "aws_cloudwatch_metric_alarm" "database_cpu" {
  alarm_name          = "govtech-prod-db-high-cpu"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = "2"
  metric_name         = "CPUUtilization"
  namespace           = "AWS/RDS"
  period              = "300"
  statistic           = "Average"
  threshold           = "80"
  alarm_description   = "Database CPU above 80%"
  
  dimensions = {
    DBInstanceIdentifier = module.database.db_instance_id
  }
}

Security Best Practices

Network Isolation

RDS deployed in private subnets only
No public accessibility (publicly_accessible = false)
Security group restricts access to EKS cluster only

Encryption

At rest: All data encrypted using KMS

In transit: Enforce SSL connections in application:

DATABASE_URL="postgresql://user:pass@host:5432/db?sslmode=require"

Deletion Protection

Enabled automatically in production:

Prevents accidental deletion
Must be manually disabled before destroying instance

Password Management

Never hardcode passwords:

# ❌ BAD - Password in code
db_password = "MyPassword123"

# ✅ GOOD - Password from variable
db_password = var.db_password

# ✅ BETTER - Password from Secrets Manager
data "aws_secretsmanager_secret_version" "db_password" {
  secret_id = "govtech/prod/db-master-password"
}

Maintenance and Updates

Maintenance Window

Default: Monday 04:00-05:00 UTC Scheduled maintenance includes:

OS patches
Database engine patches
Infrastructure updates

Customize maintenance window:

maintenance_window = "Sun:03:00-Sun:04:00"  # Sunday 3-4 AM UTC

Minor Version Upgrades

Automatically applied during maintenance window:

Example: PostgreSQL 15.4 → 15.5
Disabled with auto_minor_version_upgrade = false

Major Version Upgrades

Must be performed manually:

aws rds modify-db-instance \
  --db-instance-identifier govtech-prod-postgres \
  --engine-version 16.1 \
  --allow-major-version-upgrade \
  --apply-immediately

Test major upgrades in staging environment first.

Cost Optimization

Development Environments

db_instance_class        = "db.t3.micro"  # ~$15/month
multi_az                 = false
backup_retention_days    = 1
db_allocated_storage     = 20
db_max_allocated_storage = 50

Production Optimization

Use Reserved Instances for 1-3 year commitment (up to 60% savings)
Monitor storage growth and right-size max_allocated_storage
Use Performance Insights to identify and optimize slow queries
Consider Aurora Serverless for variable workloads

Troubleshooting

Cannot Connect from EKS

Verify security group allows port 5432 from EKS security group
Check pods are in same VPC as RDS

Test connectivity from pod:

kubectl run -it --rm debug --image=postgres:15 --restart=Never -- \
  psql -h db-host -U govtech_admin -d govtech

Storage Full

If auto-scaling is disabled or max limit reached:

aws rds modify-db-instance \
  --db-instance-identifier govtech-prod-postgres \
  --allocated-storage 100 \
  --apply-immediately

High CPU Usage

Check Performance Insights for slow queries
Review query execution plans
Add database indexes
Consider scaling up instance class

Terraform Modules

Kubernetes Resources

CI/CD

​Overview

​Resources Created

​RDS Instance

​Database Subnet Group

​Parameter Group

​IAM Role for Monitoring

​Variables

​Outputs

​Usage Example

​Connection Configuration

​Application Connection String

​Using AWS Secrets Manager

​Backup and Recovery

​Automated Backups

​Manual Snapshots

​Restore from Snapshot

​Performance Monitoring

​Enhanced Monitoring

​Performance Insights

​CloudWatch Alarms

​Security Best Practices

​Network Isolation

​Encryption

​Deletion Protection

​Password Management

​Maintenance and Updates

​Maintenance Window

​Minor Version Upgrades

​Major Version Upgrades

​Cost Optimization

​Development Environments

​Production Optimization

​Troubleshooting

​Cannot Connect from EKS

​Storage Full

​High CPU Usage

Build docs developers (and LLMs) love

Overview

Resources Created

RDS Instance

Database Subnet Group

Parameter Group

IAM Role for Monitoring

Variables

Outputs

Usage Example

Connection Configuration

Application Connection String

Using AWS Secrets Manager

Backup and Recovery

Automated Backups

Manual Snapshots

Restore from Snapshot

Performance Monitoring

Enhanced Monitoring

Performance Insights

CloudWatch Alarms

Security Best Practices

Network Isolation

Encryption

Deletion Protection

Password Management

Maintenance and Updates

Maintenance Window

Minor Version Upgrades

Major Version Upgrades

Cost Optimization

Development Environments

Production Optimization

Troubleshooting

Cannot Connect from EKS

Storage Full

High CPU Usage