Major engine version upgrade and cross-version upgrade of an ApsaraDB RDS for PostgreSQL instance - ApsaraDB RDS

The PostgreSQL community has stopped the maintenance of earlier PostgreSQL versions such as PostgreSQL 9.4 and PostgreSQL 10. If you continue to use RDS instances that run earlier PostgreSQL versions, you may encounter potential risks. If you want your RDS instance to run a later version or use the new features of a later version, we recommend that you upgrade the major engine version of the RDS instance.

Background information

The PostgreSQL community releases a major engine version on a regular basis. Each new major engine version includes improvements in terms of functionality and performance compared with previous major engine versions. The PostgreSQL community will stop providing technical support for earlier major engine versions in phases, which increases the probability of performance risks and security risks of these versions. ApsaraDB RDS for PostgreSQL provides the major engine version upgrade feature. This feature enables you to use the performance and functionality delivered by new major engine versions and mitigates the risks that may be caused by an upgrade.

Upgrade procedure

Perform an upgrade check.
Check whether the original RDS instance supports major engine version upgrades. Then, view the check report that is generated. If the check result in the upgrade check report is Fail, you cannot upgrade the major engine version. You can upgrade the major engine version only after the upgrade check is passed.
Optional. Perform a compatibility test.
After you upgrade the major engine version of your RDS instance, the new major engine version may be incompatible with your workloads. We recommend that you use the No cutting configuration method to test whether the new major engine version is compatible with your workloads before you perform the upgrade.
Upgrade the major engine version.
Upgrade the major engine version of the original RDS instance again and select the Cutover configuration method to switch your workloads over to the new RDS instance. If you select the Cutover configuration method, the local upgrade and blue-green deployment modes are provided. You can select an upgrade mode based on your business requirements.
- Local upgrade: The major engine version upgrade is performed on the original RDS instance, and no new RDS instance is created. After the upgrade, the original RDS instance runs the new major engine version and inherits the original orders, name, tags, alert rules in CloudMonitor, and backup settings.
- Blue-green deployment: After the major engine version of the RDS instance is upgraded, the original RDS instance is retained and a new RDS instance is created. No fees are generated for the creation of the new RDS instance. After the new RDS instance is created, fees are generated based on the new billing method that may be different from the original billing method. After the upgrade is complete, fees are generated for both the original and new RDS instances, and the new RDS instance cannot enjoy the discounts provided for the original RDS instance.
  Billing method of the original RDS instance
  Billing method of the new RDS instance
  Subscription or pay-as-you-go
  Pay-as-you-go
  Serverless
  Serverless

Description of the Cutover and No cutting configuration methods

When you upgrade the major engine version, you can configure the Cutover configuration parameter to specify whether to switch your workloads over to the RDS instance that runs the new major engine version.

Configuration method		Description	Impact
No cutting		The system does not automatically change the endpoint of the original RDS instance to the endpoint of the new RDS instance for your application. Before you perform an upgrade, we recommend that you select the No cutting configuration method to test whether the new major engine version is compatible with your workloads. If the new major engine version passes the compatibility test, you can release the new RDS instance. Then, you can select the Cutover configuration method to perform the upgrade. For more information, see Release or unsubscribe from an ApsaraDB RDS for PostgreSQL instance.	The data migration does not interrupt your workloads on the original RDS instance. If you select the No cutting configuration method, you must change the endpoint of the original RDS instance to the endpoint of the new RDS instance on your application after the data is migrated to the new RDS instance. For more information about how to view the endpoint of an RDS instance, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance. If a read-only RDS instance is created for the original RDS instance, you can select the No cutting configuration method to create an RDS instance that runs the required major engine version. However, the original read-only RDS instance cannot be cloned. After the upgrade is complete, you must create a read-only RDS instance for the new RDS instance. For more information, see Create a read-only ApsaraDB RDS for PostgreSQL instance.
Cutover	Blue-green deployment	The system automatically changes the endpoint of the original RDS instance to the endpoint of the new RDS instance for your application. You do not need to modify the endpoint configuration in your application. This configuration method is used to perform an upgrade after you verify that the new major engine version is compatible with your workloads.	After the switchover is complete, you cannot roll your workloads back to the original RDS instance. Proceed with caution. During the switchover, the original RDS instance processes only read requests. You must perform the switchover during off-peak hours. If you select the local upgrade mode, a regular backup is performed before and after the upgrade. If you want to use an RDS instance that has the same configuration as the original RDS instance and the data of the original RDS instance, you can use the latest backup set that is created for the original RDS instance to clone the RDS instance.
	Local upgrade	If you use the local upgrade mode, only the major engine version of the original RDS instance is upgraded and no new RDS instance or order is created. After the upgrade, the original RDS instance runs the new major engine version and inherits the original configurations. You do not need to modify the endpoint configuration in your application. This configuration method is used to perform an upgrade after you verify that the new major engine version is compatible with your workloads.

