RDS Mastery: PostgreSQL, MySQL, and Aurora with Backups, Replicas, and High Availability

RDS Mastery: PostgreSQL, MySQL, and Aurora with Backups, Replicas, and High Availability

Introduction

Your Rails application is growing. Database queries are slowing down, manual backups are forgotten, disaster recovery is non-existent, and scaling requires downtime for hardware upgrades. You’re spending more time managing PostgreSQL servers than building features. Patching, backups, replication, monitoring—it’s all manual, error-prone, and time-consuming.

Amazon Relational Database Service (RDS) eliminates the operational burden of running databases. It’s not just “PostgreSQL in the cloud”—it’s automated backups, point-in-time recovery, automatic failover, read replicas for scaling, and managed maintenance windows. You get enterprise-grade database reliability without the enterprise-grade database administrator.

But RDS isn’t just clicking “Launch Database.” It’s understanding instance classes, configuring Multi-AZ for high availability, implementing read replicas for read-heavy workloads, optimizing storage performance, securing connections, and choosing between RDS PostgreSQL and Amazon Aurora.

In this guide, we’ll master RDS: launching databases, configuring backups and recovery, implementing Multi-AZ deployments, scaling with read replicas, and building production architectures that handle millions of transactions.

What Is Amazon RDS?

RDS is a managed relational database service supporting multiple engines:

Engine	Use Case
PostgreSQL	Open-source, advanced features, full Rails support
MySQL	Open-source, widely used, WordPress, Drupal
MariaDB	MySQL fork, community-driven
Amazon Aurora	AWS-built, MySQL/PostgreSQL compatible, 5x performance
Oracle	Enterprise applications, legacy systems
SQL Server	Microsoft stack, .NET applications

RDS vs Self-Managed Databases

Task	Self-Managed	RDS
Provisioning	Install, configure OS, DB	Click button
Patching	Manual apt upgrade	Automatic during maintenance window
Backups	Cron jobs, scripts	Automated, point-in-time recovery
High Availability	Configure replication manually	Enable Multi-AZ
Scaling	Downtime for hardware	Click to change instance type
Monitoring	Install Prometheus, Grafana	Built-in CloudWatch

Creating Your First RDS Instance

Step 1: Choose Database Engine

For Rails applications, PostgreSQL is standard:

# Launch PostgreSQL 15 instance
aws rds create-db-instance \
  --db-instance-identifier myapp-production-db \
  --db-instance-class db.t3.medium \
  --engine postgres \
  --engine-version 15.4 \
  --master-username dbadmin \
  --master-user-password 'SecurePassword123!' \
  --allocated-storage 100 \
  --storage-type gp3 \
  --storage-encrypted \
  --vpc-security-group-ids sg-0abc123 \
  --db-subnet-group-name myapp-db-subnet-group \
  --backup-retention-period 7 \
  --preferred-backup-window "03:00-04:00" \
  --preferred-maintenance-window "mon:04:00-mon:05:00" \
  --multi-az \
  --publicly-accessible false \
  --enable-cloudwatch-logs-exports '["postgresql"]' \
  --tags Key=Environment,Value=production Key=Application,Value=myapp

Key parameters:

• db-instance-class: Instance size (CPU/RAM)
• allocated-storage: Initial storage (GB)
• multi-az: High availability across AZs
• backup-retention-period: Days to retain backups
• publicly-accessible: false for security (access via VPC only)

Step 2: Create DB Subnet Group

RDS requires subnet group (minimum 2 subnets in different AZs):

aws rds create-db-subnet-group \
  --db-subnet-group-name myapp-db-subnet-group \
  --db-subnet-group-description "Subnet group for myapp database" \
  --subnet-ids subnet-0abc123 subnet-0def456 \
  --tags Key=Name,Value=myapp-db-subnets

Step 3: Wait for Availability

# Wait for database to be available
aws rds wait db-instance-available --db-instance-identifier myapp-production-db

