PostgreSQL version upgrades¶
How to move a client's database to a new PostgreSQL major version with a switchover measured in seconds rather than minutes of downtime.
This is written to be followed cold. If you have never done one of these, start at the top and do not skip the pre-flight section - two of its checks cannot be automated, and they are the two that decide whether the upgrade can work at all.
How the pieces fit together¶
Terraform declares which major version a client should run. It deliberately does not perform the upgrade.
That separation is the whole design.
Terraform is good at describing a steady state and bad at performing a stateful migration between two versions, and if it owned the version field then raising it would make the next routine deploy attempt an in-place major upgrade - fifteen to thirty minutes of downtime, triggered by a merge.
So infrastructure/modules/rds/main.tf carries ignore_changes = [engine_version, parameter_group_name], and the transition is performed by a workflow a human runs.
The upgrade itself uses an RDS Blue/Green deployment. AWS builds a complete copy of the database on the new version (green), keeps it in sync with the live one (blue) by logical replication, and switches the two over on command. Read replicas need no special handling: AWS rebuilds them against green and switches them over together with the primary.
The cost of ignore_changes is that Terraform stops enforcing the version on instances that already exist, so a declared version could sit unapplied forever with a clean plan.
Every deploy therefore prints the declared and actual versions and raises a workflow warning when they differ.
That warning is the only thing standing between a declared upgrade and it being quietly forgotten.
Where the version is declared¶
The default lives in one place, infrastructure/base/variables.tf:
A client that must stay behind sets a POSTGRES_MAJOR variable in their GitHub Environment.
Anything else inherits the default, so a newly onboarded client is created on the current major with no configuration at all.
A changed declaration must be deployed before it can be acted on. The upgrade workflow reads the declaration from applied Terraform state, and it refuses to run when the client's configuration and the applied state disagree - the fix is simply to deploy the client first.
Only the major is declared, never the minor.
Pinning a minor would mean a pull request per client for every PostgreSQL security release, with nothing watching for them; auto_minor_version_upgrade is on, so minors move on their own during the maintenance window.
Instance-class changes use the same machinery¶
The database instance class works exactly like the version: RDS_INSTANCE_CLASS declares it, ignore_changes stops Terraform resizing a live instance (an in-place resize is a five-to-fifteen-minute reboot), every deploy warns when declared and actual differ, and this workflow performs the transition with the same seconds-long switchover.
A new environment needs none of this - it is created on its declared class directly.
A run converges the database to the full declaration. Preflight compares both the version and the class, and whichever differs is changed by the same Blue/Green run - so dispatching a version upgrade for a client whose declared class also changed applies both at once, deliberately, rather than spending a second deploy freeze later. The workflow's "Show what will be acted on" step prints declared and actual for both dimensions before anything is created; read it.
What differs from a version upgrade on a class-only run: no ANALYZE is owed afterwards (planner statistics survive a same-version switchover), and cleanup's final snapshot is named <instance>-pre-<old-class>-<date> instead of the pre-pg<major>-upgrade form.
One sharp edge: if a class-only cleanup is re-run after the deployment record was already deleted, it refuses to delete the old instance - the record is what confirms the instance's identity, and without it only an older major is proof enough.
That refusal prints the by-hand commands; deleting the old instance manually is then a deliberate operator decision.
The one-time prerequisite¶
Blue/Green replicates logically, because the two sides differ on disk across a major version.
That requires rds.logical_replication to be active on the source database, and RDS refuses to create the deployment otherwise.
Terraform now sets that parameter, so any instance created from here on is upgrade-ready from birth.
An instance that predates it needs one reboot, because wal_level is fixed at server start.
Applying the parameter is safe and boring: it is an in-place parameter group modification, the database keeps serving traffic, and RDS simply records the instance as pending-reboot.
Nothing happens until you restart it.
Schedule that reboot like any other brief outage.
On the VSU/Troy pilot it also clears an older outstanding pending-reboot: shared_preload_libraries is a static parameter too, which means pgaudit is not loaded at all until that first restart, so database-level audit logging starts working at the same moment.
Confirm it took effect before going further - ParameterApplyStatus must read in-sync:
aws rds describe-db-instances --db-instance-identifier campuscore-db-<env> \
--query 'DBInstances[0].DBParameterGroups[0].[DBParameterGroupName,ParameterApplyStatus]' --output text
The procedure is proven¶
This path has been driven end to end for real: the VSU/Troy pilot went from 17.9 to 18.4 on 2026-07-28, with prepare, switchover and cleanup all succeeding on their first attempt and writer downtime of a few seconds.
The refusal paths (abort with nothing to abort, preflight before the reboot, cleanup with nothing to clean) were exercised against the same live instance.
An optional rehearsal against a scratch instance is still worth considering when something material has changed since that run - a new PostgreSQL major with its own surprises (18 renamed the data directory and changed log_connections), a reshaped script, or an operator doing this for the first time.
Restore a snapshot into a new instance under a different identifier, apply the logical replication parameter, reboot it, then drive scripts/rds-upgrade.sh through preflight, create, switchover and cleanup, and abort a second prepared deployment so the recovery path is exercised too.
Driving the script by hand needs its four environment variables: RDS_DB_IDENTIFIER, RDS_DECLARED_MAJOR, RDS_DECLARED_CLASS, and (for a version change) RDS_TARGET_PARAM_GROUP.
Then delete everything, and confirm the deletion: a forgotten scratch instance bills every hour.
Be aware that a rehearsal freezes the real client's application deploys for as long as its blue/green deployment exists, because the deploy guard blocks on any live deployment in the account - it cannot tell a scratch upgrade from a production one.
Note anything that behaves differently from this document and fix the document.
Pre-flight¶
Two checks need a connection to the database and cannot run from CI, because GitHub Actions has no network route into the private subnet.
scripts/rds-upgrade.sh preflight prints both, but you have to run them yourself and read the answers.
Every table needs a primary key. Logical replication cannot replicate an update or a delete on a table without one, so rows would silently diverge between blue and green.
SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary);
Zero rows is the only acceptable answer.
Replication slots need headroom.
Everything else the pre-flight checks for itself: the instance exists and is available, it is not already on the declared major, automated backups are on, logical replication is active rather than merely set, the target parameter group exists, RDS actually offers the version being asked for, and no deployment is already in flight.
Two more things are worth knowing before you start. Large objects are not replicated, and neither are materialized views or unlogged tables - the schema currently has none of these, but a future one might. The source instance must be on PostgreSQL 16.1 or later for Blue/Green to be available at all.
Running the upgrade¶
Everything below is .github/workflows/db-upgrade.yml, run from the Actions tab against one named client.
prepare¶
Runs the pre-flight, then builds the green environment and waits for replication to catch up. This takes tens of minutes.
prepare starts a deploy freeze.
From the moment a blue/green deployment exists, deploy-aws.yml refuses to deploy this client, and it is right to: the deploy applies Django migrations, DDL is not replicated, and RDS responds by putting the green databases into Replication degraded - from which the only recovery is deleting the whole deployment and building it again.
So run prepare close to the switchover window, not days ahead of it.
An urgent hotfix during the gap costs an abort plus a full re-prepare, discarding however much replication sync had accumulated.
When it finishes, look at the switchover details it prints and satisfy yourself that green is healthy.
A green build can take longer than the one hour AWS allows a chained role, in which case the job fails partway through the wait.
That is not a lost deployment: re-running prepare resumes waiting on the deployment that is already building rather than starting a second one.
switchover¶
Re-checks that the deployment is still AVAILABLE, then cuts over.
Writer downtime is typically five seconds or less.
The green instance takes over the blue instance's identifier, and blue is renamed with an -old1 suffix.
That handover is why Terraform needs no state surgery afterwards: aws_db_instance is keyed on the identifier, so state keeps resolving to the upgraded instance and the next plan is clean.
The workflow then verifies the new primary reports the expected major and restores deletion_protection if the switchover did not carry it across.
It does not delete anything.
The pre-upgrade database is still running, under its new -old1 name, and it is your way back.
Nothing about the upgrade is irreversible until someone deliberately runs cleanup.
After switchover¶
Run ANALYZE. Planner statistics do not survive a major version upgrade, and queries can be badly slow until it has run. This is the most common cause of "the upgrade worked but the site is slow".
Then confirm the things this application actually depends on:
SELECT version();
SELECT extname, extversion FROM pg_extension; -- vector must be present
SELECT indexname FROM pg_indexes WHERE indexdef ILIKE '%hnsw%'; -- HNSW indexes intact
Finally, drive a chat query through the SPA and confirm it returns cited sources. That exercises the vector search path end to end, which is the thing a version upgrade is most likely to break quietly.
cleanup¶
Deletes the demoted pre-upgrade instance, once you are satisfied the upgrade is good. Run it after the verification below, not before, and not on the same day if you would rather wait.
It refuses unless the new instance is available and on the declared major.
It resolves the old instance's real identifier from the deployment record while that record still exists; when a half-finished cleanup has already deleted the record, it falls back to the conventional -old1 name but refuses to delete anything that is not on an older major than the declared one.
The instance is deleted with a final snapshot, named <instance>-pre-pg<major>-upgrade, and its automated backups are left in place.
Both survive deliberately: they are what a rollback would be restored from, and deleting them is a separate decision for a later day.
While cleanup has not run, the deploy freeze is over - the guard ignores a completed deployment, since replication has ended and green is production - but the old instance is still billing.
abort¶
Deletes the deployment and its green databases without switching over. Blue keeps serving throughout and nothing about it changes.
Use it when prepare times out, when replication degrades, or when you simply need the deploy freeze lifted.
Running it is always safe before a switchover, and it refuses to run after one - by then the green databases are production.
When something goes wrong¶
Replication degraded. Almost always DDL reaching blue during the window, which usually means an application deploy slipped through. There is no repair: abort, work out how the deploy got through, then prepare again.
prepare fails saying logical replication is not active. The parameter is set but the instance has not been rebooted. See the one-time prerequisite above.
switchover reports success but the version has not changed. Stop, and do not run cleanup - the old instance is the rollback, and cleanup is what deletes it. Switchover and cleanup are separate actions precisely so that stopping here is possible.
Storage pressure during the window. WAL accumulates on blue for as long as the deployment exists. preflight reports free storage; if it is tight, shorten the window rather than hoping.
Retiring an old major¶
infrastructure/modules/rds/main.tf keeps a postgres17-family parameter group alongside the current one.
It exists because a parameter group's family cannot change while an instance is attached to it, and the group cannot be renamed or destroyed either, so the upgrade moves instances onto a new group rather than mutating the old one.
Delete that resource only once no client is left on 17.
Removing it while a client is still attached will fail the apply, and a failed deploy-infra blocks every application deploy for that client.
What is not covered here¶
Restoring a client to an older major version. Blue/Green is a one-way upgrade; going back means restoring from a snapshot, which is a different procedure with real data loss between the snapshot and now.