Upgrade shared RDS PostgreSQL from 13 to 15

[ale210]

Dependency

• Enable automated backups and take a baseline snapshot on the shared RDS instance #149 — Enable automated backups and take a baseline snapshot on the shared RDS instance

Overview

We need to upgrade the shared incubator-prod-database RDS instance from PostgreSQL 13.20 to 15, because 13.20 is deprecated and the instance is running on paid RDS Extended Support. Every HfLA project in the incubator shares this single instance, so the upgrade needs a planned window and a verified rollback path.

Action Items

Pre-flight checks. These need a psql session against the instance; the master credentials appear to be stored in the rds_credentials SSM parameter. Record the results in a comment on this issue — the post-upgrade verification compares against them.

• Inventory every database and its owner (\l), so you can prove afterwards that they all survived.
• List installed extensions in each database (SELECT * FROM pg_extension;). Some extensions block pg_upgrade or need their own version handling — PostGIS in particular. Check anything non-stock against the postgres15 family before scheduling the window.
• Check for logical replication slots (SELECT slot_name FROM pg_replication_slots;). Any existing slot causes pg_upgrade to fail and must be dropped first.
• Check user tables for reg* columns (regproc, regclass, and similar). pg_upgrade cannot carry these across and will fail.
• Decide how to handle password encryption. PostgreSQL 14 changed the default password_encryption from md5 to scram-sha-256. Existing roles keep their md5 hashes through the upgrade so nothing breaks on day one, but roles created afterwards may get scram, which older client drivers (old node-pg, old psycopg2) cannot authenticate with — and terraform/modules/database creates roles for every new project. Check the value in the postgres15 parameter family and record the decision here.

Terraform changes, all in terraform/database.tf unless noted. Line numbers below were accurate when this was written and may drift; find each by attribute name.

• Add allow_major_version_upgrade = true. It is absent today, and without it the apply fails outright on a major version bump.
• Change engine_version from "13.20" to "15". Pin the major version only — auto_minor_version_upgrade = true is already set, so pinning a full minor guarantees drift the next time AWS patches the instance.
• Replace parameter_group_name = "default.postgres13" with a postgres15-family group. RDS rejects a major upgrade that keeps an old-family parameter group. Prefer adding an aws_db_parameter_group resource with family = "postgres15" over referencing default.postgres15: that default group does not exist in the account yet (only default.postgres13 does — AWS creates defaults lazily), and a managed group gives future tuning somewhere to live.
• Change option_group_name from "default:postgres-13" to "default:postgres-15" — same family constraint. Option groups do nothing for Postgres, but the attribute is declared, so it has to move with the family.
• Decide and set engine_lifecycle_support. Leaving it at "open-source-rds-extended-support" means paying Extended Support again once 15 reaches end of standard support; "open-source-rds-extended-support-disabled" means AWS force-upgrades at that point instead of billing. Record which was chosen and why in a comment here.
• Remove the import block at the top of the file (lines 1-4 as of writing; find it by import {). It was the one-time adoption of the pre-existing instance and has already run — it is stale, not load-bearing.
• Decide whether apply_immediately = true stays for this change. Left as-is, the upgrade begins the moment the merge lands. Set to false and RDS defers it to the mon:00:00-mon:03:00 maintenance window instead. Either is defensible — the point is to choose, rather than let the existing value decide.

Execution.

• Schedule and announce a maintenance window with all project teams. Plan for 30-45 minutes; actual downtime for this data volume on a db.t3.small is typically 10-20 minutes including RDS's pre- and post-upgrade reboots.
• Time the merge to that window. Merging the PR is the deploy — .github/workflows/terraform-apply.yaml runs terraform apply with auto_approve: true on every push to main touching a .tf file. There is no separate apply step to schedule.
• Review the terraform-plan output on the PR and confirm it is an in-place update to aws_db_instance.default. A plan proposing to replace this resource must not be merged — skip_final_snapshot = true and deletion_protection = false mean nothing would stop a destroy of the shared database.
• Decide whether to scale the affected ECS services to zero first or let them error and reconnect. There are ~21 persistent connections; letting tasks cycle is survivable but noisy, and scaling down makes the "did it come back" check unambiguous.
• Merge, then watch progress: aws rds describe-events --source-identifier incubator-prod-database --source-type db-instance --region us-west-2.

After the PR merges. These cannot be checked from a branch — the upgrade only happens on merge, so the assignee has to come back to them.

• Confirm the engine version: aws rds describe-db-instances --db-instance-identifier incubator-prod-database --region us-west-2 --query 'DBInstances[0].EngineVersion'.
• Run ANALYZE on every database. pg_upgrade does not carry optimizer statistics across, so query plans will be poor until this runs. This is the most commonly missed step of a major version upgrade.
• Verify every database and role from the pre-flight inventory is still present and owned as before.
• Confirm the endpoint address is unchanged (it should be, for an in-place upgrade). terraform/projects/civic-tech-index/environment-stage.tf and environment-prod.tf hardcode the hostname in POSTGRES_HOST, so a changed endpoint would silently break those two services.
• Bring services back and check each one's logs for connection and authentication errors, production first: vrms-backend-prod and cti-backend-prod.
• Re-run terraform plan and confirm it is clean. If engine_version or parameter_group_name shows drift, resolve it now rather than leaving it for whoever plans next.
• Take a post-upgrade manual snapshot once services are verified healthy.

Resources/Instructions

• terraform/database.tf — the RDS instance declaration.
• terraform/modules/database/ — the per-project module that creates logical databases and roles on this instance. It reaches the instance through a data source with a hardcoded identifier, so it has no dependency edge on the resource.
• .github/workflows/terraform-apply.yaml — the apply-on-merge workflow.
• AWS: account 035866691871 (incubator), region us-west-2, instance incubator-prod-database.

Verified facts as of this ticket's creation:

• 13.20 reports Status: deprecated; the instance carries engine_lifecycle_support = "open-source-rds-extended-support".
• 13.20 → 15.x is a valid direct upgrade target — no intermediate hop through 14 is required. 15.13 through 15.18 are available; 15.12 and below are deprecated.
• This is the only RDS instance in the account, shared by all incubator projects.
• Confirm the current target list before starting, since it moves: aws rds describe-db-engine-versions --engine postgres --engine-version 13.20 --region us-west-2 --include-all --query 'DBEngineVersions[0].ValidUpgradeTarget[?IsMajorVersionUpgrade==\true`].EngineVersion'`

Rollback. There is no in-place downgrade. Rollback means restoring the incubator-prod-database-pre-pg15 snapshot from the dependency issue to a new instance and repointing consumers — roughly 30+ minutes, and any writes made after the snapshot are lost. That is why the window should be quiet and why the snapshot is a hard prerequisite.

Alternative considered — RDS Blue/Green. Would cut downtime to about a minute, but requires a custom parameter group with rds.logical_replication = 1 plus a reboot to apply it, and doubles instance cost while both environments are live. Not proposed here: the data volume is small and most consumers are dev/qa/stage. Reconsider only if vrms-backend-prod or cti-backend-prod cannot take the window.

• Upgrading the PostgreSQL DB engine for Amazon RDS
• PostgreSQL 15 release notes