Upgrading the Major Version of a DB Instance on the Console

Scenarios

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) of the original instance is automatically switched to the new instance and your workloads are automatically connected to the new instance. The upgrade is complete. The original instance remains read-only.

To upgrade a major version, submit a service ticket to request required permissions.

Video

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.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
  • Major version upgrades are unavailable to RDS for PostgreSQL 11 and Enhanced Edition.
• Due to OS restrictions, some DB instances do not support major version upgrades. To check whether your instance can be upgraded, go to the Major Version Upgrade page on the console. For the available target versions, see Upgrade Information on the console.
• The primary DB instance in a DR relationship can only be upgraded without workload cutover. To upgrade it with workload cutover, remove the DR relationship first.
• The DR instance in a DR relationship does not support major version upgrades. To upgrade it, remove the DR relationship first.
• 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

If any extension listed in Table 1 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

Precautions

• If the major version upgrade is complete and the application is cut over to the new instance but the workloads are incompatible with the new version, you need to roll back the upgrade. Submit a service ticket to unlock the original instance from the read-only status. Then, you can continue to use the original instance.
  

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

• The major version upgrade check consumes certain resources. Perform the check during off-peak hours.

  For instances that contain a large number of table-level objects (such as more than 100,000 tables, views, and functions), system resource usage (especially CPU and I/O) will exhibit significant periodic spikes. The resource consumption mainly occurs during the pre-check phase, where the pg_upgrade tool is invoked on the primary node to compare metadata before and after the upgrade.

• After a major version upgrade is complete, a new DB instance is created. The original DB instance is still retained and billed. You can release the original instance when the workloads on the new instance run stably.
• After a major version upgrade, the audit logs, error logs, and slow query logs of the original instance are still stored in the original instance. You can only view the logs generated after the upgrade on the new instance.
• Read replicas do not support major version upgrades. If your DB instance has a read replica, the read replica will not be upgraded synchronously. You need to recreate one 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.
• If a major version upgrade check fails and the check report is empty, submit a service ticket.
• A major version upgrade has the following impacts:
  If you upgrade your instance with workload cutover, the instance will be set to read-only. The instance will not be automatically unlocked from the read-only status even after the upgrade. The upgrade will interrupt your workloads for several minutes. Perform the upgrade during off-peak hours. If you choose to upgrade your instance without cutover, there is no impact on your workloads.

  

  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.

• After a major version upgrade is complete, the parameters of the new version have the following changes:
  • New parameters in the parameter template of the new version use the values defined in the template.
  • Existing parameters keep the values from the original version. If these values fall outside the new version's allowed ranges, the values specified in selected template are used instead.
  • Parameters from the original version that are not supported in the new version are automatically deleted.
  • Special parameters: wal_keep_segments (earlier than RDS for PostgreSQL 13) and wal_keep_size (RDS for PostgreSQL 13 or later) are WAL log retention parameters. After an instance is upgraded from a version earlier than RDS for PostgreSQL 13 to RDS for PostgreSQL 13 or later, the wal_keep_size parameter replaces the wal_keep_segments parameter, and the parameter value is automatically converted.
• Upgrading a major version will not upgrade the versions of extensions. For details about supported extension versions, see Supported Extensions. If the new instance supports any extension of a later version, you can run ALTER EXTENSION extension_name UPDATE TO 'new_version'; to update the extension, or uninstall and reinstall the extension of the latest version.
  

  Certain extensions (such as postgis) will cause a major version upgrade to fail. To prevent this issue, uninstall the extensions first before upgrade.

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.