Upgrading PostgreSQL on AWS RDS via Command Line

AWS periodically retires PostgreSQL minor versions on RDS. When you receive an email or see an alert in the AWS console about an upcoming end of standard support for your current version, follow these steps to upgrade before AWS does it automatically during a maintenance window.

Prerequisites

AWS CLI installed and configured
IAM permissions for rds:DescribeDBInstances, rds:CreateDBSnapshot, rds:ModifyDBInstance
Logged into the correct AWS account (aws login)

Step 1: Check current versions

aws rds describe-db-instances --region us-east-2 \
 --query "DBInstances[].{ID:DBInstanceIdentifier,Version:EngineVersion,Status:DBInstanceStatus}" \
 --output table

Note the current version for each instance and identify the target version from the AWS alert. For minor version upgrades, stay on the same major version (e.g., 14.17 → 14.20).

Step 2: Upgrade the development database

2a. Take a manual snapshot

Replace YYYYMMDD with today’s date (e.g., 20260408).

aws rds create-db-snapshot --region us-east-2 \
 --db-instance-identifier simsportdbdevelopment \
 --db-snapshot-identifier simsportdbdevelopment-pre-upgrade-YYYYMMDD

Wait for the snapshot to complete — you can check its status with:

aws rds describe-db-snapshots --region us-east-2 \
 --db-snapshot-identifier simsportdbdevelopment-pre-upgrade-YYYYMMDD \
 --query "DBSnapshots[0].Status" \
 --output text

It should return available when ready.

2b. Apply the upgrade

Replace TARGET_VERSION with the version from the AWS alert (e.g., 14.20).

aws rds modify-db-instance --region us-east-2 \
 --db-instance-identifier simsportdbdevelopment \
 --engine-version TARGET_VERSION \
 --apply-immediately

2c. Monitor progress

The instance will transition through backing-up → modifying → available. This typically takes 10–20 minutes.

aws rds describe-db-instances --region us-east-2 \
 --db-instance-identifier simsportdbdevelopment \
 --query "DBInstances[0].{Status:DBInstanceStatus,Version:EngineVersion}" \
 --output table

2d. Validate

Once the status returns to available and shows the new version:

Open the dev version of SIMS Portal and confirm it loads
Test login, navigation, and a few key pages
Check that any background tasks or scheduled jobs run normally

Step 3: Upgrade the production database

Once dev is validated, repeat the same process for production.

3a. Snapshot

aws rds create-db-snapshot --region us-east-2 \
 --db-instance-identifier simsportaldb \
 --db-snapshot-identifier simsportaldb-pre-upgrade-YYYYMMDD

3b. Upgrade

aws rds modify-db-instance --region us-east-2 \
 --db-instance-identifier simsportaldb \
 --engine-version TARGET_VERSION \
 --apply-immediately

3c. Monitor and validate

aws rds describe-db-instances --region us-east-2 \
 --db-instance-identifier simsportaldb \
 --query "DBInstances[0].{Status:DBInstanceStatus,Version:EngineVersion}" \
 --output table

Step 4: Post-upgrade cleanup

Run ANALYZE on both databases to refresh query planner statistics:

# connect via psql or your preferred client
ANALYZE;

Notes

Minor version upgrades (e.g., 14.17 → 14.20) stay within the same major version and are generally safe.
Major version upgrades (e.g., 14.x → 16.x) are more complex and require additional testing.
Downtime: Expect 10–20 minutes of downtime per instance during the upgrade.
Always upgrade dev first. Never upgrade production without validating dev.