Highlights

Cross-version upgrade: You can upgrade the major engine version of the original RDS instance to a later version. For example, you can upgrade the major engine version from PostgreSQL 10 to PostgreSQL 13.
Upgrade trial: You can use the No cutting configuration method to verify the feasibility of an upgrade without interruptions to your workloads on the original RDS instance.
Smooth upgrade:
- No application modification: You can use the Cutover configuration method to perform an upgrade. You do not need to modify the endpoint configuration in your application. If you use the blue-green deployment mode, the system automatically changes the endpoint of the original RDS instance to the endpoint of the new RDS instance after the switchover is complete. If you use the local upgrade mode, the endpoint of the original RDS instance remains unchanged.
  Note
  - If you use the local upgrade mode, the virtual IP address (VIP) of the RDS instance remains unchanged.
  - If you use the blue-green deployment mode, the VIP of the new RDS instance is different from the VIP of the original RDS instance. If you configure the VIP of the original RDS instance in your application, you must modify the application configuration. We recommend that you configure the endpoint instead of VIP of the original RDS instance in your application. For more information, see View and change the endpoints and port numbers.
- No downtime: An upgrade does not cause downtime on the original RDS instance. This mitigates the risks of service interruptions. However, the original RDS instance processes only read requests for a few minutes during the upgrade.
- Reserved instance configuration:
  - The new RDS instance carries over the IP address whitelists, parameter settings, and extensions of the original RDS instance. However, this does not apply to the extensions or parameters that are not supported in the new major engine version.
  - If the original RDS instance supports fully encrypted databases, the new RDS instance also supports fully encrypted databases and carries over the key that is used on the original RDS instance to encrypt data. For more information, see Create a fully encrypted database on an ApsaraDB RDS for PostgreSQL instance.

Billing rules

Local upgrades do not cause changes in fees and do not generate orders.
Blue-green deployment
- The billing method of the new RDS instance may be changed.
  Billing method of the original RDS instance
  Billing method of the new RDS instance
  Subscription or pay-as-you-go
  Pay-as-you-go
  Serverless
  Serverless
- After the upgrade is complete, you are charged for both the original and the new RDS instances. If you confirm that your workloads run stably on the new RDS instance, you can change the billing method of the new RDS instance to subscription and release or unsubscribe from the original RDS instance. For more information, see Change the billing method of an ApsaraDB RDS for PostgreSQL instance from pay-as-you-go to subscription and Release or unsubscribe from an ApsaraDB RDS for PostgreSQL instance. However, you must take note of the following items:
  - If you release a subscription RDS instance before it expires, you are eligible for a refund. However, the refund will be less than the original purchase amount.
  - If you purchased your original RDS instance at discount rates, the new RDS instance does not carry over the discounts that are offered for the original RDS instance. To determine whether to upgrade the major engine version of the original RDS instance, you can log on to the Alibaba Cloud Management Console and go to the Unsubscribe page to check the refund amount.
  - The refund amount and the arrival time of the refund are displayed in your bills. The refund does not arrive immediately.

Prerequisites

