AWS RDS and Aurora skill for Codex

Done well means: the database survives an AZ failure with less than a minute of errors that the application retries through, parameter changes are tracked in code and applied in a known reboot window, upgrades are rehearsed on a clone first, and a restore has been tested rather than assumed.

Before changing anything

1. Read AGENTS.md and the IaC defining the database. Note engine, version, instance class, Multi-AZ, parameter groups, backup retention and deletion protection.
2. Inspect the live state (needs network and credentials):

aws rds describe-db-clusters --db-cluster-identifier orders \
  --query 'DBClusters[0].{engine:Engine,ver:EngineVersion,members:DBClusterMembers,pg:DBClusterParameterGroup,backup:BackupRetentionPeriod,delprot:DeletionProtection,storage:StorageType}'
aws rds describe-db-instances --query 'DBInstances[].{id:DBInstanceIdentifier,class:DBInstanceClass,ver:EngineVersion,maz:MultiAZ,pg:DBParameterGroups,pending:PendingModifiedValues}'
aws rds describe-pending-maintenance-actions

3. Check engine support dates. Running past the end of standard support enrolls the database in RDS Extended Support, which is billed per vCPU-hour and can exceed the instance cost. Upgrades are often a cost item, not just hygiene.

Choosing the shape

Need	Choose
Small or medium app, predictable load, lowest cost	RDS PostgreSQL/MySQL, Multi-AZ instance deployment
Fast failover, up to 15 low-lag readers, storage that grows automatically	Aurora provisioned cluster
Spiky or dev/test load, idle periods	Aurora Serverless v2 (min capacity can be 0 ACU with auto-pause)
Readable standbys and faster commit latency on RDS	RDS Multi-AZ DB cluster (one writer, two readable standbys)
Global reads or cross-region DR	Aurora Global Database

Aurora storage: Standard bills I/O per request; I/O-Optimized has no I/O charges but higher instance and storage prices. Switch to I/O-Optimized when I/O is more than roughly a quarter of the cluster’s spend.

Parameter groups

• Never edit the default.* groups; you cannot. Create custom groups in IaC and attach them at creation to avoid a later reboot.
• Aurora has two levels: the DB cluster parameter group (cluster-wide settings such as rds.logical_replication, binlog_format) and the DB parameter group (per instance, such as work_mem).
• ApplyType tells you the cost: dynamic applies immediately; static needs a reboot and shows pending-reboot until then.

aws rds describe-db-parameters --db-parameter-group-name orders-pg16 \
  --query "Parameters[?ParameterName=='shared_preload_libraries'].[ParameterValue,ApplyType]"

Useful PostgreSQL settings to set deliberately: log_min_duration_statement (e.g. 500 ms), shared_preload_libraries including pg_stat_statements, idle_in_transaction_session_timeout, rds.force_ssl = 1 (the default on recent versions). Leave memory settings that are formulas of DBInstanceClassMemory alone unless you have measurements.

Connections and failover

Failover on Multi-AZ RDS takes about one to two minutes; on Aurora usually under 30 seconds. In both cases the endpoint DNS name moves to a new host and existing connections die. Design for it:

• Connect through the cluster (writer) endpoint for writes and the reader endpoint for reads; never to an instance endpoint in application config.
• Keep client DNS caching short. The JVM caches DNS indefinitely with a security manager; set networkaddress.cache.ttl=5.
• Retry transient errors (connection reset, read-only transaction after failover) with backoff, and make the retried operations idempotent.
• Use RDS Proxy for Lambda and other bursty connection sources, and to cut failover impact: the proxy holds client connections while it reconnects to the new writer. Proxy pins sessions that use session state (temp tables, SET), which removes the pooling benefit; watch the pinning metric.
• For Aurora, the AWS Advanced JDBC/Python/Node wrappers add topology-aware fast failover.

Test it: aws rds failover-db-cluster --db-cluster-identifier orders (Aurora) or aws rds reboot-db-instance --db-instance-identifier orders --force-failover (RDS Multi-AZ). This causes a real outage window, so run it in staging first and in production only with user confirmation.

Backups and restore

• Automated backups: retention 1-35 days, point-in-time restore to any second within it. Set at least 7 days in production. Manual snapshots persist until deleted.
• A restore always creates a new instance or cluster; it never overwrites the old one.

aws rds restore-db-cluster-to-point-in-time \
  --source-db-cluster-identifier orders \
  --db-cluster-identifier orders-restore-0930 \
  --restore-to-time 2026-03-14T09:30:00Z \
  --db-subnet-group-name db-private --vpc-security-group-ids sg-0abc
# Aurora then needs an instance in the restored cluster:
aws rds create-db-instance --db-instance-identifier orders-restore-0930-1 \
  --db-cluster-identifier orders-restore-0930 --engine aurora-postgresql --db-instance-class db.r7g.large

• Aurora fast clone (restore-db-cluster-to-point-in-time --restore-type copy-on-write --use-latest-restorable-time) creates a test copy in minutes without copying storage.
• Turn on DeletionProtection and require --final-db-snapshot-identifier in any delete. Deleting a database, its snapshots, or reducing backup retention needs explicit user confirmation.

Upgrades

Upgrade	Approach
Minor version	AutoMinorVersionUpgrade in the maintenance window, or apply manually after testing
Major version, downtime of tens of minutes acceptable	In-place modify-db-* --engine-version X --allow-major-version-upgrade after a snapshot
Major version, near-zero downtime	RDS Blue/Green Deployment, then switchover

Blue/Green workflow:

• Clone or restore production to staging; upgrade it; run the test suite and the top queries from pg_stat_statements with EXPLAIN to catch planner regressions
• Create a new parameter group for the target major version
• aws rds create-blue-green-deployment --blue-green-deployment-name orders-pg17 --source <arn> --target-engine-version 17.x --target-db-cluster-parameter-group-name orders-pg17
• Wait until the green environment is AVAILABLE; check replication lag
• User confirms the switchover time
• aws rds switchover-blue-green-deployment --blue-green-deployment-identifier <id> --switchover-timeout 300
• Verify application health, then delete the old blue environment only after a soak period and with confirmation

PostgreSQL specifics: logical replication (used by Blue/Green) needs primary keys or replica identity on tables, does not replicate DDL during the blue/green window, and extensions must be upgraded (ALTER EXTENSION ... UPDATE) after major upgrades. Run ANALYZE after a major upgrade; planner statistics are not carried over.

Monitoring

• Metrics: CPUUtilization, FreeableMemory, DatabaseConnections, ReplicaLag / AuroraReplicaLag, FreeStorageSpace (RDS), DiskQueueDepth, burst balance on gp2.
• Query-level: pg_stat_statements plus Performance Insights / CloudWatch Database Insights for wait events.
• Alarm on storage below 15% free (RDS) and enable storage autoscaling with a maximum.

Gotchas

• ApplyImmediately: false defers instance class and some setting changes to the next maintenance window; IaC shows success while nothing changed yet. Check PendingModifiedValues.
• Changing instance class causes a restart (a failover on Multi-AZ). Schedule it.
• Burstable db.t* classes run out of CPU credits under steady load; production databases belong on m/r classes.
• Snapshots of encrypted databases share only with accounts that can use the KMS key; the AWS-managed aws/rds key cannot be shared, so use a customer-managed key for cross-account DR.
• Public accessibility should be off; reach the database through a bastion, SSM port forwarding or VPN.
• Storage on RDS can grow but never shrink in place.

Output

Report: current and target configuration, parameter changes with apply type, the upgrade or restore plan with its downtime estimate and rollback path, and what was verified in staging.