Upgrade a major version by using the in-place upgrade method - ApsaraDB RDS

This topic describes how to upgrade the major version of an ApsaraDB RDS for PostgreSQL instance using the in-place upgrade method.

Prerequisites

The instance runs RDS for PostgreSQL 16 or an earlier version.
The storage type of the instance is cloud disk.
Note
If the instance uses high-performance local disks, you can only upgrade the major version using the blue-green deployment method.
The billing method of the instance is pay-as-you-go or subscription.
Note
If the instance is a serverless instance, you can only upgrade the major version using the blue-green deployment method.
The instance is not a read-only instance or a dedicated cluster instance.
Babelfish is not enabled for the instance. The minor engine version number does not end with babelfish.

Background information

The in-place upgrade method uses pg_upgrade to upgrade the source instance to the destination version. All metadata, including the configuration and billing information of the source instance, is retained.

The ApsaraDB RDS console also supports major version upgrades using the zero-downtime method and the blue-green deployment method. For a comparison of the upgrade methods, see Introduction to major version upgrade methods.

Upgrade fee

Free.

Precautions

Service impact: During the switchover, the instance is set to read-only, and a transient connection that lasts for several minutes may occur. We recommend that you perform the upgrade during off-peak hours.
The duration of the read-only state depends on the number of database objects. The more database objects an instance has, the longer the read-only state lasts. If the number of database objects is in the millions, the read-only state may last for tens of minutes or even hours. You can run the SELECT count(1) FROM pg_class; command to view the number of database objects.
Instance type: If the instance type does not meet the recommended specifications during the upgrade, the system automatically attempts to upgrade the instance using the recommended instance type. This action places the instance in a read-only state for several minutes and causes an additional transient connection that lasts for a few seconds. Before you upgrade the major version, you must resolve the alerts about the instance type in the major version upgrade check report.
Replication slots:
- If the source instance is a publisher that has replication slots, the replication slots are lost after the upgrade.
- If the source instance is a subscriber that has replication slots, a replication slot preemption may occur during the upgrade and cause data inconsistency. For more information about how to resolve this issue, see How do I prevent data inconsistency caused by replication slot preemption during an upgrade?
Parameter changes:
- If the source instance uses parameters that are not supported by the destination version, these parameters are automatically deleted after the upgrade.
- If the value of a parameter on the source instance is outside the valid value range of the corresponding parameter in the destination version, the parameter is reset to its default value from the parameter template of the destination version.
- During the upgrade, the system temporarily changes the value of statement_timeout to 0 and restores the initial value after the upgrade is complete.
DTS tasks: If the instance to be upgraded is used as a source or destination instance for Data Transmission Service (DTS), you must rebuild the DTS task after the upgrade.
Plugin compatibility issues: When you upgrade the major version, the system automatically updates the instance to the latest minor engine version. This may cause plugin compatibility issues.
Instance backup: Before and after the upgrade, a full backup of the instance is created. This lets you clone and restore the instance later.

Step 1: Perform a pre-upgrade check

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.
(Optional) If read-only instances are created for the instance to be upgraded, change the endpoint of the read-only instances in your application to the endpoint of the primary instance, and then delete the read-only instances.
Note
To ensure service stability, we recommend that you change the application endpoint during off-peak hours.
In the navigation pane on the left, click Major Version Upgrade.
Note
If Major Version Upgrade does not appear in the navigation pane on the left, check the version and configuration of your ApsaraDB RDS for PostgreSQL instance. For more information, see Prerequisites.
On the Upgrade Check tab, click Create upgrade check report.
Select the destination version, set Upgrade Mode to Local Upgrade, and then click OK.
The instance enters the Maintaining Instance state. After the pre-upgrade check is complete, the instance status changes to Running.
If the result of the upgrade check report is Success or Warning, you can proceed with the major version upgrade. If the result is Failed, click View Information, fix the failed check items based on the report, and then perform the pre-upgrade check again. For more information about common errors and their causes, see Interpret the major version upgrade check report for an ApsaraDB RDS for PostgreSQL instance.
Important
- To ensure a successful upgrade, if the check result is Warning, we recommend that you fix the failed check items based on the report and perform the pre-upgrade check again until the result is Success.
- After the upgrade check is successful, if you create a plugin on the primary instance, you must perform the check again.

Step 2: Upgrade the major version

Click the Upgrade Instance tab. Read the warnings, select the destination version, and then click Create Upgrade Task.
In the dialog box that appears, read the prompt and click OK.
In the Create Major Engine Version Upgrade Task section, set Upgrade Mode to Local Upgrade and configure the Cutover time. The switchover time is when your services are switched to the destination instance after the migration is complete.
- immediately: The switchover is performed immediately after the migration is complete.
- Instance operation and maintenance time: The switchover is performed within the specified maintenance window.
Click Create.
When the instance status changes to Migrating, the upgrade task has started.
The time required for the upgrade is closely related to the number of database objects in the instance. The more database objects there are, the longer the upgrade takes. During the major version upgrade, you can view the upgrade progress in the Task Hub.
Important
- You cannot modify or delete an upgrade task after it is created.
- When the source instance is in the Migrating state, you cannot perform operations and maintenance (O&M) on the instance, such as modifying parameters, restarting the instance, or releasing the instance.
View the upgrade result.
The instance is successfully upgraded when the status of both the source and destination instances is Running.
Note
After the upgrade is complete, on the Upgrade History tab, find the upgrade task and click View Information in the Upgrade Log column to view the read-only duration and detailed upgrade process. The read-only duration is the period between the Cutover Time and the Switchover End Time. This period does not include the time during which the instance is inaccessible because the DNS cache is not purged.

