Upgrading the Major Version of a DB Instance on the Console

Scenarios

You can upgrade the major version of your RDS for PostgreSQL instance to unlock a richer set of features.

To use major version upgrades, submit a service ticket.

Upgrade Methods

RDS for PostgreSQL allows you to upgrade the major version of your DB instance in either of the following methods:

• Upgrade without cutover: It does not affect workloads. During the upgrade, a new instance is created using a snapshot, and no operation is performed on the original instance. The major version of the new instance is upgraded. After the upgrade is complete, you can connect to the new instance to test workload compatibility. Upgrading a major version may cause compatibility issues, so compatibility testing is strongly recommended. After the test is passed, perform an upgrade in cutover mode.
• Upgrade with cutover: During this process, a new instance is created using a snapshot, the original instance is set to read-only, and workloads will be interrupted for several minutes. After the major version of the new instance is upgraded, the virtual IP address (VIP) and private domain name of the original instance are automatically switched to the new instance and your application automatically connects to the new instance. The upgrade is complete. The original instance remains read-only and will not be unlocked automatically.

Video

Supported Versions

• Major version upgrades are supported for the following source instances:
  • 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.6.3 or later
  • RDS for PostgreSQL 13: 13.2.3 or later
  • RDS for PostgreSQL 14: 14.4 or later
  • RDS for PostgreSQL 15: 15.4 or later
  • RDS for PostgreSQL 16 and 17 are supported, with no restrictions on minor versions
  • Major version upgrades are unavailable to RDS for PostgreSQL 11 and Enhanced Edition.

Upgrade Precautions

Extensions Unsupported for Major Version Upgrades

If any extension listed in Table 2 is detected in the upgrade path during a major version upgrade pre-check, uninstall the extension before the upgrade is performed and reinstall it after the upgrade is complete. If you perform the upgrade without removing this extension, the upgrade will fail, or the extension cannot be used after the instance is upgraded.

Billing

Procedure

Upgrade Check Reports and Upgrade Reports

If an upgrade check or an upgrade fails, you can analyze the causes based on the upgrade check report or upgrade report. The procedure is as follows:

1. Check the pg_upgrade_internal.log file.

   pg_upgrade_internal.log is an important log file for debugging. If an upgrade fails, check for errors recorded 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 (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. If you are sure that the deletion will not affect workloads, delete them before the upgrade.

   • 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"

3. Troubleshoot the fault if the following error message is displayed after the upgrade check fails.

   Error message

   Relation names for OID 16479 in database postgres do not match toast: relname recyclebin_tb_1;Relation names for OID 16472 in database postgres do not match toast: relname tb_toast;

   Handling method (The following operations involve modifications to table objects. To prevent data inconsistency, stop all write operations to the target table.)

   1. Back up the abnormal table. Table public.tb_toast is used as an example.

      CREATE TABLE public.tb_toast_bak AS SELECT * FROM public.tb_toast;

   2. Uninstall the recyclebin plugin. After it is uninstalled, objects (such as recyclebin_tb_1) in the recycle bin will be automatically deleted.
   3. Delete the abnormal table.

      DROP TABLE public.tb_toast;

   4. Restore the original table name.

      ALTER TABLE public.tb_toast_bak RENAME TO tb_toast;

   5. Perform the upgrade check again and verify that the error message "do not match toast" no longer appears.