Upgrading the Major Version of a DB Instance on the Console

Scenarios

• Upgrade without cutover: Use it to test workload compatibility with new versions. Upgrading the major version of a DB instance may cause compatibility issues. Therefore, perform thorough compatibility testing before performing an upgrade with cutover. An upgrade without workload cutover will not affect the original instance.
• Upgrade with cutover: During such an upgrade, the original instance is set to read-only and workloads may be interrupted for minutes. After the upgrade is complete, the original and new instances automatically exchange their virtual IP addresses and the application connection will be switched to the new instance. No changes need to be made to your application. The original instance remains read-only and will not be unlocked automatically.

Constraints

• Major version upgrades are available to the following versions:
  • RDS for PostgreSQL 9.5: 9.5.25 or later
  • RDS for PostgreSQL 9.6: 9.6.24 or later
  • RDS for PostgreSQL 10: 10.21 or later
  • RDS for PostgreSQL 12: 12.7 or later
  • RDS for PostgreSQL 13: 13.3 or later
  • RDS for PostgreSQL 14: 14.4 or later
  • RDS for PostgreSQL 15: 15.4 or later
  • RDS for PostgreSQL 16 and RDS for PostgreSQL 17
• Due to OS restrictions, some DB instances do not support major version upgrades. Refer to the console for the supported options.
• Before a major version upgrade, perform an upgrade check. If there is no successful upgrade check in the validity period, a major version upgrade is not allowed.

Extensions Unsupported for Major Version Upgrades

Precautions

• RDS for PostgreSQL 10 and 11 have been brought offline. Functions such as backing up and restoring data, changing instance classes, scaling down storage space, creating read replicas, and changing the instance type from single-node to primary/standby for existing DB instances of these two versions are no longer available. You are advised to upgrade the major versions of such existing instances as soon as possible.

• If a major version upgrade is complete and the workload is cut over to the new instance but is incompatible with the new version, you need to roll back the upgrade. Contact customer service to unlock the original instance from the read-only state. Then, you can continue to use the original instance.
  Notice

  The data added after an upgrade is complete will not be automatically synchronized to the original instance.

• After a major version upgrade is complete, a new instance is created. The original instance is still retained and billed. When the workload runs stably, you can release the original instance.
• After a major version upgrade, audit logs, error logs, and slow query logs of the original instance are still stored in the original instance. On the new instance, you can only view the logs generated after the upgrade is complete.
• Read replicas do not support major version upgrades. If your DB instance has read replicas, the read replicas will not be upgraded along with your DB instance. You need to recreate them after a major version upgrade. For details, see Creating a Read Replica.
• If your DB instance has a DR instance, the DR instance will not be upgraded synchronously and the DR relationship will be interrupted. After a major version upgrade, recreate a DR instance running the same version as the new instance and set up a DR relationship between them.
• A major version upgrade has the following impacts:

  Upgrading an instance with a workload cutover will set the original instance to read-only and may interrupt your workload for minutes. Schedule this upgrade during off-peak hours. Upgrading an instance without a workload cutover does not affect your workload.

  Notice

  The default_transaction_read_only parameter controls the read-only settings. Before the upgrade, check whether any modification has been made to this parameter. If yes, the data inserted into the instance during the cutover will be lost after the upgrade.

• Upgrading a major version will not upgrade extension versions. For details about supported extension versions, see Supported Extensions. If the new instance supports any extension of a later version, you can use the following command to update the extension, or uninstall and reinstall the extension of the latest version.

  ALTER EXTENSION extension_name UPDATE TO 'new_version';
  
  

  Notice

  Certain extensions (such as postgis) will cause the upgrade task to fail. In this case, uninstall the extensions before performing a major version upgrade.

Upgrade without Cutover

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Database > Relational Database Service.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose Major Version Upgrade.
6. On the displayed page, select a target version and click Next.
7. On the pre-check page, start a pre-check. It takes several minutes to complete the check.
   Note

   • If you add extensions after a successful upgrade check, they may introduce compatibility issues and cause the upgrade to fail. If this happens, perform the check again. For details, see Extensions Unsupported for Major Version Upgrades.
   • If the upgrade check fails, you can view the check report by clicking View Report on the Upgrade Checks tab page and rectify the incompatibility based on the report.

8. After the check is passed, select No for Cutover, click Autofill to fill UPGRADE, and click Upgrade Now.
9. Confirm the upgrade information and click Submit.
10. After the upgrade is complete, check for the new instance of the target version. You can connect to the new instance to test workload compatibility.