The original RDS instance must meet the following requirements:

The original RDS instance runs PostgreSQL 16 or earlier.
Note
You can directly upgrade the major engine version of your RDS instance from PostgreSQL 9.4 to PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, and PostgreSQL 14. If you want to upgrade the major engine version from PostgreSQL 9.4 to PostgreSQL 15, you must upgrade the major engine version to PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, or PostgreSQL 14. Then, you can upgrade the major engine version to PostgreSQL 15 or later.
The original RDS instance resides in a virtual private cloud (VPC).
If the original RDS instance resides in the classic network, you must change the network type of the original RDS instance to VPC before you perform an upgrade. When you change the network type, clear Reserve original classic network endpoint. For more information about how to view or change the network type of an RDS instance, see Change the network type of an ApsaraDB RDS for PostgreSQL instance.
Note
If you select Reserve original classic network endpoint, you must wait until the retention period of the endpoint ends before you can start the upgrade task.
The original RDS instance cannot be a read-only instance and cannot be created in a dedicated cluster. For more information, see Overview of read-only ApsaraDB RDS for PostgreSQL instances and What is ApsaraDB for MyBase?
The ID of the original RDS instance does not start with pg-cn.
Babelfish is not enabled for the original RDS instance. If the minor engine version of the original RDS instance does not include babelfish, Babelfish is not enabled.
If a read-only RDS instance is created for the original RDS instance, you cannot use the Cutover configuration method. If you want to use the Cutover configuration method, you must follow the instructions in the Perform the following operations before and after you perform an upgrade section.

Impacts

An upgrade has the following impacts:

If you select the Cutover configuration method, the system needs to switch your workloads over to a new RDS instance during the upgrade. The original RDS instance processes only read requests and a transient connection that lasts a few minutes occurs during the switchover. We recommend that you perform an upgrade during off-peak hours. If you select the No cutting configuration method, the original RDS instance is not affected.
Note
- The period of time during which the original RDS instance processes only read requests varies based on the number of database objects. If a large number of database objects exist in the original RDS instance, the original RDS instance processes only read requests for a long period of time. If millions of database objects exist, the original RDS instance may process only read requests for dozens of minutes or several hours. You can execute the SELECT count(1) FROM pg_class; statement to query the number of database objects in the original RDS instance.
- The duration of a transient connection varies based on the interval at which Domain Name System (DNS) caches are refreshed. You can switch to a different vSwitch and estimate the intervals to refresh DNS caches on your database client based on the duration of the transient connection. For more information, see Switch an ApsaraDB RDS for PostgreSQL instance to a different vSwitch.
- The period of time that is required to complete the upgrade varies based on the data volume and the number of database objects in the original RDS instance. If a large amount of data and a large number of database objects exist in the original RDS instance, the upgrade requires a long period to complete.
- If you use the blue-green deployment mode and do not want the original RDS instance to be read-only after the switchover, you must set the rds_force_trans_ro_non_sup parameter to off after the upgrade. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
If the original RDS instance uses a parameter that is not supported by the new major engine version, the parameter is deleted from the new RDS instance. If the value of a parameter in the previous major engine version is not within the value range that is supported in the new major engine version, the system sets the parameter to the default value that is specified in the new major engine version.
If you use the blue-green deployment mode, the new RDS instance does not inherit the name, tags, alert rules in CloudMonitor, and backup data of the original RDS instance. For more information, see Add tags to ApsaraDB RDS instances, Manage alerts, and Back up an ApsaraDB RDS for PostgreSQL instance.
If you use the blue-green deployment mode, the self-managed read-only RDS instances and logical replication slots of the original RDS instance are not automatically transferred to the new RDS instance after the upgrade. After the upgrade, you must create read-only RDS instances and logical replication slots for the new RDS instance.
If you use the local upgrade mode, the self-managed read-only RDS instances and logical replication slots of the original RDS instance are lost after the upgrade. You must select an upgrade mode based on your business requirements.
If the original RDS instance serves as the source RDS instance or destination RDS instance of a migration task that is created in the Data Transmission Service (DTS) console, you must re-create the migration task after you perform an upgrade. For more information about how to create a migration task in the DTS console, see What is DTS?
If the subscriber of replication slots resides on the original RDS instance, the replication slots may be preempted. If the replication slots are preempted, data is not synchronized. To avoid data synchronization issues, you must perform the following operations: If you use the local upgrade mode, we recommend that you disable the subscription to replication slots on the original RDS instance before the upgrade.
How do I resolve the issue that data is not synchronized when replication slots are preempted during the upgrade?
- If you want to store subscribed data on the original RDS instance, make sure that the RDS instance does not fail due to heavy loads during the upgrade. If the RDS instance fails due to heavy loads during the upgrade, the replication slots may be preempted by the new RDS instance. As a result, data is not synchronized.
  After the upgrade is complete, execute the following SQL statement to disable the subscription to replication slots on the new RDS instance:
