mrvitej.in
Happy Dussehra! May good always win. Happy Diwali! Wishing you a bright year ahead. Merry Christmas! Warm wishes from Vizag. Happy New Year! Here’s to a great year ahead.

Archive · AWS

RDS: Multi-AZ, Backups and Restores

Run a relational database on RDS with Multi-AZ failover, automated backups and snapshots, point-in-time restore, encryption, parameter groups and a tested recovery plan.

4 min read

  • Amazon RDS
  • PostgreSQL 14
  • MySQL 8.0
  • Multi-AZ
  • Region ap-south-1

Amazon RDS runs MySQL, PostgreSQL, MariaDB, SQL Server and Oracle, handling provisioning, patching, backups and failover. You still own the schema, the queries, capacity choices and, importantly, proving that you can restore.

Versions in use on this date. PostgreSQL 14 and MySQL 8.0 on RDS. Aurora Serverless v2 reached general availability in April 2022 and suits spiky workloads. Pick the exact engine minor version from aws rds describe-db-engine-versions --engine postgres --query 'DBEngineVersions[].EngineVersion' rather than copying a number from a blog.

Topology

flowchart TB
  APP[Application in private subnets<br/>security group app-sg] -->|5432 from app-sg only| EP[DB endpoint DNS name]
  subgraph VPC[VPC with DB subnet group in two AZs]
    EP --> PRI[Primary instance AZ a]
    PRI -->|synchronous replication| STB[Standby instance AZ b<br/>not readable]
    PRI -->|asynchronous| RR[Read replica optional]
  end
  PRI --> BK[Automated backups<br/>daily snapshot plus transaction logs in S3]
  PRI --> SNAP[Manual snapshots<br/>kept until deleted]
  BK -->|point-in-time restore| NEW[New instance with new endpoint]
  SNAP -->|restore| NEW
  PRI -.->|failover| STB

What each feature does

Feature What it does What it does not do
Multi-AZ Synchronous standby in another AZ; automatic failover, usually 60 to 120 seconds, the endpoint DNS name stays the same Does not give you a readable second copy, protect against bad queries or deleted data
Automated backups Daily snapshot plus transaction logs, retained 1 to 35 days, enabling restore to any second Do not survive deleting the instance unless you choose to retain them
Manual snapshots Point-in-time copies kept until you delete them; can be copied across regions and shared Not scheduled by themselves
Read replicas Scale reads, can be promoted, can live in another region Replication is asynchronous, so they lag and are not a substitute for Multi-AZ
Storage autoscaling Grows storage up to a maximum you set Does not shrink it

Create a production instance

aws rds create-db-subnet-group --db-subnet-group-name shop-private \
  --db-subnet-group-description "Private subnets" --subnet-ids subnet-0a1 subnet-0b2

aws rds create-db-instance --db-instance-identifier shop-db \
  --engine postgres --engine-version <14.x-minor> --db-instance-class db.t3.medium \
  --allocated-storage 50 --max-allocated-storage 200 --storage-type gp2 --storage-encrypted \
  --multi-az --backup-retention-period 14 --preferred-backup-window 21:00-22:00 \
  --preferred-maintenance-window sun:22:30-sun:23:30 \
  --db-subnet-group-name shop-private --vpc-security-group-ids sg-0abc123 \
  --no-publicly-accessible --deletion-protection --copy-tags-to-snapshot \
  --master-username dbadmin --master-user-password "$(openssl rand -base64 24)" --region ap-south-1

Store the generated password straight into Secrets Manager (do not paste it in chat or tickets), and plan to rotate it. Choose gp3 storage as it becomes available for your engine, and move to larger classes (m6g, r6g) when CPU, memory or IOPS metrics show pressure; burstable t3 credits can run out under sustained load.

Notes:

  • Keep the database in private subnets; allow the port only from the application’s security group, never 0.0.0.0/0.
  • Encryption at rest must be chosen at creation. To encrypt an existing database, copy a snapshot with encryption enabled and restore from it.
  • Use a parameter group for settings (log_min_duration_statement, rds.force_ssl) and a custom one per engine version, because defaults cannot be edited.
  • Require TLS and download the regional CA bundle for clients.
  • Enable Performance Insights and publish the PostgreSQL logs to CloudWatch for slow-query analysis.

Backups and restores

aws rds create-db-snapshot --db-instance-identifier shop-db --db-snapshot-identifier shop-db-before-migration
aws rds restore-db-instance-to-point-in-time --source-db-instance-identifier shop-db \
  --target-db-instance-identifier shop-db-restore --restore-time 2022-10-03T09:15:00Z
aws rds wait db-instance-available --db-instance-identifier shop-db-restore

A restore always creates a new instance with a new endpoint, default parameter and option groups, and a default security group unless you specify them. After restoring, you compare data, then either repoint the application or copy back the rows you need. Time depends on size; measure it, because that is your real recovery time.

For disaster recovery in another region, copy snapshots (or use AWS Backup with cross-region copy, or a cross-region read replica you can promote). Encrypted snapshots need a KMS key in the destination region.

Failover and maintenance behaviour

  • Failover happens on host failure, AZ problems, instance class changes and some patching. Applications must reconnect and retry; keep connection pool timeouts short and use the DNS endpoint, not an IP.
  • Test it: aws rds reboot-db-instance --db-instance-identifier shop-db --force-failover in a non-production copy, and watch the application behave.
  • Apply minor version updates in the maintenance window, after trying them on a copy.

Monitoring and runbook

Alarm on CPUUtilization, FreeableMemory, FreeStorageSpace, DatabaseConnections, ReplicaLag, ReadLatency/WriteLatency and BurstBalance/credit balance. Subscribe to RDS events (failover, backup failure) through SNS.

Restore drill, quarterly: restore the latest backup to a temporary instance, run integrity checks and a sample query, record the elapsed time, then delete it. A backup you have never restored is only a hope.