# Get endpoint
aws rds describe-db-instances \
  --db-instance-identifier myapp-production-db \
  --query 'DBInstances[0].Endpoint.Address' \
  --output text

Output:

myapp-production-db.abc123.us-east-1.rds.amazonaws.com

Step 4: Connect from Rails

database.yml:

production:
  adapter: postgresql
  encoding: unicode
  database: myapp_production
  username: <%= ENV['DB_USERNAME'] %>
  password: <%= ENV['DB_PASSWORD'] %>
  host: myapp-production-db.abc123.us-east-1.rds.amazonaws.com
  port: 5432
  pool: 5

Environment variables (stored in AWS Secrets Manager):

export DB_USERNAME=dbadmin
export DB_PASSWORD=SecurePassword123!

Test connection:

rails db:create
rails db:migrate

RDS Instance Classes

Choose instance class based on workload:

Class	Type	vCPU	RAM	Use Case
db.t3.micro	Burstable	2	1 GB	Dev/test
db.t3.medium	Burstable	2	4 GB	Small production
db.m6i.large	General Purpose	2	8 GB	Balanced workloads
db.m6i.2xlarge	General Purpose	8	32 GB	Standard production
db.r6i.xlarge	Memory Optimized	4	32 GB	High-memory workloads
db.r6i.4xlarge	Memory Optimized	16	128 GB	Large databases

Choosing instance class:

• Small apps (<1000 users): db.t3.medium
• Medium apps (1000-10000 users): db.m6i.large or db.r6i.large
• Large apps (>10000 users): db.r6i.xlarge or larger

Note: Memory-optimized (r6i) is better for databases (more cache).

Storage Options

Storage Types

Type	IOPS	Throughput	Use Case	Cost
gp3	3,000-16,000	125-1,000 MB/s	General purpose	$0.115/GB-month
gp2	Baseline 3 IOPS/GB	Burst to 3,000	Legacy	$0.115/GB-month
io1	Up to 64,000	Up to 1,000 MB/s	High performance	$0.125/GB-month + IOPS cost

Recommendation: Use gp3 for most workloads (better performance than gp2, same cost).

Storage Autoscaling

Enable autoscaling to avoid running out of disk space:

aws rds modify-db-instance \
  --db-instance-identifier myapp-production-db \
  --max-allocated-storage 1000 \
  --apply-immediately

How it works:

• Initial storage: 100GB
• Autoscaling enabled: up to 1000GB
• RDS automatically increases storage when < 10% free
• No downtime for storage expansion

Automated Backups

RDS automatically backs up your entire database.

Backup Configuration

aws rds modify-db-instance \
  --db-instance-identifier myapp-production-db \
  --backup-retention-period 30 \
  --preferred-backup-window "03:00-04:00"

Backup behavior:

• Retention: 0-35 days (0 = disabled)
• Frequency: Daily automatic snapshots
• Window: Specify time (low-traffic period)
• Performance: Brief I/O suspension for Multi-AZ

Manual Snapshots

# Create manual snapshot
aws rds create-db-snapshot \
  --db-instance-identifier myapp-production-db \
  --db-snapshot-identifier myapp-before-migration-2024-01-15

# List snapshots
aws rds describe-db-snapshots \
  --db-instance-identifier myapp-production-db

# Restore from snapshot
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier myapp-restored \
  --db-snapshot-identifier myapp-before-migration-2024-01-15

Use cases:

• Before major migrations
• Before schema changes
• Compliance/audit requirements

Point-in-Time Recovery

Restore database to any point within retention period:

# Restore to 5 minutes ago
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier myapp-production-db \
  --target-db-instance-identifier myapp-recovered \
  --restore-time $(date -u -d '5 minutes ago' +%Y-%m-%dT%H:%M:%SZ)

# Or restore to latest restorable time
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier myapp-production-db \
  --target-db-instance-identifier myapp-recovered \
  --use-latest-restorable-time

Use cases:

• Accidental data deletion
• Corruption recovery
• Testing rollback scenarios