```
\c your_database
ALTER SUBSCRIPTION your_subscription_name DISABLE;
```
- If you want to store subscribed data on the new RDS instance, you must disable the subscription to replication slots on the original RDS instance. Then, you can perform the upgrade. After the upgrade is complete, enable the subscription to replication slots on the new RDS instance. Sample SQL statements:
  - Disable the subscription on the original RDS instance.
    \c your_database ALTER SUBSCRIPTION your_subscription_name DISABLE;
  - Enable the subscription on the new RDS instance.
    \c your_database ALTER SUBSCRIPTION your_subscription_name ENABLE;
Note
For more information about how to use replication slots to subscribe to data, see Use the logical subscription feature and Change tracking.
If you do not follow the preceding steps, the subscribed data may be inconsistent. If data inconsistency occurs, we recommend that you handle the issue based on How do I handle the issue that subscribed data is inconsistent after an upgrade?
If a read-only RDS instance is attached to the original RDS instance, you cannot directly perform an upgrade.
Perform the following operations before and after you perform an upgrade:
1. Change the endpoint of the read-only RDS instance to the endpoint of the original RDS instance on your application.
  Note
  For service stability purposes, we recommend that you modify the endpoint configuration on your application during off-peak hours.
2. Delete the read-only RDS instance.
3. Upgrade the major engine version. For more information, see Procedure.
4. After the upgrade is complete, create a read-only RDS instance for the RDS instance that runs the new major engine version. For more information, see Create a read-only ApsaraDB RDS for PostgreSQL instance.
5. Change the endpoint of the original RDS instance to the endpoint of the new read-only RDS instance on your application.
When you upgrade the major engine version, the latest minor engine version is required. This may cause extension compatibility issues. For more information, see Update the minor engine version.
The period of time that is required to complete the upgrade varies based on the data volume of your RDS instance. You can view the upgrade progress on the Task Center page in the ApsaraDB RDS console. For more information, see Use Task Center.
After you upgrade the major engine version of your RDS instance, you cannot roll back the upgrade. If you want to use an early major engine version, you must create another RDS instance that runs the required major engine version and use Data Transmission Service (DTS) to migrate the data of the upgraded RDS instance to the newly created RDS instance.

Procedure

If a read-only RDS instance is attached to the original RDS instance, perform the following steps before you perform an upgrade:
1. Change the endpoint of the read-only RDS instance to the endpoint of the original RDS instance on your application.
  Note
  For service stability purposes, we recommend that you modify the endpoint configuration on your application during off-peak hours.
2. Delete the read-only RDS instance.
Go to the Major Version Upgrade page.
1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
2. In the left-side navigation pane, click Major Version Upgrade.
  Note
  If you do not find Major Version Upgrade in the ApsaraDB RDS console, check whether your RDS instance meets all requirements that are described in Prerequisites.
