Theory, every command, and every measured result — building RDS and Aurora PostgreSQL from scratch in a private-subnet lab, CLI only. Each section explains the concept first, then shows the commands, then the real numbers and errors I hit.
Table of contents
- Why I did this
- Two things that changed since I last certified
- The mental shift that matters more than any command
- Part 1 — The network your database lives in
- Part 2 — RDS anatomy and launching it properly
- Part 3 — Connecting from a no-internet VPC
- Part 4 — Observability and the diagnosis loop
- Part 5 — Backup, restore, and recovery
- Part 6 — High availability and read replicas
- Part 7 — Aurora architecture
- RDS vs Aurora — the measured comparison
- Certification exam-style questions
- Interview questions
- Full teardown
- What’s next
The one habit that prevents most errors. Commands marked laptop are control-plane (
aws …) run from your workstation. Commands marked bastion are data-plane (psql,pgbench) run inside the VPC via SSM. Control-plane outside, data-plane inside. Get this wrong and you’ll spend the day debugging hangs and “could not translate host name” errors — theawsAPI and the database live on different network paths.
Why I did this
I’ve been a database architect for years and earned an AWS certification about a decade ago. When a current project pushed toward AWS certification again, I assumed I’d dust off what I knew. I was wrong before I even started.
So I gave myself one week, a rule of doing everything from the command line — because console wizards make the important decisions for you and hide them — and a private-subnet lab with no shortcuts. Everything below I ran myself, and every number is one I measured.
Two things that changed since I last certified
The AWS Certified Database – Specialty exam no longer exists. AWS retired it on 30 April 2024, along with Data Analytics – Specialty and SAP on AWS – Specialty, as part of a shift toward role-based rather than technology-based credentials. There’s no direct replacement. For a database person today the honest targets are Data Engineer – Associate (DEA-C01), Solutions Architect – Professional (SAP-C02), and — if your project is compliance-driven — Security – Specialty (SCS-C03). Also note SysOps Administrator – Associate was renamed CloudOps Engineer – Associate.
Free Tier is credit-based now. Since 15 July 2025, new accounts no longer get the old 12-month per-service allowance. You pick a Free Plan or Paid Plan, both starting with $100 in credits plus another $100 earnable, and the Free Plan ends at six months or when credits run out, whichever comes first. One genuinely useful recent change: Aurora PostgreSQL Serverless v2 joined the Free Tier in March 2026, which is what made the Aurora half of this lab affordable.
The cost traps that will actually get you, in order of likelihood: VPC interface endpoints (~$7/month each, and you’ll create three), NAT Gateway (~$33/month just to exist — I avoided it entirely), and orphaned snapshots and unattached volumes that survive instance deletion and bill silently for years.
The mental shift that matters more than any command
You already know databases. What changes in AWS isn’t the database — it’s that the boundary of your responsibility moves, and things you used to control with a config file are now controlled by API, IAM policy, and network topology. Three reframings I had to internalise:
Failure is routine, not an incident. On-premises you build HA to survive rare hardware failures. In AWS, an instance can be retired with notice or replaced during a maintenance window. Multi-AZ isn’t insurance — it’s the normal operating configuration.
Storage is a network service, not a disk. RDS storage is remote block storage reached over the network. Aurora goes further and has no local storage at all. This single fact explains most of the performance and durability behaviour you’ll see this week.
The endpoint is a DNS name, and that is load-bearing. Failover in AWS is usually a DNS record change, not an IP takeover. Your application’s DNS caching behaviour is therefore part of your availability design. This catches almost everyone once — it caught me.
Part 1 — The network your database lives in
The theory
A VPC is a logically isolated network you define with a CIDR block, existing in one region. A subnet is a slice of that CIDR living in exactly one Availability Zone — and an AZ is one or more physically separate data centres with independent power, connected to sibling AZs by low-latency fibre (typically 1–2 ms round trip). That latency figure matters: it’s why synchronous replication across AZs is viable but synchronous replication across regions generally isn’t.
A subnet is public only if its route table has a route to an Internet Gateway. Otherwise it’s private, and that’s where databases belong — always. If you ever set --publicly-accessible, you’re almost certainly solving a connectivity problem the wrong way.
RDS requires a DB subnet group listing subnets in at least two AZs, even for a single-AZ instance. The reason is forward-looking: RDS needs somewhere to put a standby if you later enable Multi-AZ, and somewhere to place a replacement if an AZ becomes unavailable. It reserves the option at creation time.
Security groups versus NACLs is worth getting straight. A security group is stateful (return traffic is auto-allowed), attaches to the instance, and — critically — a rule can reference another security group as its source. Allow 5432 from sg-app means “any instance carrying the app security group may reach me,” regardless of IP, regardless of how the app tier scales. That’s the AWS-native equivalent of a role-based firewall rule, and it’s the correct pattern. CIDR rules for internal traffic are a code smell. NACLs are stateless, subnet-level, and best used sparingly as a coarse backstop.
Finally, reaching a private database without an SSH bastion: the modern pattern is AWS Systems Manager Session Manager. The instance makes an outbound connection to SSM; you connect through the AWS API. No inbound rules, no SSH keys, no public IP, every session logged. For it to work from a private subnet with no internet route, you need VPC interface endpoints for ssm, ssmmessages, and ec2messages. There are two endpoint flavours worth knowing: gateway endpoints (S3 and DynamoDB only, a route-table entry, free) and interface endpoints (everything else, an ENI, priced hourly).
The commands
CLI access via IAM Identity Center (short-lived credentials, never long-lived access keys), then a budget guardrail before any resources exist:
# laptop
aws configure sso
export AWS_PROFILE=dblab AWS_REGION=us-east-1
aws sts get-caller-identity
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
cat > budget.json <<'EOF'
{ "BudgetName":"dblab-monthly","BudgetLimit":{"Amount":"20","Unit":"USD"},
"TimeUnit":"MONTHLY","BudgetType":"COST" }
EOF
cat > notify.json <<'EOF'
[{ "Notification":{"NotificationType":"ACTUAL","ComparisonOperator":"GREATER_THAN",
"Threshold":50,"ThresholdType":"PERCENTAGE"},
"Subscribers":[{"SubscriptionType":"EMAIL","Address":"you@example.com"}] }]
EOF
aws budgets create-budget --account-id "$ACCOUNT_ID" \
--budget file://budget.json --notifications-with-subscribers file://notify.json
The VPC and two private subnets (note: no internet gateway, no route-table change — private by construction):
# laptop
VPC_ID=$(aws ec2 create-vpc --cidr-block 10.20.0.0/16 \
--tag-specifications 'ResourceType=vpc,Tags=[{Key=Name,Value=dblab-vpc}]' \
--query 'Vpc.VpcId' --output text)
aws ec2 modify-vpc-attribute --vpc-id $VPC_ID --enable-dns-hostnames
aws ec2 modify-vpc-attribute --vpc-id $VPC_ID --enable-dns-support
SUBNET_A=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.20.1.0/24 \
--availability-zone ${AWS_REGION}a --query 'Subnet.SubnetId' --output text)
SUBNET_B=$(aws ec2 create-subnet --vpc-id $VPC_ID --cidr-block 10.20.2.0/24 \
--availability-zone ${AWS_REGION}b --query 'Subnet.SubnetId' --output text)
cat > ~/dblab-ids.txt <<EOF
export VPC_ID=$VPC_ID SUBNET_A=$SUBNET_A SUBNET_B=$SUBNET_B
export ACCOUNT_ID=$ACCOUNT_ID AWS_REGION=$AWS_REGION
EOF
Three security groups, using the SG-to-SG pattern:
# laptop
source ~/dblab-ids.txt
SG_APP=$(aws ec2 create-security-group --group-name dblab-app-sg \
--description "App/bastion" --vpc-id $VPC_ID --query GroupId --output text)
SG_DB=$(aws ec2 create-security-group --group-name dblab-db-sg \
--description "Database" --vpc-id $VPC_ID --query GroupId --output text)
SG_EP=$(aws ec2 create-security-group --group-name dblab-endpoint-sg \
--description "Endpoints" --vpc-id $VPC_ID --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id $SG_DB \
--protocol tcp --port 5432 --source-group $SG_APP # DB accepts 5432 only from app SG
aws ec2 authorize-security-group-ingress --group-id $SG_EP \
--protocol tcp --port 443 --source-group $SG_APP # endpoints accept 443 only from app SG
cat >> ~/dblab-ids.txt <<EOF
export SG_APP=$SG_APP SG_DB=$SG_DB SG_EP=$SG_EP
EOF
The three SSM interface endpoints and the bastion:
# laptop
for SVC in ssm ssmmessages ec2messages; do
aws ec2 create-vpc-endpoint --vpc-id $VPC_ID --vpc-endpoint-type Interface \
--service-name com.amazonaws.${AWS_REGION}.${SVC} \
--subnet-ids $SUBNET_A $SUBNET_B --security-group-ids $SG_EP --private-dns-enabled
done
# instance profile + bastion (SSM-managed, no inbound, no public IP)
aws iam create-role --role-name dblab-bastion-role \
--assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"ec2.amazonaws.com"},"Action":"sts:AssumeRole"}]}'
aws iam attach-role-policy --role-name dblab-bastion-role \
--policy-arn arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
aws iam create-instance-profile --instance-profile-name dblab-bastion-profile
aws iam add-role-to-instance-profile \
--instance-profile-name dblab-bastion-profile --role-name dblab-bastion-role
AMI=$(aws ssm get-parameter \
--name /aws/service/ami-amazon-linux-latest/al2023-ami-kernel-default-arm64 \
--query 'Parameter.Value' --output text)
BASTION=$(aws ec2 run-instances --image-id $AMI --instance-type t4g.micro \
--subnet-id $SUBNET_A --security-group-ids $SG_APP \
--iam-instance-profile Name=dblab-bastion-profile \
--query 'Instances[0].InstanceId' --output text)
echo "export BASTION=$BASTION" >> ~/dblab-ids.txt
aws ssm start-session --target $BASTION # after ~3 min for the agent to register
What actually happened
Two errors here taught more than a clean run would have.
Empty variables. Shell variables don’t survive a new terminal. My first
authorize-security-group-ingressfailed withexpected one argumentbecause$SG_APPwas empty in a fresh shell. Almost every early “InvalidParameterValue” is this. Fix:source ~/dblab-ids.txtat the start of every session.
InvalidPermission.Duplicate. Re-running anauthorizeafter it already worked isn’t an error — the rule exists and the call isn’t idempotent. Verify withaws ec2 describe-security-groups --group-ids $SG_DB --query 'SecurityGroups[0].IpPermissions'and move on.
Part 2 — RDS anatomy and launching it properly
The theory
RDS runs the engine on an EC2 instance you can’t log into, with EBS storage you can’t see, and gives you an API instead of a shell. AWS handles OS and engine patching, backups, failover, and hardware replacement; you lose superuser, filesystem access, and the ability to run agents on the host. That last loss is the real design constraint — if your existing tooling assumes shell access to the database host, it won’t survive the migration.
Instance classes come in burstable (t3/t4g), general purpose (m), memory optimised (r/x), and compute optimised (c) families. The g suffix means Graviton (ARM), which usually gives better price-performance for PostgreSQL — default to it. Burstable instances have a failure mode worth understanding: they earn CPU credits at a fixed rate and spend them above a small baseline. Under sustained load, credits deplete and the instance throttles hard — the database doesn’t fail, it becomes glacially slow, which is worse to diagnose. Watch CPUCreditBalance, and never put production OLTP on burstable.
Storage should be gp3. The improvement over legacy gp2 is decoupling: gp2 tied IOPS to volume size (a 100 GiB volume gave 300 IOPS, so you over-allocated storage to buy IOPS), while gp3 lets you provision 3,000 baseline IOPS independent of size. io2 Block Express is for sub-millisecond, consistent latency when p99 is in your SLA — expensive, and usually unnecessary.
Parameter groups are the managed equivalent of postgresql.conf. Each parameter has an apply method: immediate for dynamic parameters (applied without restart) and pending-reboot for static ones. This distinction is a favourite exam target. Values can be formulas referencing instance memory — the default shared_buffers is roughly 25% of instance RAM, which is why parameter groups are portable across instance sizes.
Credentials have three tiers: a typed --master-user-password (appears in history and logs — avoid); --manage-master-user-password, which generates the password, stores it in Secrets Manager, and rotates it (the right default); and IAM database authentication, where there’s no password at all — the client requests a short-lived token from IAM (best security, some latency).
Encryption at rest uses --storage-encrypted with a KMS key. Two hard rules: you cannot encrypt an existing unencrypted instance in place (the path is snapshot → copy with encryption → restore), and a snapshot encrypted with the default aws/rds key cannot be shared cross-account because AWS-managed keys have no shareable policy. Use a customer-managed key from day one if cross-account DR is even remotely plausible. In transit, sslmode=require only encrypts; verify-full also validates the certificate and hostname, which is what actually prevents a man-in-the-middle.
The commands
Discover the current version, then build the subnet group and a custom parameter group:
# laptop
aws rds describe-db-engine-versions --engine postgres \
--query 'DBEngineVersions[?starts_with(EngineVersion,`17.`)].[EngineVersion,DBParameterGroupFamily]' \
--output table
aws rds create-db-subnet-group --db-subnet-group-name dblab-subnets \
--db-subnet-group-description "dblab private" --subnet-ids $SUBNET_A $SUBNET_B
aws rds create-db-parameter-group --db-parameter-group-name dblab-pg17 \
--db-parameter-group-family postgres17 --description "dblab tuned"
aws rds modify-db-parameter-group --db-parameter-group-name dblab-pg17 --parameters \
"ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot" \
"ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate" \
"ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=pending-reboot"
Enhanced Monitoring role, then the launch itself — read every flag as a decision:
# laptop
aws iam create-role --role-name rds-monitoring-role \
--assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"monitoring.rds.amazonaws.com"},"Action":"sts:AssumeRole"}]}'
aws iam attach-role-policy --role-name rds-monitoring-role \
--policy-arn arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole
aws rds create-db-instance \
--db-instance-identifier dblab-pg-01 --db-instance-class db.t4g.micro \
--engine postgres --engine-version 17.4 \
--master-username dbadmin --manage-master-user-password \
--allocated-storage 20 --max-allocated-storage 100 \
--storage-type gp3 --storage-encrypted \
--db-subnet-group-name dblab-subnets --vpc-security-group-ids $SG_DB \
--db-parameter-group-name dblab-pg17 \
--backup-retention-period 7 \
--no-publicly-accessible --enable-performance-insights \
--monitoring-interval 60 \
--monitoring-role-arn arn:aws:iam::${ACCOUNT_ID}:role/rds-monitoring-role \
--copy-tags-to-snapshot --deletion-protection
aws rds wait db-instance-available --db-instance-identifier dblab-pg-01
Retrieve the endpoint and the Secrets Manager credential:
# laptop
ENDPOINT=$(aws rds describe-db-instances --db-instance-identifier dblab-pg-01 \
--query 'DBInstances[0].Endpoint.Address' --output text)
SECRET_ARN=$(aws rds describe-db-instances --db-instance-identifier dblab-pg-01 \
--query 'DBInstances[0].MasterUserSecret.SecretArn' --output text)
aws secretsmanager get-secret-value --secret-id $SECRET_ARN \
--query SecretString --output text | python3 -m json.tool
What actually happened
--manage-master-user-password is the right default — but hold that thought, because it silently blocks read replicas later (Part 6). When I connected and inspected settings, the formulas were real: on a t4g.micro, shared_buffers came back around 180 MB (~25% of ~1 GB RAM) and max_connections was 79. That 79 is exactly why RDS Proxy exists once serverless compute fronts a small instance — it exhausts connections fast.
Part 3 — Connecting from a no-internet VPC
The theory
I wanted sslmode=verify-full because encryption without authentication is only half the job — require stops eavesdropping but not a man-in-the-middle who presents their own certificate; only verify-full validates the server against a trusted CA bundle. But fetching that bundle normally means a curl to a public URL, and my bastion had no internet by design. That constraint forced the genuinely useful skill: getting artifacts into a locked-down VPC without opening it to the internet.
The tools are the two endpoint types from Part 1. Amazon Linux package repos live in S3, so a free S3 gateway endpoint makes dnf work with no internet. And distributing a file the bastion can’t fetch publicly means staging it in your own S3 bucket, which the bastion’s IAM role can read.
The commands
Free S3 gateway endpoint so dnf works:
# laptop
RT_ID=$(aws ec2 describe-route-tables \
--filters Name=vpc-id,Values=$VPC_ID Name=association.main,Values=true \
--query 'RouteTables[0].RouteTableId' --output text)
aws ec2 create-vpc-endpoint --vpc-id $VPC_ID --vpc-endpoint-type Gateway \
--service-name com.amazonaws.${AWS_REGION}.s3 --route-table-ids $RT_ID
# bastion
sudo dnf clean all && sudo dnf install -y postgresql16
Distribute the CA bundle via your own bucket:
# laptop — check which CA your instance uses, fetch the matching bundle, stage it
aws rds describe-db-instances --db-instance-identifier dblab-pg-01 \
--query 'DBInstances[0].CACertificateIdentifier' --output text # e.g. rds-ca-rsa2048-g1
curl -o /tmp/rds-ca.pem https://truststore.pki.rds.amazonaws.com/us-east-1/us-east-1-bundle.pem
BUCKET=dblab-scratch-$ACCOUNT_ID
aws s3 mb s3://$BUCKET
aws s3 cp /tmp/rds-ca.pem s3://$BUCKET/rds-ca.pem
aws iam put-role-policy --role-name dblab-bastion-role --policy-name dblab-scratch-read \
--policy-document "{\"Version\":\"2012-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Action\":[\"s3:GetObject\",\"s3:ListBucket\"],\"Resource\":[\"arn:aws:s3:::$BUCKET\",\"arn:aws:s3:::$BUCKET/*\"]}]}"
# bastion — pull it down and connect with full verification
aws s3 cp s3://dblab-scratch-XXXX/rds-ca.pem ~/rds-ca.pem
psql "host=<rds-endpoint> port=5432 dbname=postgres user=dbadmin \
sslmode=verify-full sslrootcert=$HOME/rds-ca.pem"
Reboot to apply the static parameters, then verify TLS and enable the extension:
# laptop
aws rds reboot-db-instance --db-instance-identifier dblab-pg-01
aws rds wait db-instance-available --db-instance-identifier dblab-pg-01
-- bastion
SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid(); -- t | TLSv1.3
SHOW shared_preload_libraries; -- now: rdsutils,pg_stat_statements
CREATE EXTENSION pg_stat_statements;
SHOW rds.force_ssl; -- on
What actually happened
This is where the three-layer failure taxonomy became muscle memory:
Three connection failures, three layers.
could not translate host name→ DNS didn’t resolve → you’re outside the VPC or the name is wrong.Connection refused→ name resolved, host reached, nothing listening → the service is stillcreating.timeout / no route to host→ resolved but packets never arrive → security group or routing. Read which one you got and you know which layer to check.
Pulling from AWS’s public bucket gave AccessDenied from the bastion (IAM role plus endpoint policy), which is why the own-bucket route is the reliable one. When verify-full finally connected over TLS 1.3, I’d proven the whole chain — SSM in, S3 gateway for artifacts, RDS over a private endpoint with a verified certificate — with zero bytes over the public internet. That’s the posture a real production database should have, built by hand.
Part 4 — Observability and the diagnosis loop
The theory
You have three instruments measuring three different things, and confusing them is the most common diagnostic error. CloudWatch is the hypervisor’s view at 60-second granularity — free and alarmable, but 60 seconds averages away a 20-second full-CPU stall into something that looks like 33% utilisation. Enhanced Monitoring is the operating system’s view at up to 1-second granularity. Performance Insights is the database’s view — the one that answers “why is it slow” — sampling active sessions once per second and attributing each to a wait event.
PI’s central metric is DB Load in Average Active Sessions, against a reference line of max vCPU. Below the line the database keeps up; consistently above it, sessions queue. And PI breaks that load down by wait event and by SQL statement, turning “the database is slow” into “70% of load is IO:DataFileRead from this one query.”
The PostgreSQL wait-event classes you must recognise: CPU (executing, healthy under max vCPU); IO:DataFileRead (buffer cache missed — classic missing-index signature); IO:WALWrite / IO:XactSync (flushing the write-ahead log before commit — write-bound); Lock:transactionid (application row contention, not infrastructure); and Client:ClientRead (waiting on the client — the bottleneck is the app or network, and this one is frequently misread as a database problem).
The diagnosis loop: (1) Is DB Load above max vCPU? No → look at the app. (2) What’s the top wait event? → the category. (3) Which SQL produces it? → PI attributes it. (4) EXPLAIN (ANALYZE, BUFFERS) it → check shared read vs shared hit. (5) Fix, then re-measure — and note the wait-event shift, not just the speedup. Step 5 is the one people skip, and it’s where the understanding lives.
One more rule from pg_stat_statements: sort by total execution time, not mean. A 2 ms query run 4 million times (8,000 seconds total) costs far more than a 9-second report run twice a day. The expensive query is rarely the slow one.
The commands
# bastion — pgbench is a separate contrib package
sudo dnf install -y postgresql16-contrib
export PGHOST=<rds-endpoint> PGUSER=dbadmin \
PGSSLMODE=verify-full PGSSLROOTCERT=$HOME/rds-ca.pem PGPASSWORD='<secret>'
createdb bench
pgbench -i -s 50 bench # ~5M rows, ~750MB (bigger than shared_buffers, on purpose)
pgbench -c 10 -j 2 -T 300 -S bench # read-only baseline
pgbench -c 10 -j 2 -T 300 bench # read-write baseline
The core exercise — break it, watch the wait event shift:
-- bastion
SELECT pg_stat_statements_reset();
EXPLAIN (ANALYZE, BUFFERS) SELECT abalance FROM pgbench_accounts WHERE aid=1234567;
ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey;
-- re-run pgbench -S: TPS collapses, top wait event slams to IO:DataFileRead
EXPLAIN (ANALYZE, BUFFERS) SELECT abalance FROM pgbench_accounts WHERE aid=1234567; -- Seq Scan
ALTER TABLE pgbench_accounts ADD PRIMARY KEY (aid);
-- re-run: recovers; record the before/after TPS and wait-event shift
Find expensive queries by total time:
SELECT substr(query,1,60) AS q, calls,
round(total_exec_time::numeric,1) AS total_ms,
round(mean_exec_time::numeric,3) AS mean_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
What actually happened
Dropping the primary key and watching Performance Insights shift the top wait event to IO:DataFileRead as TPS collapsed — then recover when the index came back — is the single most useful hour of the week. The before/after wait-event shift makes the whole diagnosis loop concrete in a way no diagram can.
Part 5 — Backup, restore, and recovery
The theory
RDS point-in-time recovery has two components: a daily storage-level snapshot and transaction logs uploaded to S3 continuously — in practice about every five minutes. To restore to time T, RDS takes the most recent snapshot before T and replays logs forward to exactly T. That five-minute upload interval is why the quoted RPO is ~5 minutes: it’s the maximum committed data you can lose to an infrastructure failure. It says nothing about logical corruption — if a bad UPDATE ran four hours ago, RPO is irrelevant; what matters is that you can restore to a point before it.
The constraint that shapes every runbook: PITR always restores to a NEW instance, never in place. So recovery isn’t “restore” — it’s restore, validate, then cut over. And cutover means changing the connection string. If applications hardcode the RDS endpoint, every one needs a config change and restart. If they resolve a Route 53 CNAME you control, cutover is a single low-TTL DNS change. Create that CNAME on day one — it converts a multi-hour recovery into a five-minute one.
On the DR side, automated backups are deleted with the instance (unless explicitly retained) while manual snapshots survive indefinitely and keep billing — the number-one cause of surprise AWS storage charges. Cross-region snapshot copy is the basic DR primitive, but remember the KMS constraint: a snapshot on the default aws/rds key can’t be shared cross-account, and cross-region copies must be re-encrypted with a key in the destination region.
The commands
Manual snapshot, simulate logical corruption, then hit the restore-window boundaries:
# laptop
SNAP_ID=dblab-pg-01-manual-$(date +%Y%m%d-%H%M)
aws rds create-db-snapshot --db-instance-identifier dblab-pg-01 --db-snapshot-identifier $SNAP_ID
aws rds wait db-snapshot-available --db-snapshot-identifier $SNAP_ID
-- bastion — record the timestamp and count, wait 5 min, then delete a third of the table
SELECT now(); SELECT count(*) FROM pgbench_accounts;
DELETE FROM pgbench_accounts WHERE aid % 3 = 0;
# laptop — check the window, then restore to just before the DELETE
aws rds describe-db-instances --db-instance-identifier dblab-pg-01 \
--query 'DBInstances[0].LatestRestorableTime'
START=$(date +%s)
aws rds restore-db-instance-to-point-in-time \
--source-db-instance-identifier dblab-pg-01 \
--target-db-instance-identifier dblab-pg-restored \
--restore-time <JUST-BEFORE-DELETE> \
--db-subnet-group-name dblab-subnets --vpc-security-group-ids $SG_DB \
--db-instance-class db.t4g.micro --no-publicly-accessible --no-deletion-protection
aws rds wait db-instance-available --db-instance-identifier dblab-pg-restored
echo "Restore wall clock: $(( $(date +%s) - START )) seconds"
Cross-region DR copy:
# laptop
SNAP_ARN=$(aws rds describe-db-snapshots --db-snapshot-identifier $SNAP_ID \
--query 'DBSnapshots[0].DBSnapshotArn' --output text)
aws rds copy-db-snapshot --source-db-snapshot-identifier $SNAP_ARN \
--target-db-snapshot-identifier dblab-pg-01-dr \
--source-region us-east-1 --region us-west-2 --kms-key-id alias/aws/rds
What actually happened
The two restore errors were the lesson. First, passing a date earlier than any backup returned cannot be restored to a time earlier than … no older backups available. Then, aiming past the log frontier returned cannot be restored to a time later than <LatestRestorableTime>. You can only restore inside the window that exists, and right after damage the point you need may not be uploaded yet — in a real incident the skill is exactly this: read LatestRestorableTime and, if the point you need isn’t covered, wait for it to advance.
My restore time for ~1 GB was dominated by instance provisioning, not data — so for small databases you can’t shrink RTO by shrinking data; you shrink it with a standby that’s already running. And the cross-region copy completed to available, 100% — reminding me that copying a snapshot doesn’t create a database, only a snapshot in the other region.
Part 6 — High availability and read replicas
The theory
These solve different problems and are constantly confused. Multi-AZ is about surviving failure; read replicas are about serving more reads. Neither substitutes for the other.
A Multi-AZ DB instance has one primary and one standby in another AZ, replicated synchronously — every write commits to both before acknowledgement. The standby is not readable; you pay for it to sit idle. Failover is automatic, typically 60–120 seconds, and the endpoint DNS is repointed at the standby. Why pay for idle capacity? Because it converts an unplanned multi-hour outage into a ~90-second blip and lets AWS maintain the database by failing over rather than taking downtime.
Read replicas replicate asynchronously — the primary doesn’t wait. They’re readable, can be in another AZ, region, or account, and each has its own endpoint. But replica lag is real and unbounded, and promotion to standalone is one-way and irreversible. The three causes of lag: write volume exceeding replica apply capacity, long-running replica queries blocking replay, and network/storage limits. The first is the important one — WAL replay in PostgreSQL is essentially single-threaded, so adding vCPU to the replica does not fix it.
Async replication also creates the read-after-write problem: a user who writes then immediately reads a replica may not see their own write. There’s no config that makes this vanish — it’s an application design decision (route post-write reads to the primary for a window, use version tokens, or design the UX around it).
The commands
Enable Multi-AZ and force a failover under load:
# laptop
aws rds modify-db-instance --db-instance-identifier dblab-pg-01 --multi-az --apply-immediately
aws rds wait db-instance-available --db-instance-identifier dblab-pg-01
# with pgbench running on the bastion:
aws rds reboot-db-instance --db-instance-identifier dblab-pg-01 --force-failover
aws rds describe-events --source-identifier dblab-pg-01 --source-type db-instance \
--duration 60 --query 'Events[].[Date,Message]' --output table
Create a read replica — and hit the managed-password wall:
# laptop
aws rds create-db-instance-read-replica --db-instance-identifier dblab-pg-rr1 \
--source-db-instance-identifier dblab-pg-01 --db-instance-class db.t4g.micro --no-publicly-accessible
# InvalidParameterValue: Creating read replicas ... where ManageMasterUserPassword is enabled
# is not supported.
# fix: disable managed mode AND set a password in the SAME call
aws rds modify-db-instance --db-instance-identifier dblab-pg-01 \
--no-manage-master-user-password --master-user-password '<strong password>' --apply-immediately
aws rds wait db-instance-available --db-instance-identifier dblab-pg-01
# now it succeeds
aws rds create-db-instance-read-replica --db-instance-identifier dblab-pg-rr1 \
--source-db-instance-identifier dblab-pg-01 --db-instance-class db.t4g.micro --no-publicly-accessible
aws rds wait db-instance-available --db-instance-identifier dblab-pg-rr1
Prove read-only, measure lag, reproduce read-after-write, promote:
-- bastion, on the replica
INSERT INTO pgbench_history VALUES (1,1,1,1,now()); -- ERROR: read-only transaction
SELECT pg_is_in_recovery(); -- t
# laptop — lag under write load
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name ReplicaLag \
--dimensions Name=DBInstanceIdentifier,Value=dblab-pg-rr1 \
--start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ) \
--end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
--period 60 --statistics Maximum \
--query 'sort_by(Datapoints,&Timestamp)[].[Timestamp,Maximum]' --output table
aws rds promote-read-replica --db-instance-identifier dblab-pg-rr1 # one-way
aws rds wait db-instance-available --db-instance-identifier dblab-pg-rr1
What actually happened
Failover: 44 seconds end to end (events showed started 20:11:57, completed 20:12:41), with the new primary accepting connections at the 24-second mark. But the events tell you what AWS did — the application’s outage is the pgbench gap where TPS hit zero, which includes the client noticing the drop, re-resolving DNS, and reconnecting. A stale JVM DNS cache turns a 44-second database failover into a multi-minute app outage. That tail is your responsibility.
Managed password blocks replicas. The two credential models are mutually exclusive states you toggle between, not settings you layer. Disabling managed mode traded automatic rotation for the ability to have a replica; in production you’d sidestep it entirely with RDS Proxy plus IAM database authentication.
Replica lag drew a whole story: ~481 seconds peak during catch-up under a write burst, draining 481 → 31 → 1 in three minutes, then ~1 second steady state. The peak was write volume exceeding apply capacity — the cause more vCPU wouldn’t fix. And promotion flipped pg_is_in_recovery() from t to f: the same instance that rejected a write an hour earlier now accepted one, permanently severed from its source. Irreversible — restrict rds:PromoteReadReplica in production to avoid split-brain.
Part 7 — Aurora architecture
The theory
Aurora is PostgreSQL-compatible at the SQL and wire-protocol layer with a completely re-engineered storage layer — and that one substitution explains every behavioural difference. Aurora stores six copies of your data across three AZs in 10 GB segments. Writes need a 4-of-6 quorum; reads need 3-of-6. Crucially, the database instance ships only the redo log to storage, not pages — the storage layer materialises pages itself, in parallel across the fleet. Traditional PostgreSQL writes the page, the WAL, and the replica’s copy; Aurora writes the log record. That’s where the performance advantage originates.
What follows from this is the whole story. Replicas share the same storage — they don’t maintain their own copy, so adding one takes about a minute regardless of database size, lag is typically tens of milliseconds (cache invalidation, not log replay), and you can have up to 15 of them with near-zero write overhead. Failover is fast — usually under 30 seconds — because a replica is promoted by taking over write access to storage that’s already current; there’s nothing to catch up. This is why Aurora failover beats RDS Multi-AZ, and the reason is architectural, not about instance size. Storage auto-scales to 128 TiB with no provisioning. And fast cloning creates a new cluster sharing the source’s pages copy-on-write — instant regardless of size, billing only for divergence.
Serverless v2 measures capacity in ACUs (~2 GiB each), scaling in fine increments in place without dropping connections. Minimum capacity can be 0 (pauses when idle, ~10–15 s resume — fine for dev, unacceptable for user-facing SLAs). The cost model differs too: standard Aurora charges per million I/O requests on top of instance and storage, which can dominate I/O-heavy bills; Aurora I/O-Optimized removes per-I/O charges for higher instance/storage rates, and wins roughly when I/O exceeds 25% of your Aurora spend.
The commands
Create the cluster and two serverless instances:
# laptop
aws rds create-db-cluster --db-cluster-identifier dblab-aurora \
--engine aurora-postgresql --engine-version 16.4 \
--master-username dbadmin --manage-master-user-password \
--db-subnet-group-name dblab-subnets --vpc-security-group-ids $SG_DB \
--serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=8 \
--storage-encrypted --backup-retention-period 7
for ROLE in writer reader; do
aws rds create-db-instance --db-instance-identifier dblab-aurora-$ROLE \
--db-cluster-identifier dblab-aurora --engine aurora-postgresql \
--db-instance-class db.serverless
done
aws rds wait db-instance-available --db-instance-identifier dblab-aurora-writer
aws rds describe-db-clusters --db-cluster-identifier dblab-aurora \
--query 'DBClusters[0].[Endpoint,ReaderEndpoint]' --output table
Load, watch ACUs scale, then the two lag queries:
# bastion
export PGPASSWORD='<aurora secret>'
createdb -h <aurora-writer-endpoint> -U dbadmin bench
pgbench -h <aurora-writer-endpoint> -U dbadmin -i -s 50 bench
pgbench -h <aurora-writer-endpoint> -U dbadmin -c 40 -j 8 -T 300 bench
# laptop
aws cloudwatch get-metric-statistics --namespace AWS/RDS \
--metric-name ServerlessDatabaseCapacity \
--dimensions Name=DBClusterIdentifier,Value=dblab-aurora \
--start-time $(date -u -d '30 minutes ago' +%Y-%m-%dT%H:%M:%SZ) \
--end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
--period 60 --statistics Average Maximum \
--query 'sort_by(Datapoints,&Timestamp)[].[Timestamp,Average,Maximum]' --output table
-- bastion, on the Aurora writer
SELECT now() - pg_last_xact_replay_timestamp() AS lag;
-- ERROR: Function pg_last_xact_replay_timestamp() is currently not supported for Aurora
SELECT server_id,
CASE WHEN session_id='MASTER_SESSION_ID' THEN 'writer' ELSE 'reader' END AS role,
replica_lag_in_msec
FROM aurora_replica_status();
Copy-on-write clone and failover:
# laptop
aws rds restore-db-cluster-to-point-in-time --source-db-cluster-identifier dblab-aurora \
--db-cluster-identifier dblab-aurora-clone --restore-type copy-on-write \
--use-latest-restorable-time --db-subnet-group-name dblab-subnets --vpc-security-group-ids $SG_DB
aws rds create-db-instance --db-instance-identifier dblab-aurora-clone-inst \
--db-cluster-identifier dblab-aurora-clone --engine aurora-postgresql --db-instance-class db.serverless
aws rds failover-db-cluster --db-cluster-identifier dblab-aurora \
--target-db-instance-identifier dblab-aurora-reader
What actually happened
The architecture was visible before I ran a benchmark: the launch showed "AllocatedStorage": 1 / "StorageType": "aurora" — no pre-allocated volume, because storage is the distributed service, not a disk.
The lag query refused to run — pg_last_xact_replay_timestamp() is currently not supported for Aurora — and that error is the answer: Aurora replicas don’t replay WAL, so there’s no replay position. aurora_replica_status() instead reported 4 milliseconds on the reader (and nothing on the writer, because it’s the source of truth), versus my RDS replica’s ~1,000 ms steady and ~481,000 ms catch-up.
The ACU trace drew the whole serverless value proposition: ramped to ~5.25 ACU, tapered gradually (3.25 → 2.5 → 1.5 → 0.5) as load eased, parked at my 0.5 floor when idle, then 0.5 → 7 → 8 in ~2 minutes on a second burst — all connection-preserving, 0 failed transactions. Peak throughput 2,090 TPS at 19 ms, vs low hundreds on the fixed RDS micro.
The clone proved copy-on-write in metadata: source and clone shared the same CloneGroupId, so zero data was copied. My ~11.7-minute wall-clock was almost entirely provisioning the compute instance — on a 10 TB database the storage step would still be instant, which is the superpower a tiny lab hides.
Cluster endpoint before instances are ready. Connecting immediately gave
Connection refusedat a private IP — name resolved, host reachable, but the instances were stillcreating. Also, the Aurora cluster kept managed passwords while my RDS instance had to give them up for a replica — because Aurora replicas share storage and don’t need the credential propagated. Same feature, blocked on one, fine on the other, for a real architectural reason.
RDS vs Aurora — the measured comparison
| Dimension | RDS PostgreSQL | Aurora PostgreSQL |
|---|---|---|
| Replica lag (measured) | ~1,000 ms steady, ~481,000 ms catch-up | 4 ms |
| Lag introspection | pg_last_xact_replay_timestamp() works | that function unsupported; use aurora_replica_status() |
| What “lag” means | WAL replay distance | shared-storage visibility delay |
| Fixed by more vCPU? | No — replay is single-threaded | N/A — no replay happens |
| Failover | 44 s measured (60–120 s typical) | Fast DNS cutover, redundancy preserved |
| Clone / restore | New instance, provisioning-bound | Copy-on-write, zero data moved, shared CloneGroupId |
| Storage | Provisioned (--allocated-storage 20) | No pre-allocation, auto-grows to 128 TiB |
| Peak TPS (this lab) | Low hundreds (fixed micro) | 2,090 @ 19 ms (scaled) |
| Cost model | Instance + storage + IOPS | Instance + storage + I/O requests |
| Baseline cost | Lower | Higher |
Choose RDS when cost is binding, you need a very recent PostgreSQL version or a specific extension, or the workload is modest and steady. Choose Aurora when you need fast failover, many read replicas, large or unpredictable data, cheap clones, or serverless scaling. There’s also Aurora DSQL (GA May 2025) for globally distributed apps needing strong multi-region consistency — PostgreSQL-compatible for a subset of PG16, so not a drop-in migration target.
Certification exam-style questions
Try them before reading the answers.
Q1. RDS for PostgreSQL has ManageMasterUserPassword enabled; adding a read replica fails. Cause and best long-term fix? A. Move the replica to the same AZ. B. RDS doesn’t support replicas with managed master passwords; disable it, or use RDS Proxy with IAM database authentication. C. Create a parameter group first. D. Enable Multi-AZ on the source.
Q2. A db.t4g.micro shows normal CPUUtilization but severe intermittent slowness. Which metric explains it? A. FreeableMemory B. DatabaseConnections C. CPUCreditBalance D. ReadThroughput
Q3. Which statement about RDS point-in-time recovery is correct? A. It restores in place. B. It restores to a new instance; you validate and cut over. C. It needs a manual snapshot before the event. D. It can restore to any time in 35 days regardless of retention.
Q4. An Aurora reader shows millisecond lag under heavy writes where RDS shows seconds. Architectural reason? A. Faster instance classes. B. Aurora replicas read the same distributed storage and don’t replay WAL. C. Aurora compresses WAL. D. Same-AZ placement.
Q5. Fastest, cheapest copy of a 10 TB Aurora database for testing? A. Snapshot and restore. B. Aurora fast cloning (copy-on-write). C. pg_dump/pg_restore. D. Cross-region replica then promote.
Q6. Lowest-cost storage giving provisioned IOPS independent of volume size for general OLTP? A. gp2 B. gp3 C. io2 Block Express D. magnetic
Q7. Why can’t a snapshot on the default aws/rds key be shared cross-account, and the fix? A. Snapshots are never shareable. B. AWS-managed keys have no shareable policy; re-encrypt with a customer-managed key. C. Copy to S3 first. D. Requires Multi-AZ.
Q8. Multi-AZ failover completed in 90 s but the app stayed broken 20+ minutes. Most likely cause? A. Standby in another region. B. The app cached the old endpoint IP (DNS TTL) and never reconnected. C. Parameter group out of sync. D. Backups disabled.
Answers: Q1 B — managed passwords and replicas are mutually exclusive; production uses RDS Proxy + IAM auth. Q2 C — burstable throttles when credits deplete; CPUCreditBalance warns. Q3 B — always a new instance, hence the Route 53 CNAME pattern. Q4 B — shared storage, no replay. Q5 B — copy-on-write, instant regardless of size. Q6 B — gp3 decouples IOPS from size below io2’s cost. Q7 B — AWS-managed keys have no editable policy. Q8 B — the DB moved (DNS repointed) but a client cached the dead IP.
Interview questions
1. Walk through an RDS Multi-AZ failover end to end. AWS promotes the synchronous standby and repoints the endpoint CNAME (60–120 s). In-flight transactions roll back, connections drop; the app must reconnect respecting DNS TTL and retry idempotent operations. The standby isn’t readable — it adds availability, not read capacity.
2. Aurora over RDS, and when not? Aurora for fast failover, 15 shared-storage replicas with millisecond lag, large/unpredictable data, cheap clones, serverless scaling. RDS when cost binds, you need a recent version or specific extension, or load is modest and steady. Note Aurora’s per-I/O billing unless I/O-Optimized.
3. Explain replica lag; why doesn’t more vCPU always fix it? Lag is WAL replay delay. Causes: write volume exceeding apply capacity, replica query conflicts, network/storage. Replay is single-threaded, so scaling up doesn’t help the first cause — reduce writes or move to Aurora where replicas don’t replay.
4. Design a runbook so RPO/RTO are actually met. RPO from log frequency (~5 min); RTO includes detection, decision, restore, cutover. PITR restores to a new instance, so front apps with a low-TTL Route 53 CNAME. Measure restore time for your data size — for small DBs it’s provisioning-bound.
5. Move a 4 TB on-prem PostgreSQL OLTP DB with under 15 minutes downtime. DMS full-load plus CDC keeps the target current while the source stays live; cutover drains CDC and repoints the app. Validate with SCT, choose Aurora for scale/failover, evaluate I/O-Optimized, and export snapshots to S3/Glacier for long retention.
6. Secure RDS so no human reads data at rest or in transit, fully auditable. Customer-managed KMS key granting decrypt only to app roles; rds.force_ssl with verify-full; Secrets Manager rotation or IAM auth; human access only via break-glass role through SSM with session logging; CloudTrail plus Database Activity Streams / pgaudit to a locked logging account; private subnets, SG-to-SG rules, no public access.
7. CIDR vs SG-to-SG security group rules. SG-to-SG authorizes by identity — any instance carrying the referenced SG is allowed regardless of IP or autoscaling. CIDR authorizes by address and needs maintenance, drifting over-broad. For tier-to-tier traffic, SG references are the correct low-maintenance pattern.
8. Aurora Serverless v2 scale-to-zero — when right, when dangerous? Pauses when idle with a ~10–15 s resume. Fine for dev/test, batch, internal tools; unacceptable for user-facing SLAs — set a non-zero minimum. The trade-off is idle cost versus cold-start latency.
Full teardown
Everything here is billable — teardown discipline matters as much as the build. Run from the laptop.
source ~/dblab-ids.txt
# Aurora clone (instance before cluster), then main cluster, then RDS, bastion, DR snapshot
aws rds delete-db-instance --db-instance-identifier dblab-aurora-clone-inst --skip-final-snapshot
aws rds wait db-instance-deleted --db-instance-identifier dblab-aurora-clone-inst
aws rds delete-db-cluster --db-cluster-identifier dblab-aurora-clone --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier dblab-aurora-reader --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier dblab-aurora-writer --skip-final-snapshot
aws rds wait db-instance-deleted --db-instance-identifier dblab-aurora-writer
aws rds delete-db-cluster --db-cluster-identifier dblab-aurora --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier dblab-pg-01 \
--final-db-snapshot-identifier dblab-pg-01-final-$(date +%Y%m%d)
aws ec2 terminate-instances --instance-ids $BASTION
aws rds delete-db-snapshot --db-snapshot-identifier dblab-pg-01-dr --region us-west-2
for EP in $(aws ec2 describe-vpc-endpoints --filters Name=vpc-id,Values=$VPC_ID \
--query 'VpcEndpoints[?VpcEndpointType==`Interface`].VpcEndpointId' --output text); do
aws ec2 delete-vpc-endpoints --vpc-endpoint-ids $EP
done
Then verify nothing survives — check the DR region and manual snapshots especially, since they outlive everything — and finish with a Cost Explorer review grouped by service. On my week the biggest line items were Aurora ACU-hours and the three interface endpoints, not the database compute. In the cloud, the surprising costs are rarely where you expect. Keep the VPC, subnets, security groups, DB subnet group, and parameter group for next time.
What’s next
Week 2 is DynamoDB from a relational architect’s perspective — access-pattern-first modelling, single-table design and when it’s the wrong idea, partition-key cardinality and hot partitions, GSI vs LSI, capacity modes, and when not to use it. It’s where relational architects lose the most points on both exams.
My strongest advice for experienced database people prepping for AWS certification: don’t watch — build. Run every command, break things on purpose, and record your own numbers. The 4 ms, the 481 s, the 44 s, the shared CloneGroupId — those are mine, from my hands. Go get yours.
I’m documenting the full three-month journey week by week. Bookmark the blog and follow along.