Setting the file. One moment.
Skill 62 · Amazon Aurora PostgreSQL
Subchapter 62.34
references/upgrade-planning-post-upgrade-detail.mdMarkdown6 KBView on GitHub
Companion to post-upgrade-validation.md. These are the detailed Aurora PostgreSQL post-upgrade procedures referenced there. Surface them alongside the four Aurora-specific items.
A major-version upgrade does NOT carry over optimizer statistics — pg_upgrade discards the contents of pg_statistic entirely, so the new planner starts with no statistics. You MUST regenerate statistics before trusting query plans post-upgrade:
Run ANALYZE on the whole database, or more thoroughly VACUUM ANALYZE which also reclaims bloat accumulated during the pre-upgrade snapshot + backup window.
-- fast: refresh statistics only
ANALYZE;
-- thorough: refresh statistics + reclaim dead-tuple bloat
VACUUM ANALYZE;
-- per-table if you want to prioritise
VACUUM ANALYZE VERBOSE public.my_critical_table;Aurora PostgreSQL runs autovacuum automatically, but post-upgrade is a worthwhile one-time manual pass. On large schemas, budget 30–120 min.
After a major PG upgrade, Aurora does NOT automatically update extensions to the version matching the new major. You MUST run:
-- list installed extensions and versions
SELECT extname, extversion FROM pg_extension ORDER BY extname;
-- run this for each extension
ALTER EXTENSION <extension_name> UPDATE;Common Aurora extensions that need updates: pg_stat_statements, pgvector, apg_plan_mgmt, pgaudit, postgis. Failing to update pg_stat_statements in particular will cause it to silently stop recording some query types until updated.
Diagnostic queries to confirm extensions are working:
-- pg_stat_statements: should return non-empty, most-recent calls
SELECT calls, mean_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
-- pgvector: confirm operators available (if using vector search)
SELECT '[1,2,3]'::vector <-> '[4,5,6]'::vector;Optimiser changes across major versions are one of the top causes of post-upgrade regression. For each critical query that was in the “hot queries” set before the upgrade, capture a fresh plan and compare. Use EXPLAIN (ANALYZE, BUFFERS) — ANALYZE runs the query and reports actual timing; BUFFERS reports cache hit/miss ratios:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ... FROM hot_table WHERE ...;Look for:
Rows Removed by Filter numbers (selectivity estimates degraded).Aurora cluster parameter groups are pinned to a specific major version family (e.g. aurora-postgresql14, aurora-postgresql15, aurora-postgresql16, aurora-postgresql17). The upgrade process creates a new parameter group in the target family OR requires you to assign one — you cannot reuse an aurora-postgresql15 parameter group on a PG16 cluster.
Verify the cluster is actually using a target-family parameter group:
aws rds describe-db-clusters --db-cluster-identifier <cluster> \
--query "DBClusters[0].{PG:DBClusterParameterGroup}" --region <region>
# Inspect custom parameter values
aws rds describe-db-cluster-parameters \
--db-cluster-parameter-group-name <new-pg> \
--query "Parameters[?Source=='user'].{Name:ParameterName,Value:ParameterValue}" \
--output table --region <region>Risk: if the pre-upgrade cluster had custom parameters (e.g. shared_buffers, work_mem, max_connections, custom logging settings), those MUST be re-applied to the new-family parameter group — they are NOT carried across automatically. Mis-applied parameter groups are the second-largest source of post-upgrade regressions after optimiser changes.
If you took a pre-upgrade manual snapshot (you should have — it’s a pre-upgrade-checklist item), note that:
Watch these CloudWatch metrics for the first 24–72 hours and compare to pre-upgrade baselines:
CPUUtilization — a 5–15% change is normal; > 25% indicates a plan regression.DatabaseConnections — should be stable; sudden rise can mean connection-pool re-auth loops on engine changes.ReadLatency, WriteLatency, DMLLatency, SelectLatency — p95 should return to baseline within 2 hours; sustained elevation indicates query-plan issues.FreeableMemory — especially important if the cluster uses a custom shared_buffers; freezing at a different level indicates a parameter-group-family migration issue.BufferCacheHitRatio — should be ≥95% for OLTP; drop below 90% means statistics or cache warmup issue.AuroraReplicaLag, AuroraReplicaLagMaximum — as above, should settle below 100 ms for readers.Deadlocks, LoginFailures — both should be near pre-upgrade baseline; a spike can signal a reserved-word conflict introduced by the new major version.modify-db-cluster --engine-version to downgrade — downgrades are not supported in-place. (Downgrades are not supported in-place by Aurora.)