On the Upgrade Check tab, select the new major engine version and click Create upgrade check report. This process requires a few minutes.
Note
- Make sure that your RDS instance passes the upgrade check. Then, proceed with the subsequent steps.
- If you execute the CREATE EXTENSION statement on your RDS instance after the RDS instance passes the upgrade check, you must perform an upgrade check again.
If the upgrade check report indicates that your RDS instance fails the upgrade check, you can click View Information in the Report Content column to view the details about the failure in the report. For more information, see Introduction to the check report of a major engine version upgrade to an ApsaraDB RDS for PostgreSQL instance.
Click the Upgrade Instance tab. On the tab that appears, read the warning that is displayed, configure the Select upgrade version, and then click Continue to create upgrade instance.

In the message that appears, read the tips, click OK, and then configure parameters. The following table describes the key parameters.

Parameter	Description
Storage type	Select the storage type that is used for the new RDS instance. Note If the Upgrade mode parameter is set to Local upgrade, you do not need to configure this parameter. The major engine version upgrade feature is based on snapshots for cloud disks. You can select one of the following storage types: General ESSD ESSD PL1 ESSD PL2 ESSD PL3
The Available Zone	Specify the zones and vSwitches of the new RDS instance and its secondary RDS instance. The zones that you specify can be different from the zones of the original RDS instance. Note If the Upgrade mode parameter is set to Local upgrade, you do not need to configure these parameters.
For Available Zone
Primary Instance Switch
Standby Instance Switch
Cutover configuration	Specify whether to enable the system to automatically switch your workloads over to the new RDS instance. No cutting: The system does not automatically switch your workloads over to the new RDS instance. Before you perform an upgrade, we recommend that you select the No cutting configuration method to test whether the new major engine version is compatible with your workloads. If the new major engine version passes the compatibility test, you can release the new RDS instance. Then, you can select the Cutover configuration method to perform the upgrade. For more information, see Release or unsubscribe from an ApsaraDB RDS for PostgreSQL instance and Procedure. Note The data migration does not interrupt your workloads on the original RDS instance. If you select the No cutting configuration method, you must change the endpoint of the original RDS instance to the endpoint of the new RDS instance on your application after the data is migrated to the new RDS instance. For more information about how to view the endpoint of an RDS instance, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance. Cutover: The system automatically switches your workloads over to the new RDS instance. This configuration method is used to perform an upgrade after you verify that the new major engine version is compatible with your workloads. If you use the local upgrade mode, no new RDS instance is created and the original RDS instance inherits the original configurations. You do not need to modify the endpoint configuration in your application. If you use the blue-green deployment mode, your application is automatically connected to the new RDS instance after the switchover is complete. You do not need to modify the endpoint configuration in your application. Note After the switchover is complete, you cannot roll your workloads back to the original RDS instance. Proceed with caution. During the switchover, the original RDS instance processes only read requests. We recommend that you perform the switchover during off-peak hours. If read-only RDS instances are attached to the original RDS instance, you cannot select the Cutover configuration method. In this case, you can upgrade the major engine version of the original RDS instance only by using the No cutting configuration method. In addition, the read-only RDS instances that are attached to the original RDS instance are not cloned. After the upgrade is complete, you must create a read-only RDS instance for the RDS instance that runs the new major engine version. For more information, see Create a read-only ApsaraDB RDS for PostgreSQL instance.
Cutover time	Specify the point in time when the system switches your workloads over to the new RDS instance after the data is migrated to the new RDS instance. immediately: After the data is migrated to the new RDS instance, the system immediately switches your workloads over to the new RDS instance. Instance operation and maintenance time: The system switches your workloads over to the new RDS instance during the maintenance window that you specified. For more information, see Set the maintenance window of an ApsaraDB RDS for PostgreSQL instance. Note This parameter is required only when the Cutover configuration parameter is set to Cutover.
Upgrade mode	Select an upgrade mode. Note This parameter is required only when the Cutover configuration parameter is set to Cutover. Local upgrade: The major engine version upgrade is performed on the original RDS instance, and no new RDS instance is created. After the upgrade, the original RDS instance runs the new major engine version and inherits the original orders, name, tags, alert rules in CloudMonitor, and backup settings. Blue-green deployment: After the major engine version of the RDS instance is upgraded, the original RDS instance is retained and a new RDS instance is created. No fees are generated for the creation of the new RDS instance. After the new RDS instance is created, fees are generated based on the new billing method that may be different from the original billing method. After the upgrade is complete, fees are generated for both the original and new RDS instances, and the new RDS instance cannot enjoy the discounts provided for the original RDS instance.
Statistical information collection mode	Specify the point in time at which the system collects the statistics of the new RDS instance. Collection before cutting: This option ensures the stability of your database service. If the original RDS instance contains a large amount of data, the upgrade may require a long period of time. Collection after cutting: This option accelerates the upgrade process. If you access tables for which no statistics are generated, the query plans that you specify may be inaccurately executed. In addition, your database service may be unavailable during peak hours. Note If you select the No cutting configuration method, the Collection before cutting option specifies that the system collects the statistics of the new RDS instance before the new RDS instance starts to process read and write requests, and the Collection after cutting option specifies that the system collects the statistics of the new RDS instance after the new RDS instance starts to process read and write requests.
Storage space	Specify the storage capacity of the new RDS instance. Note If the Upgrade mode parameter is set to Local upgrade, you do not need to configure this parameter. If the original RDS instance uses local SSDs, you can reduce the storage capacity of the RDS instance when you upgrade the major engine version of the RDS instance. The minimum value that you specify for the Storage space parameter must meet the following requirements: The minimum value is determined by the smaller one between the following values: The used storage of the original RDS instance multiplied by 120% in GB. If the result is not a multiple of 5, the obtained value is rounded up to a multiple of 5. Note You can view the used storage of the original RDS instance in the Disk Storage(MB) section on the Monitoring and Alerting page. For more information, see View the Enhanced Monitoring metrics of an ApsaraDB RDS for PostgreSQL instance. The storage capacity of the original RDS instance. The minimum value is greater than or equal to the minimum storage capacity of an ESSD. If the minimum value is smaller than the minimum storage capacity of an ESSD, you must use the minimum storage capacity of the ESSD as the minimum value. The following list provides the minimum storage capacities of ESSDs of different performance levels (PLs). ESSD PL1: 20 GB ESSD PL2: 500 GB ESSD PL3: 1,500 GB Note Examples: The storage capacity of the original RDS instance is 100 GB, 70 GB of storage is used, and ESSD PL1 is selected as the storage type when you upgrade the major engine version of the RDS instance. Calculation method: 70 × 120% = 84, rounded up to 85. The minimum value for the Storage space parameter is 85 GB. The storage capacity of the original RDS instance is 700 GB, 350 GB of storage is used, and ESSD PL2 is selected as the storage type when you upgrade the major engine version of the RDS instance. Calculation method: 350 × 120% = 420. The obtained value 420 GB is smaller than 500 GB that is supported by an ESSD of PL2. The minimum value for the Storage space parameter is 500 GB.
Instance specification	Specify the instance type of the new RDS instance. For more information about the instance types that are supported, see Primary ApsaraDB RDS instance types. Note If the Upgrade mode parameter is set to Local upgrade, you do not need to configure this parameter.