Multi-AZ Deployments

Multi-AZ provides high availability through synchronous replication.

Architecture

Primary DB (us-east-1a)
     ↓ Synchronous replication
Standby DB (us-east-1b)
     ↓ Automatic failover (60-120 seconds)
Application connects to endpoint (always points to primary)

Enable Multi-AZ

aws rds modify-db-instance \
  --db-instance-identifier myapp-production-db \
  --multi-az \
  --apply-immediately

How it works:

1. RDS creates standby replica in different AZ
2. Synchronous replication (all writes replicated)
3. Automatic health checks
4. If primary fails, DNS endpoint updates to standby (60-120s)
5. Application reconnects automatically

When failover occurs:

• AZ outage
• Primary instance failure
• Network connectivity loss
• Storage failure
• Manual failover (for testing)

Test Failover

aws rds reboot-db-instance \
  --db-instance-identifier myapp-production-db \
  --force-failover

Application behavior during failover:

15:00:00: Primary fails
15:00:05: RDS detects failure
15:00:30: DNS updates to standby
15:01:00: New connections succeed
15:01:30: All active connections restored

Best practice: Configure connection pool to retry (ActiveRecord does this automatically).

Read Replicas

Read replicas scale read traffic by creating read-only copies.

Architecture

Primary (Write)
  ↓ Asynchronous replication
Read Replica 1 (Read)
Read Replica 2 (Read)
Read Replica 3 (Read)

Create Read Replica

# Create read replica in same region
aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-read-replica-1 \
  --source-db-instance-identifier myapp-production-db \
  --db-instance-class db.m6i.large \
  --publicly-accessible false

# Create read replica in different region (cross-region)
aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-read-replica-eu \
  --source-db-instance-identifier myapp-production-db \
  --db-instance-class db.m6i.large \
  --source-region us-east-1 \
  --region eu-west-1

Use Read Replicas in Rails

database.yml:

production:
  primary:
    adapter: postgresql
    host: myapp-production-db.abc123.us-east-1.rds.amazonaws.com
    # Write queries go here
  
  read_replica:
    adapter: postgresql
    host: myapp-read-replica-1.abc123.us-east-1.rds.amazonaws.com
    replica: true
    # Read queries go here

Application code:

# Explicitly use replica
ActiveRecord::Base.connected_to(role: :reading) do
  @users = User.where(active: true).limit(100)
end

# Or configure automatic read routing
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
  connects_to database: { writing: :primary, reading: :read_replica }
end

# Rails automatically routes reads to replica
User.find(1)  # Reads from replica
User.create!(name: "John")  # Writes to primary

Monitoring Replication Lag

aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name ReplicaLag \
  --dimensions Name=DBInstanceIdentifier,Value=myapp-read-replica-1 \
  --start-time 2024-01-15T00:00:00Z \
  --end-time 2024-01-15T23:59:59Z \
  --period 300 \
  --statistics Average

Acceptable lag: < 1 second for most applications

High lag causes:

• Heavy write traffic on primary
• Complex transactions
• Under-provisioned replica (increase instance class)

Promote Read Replica

Convert read replica to standalone database:

aws rds promote-read-replica \
  --db-instance-identifier myapp-read-replica-1

Use cases:

• Disaster recovery (promote replica if primary fails)
• Create test database from production
• Regional expansion (promote cross-region replica)

Amazon Aurora

Aurora is AWS’s cloud-native database, MySQL/PostgreSQL compatible.

Aurora vs RDS PostgreSQL

Feature	RDS PostgreSQL	Aurora PostgreSQL
Performance	Standard	3x faster (AWS claims)
Storage	EBS-based	Distributed, self-healing
Scaling	Vertical (change instance)	Horizontal (add read replicas)
Failover	60-120 seconds	<30 seconds
Replicas	5 max	15 max
Cost	Lower for small DBs	Higher, but better performance

When to Use Aurora

✅ Use Aurora if:

• High read traffic (need many replicas)
• Need fast failover (<30s)
• Require global databases (multi-region)
• Large database (>1TB)

❌ Use RDS PostgreSQL if:

• Small database (<100GB)
• Predictable, low traffic
• Cost-sensitive
• Need specific PostgreSQL extensions not in Aurora

Create Aurora Cluster

# Create Aurora PostgreSQL cluster
aws rds create-db-cluster \
  --db-cluster-identifier myapp-aurora-cluster \
  --engine aurora-postgresql \
  --engine-version 15.4 \
  --master-username admin \
  --master-user-password 'SecurePassword123!' \
  --vpc-security-group-ids sg-0abc123 \
  --db-subnet-group-name myapp-db-subnet-group \
  --backup-retention-period 7

# Create primary instance
aws rds create-db-instance \
  --db-instance-identifier myapp-aurora-instance-1 \
  --db-instance-class db.r6g.large \
  --engine aurora-postgresql \
  --db-cluster-identifier myapp-aurora-cluster

# Create read replica
aws rds create-db-instance \
  --db-instance-identifier myapp-aurora-instance-2 \
  --db-instance-class db.r6g.large \
  --engine aurora-postgresql \
  --db-cluster-identifier myapp-aurora-cluster

Aurora architecture:

Cluster Endpoint (write)
  └─→ Primary Instance
Reader Endpoint (read, load-balanced)
  ├─→ Replica 1
  ├─→ Replica 2
  └─→ Replica 3

Security Best Practices

1. Never Expose Publicly

# Ensure publicly-accessible is false
aws rds modify-db-instance \
  --db-instance-identifier myapp-production-db \
  --no-publicly-accessible \
  --apply-immediately

Access database only from:

• EC2 instances in same VPC
• VPN connection
• AWS Lambda functions

2. Use Security Groups

# Database security group
aws ec2 create-security-group \
  --group-name rds-postgres-sg \
  --description "Security group for PostgreSQL RDS" \
  --vpc-id vpc-0abc123

# Allow PostgreSQL from app servers only
aws ec2 authorize-security-group-ingress \
  --group-id sg-rds123 \
  --protocol tcp \
  --port 5432 \
  --source-group sg-app-servers

3. Enable Encryption at Rest

# Enable encryption on new instance
aws rds create-db-instance \
  --db-instance-identifier myapp-encrypted-db \
  --storage-encrypted \
  --kms-key-id arn:aws:kms:us-east-1:123456789012:key/abc-123 \
  ...

Note: Cannot enable encryption on existing unencrypted instance. Must create encrypted snapshot and restore.

4. Enable IAM Database Authentication

# Enable IAM auth
aws rds modify-db-instance \
  --db-instance-identifier myapp-production-db \
  --enable-iam-database-authentication \
  --apply-immediately

Connect without password:

# Generate auth token
TOKEN=$(aws rds generate-db-auth-token \
  --hostname myapp-production-db.abc123.us-east-1.rds.amazonaws.com \
  --port 5432 \
  --username iamuser)

# Connect using token as password
psql "host=myapp-production-db.abc123.us-east-1.rds.amazonaws.com port=5432 dbname=myapp user=iamuser password=$TOKEN sslmode=require"

Monitoring and Performance

Key CloudWatch Metrics

# CPU utilization
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name CPUUtilization \
  --dimensions Name=DBInstanceIdentifier,Value=myapp-production-db \
  --start-time 2024-01-15T00:00:00Z \
  --end-time 2024-01-15T23:59:59Z \
  --period 300 \
  --statistics Average

# Database connections
aws cloudwatch get-metric-statistics \
  --metric-name DatabaseConnections \
  ...

# Free storage space
aws cloudwatch get-metric-statistics \
  --metric-name FreeStorageSpace \
  ...

Critical metrics:

• CPUUtilization: > 80% sustained = scale up
• FreeableMemory: Low memory = scale up or optimize queries
• ReadLatency/WriteLatency: High latency = storage issue
• DatabaseConnections: Near max = connection pool issue

