How to upgrade Aurora PostgreSQL (Deprecated Engine version 10.21)

Question

Background:
I had run application that use Aurora PostgreSQL 10.21 until around mid of 2023. I have paused the service/application for about 1 year. Now I want to continue the application so i need db back.

Problem:
I cannot upgrade engine version to newer version (DB Instance class: db.r5.large, region: Asia Pacific (Singapore) ap-southeast-1).
I have tried upgrading engine version 10.21 -> 12.16, 13.12, 14.9, 15.4 but they were all failed with notification message "An error occurred during upgrade of the DB cluster . The upgrade will be rolled back."
I checked "Logs & events" and found message "Database cluster is in a state that cannot be upgraded: The cluster couldn't be upgraded because one or more databases have settings or usages that aren't compatible with the target DB engine version. Examine the precheck log file for more details."

I don't know where the precheck log file is.
Relate post may be https://repost.aws/questions/QU2tABm6vpTAe5YAgx1q3SkQ/where-is-precheck-log-file-for-aurora-serverless-postgres.
It may relate to PostGIS since I have installed it in our application.

Here is pg_extension.
SELECT extname, extversion FROM pg_extension;
|extname         |extversion|
|----------------|----------|
|plpgsql         |1.0       |
|postgis         |2.4.4     |
|postgis_topology|2.4.4     |
|pgcrypto        |1.3       |

Some other problems I found:
1. Cannot Upgrade DB Engine Version
2. Cannot Manually Pause instance
3. Cannot Restore from snapshots
4. Sometimes cannot manually start instance
5. Sometimes stop instance automatically (Status: Stopped temporarily) - over 7 days

One approach is to manually dump current database and migrate to new instance with newer engine version. and then change db configuration in my application to the new one.
Any other options that i can upgrade in the same instance?

re:Post Agent · Answer

Given your situation with Aurora PostgreSQL 10.21, which is a deprecated engine version, and the various issues you're encountering, upgrading to a newer version is indeed necessary. However, the problems you're facing indicate that a direct in-place upgrade might not be possible due to compatibility issues, possibly related to your PostGIS extension or other database settings.

Here are the recommended steps to address your situation:

1. Investigate the precheck log: Unfortunately, the precheck log file for Aurora PostgreSQL is not directly accessible to customers. You would need to contact AWS Support to get more details about the specific incompatibilities preventing the upgrade.

2. Extension compatibility: Your PostGIS extension (version 2.4.4) is quite old and may not be compatible with newer PostgreSQL versions. Before upgrading, you'll need to ensure all extensions are compatible with the target version.

3. Dump and restore approach: Given the complications you're facing, the most reliable method would be to perform a logical dump of your existing database and restore it to a new Aurora PostgreSQL instance with a more recent engine version. This approach, while more involved, allows you to:
   - Start with a clean, up-to-date instance
   - Avoid issues related to in-place upgrades
   - Upgrade extensions during the process

Here's a high-level overview of the process:

a. Create a logical dump of your current database using pg_dump.
b. Launch a new Aurora PostgreSQL cluster with the desired engine version (e.g., 14.9 or 15.4).
c. Restore the dump to the new cluster.
d. Update extensions to versions compatible with the new PostgreSQL version.
e. Test your application thoroughly with the new database.
f. Update your application's database configuration to point to the new cluster.

4. Consider Aurora PostgreSQL Serverless v2: If your workload is intermittent or unpredictable, you might want to consider using Aurora PostgreSQL Serverless v2, which can automatically scale and pause based on usage.

5. Regular maintenance: Once you've upgraded, make sure to keep your database updated regularly to avoid similar situations in the future. Enable auto minor version upgrades and plan for major version upgrades periodically.

While manually dumping and restoring to a new instance requires more effort, it's often the most reliable way to upgrade from a significantly outdated version, especially when direct upgrades are failing. This approach also gives you the opportunity to start fresh with the latest engine version and compatible extensions.  
**Sources**  
[Performing a minor version upgrade - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.MinorUpgrade.html)  
[Performing a major version upgrade - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.MajorVersion.html)  
[Using logical replication to perform a major version upgrade for Aurora PostgreSQL - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.MajorVersionUpgrade.html)

extname	extversion
plpgsql	1.0
postgis	2.4.4
postgis_topology	2.4.4
pgcrypto	1.3

How to upgrade Aurora PostgreSQL (Deprecated Engine version 10.21)

Relevant content