ApsaraDB RDS:In-place major version upgrade

Prerequisites

Before you begin, ensure that:

• The instance runs ApsaraDB RDS for PostgreSQL 16 or an earlier version.

• The storage type is cloud disk.

• The billing method is pay-as-you-go or subscription.

• The instance is not a read-only instance or a dedicated cluster instance.

• Babelfish is not enabled. The minor engine version number does not end with babelfish.

Billing

Free.

Usage notes

Review the following before starting the upgrade.

SELECT count(1) FROM pg_class;

Step 1: Run a pre-upgrade check

1. Log on to the ApsaraDB RDS console. In the top navigation bar, select the region where the instance resides. Find and click the instance ID.

2. (Optional) If the instance has read-only instances, change the application endpoint from the read-only instance endpoint to the primary instance endpoint, then delete the read-only instances.

   Note Change the application endpoint during off-peak hours to minimize service impact.

3. In the left navigation pane, click Major Version Upgrade.

   Note If Major Version Upgrade does not appear, check the version and configuration of your instance. For more information, see Prerequisites.

4. On the Upgrade Check tab, click Create upgrade check report.

5. Select the destination version, set Upgrade Mode to Local Upgrade, and click OK. The instance status changes to Maintaining Instance while the check runs, then returns to Running when the check completes.

6. Review the check results:

   • Success or Warning: proceed to Step 2.

   • Failed: click View Information, fix the issues identified in the report, then run the check again. For details on common errors, see Interpret an ApsaraDB RDS for PostgreSQL major version upgrade check report.

   Important

   - If the result is Warning, fix the issues and rerun the check until the result is Success. - If you create a plugin on the primary instance after the check succeeds, run the check again.

Step 2: Upgrade the major engine version

1. Click the Upgrade Instance tab. Read the warnings, select the upgrade version, and click Create Upgrade Task.

2. In the dialog box, read the prompt and click OK.

3. In the Create Major Engine Version Upgrade Task section, set Upgrade Mode to Local Upgrade and configure Cutover time:

   • immediately: the switchover happens immediately after the migration completes.

   • Instance operation and maintenance time: the switchover happens within the configured maintenance window.

4. Click Create now. The instance status changes to Migration in Progress when the upgrade task starts. The duration scales with the number of database objects. Track progress in Task Hub.

   Important

   - Upgrade tasks cannot be modified or deleted after creation. - While the instance is in Migration in Progress, operations such as modifying parameters, restarting, or releasing the instance are unavailable.

5. The upgrade is complete when both the source and destination instances show a status of Running.

   Note On the Upgrade History tab, click View Information in the Upgrade Log column to see the read-only duration and detailed upgrade log. The read-only duration is the time between Cutover Time and Cutover End Time, excluding the DNS cache flush period.

What's next

If you deleted read-only instances before the upgrade:

1. Create read-only instances on the destination instance.

2. In your application, update the endpoint to point to the new read-only instance.

Upgrade result reference

The Upgrade Result column on the Upgrade History tab shows one of the following statuses:

API reference

References

• Introduction to major version upgrades

• Upgrade a major engine version using the zero-downtime method

• Upgrade a major engine version using the blue-green deployment method

• Interpret the major engine version upgrade check report

FAQ

Can I modify the instance during a major engine version upgrade?

No. Wait for the upgrade to complete before making changes to the instance.

Are automatic major engine version upgrades supported?

No. Major engine version upgrades must be initiated manually.

Are major engine version downgrades supported?

No. To run a lower version, purchase a new instance at the target version and use DTS to migrate the data.

After upgrading, I get a `raster_overviews` conflict error when creating a view. How do I fix this?

This affects PostGIS versions earlier than 2.5.2 on PostgreSQL 10 or 11 when upgrading to PostgreSQL 12. Follow these steps:

1. On the source instance, upgrade the PostGIS plugin. Run the following command twice to make sure it succeeds:

   SELECT PostGIS_Extensions_Upgrade();
   SELECT PostGIS_Extensions_Upgrade();

2. Choose the fix based on whether you use the PostGIS Raster plugin.

If you use PostGIS Raster:

1. On the source instance, drop the raster_overviews view from the extension and replace it with a placeholder:

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

2. Upgrade the PostgreSQL instance to at least PostgreSQL 12.

3. On the destination instance, recreate the full view and add it back to the extension:

   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;

If you do not use PostGIS Raster:

1. On the source instance, drop the extension:

   DROP EXTENSION PostGIS_Raster;

2. Upgrade the PostgreSQL instance to at least PostgreSQL 12.

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

Choose one of the following approaches based on where you want the subscription data to reside after the upgrade.

Keep subscription data on the source instance:

Make sure the source instance does not go down under excessive load during the upgrade. If it does, the replication slot may be preempted by the destination instance, causing data inconsistency. After the upgrade, disable the subscription on the destination database:

\c your_database
ALTER SUBSCRIPTION your_subscription_name DISABLE;

Move subscription data to the destination instance:

Before the upgrade, disable the subscription on the source instance:

\c your_database
ALTER SUBSCRIPTION your_subscription_name DISABLE;

After the upgrade, enable the subscription on the destination instance:

\c your_database
ALTER SUBSCRIPTION your_subscription_name ENABLE;

How do I fix subscription data inconsistency after an upgrade?

1. On the destination instance (higher version), delete the affected table data. Then recreate the subscription with copy_data=true. For details, see ALTER SUBSCRIPTION.

2. Use ON CONFLICT to import the data from the source instance (lower version) into the destination. The following example shows the pattern:

   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');
   
   -- Newer timestamp: update the existing row
   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;
   
   -- Older timestamp: skip the row
   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;
   
   -- New row: 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 engine version upgrade?

Read-only nodes and their replication slots remain attached to the source instance after the upgrade and are not automatically transferred to the destination instance. Delete them before the upgrade and recreate them on the destination instance afterward.