Major version upgrades to Aurora PostgreSQL - some gotcha's

Background

Major version upgrades for Aurora PostgreSQL used to be painful if you were behind on versions.

For example - lets assume you were on Aurora PostgreSQL 9.6 and AWS notified users that they are no longer supporting this version.
You then decided to take the opportunity to upgrade all the way to the latest version (version 13).
You had to upgrade one major version at a time.
i.e., follow this upgrade path

9.x to 10.x first
10.x to 11.x next
11.x to 12.x after that
12.x to 13.x finally!

OR you resorted to dumping out the entire database and importing it back into a new database with the latest version.

Neither option was elegant and has many considerations (like time taken, outages required or even data integrity)

Recent Change

AWS recently announced that Amazon Aurora supports Multi Major Version Upgrade to Aurora PostgreSQL 11 and higher.

In that they mention that you could upgrade from

Upgrade from PostgreSQL 9.6.X to PostgreSQL 12.X OR
Upgrade from PostgreSQL 10.X to PostgreSQL 13.X

Gotcha!

Not so fast though! The devil is in the detail of the source and target version - see this from the linked documentation

In my opinion that documentation is not very clear. What happens if you have version 10.16? It is not in the list and you have to either assume that it is treated the same as version 10.7 OR 10.18 and assumptions are bad.

For example - upgrading to 10.16 to 13.3 fails with

error message = Cannot upgrade aurora-postgresql from 10.16 to 13.4 (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterCombination)

Recommendations / Workaround

Fortunately there is a way to figure out which major versions are supported using the aws cli and this is in the same documentation linked above

Using the cli - we figure out that 10.16 is only upgradable to 11.x

$ aws rds describe-db-engine-versions --engine aurora-postgresql --engine-version 10.16 --query 'DBEngineVersions[].ValidUpgradeTarget[?IsMajorVersionUpgrade ==true].{EngineVersion:EngineVersion}' --output text
11.11
11.12
11.13
11.14

Rather than do this major version upgrade, I would suggest doing a minor version upgrade to 10.18 first and THAT supports the giant leap all the way to 13.4

$ aws rds describe-db-engine-versions --engine aurora-postgresql --engine-version 10.18 --query 'DBEngineVersions[].ValidUpgradeTarget[?IsMajorVersionUpgrade ==true].{EngineVersion:EngineVersion}' --output text
11.13
11.14
12.8
13.4

Conclusion

Announcement blog posts tend to provide just high level detail and in order to understand gotcha's and workarounds its essential to read the documentation. In this case the table of source, target could be clearer to say something like "Versions from 9.7 and to 10.17 allow to 11.x". Using the awscli provides a good way to clarify the exact upgrades allowed.