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: You can use it to test service compatibility of a new version. Upgrading a major version may cause service compatibility issues. A compatibility test is strongly recommended. After the test is passed, perform an upgrade in cutover mode. An upgrade without service cutover will not affect the original instance.
Upgrade with cutover: During an upgrade, the original instance is set to read-only and services are 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.

Constraints

To use this function, contact customer service.
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
- Major version upgrades are unavailable to RDS for PostgreSQL 11 and Enhanced Edition.
Due to OS restrictions, some instances do not support major version upgrades. To learn which versions your instance can be upgraded to, see the list of available versions on the Major Version Upgrade page.
DR instances do not support major version upgrades.
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 check, uninstall the extension before you perform a major version upgrade and reinstall it after the upgrade is complete. Otherwise, the instance will fail to be upgraded, or the extension cannot be used after the instance is upgraded.

**Table 1** Extensions unsupported for major version upgrades
Source Version	Target Version	Extension That Can Cause an Upgrade Failure	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
13	14	-	anon, pgaudit,pg_stat_kcache
	15	-	anon, pgaudit,pg_stat_kcache
	16	pgl_ddl_deploy	anon, pgaudit,pg_stat_kcache
14	15	-	pgaudit,pg_stat_kcache
14	16	pgl_ddl_deploy	pgaudit,pg_stat_kcache
15	16	pgl_ddl_deploy	pgaudit

Billing

The new instance generated after a major version upgrade is billed in pay-per-use mode. After confirming that the workloads are running stably, you can perform any of the following operations:

Change the billing mode of the new instance to yearly/monthly. For details, see Changing the Billing Mode from Pay-per-Use to Yearly/Monthly.
If the original instance is billed in pay-per-use mode, release the original instance to reduce fees. For details, see Deleting a Pay-per-Use DB Instance or Read Replica.
If the original instance is billed in yearly/monthly mode, unsubscribe from it. For details, see Unsubscribing from a Yearly/Monthly Instance. Unsubscribing from a yearly/monthly instance before it expires may cause fee loss. Before an upgrade, learn about the unsubscription rules in Unsubscribing from In-Use Resources.

Precautions

If you upgrade the major version of your instance with cutover and find that workloads are incompatible with the new version after the upgrade, you need to roll back the upgrade. Contact customer service to remove the read-only status of the original instance. 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.
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. You need to recreate a DR instance of the new version after a major version upgrade.
A major version upgrade has the following impacts:
- If you upgrade your instance with service cutover, the instance will be set to read-only during the upgrade and services will be interrupted for minutes. Perform the upgrade during off-peak hours. If you upgrade your instance without service cutover, there is no impact on your services.
  
  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:
  - Parameter modifications in the original instance are automatically synchronized to the new instance. For parameters that were not modified, default values for the new version are used.
  - If the original instance uses any parameter that is not supported by the new version, the parameter will be automatically deleted from the new instance.
  - If the value of any parameter in the original instance is not within the valid range configured in the new version, the new instance will use the default value defined in the parameter template of the new version.
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 the upgrade task to fail. In this case, uninstall the extensions before performing a major version upgrade.

Procedure

Log in to the management console.
Click in the upper left corner and select a region.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
On the Instances page, click the instance name.
In the navigation pane, choose Major Version Upgrade.

If the Major Version Upgrade tab is not displayed, contact customer service.
On the displayed page, select the target version, click Check Now, and wait for several minutes.
- If extensions are installed on the instance after a successful upgrade check, an upgrade may fail due to compatibility issues. If this happens, perform a check again.
- 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.
After the check is successful, click Next. Select "I understand Precautions for the Upgrade." and click Upgrade Now.
Specify Cutover and Collect Statistics.
During a major version upgrade, optimizer statistics are not automatically synchronized. Statistics need to be collected after the upgrade is complete.
- Before cutover: Service stability is ensured. If your instance has a large amount of 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 instance breakdowns during peak hours.

Upgrade Check Reports and Upgrade Reports

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

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 incompatible extensions that 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 be started 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.
Analyze causes based on the report items.
- loadable_libraries.txt
  This item displays incompatible libraries that usually correspond to incompatible extensions. Check the extensions listed in loadable_libraries.txt and determine whether to delete them. If the service stability is not affected after the extensions are deleted, 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 service 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 the following error information is displayed, the xxx extension does not exist in the target version. Delete xxx from shared_preload_libraries as required and then perform the upgrade.
```
FATLA: could not access file "xxx": No such file or directory.
```
  Example:
```
FATLA: 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 the following error information is displayed, there are too many tables in the current instance. In this case, increase the value of max_logcks_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 the following error information 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"
```