Upgrade with Cutover

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Database > Relational Database Service.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose Major Version Upgrade.
6. On the displayed page, select a target version and click Next.
7. On the pre-check page, start a pre-check. It takes several minutes to complete the check.
   Note

   • If you add extensions after a successful upgrade check, they may introduce compatibility issues and cause the upgrade to fail. If this happens, perform the check again. For details, see Extensions Unsupported for Major Version Upgrades.
   • If the upgrade check fails, you can view the check report by clicking View Report on the Upgrade Checks tab page and rectify the incompatibility based on the report.

8. After the check is passed, select Yes for Cutover, specify Collect Statistics, click Autofill to fill UPGRADE, and click Upgrade Now.

   During a major version upgrade, optimizer statistics are not automatically synchronized. Statistics need to be collected after the upgrade is complete.

   • Before cutover: Workload stability is ensured. If your instance has too much data, the upgrade may take a longer time.
   • After cutover: Faster upgrade is ensured. After the upgrade, accessing tables that no statistics have been generated for may cause inaccurate execution plans and even cause the DB instance to be unavailable during peak hours.

9. Confirm the upgrade information and click Submit.
10. After the upgrade is complete, check for the new instance of the target version. Your workload is automatically switched over to the new instance.

Upgrade Check Reports and Upgrade Reports

1. View the pg_upgrade_internal.log file.

   The pg_upgrade_internal.log file is the main log file of an upgrade check report or upgrade report. If an upgrade fails, check for errors in this file. Common errors are as follows:

   • A list of problem libraries is in the file: loadable_libraries.txt

     It means there are extensions that are incompatible with the target version. They are listed in loadable_libraries.txt.

   • A list of tables with the problem is in the file: tables_with_oids.txt

     It means there are tables that are created with the WITH OIDS clause and such tables are recorded in tables_with_oids.txt. The WITH OIDS clause is not supported by RDS for PostgreSQL 12 or later.

   • Consult the last few lines of "pg_upgrade_server.log" for the probable cause of failure.

     It means that the target version failed to start during the upgrade check and you can check pg_upgrade_server.log for the causes.

   • Consult the last few lines of "pg_upgrade_dump_xxxx.log" for the probable cause of failure.

     It means that pg_dump failed to back up data during the upgrade and you can check pg_upgrade_dump_xxxx.log for the causes.

   • The source cluster contains roles starting with "pg_"

     Users whose names start with pg_ are not allowed in the target version. Delete or rename these users first.

   • A list of the problematic objects is in the file: incompatible_polymorphics.txt

     There is the function parameter type anyarray or anyelement that is incompatible with the target version. Delete the function definitions before the upgrade. After the upgrade is complete, restore the functions and change the parameter type (from anyarray to anycompatiblearray or anyelement to anycompatible). The function objects are recorded in the incompatible_polymorphics.txt file.

2. Analyze causes based on the report items.
   • loadable_libraries.txt

     This item displays incompatible libraries, which usually correspond to incompatible extensions. Check the extensions listed in loadable_libraries.txt and determine whether to delete them. Delete them before the upgrade if you are sure that the deletion will not affect workloads.

   • tables_with_oids.txt

     This item displays tables created with the WITH OIDS clause. Check the tables listed in tables_with_oids.txt and evaluate whether the workload code depends on the OIDs. If stripping OIDs from the tables does not affect workloads, run the following SQL statement:

     ALTER TABLE {table_name} SET WITHOUT OIDS;
     
     

   • pg_upgrade_server.log

     Check the last several lines of the pg_upgrade_server.log file. If an error similar to the following appears, the extension displayed in this error does not exist in the target version. Delete it from shared_preload_libraries as required and then perform the upgrade.

     FATAL: could not access file "xxx": No such file or directory.
     
     

     Example:

     FATAL: could not access file "pg_pathman": No such file or directory.
     
     

   • pg_upgrade_dump_xxxx.log
     • Check the last several lines of pg_upgrade_dump_xxxx.log. If an error similar to the following is displayed, there are too many tables in the current instance. In this case, increase the value of max_locks_per_transaction and perform the upgrade again.

       pg_dump: error: query failed: ERROR: out of shared memory
       HINT: You might need to increase max_locks_per_transaction.
       pg_dump:error: query was: LOCK TABLE "xxx"."xxx" IN ACCESSSHARE MODE
       
       

     • Check the last several lines of pg_upgrade_dump_xxxx.log. If an error similar to the following is displayed, the pgl_ddl_deploy extension exists in the current instance. This extension is incompatible with the target version, so the upgrade failed. Check whether there are any other incompatible third-party extensions in the instance based on Extensions Unsupported for Major Version Upgrades (some incompatible third-party extensions cannot be identified through an upgrade check). Delete them as required and then perform the upgrade.

       pg_restore: error: could not execute query: ERROR: could not find function "xxx" in file xxx
       Command was: CREATE FUNCTION "pgl_ddl_deploy"."xxx"