Performance Insights

Enable Performance Insights for query-level monitoring:

aws rds modify-db-instance \
  --db-instance-identifier myapp-production-db \
  --enable-performance-insights \
  --performance-insights-retention-period 7

View slowest queries, wait events, and bottlenecks in AWS Console.

Real-World Architecture: High-Availability Rails Application

Requirements

• Zero data loss tolerance
• < 2 minutes downtime for failover
• Handle 10,000 read queries/second
• Automated backups with 30-day retention

Architecture

Application Servers (Auto Scaling Group)
  │
  ├─→ Write queries → Primary RDS (Multi-AZ)
  │                    ├─ Primary (us-east-1a)
  │                    └─ Standby (us-east-1b)
  │
  └─→ Read queries → Read Replicas (3x)
                      ├─ Replica 1 (us-east-1a)
                      ├─ Replica 2 (us-east-1b)
                      └─ Replica 3 (us-east-1c)

Implementation

1. Primary database with Multi-AZ:

aws rds create-db-instance \
  --db-instance-identifier myapp-primary \
  --db-instance-class db.r6i.2xlarge \
  --engine postgres \
  --allocated-storage 500 \
  --storage-type gp3 \
  --iops 12000 \
  --multi-az \
  --backup-retention-period 30 \
  --enable-performance-insights

2. Read replicas (3x):

for i in 1 2 3; do
  aws rds create-db-instance-read-replica \
    --db-instance-identifier myapp-read-$i \
    --source-db-instance-identifier myapp-primary \
    --db-instance-class db.r6i.xlarge
done

3. Rails configuration:

production:
  primary:
    adapter: postgresql
    host: <%= ENV['DB_PRIMARY_HOST'] %>
  replica_1:
    adapter: postgresql
    host: <%= ENV['DB_REPLICA_1_HOST'] %>
    replica: true
  replica_2:
    adapter: postgresql
    host: <%= ENV['DB_REPLICA_2_HOST'] %>
    replica: true
  replica_3:
    adapter: postgresql
    host: <%= ENV['DB_REPLICA_3_HOST'] %>
    replica: true

Result:

• Availability: 99.95% (Multi-AZ failover)
• Read capacity: 30,000+ queries/second
• Write capacity: 10,000+ queries/second
• Recovery: 30-day point-in-time recovery

Cost Optimization

Right-Size Instances

Monitor CPU and memory usage:

• CPU < 40% consistently → downsize instance
• CPU > 80% sustained → upsize instance

Reserved Instances

Commit to 1-3 years for discounts:

Term	Discount
1 year, no upfront	~30%
1 year, all upfront	~40%
3 years, all upfront	~60%

Example: db.r6i.xlarge on-demand = $0.504/hour

• Reserved (3 years): $0.302/hour
• Savings: ~$3,500/year

Stop Development Databases

# Stop instance (still pay for storage, not compute)
aws rds stop-db-instance --db-instance-identifier myapp-dev

# Start when needed
aws rds start-db-instance --db-instance-identifier myapp-dev

Savings: ~70% cost reduction for dev/test databases.

Conclusion

AWS RDS transforms database management from an operational burden into a managed utility. By mastering Multi-AZ deployments, read replicas, automated backups, and performance monitoring, you architect database infrastructure that scales reliably without manual intervention.

The shift from self-managed databases to RDS, from manual backups to automated point-in-time recovery, and from single-instance fragility to Multi-AZ resilience transforms databases from a risk into a strength.

Start simple: launch a single RDS instance, connect your application, enable automated backups. Then evolve: enable Multi-AZ, add read replicas, optimize costs with reserved instances. Every iteration makes your database more resilient and your operations simpler.

Master RDS, and you master cloud databases.

Suggested Reading

• AWS RDS Official Documentation
• RDS PostgreSQL Documentation
• Amazon Aurora Documentation
• RDS Best Practices
• RDS Security
• Performance Insights