Click Create now.
If the Upgrade Mode parameter is set to Blue green deployment, the status of the original RDS instance changes to Migrating and an RDS instance in the Creating state appears in the instance list. If both the original RDS instance and new RDS instance enter the Running state, the new RDS instance is created and the upgrade is complete. The time that is required for the upgrade varies based on the amount of data.
Note
- After you create an upgrade task, you cannot modify or delete the task.
- If the original RDS instance is in the Migrating state, you cannot perform O&M operations on the RDS instance. For example, you cannot modify the parameters of the RDS instance and restart or release the RDS instance.
- After you click Create now, if the message Insufficient resources appears, change the value of the The Available Zone parameter.
If a read-only RDS instance is attached to the original RDS instance and you deleted the read-only RDS instance in Step 1, perform the following steps after the upgrade:
1. Create a read-only RDS instance for the new RDS instance. For more information, see Create a read-only ApsaraDB RDS for PostgreSQL instance.
2. Change the endpoint of the original RDS instance to the endpoint of the new read-only RDS instance on your application. For more information, see Step 1.

What to do next

If you use the blue-green deployment mode and confirm that your workloads stably run on the new RDS instance, change the billing method of the new RDS instance to subscription. For more information, see Change the billing method of an ApsaraDB RDS for PostgreSQL instance from pay-as-you-go to subscription.
If you use the blue-green deployment mode, release the original RDS instance. For more information, see Release or unsubscribe from an ApsaraDB RDS for PostgreSQL instance.
Note
- If you release a subscription RDS instance before it expires, you are eligible for a refund. However, the refund will be less than the original purchase amount.
- If you purchased your original RDS instance at discount rates, the new RDS instance does not carry over the discounts that are offered for the original RDS instance. You can log on to the Alibaba Cloud Management Console and go to the Unsubscribe page to check the refund amount.
- The refund amount and the arrival time of the refund are displayed in your bills. The refund does not arrive immediately.
Create read-only RDS instances for the new RDS instance based on your business requirements because the new RDS instance does not carry over the read-only RDS instances of the original RDS instance. For more information, see Create a read-only ApsaraDB RDS for PostgreSQL instance.