What to do next

If you deleted read-only instances before the upgrade, perform the following steps:

Create ApsaraDB RDS for PostgreSQL read-only instances on the destination instance.
In your application, change the endpoint that you previously set to the primary instance endpoint to the new read-only instance endpoint.

Upgrade result description

During the upgrade, the upgrade record on the Upgrade History tab shows one of the following Upgrade Result.

Upgrade result	Instance status	Description	Action
Running	Migrating	The upgrade task is in progress.	None.
Succeeded	Running	The upgrade task is successful.	None.

Related API operations

API	Description
UpgradeDBInstanceMajorVersionPrecheck	Performs a pre-upgrade check for a major version upgrade of an ApsaraDB RDS for PostgreSQL instance.
DescribeUpgradeMajorVersionPrecheckTask	Queries the major version upgrade check report of an ApsaraDB RDS for PostgreSQL instance.
UpgradeDBInstanceMajorVersion	Upgrades the major version of an ApsaraDB RDS for PostgreSQL instance.
DescribeUpgradeMajorVersionTask	Queries the historical tasks of major version upgrades for an ApsaraDB RDS for PostgreSQL instance.

References

Introduction to major version upgrades
Upgrade a major version using the zero-downtime method
Upgrade a major version using the blue-green deployment mode
Interpret the major version upgrade check report for an ApsaraDB RDS for PostgreSQL instance

FAQ

Can I modify an instance during a major version upgrade, for example, by changing its instance type?

No, you cannot. You can perform other operations only after the upgrade is complete.

Is automatic major version upgrade supported?

Automatic upgrades to major database versions are not currently supported.

Is major version downgrade supported?

No, you cannot. To downgrade, you must purchase an instance that runs a lower version and then use DTS to migrate the data to that instance.

After I upgrade the major version, a `raster_overviews` conflict error is reported when I create a `raster_overviews` view on the destination instance. How do I fix this?

If the PostGIS version is earlier than 2.5.2 and the ApsaraDB RDS for PostgreSQL version is 10 or 11, a `raster_overviews` conflict may occur when you create a `raster_overviews` view on the destination instance after you upgrade the plugin and then upgrade the major version to PostgreSQL 12.

To fix this issue, perform the following steps:

Upgrade the PostGIS plugin version on the source instance.
Run the following command twice to ensure that the upgrade is successful.
```
SELECT PostGIS_Extensions_Upgrade();
SELECT PostGIS_Extensions_Upgrade();
```

Determine whether the PostGIS Raster plugin is used for your services and select the appropriate upgrade method.

The PostGIS Raster plugin is used

Run the following command on the source 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 ApsaraDB RDS for PostgreSQL instance to PostgreSQL 12 or later.

After the upgrade is complete, re-create the view on the destination 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 plugin is not used

Run the following command on the source instance to delete the plugin.
```
DROP EXTENSION PostGIS_Raster;
```
Upgrade the major version of the ApsaraDB RDS for PostgreSQL instance to PostgreSQL 12 or later.

How do I prevent data inconsistency caused by replication slot preemption during an upgrade?

If you want to keep the subscription data on the source instance, make sure that the source instance does not go down due to an excessive payload during the upgrade. Otherwise, the replication slot may be preempted by the destination instance, which can cause data inconsistency.
After the upgrade is complete, run the following SQL statement to disable the subscription on the destination database.
```
\c your_database
ALTER SUBSCRIPTION your_subscription_name DISABLE;
```
If you want to save the subscription data to the destination instance, disable the subscription on the source instance before the major version upgrade. After the upgrade is complete, enable the subscription on the destination instance. The following SQL statements provide an example:
- Disable the subscription on the source instance.
```
\c your_database
ALTER SUBSCRIPTION your_subscription_name DISABLE;
```
- Enable the subscription on the destination instance.
```
\c your_database
ALTER SUBSCRIPTION your_subscription_name ENABLE;
```

Note

For more information about how to use replication slots for data subscription, see Logical subscription and Change tracking.
If you do not perform the preceding operations, your subscription data may be inconsistent. To ensure data consistency, see How do I fix subscription data inconsistency after an upgrade?

How do I fix subscription data inconsistency after an upgrade?

After the upgrade is successful, delete the data from the tables in the destination instance. Then, re-create the subscription and set copy_data=true. For more information, see ALTER SUBSCRIPTION.

Use the CONFLICT keyword to import the data consumed by the source instance to the destination instance. The following code provides an 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;

Why do I need to delete read-only instances before a major version upgrade?

After the upgrade, the user-created read-only nodes and replication slots remain on the source instance and are not automatically transferred to the destination instance. Therefore, before you perform a major version upgrade, you must change the endpoint of the read-only instances in your application to the endpoint of the primary instance and then delete the existing read-only instances. After the upgrade is complete, you must purchase and configure new read-only instances.