These are my notes on figuring out an upgrade path for the FreshPorts PostgreSQL 12.14 database hosted on Amazon RDS.
Most of this is based on Upgrading the PostgreSQL DB engine for Amazon RDS
The sections of this post relate to the sections found at the above URL.
First, I installed the AWS CLI (via py39-awscli, created an Access Key for my AWS login, and ran:
[16:14 mydev dan ~] % aws configure AWS Access Key ID [None]: [the key name] AWS Secret Access Key [None]: [the key secret value] Default region name [None]: us-east-1 Default output format [None]:
Note that on line 4 I did not entery the full region code which was us-east-1d – I left off the trailing letter. That was on purpose. You’ll get this error otherwise:
[16:15 mydev dan ~] % aws rds describe-db-engine-versions --engine postgres --engine-version 12.14 \
--query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output text
Could not connect to the endpoint URL: "https://rds.us-east-1d.amazonaws.com/"
Choosing a major version upgrade for PostgreSQL
[16:41 aws-1 dan ~] % aws rds describe-db-engine-versions --engine postgres --engine-version 12.14 --query \
"DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output text
12.15
12.16
12.17
13.10
13.11
13.12
13.13
14.7
15.2
There, I can upgrade PostgreSQL from 12.14 to all of the above versions. I will go for 15.2
Listing the DB instance classes that are supported by a specific DB engine version in an AWS Region
From DB instance classes, I took this command:
[16:42 aws-1 dan ~] % aws rds describe-orderable-db-instance-options --engine postgres --engine-version 15.2 \
--query "*[].{DBInstanceClass:DBInstanceClass,StorageType:StorageType}|[?StorageType=='gp2']|[].{DBInstanceClass:DBInstanceClass}" \
--output text \
--region us-east-1
db.m5.12xlarge
db.m5.16xlarge
db.m5.24xlarge
db.m5.2xlarge
db.m5.4xlarge
db.m5.8xlarge
db.m5.large
db.m5.xlarge
db.m6g.12xlarge
db.m6g.16xlarge
db.m6g.2xlarge
db.m6g.4xlarge
db.m6g.8xlarge
db.m6g.large
db.m6g.xlarge
db.m6i.12xlarge
db.m6i.16xlarge
db.m6i.24xlarge
db.m6i.2xlarge
db.m6i.32xlarge
db.m6i.4xlarge
db.m6i.8xlarge
db.m6i.large
db.m6in.12xlarge
db.m6in.16xlarge
db.m6in.24xlarge
db.m6in.2xlarge
db.m6in.32xlarge
db.m6in.4xlarge
db.m6in.8xlarge
db.m6in.large
db.m6in.xlarge
db.m6i.xlarge
db.m7g.12xlarge
db.m7g.16xlarge
db.m7g.2xlarge
db.m7g.4xlarge
db.m7g.8xlarge
db.m7g.large
db.m7g.xlarge
db.r5.12xlarge
db.r5.16xlarge
db.r5.24xlarge
db.r5.2xlarge
db.r5.4xlarge
db.r5.8xlarge
db.r5b.12xlarge
db.r5b.16xlarge
db.r5b.24xlarge
db.r5b.2xlarge
db.r5b.4xlarge
db.r5b.8xlarge
db.r5b.large
db.r5b.xlarge
db.r5.large
db.r5.xlarge
db.r6g.12xlarge
db.r6g.16xlarge
db.r6g.2xlarge
db.r6g.4xlarge
db.r6g.8xlarge
db.r6g.large
db.r6g.xlarge
db.r6i.12xlarge
db.r6i.16xlarge
db.r6i.24xlarge
db.r6i.2xlarge
db.r6i.32xlarge
db.r6i.4xlarge
db.r6i.8xlarge
db.r6i.large
db.r6in.12xlarge
db.r6in.16xlarge
db.r6in.24xlarge
db.r6in.2xlarge
db.r6in.32xlarge
db.r6in.4xlarge
db.r6in.8xlarge
db.r6in.large
db.r6in.xlarge
db.r6i.xlarge
db.r7g.12xlarge
db.r7g.16xlarge
db.r7g.2xlarge
db.r7g.4xlarge
db.r7g.8xlarge
db.r7g.large
db.r7g.xlarge
db.t3.2xlarge
db.t3.large
db.t3.medium
db.t3.micro
db.t3.small
db.t3.xlarge
db.t4g.2xlarge
db.t4g.large
db.t4g.medium
db.t4g.micro
db.t4g.small
db.t4g.xlarge
db.x2g.12xlarge
db.x2g.16xlarge
db.x2g.2xlarge
db.x2g.4xlarge
db.x2g.8xlarge
db.x2g.large
db.x2g.xlarge
Given I’m now using db.t2.large, which is not recommended for production, but it seems to be running fine, I have selected db.t4g.large for the upgrade.
Determining DB instance class support in AWS Regions
From the same page, I got this command:
[16:51 aws-1 dan ~] % aws rds describe-orderable-db-instance-options --engine postgres --db-instance-class db.t4g.large \
--query "*[].{EngineVersion:EngineVersion,StorageType:StorageType}|[?StorageType=='gp2']|[].{EngineVersion:EngineVersion}" \
--output text \
--region us-east-1
12.11
12.12
12.13
12.14
12.15
12.16
12.17
13.7
13.8
13.9
13.10
13.11
13.12
13.13
14.3
14.4
14.5
14.6
14.7
14.8
14.9
14.10
15.2
15.3
15.4
15.5
16.1
Conclusions
This tells me I can upgrade:
- the DB instance class from db.t2.large to db.t4g.large
- the database engine version from PostgreSQL 12.14 to PostgreSQL 15.2
- the database engine version from PostgreSQL 15.2 to PostgreSQL 16.1
Sounds OK to me.
There is the option for blue/green deployments, but I’m not up for that configuration right now.
EDIT: 2023-11-25 – I ran through an update via a restored snapshot. No errors. I wasn’t able to connect to the database from the EC2 instance. I’m going to try again and pay more attention to the VPC and other connection details.
