Облачная платформаAdvanced

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.

Upgrade Methods

RDS for PostgreSQL allows you to upgrade the major version of your DB instance using 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 with cutover.
  • Upgrade with cutover: During this process, a new instance is created using a snapshot, the original instance is set to read-only and workloads are 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 application automatically connects to the new instance. The upgrade is complete. After the upgrade, the original instance remains read-only and will not automatically exit this state.

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.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 with no restrictions on minor versions

Upgrade Precautions

Table 1 Upgrade precautions

Phase

Precautions

Constraints

Before an upgrade

  • 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. Some DB instances of version 12 or later support major version upgrades across OSs. However, after the upgrade, backups of the instance cannot be restored to an existing instance.
  • 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.
  • Major version upgrade checks consume 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 experience significant periodic spikes. This 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.

During an upgrade

  • If a major version upgrade check fails and the check report is empty, contact customer service.
  • 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, data inserted into the instance during the cutover will be lost after the upgrade.

  • Note that the postgis extension does not support major version upgrades.
  • Read replicas do not support major version upgrades. If your instance has read replicas, the read replicas will not be upgraded along with your instance. You need to recreate them after a major version upgrade. For details, see Creating a Read Replica.

After an upgrade

  • After a major version upgrade is complete, a new DB instance is created but the original DB instance is still retained and billed. You can release the original one once you have confirmed that the workloads are running smoothly on the new instance.
  • After a major version upgrade is complete, 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 selected template.
    • Existing parameters in the new version 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 (for versions earlier than RDS for PostgreSQL 13) and wal_keep_size (for RDS for PostgreSQL 13 or later) are WAL log retention parameters. After an instance is upgraded from a version earlier than version 13 to version 13 or later, wal_keep_size replaces wal_keep_segments and its value is automatically converted.
  • If a major version upgrade with workload cutover is complete but the workload is incompatible with the new version, you must 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.
  • Data added after an upgrade with workload cutover is complete will not be automatically synchronized to 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.
  • If the current instance has a DR instance, upgrading the major version will not automatically upgrade the DR instance. As a result, the DR relationship will be disconnected, and you must re-establish it using an instance of the same version.
  • Upgrading a major version will not upgrade the versions of extensions (refer to Supported Extensions). If the new instance version 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.

Extensions Unsupported for Major Version Upgrades

After the major version upgrade pre-check is passed, if any extension listed in Table 2 is detected in the upgrade path, remove 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.

Table 2 Extensions unsupported for major version upgrades

Source Version

Target Version

Extension That Needs to Be Uninstalled Before an Upgrade to Prevent Upgrade Failures

Extension That Cannot Be Used After an Instance Upgrade

12

13

orafce, postgis_sfcgal

address_standardizer_data_us, pgaudit

14

orafce, postgis_sfcgal

anon, pgaudit

15

orafce, postgis_sfcgal

anon, pgaudit

16

orafce, postgis_sfcgal, pgl_ddl_deploy

anon, pgaudit

17

orafce, pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal

anon, pgaudit

18

orafce, pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal, pg_track_settings

anon, pgaudit

13

14

-

anon, pgaudit, pg_stat_kcache

15

-

anon, pgaudit, pg_stat_kcache

16

pgl_ddl_deploy

anon, pgaudit, pg_stat_kcache

17

pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal

anon, pgaudit, pg_stat_kcache, powa

18

pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal, pg_track_settings

anon, pgaudit, pg_stat_kcache, powa

14

15

-

pgaudit, pg_stat_kcache

16

pgl_ddl_deploy

pgaudit, pg_stat_kcache

17

pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal

pgaudit, pg_stat_kcache, powa

18

pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal, pg_track_settings

pgaudit, pg_stat_kcache, powa

15

16

pgl_ddl_deploy

pgaudit

17

pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal

pgaudit, pg_stat_kcache, powa

18

pgl_ddl_deploy, postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal, pg_track_settings

pgaudit, pg_stat_kcache, powa

16

17

postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal

pgaudit, pg_stat_kcache, powa

18

postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal, pg_track_settings, pg_tde

pgaudit, pg_stat_kcache, powa

17

18

postgis, postgis_raster, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal, pg_track_settings, pg_tde

pgaudit, pg_stat_kcache, powa

Upgrade Without Cutover

  1. Log in to the management console.
  2. Click in the upper left corner of the page and choose Database > Relational Database Service.
  3. On the Instances page, click the instance name.
  4. In the navigation pane, choose Major Version Upgrade.
  5. On the displayed page, click Upgrade Major Version. In the displayed dialog box, select a target version and click Next.
  6. On the pre-check page, click Check Now. 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.

  7. After the check is passed, click Next.
  8. On the page, choose a parameter template, select No for Cutover, click Autofill to enter 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 of the page and choose Database > Relational Database Service.
  3. On the Instances page, click the instance name.
  4. In the navigation pane, choose Major Version Upgrade.
  5. On the displayed page, click Upgrade Major Version. In the displayed dialog box, select a target version and click Next.
  6. On the pre-check page, click Check Now. 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.

  7. After the check is passed, click Next.
  8. On the page, choose a parameter template, select Yes for Cutover, specify Collect Statistics, click Autofill to enter 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 for which no statistics have been generated 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

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. 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

      Your database contains 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"
  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 extension. 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.