Related operations

Introduction to the check report of a major engine version upgrade for an ApsaraDB RDS for PostgreSQL instance
View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance
Primary ApsaraDB RDS instance types
Release or unsubscribe from an ApsaraDB RDS for PostgreSQL instance
Change the billing method of an ApsaraDB RDS for PostgreSQL instance from pay-as-you-go to subscription

You can also call API operations to upgrade the major engine version of your RDS instance. The following table describes the related API operations:

Operation	Description
UpgradeDBInstanceMajorVersionPrecheck	Checks the compatibility between a new major engine version and workloads on an ApsaraDB RDS for PostgreSQL instance before an upgrade.
DescribeUpgradeMajorVersionPrecheckTask	Queries the check report for the major engine version upgrade of an ApsaraDB RDS for PostgreSQL instance.
UpgradeDBInstanceMajorVersion	Upgrades the major engine version of an ApsaraDB RDS for PostgreSQL instance.
DescribeUpgradeMajorVersionTask	Queries the tasks that are created to upgrade the major engine version of an ApsaraDB RDS for PostgreSQL instance.

FAQ

Can I change the configuration such as the instance type of an RDS instance during the major engine version upgrade of the RDS instance?

No, you cannot change the configuration such as the instance type of an RDS instance during the major engine version upgrade of the RDS instance. You can change the configuration only after the major engine version is upgraded.

Can the major engine version of an RDS instance be automatically upgraded?

No, the major engine version of an RDS instance cannot be automatically upgraded.

When I create the raster_overviews view in a new RDS instance after a major engine version upgrade is complete, a view incompatibility issue occurs. What do I do?

If the version of PostGIS extensions is earlier than 2.5.2 and the major engine version of your RDS instance is PostgreSQL 10 or PostgreSQL 11, update the version of PostGIS extensions and then upgrade the major engine version to PostgreSQL 12. In this case, when you create the raster_overviews view in the new RDS instance, a view incompatibility issue may occur.

To resolve the issue, you can perform the following steps:

Update the version of PostGIS extensions in the original RDS instance.
Execute the following statements twice to ensure that the update is successful:
```
SELECT PostGIS_Extensions_Upgrade();
SELECT PostGIS_Extensions_Upgrade();
```

Determine whether the postgis_raster extension is used and select an update method based on your business requirements.

The postgis_raster extension is used.

Execute the following statements in the original RDS instance to modify the raster_overviews view:

ALTER EXTENSION PostGIS_Raster DROP VIEW raster_overviews;
CREATE OR REPLACE VIEW raster_overviews AS SELECT 1;

Upgrade the major version of the RDS instance to PostgreSQL 12 or later.

After the upgrade is complete, re-create the view in the new RDS instance.

CREATE 
OR REPLACE VIEW raster_overviews AS 
SELECT 
  current_database() AS o_table_catalog, 
  n.nspname AS o_table_schema, 
  c.relname AS o_table_name, 
  a.attname AS o_raster_column, 
  current_database() AS r_table_catalog, 
  split_part(
    split_part(s.consrc, '''::name', 1), 
    '''', 
    2
  ): :name AS r_table_schema, 
  split_part(
    split_part(s.consrc, '''::name', 2), 
    '''', 
    2
  ): :name AS r_table_name, 
  split_part(
    split_part(s.consrc, '''::name', 3), 
    '''', 
    2
  ): :name AS r_raster_column, 
  trim(
    both 
    from 
      split_part(s.consrc, ',', 2)
  ): :integer AS overview_factor 
FROM 
  pg_class c, 
  pg_attribute a, 
  pg_type t, 
  pg_namespace n, 
  (
    SELECT 
      connamespace, 
      conrelid, 
      conkey, 
      pg_get_constraintdef(oid) As consrc 
    FROM 
      pg_constraint
  ) AS s 
WHERE 
  t.typname = 'raster' : :name 
  AND a.attisdropped = false 
  AND a.atttypid = t.oid 
  AND a.attrelid = c.oid 
  AND c.relnamespace = n.oid 
  AND c.relkind = ANY(
    ARRAY[ 'r' : :char, 'v' : :char, 'm' : :char, 
    'f' : :char ]
  ) 
  AND s.connamespace = n.oid 
  AND s.conrelid = c.oid 
  AND s.consrc LIKE '%_overview_constraint(%' 
  AND NOT pg_is_other_temp_schema(c.relnamespace) 
  AND has_table_privilege(c.oid, 'SELECT' : :text); ALTER EXTENSION PostGIS_Raster 
ADD 
  VIEW raster_overviews;

The postgis_raster extension is not used.

Execute the following statement in the original RDS instance to delete the postgis_raster extension:
```
DROP EXTENSION PostGIS_Raster;
```
Upgrade the major version of the RDS instance to PostgreSQL 12 or later.

How do I handle the issue that the subscribed data is inconsistent after an upgrade?

After the upgrade is successful, clear all table data in the new RDS instance, re-create subscriptions, and then set copy_data to true. For more information, see ALTER SUBSCRIPTION.

Use the CONFLICT keyword to import data consumed on the original RDS instance to the new RDS instance. Example:

CREATE TABLE my_tbl(id INT PRIMARY KEY, t TIMESTAMP, val TEXT);

INSERT INTO my_tbl VALUES (1, CURRENT_TIMESTAMP, 'a');
INSERT INTO my_tbl VALUES (2, CURRENT_TIMESTAMP, 'b');
INSERT INTO my_tbl VALUES (3, CURRENT_TIMESTAMP, 'c');

-- in case with newer timestamp: do update
INSERT INTO my_tbl VALUES (1, CURRENT_TIMESTAMP, 'd') ON CONFLICT(id) DO UPDATE
	SET t = excluded.t,
		val = excluded.val
	WHERE my_tbl.t < excluded.t;

-- in case with older timestamp: do nothing
INSERT INTO my_tbl VALUES (2, CURRENT_TIMESTAMP - '10 hours'::interval, 'e') ON CONFLICT(id) DO UPDATE
	SET t = excluded.t,
		val = excluded.val
	WHERE my_tbl.t < excluded.t;

-- in case with new val: just insert
INSERT INTO my_tbl VALUES (5, CURRENT_TIMESTAMP - '10 hours'::interval, 'f') ON CONFLICT(id) DO UPDATE
	SET t = excluded.t,
		val = excluded.val
	WHERE my_tbl.t < excluded.t;

Billing method of the original RDS instance	Billing method of the new RDS instance
Subscription or pay-as-you-go	Pay-as-you-go
Serverless	Serverless