Upgrade a major version using zero-downtime mode - ApsaraDB RDS

How it works

Zero-downtime mode runs pg_upgrade to upgrade a snapshot of your instance to the target version, then uses native logical replication to sync incremental changes from the source. You verify the destination instance before triggering the switchover. When you initiate the switchover, the instance goes read-only briefly while sequences are synchronized, then traffic shifts to the new version.

Billing

Free of charge.

Prerequisites

Before you begin, make sure that:

The instance runs ApsaraDB RDS for PostgreSQL 16 or an earlier version.
The storage type is cloud disk. Instances with high-performance local disks can only use blue-green deployment mode.
The billing method is pay-as-you-go or subscription. Serverless instances can only use blue-green deployment mode.
The instance is not a read-only instance or a dedicated cluster instance.
The wal_level parameter is set to logical. If not, modify the instance parameters before proceeding.
Babelfish is not enabled. The minor engine version number must not end with babelfish.

Usage notes

Review the following before starting the upgrade.

Business impact

DDL (Data Definition Language) operations are prohibited from the moment the upgrade starts until the switchover is complete. The downtime for the source instance is measured in seconds and depends on the number of sequences and the volume of large transaction writes.

Replication slots

If the source instance has a publication (publishing end of a replication slot), that replication slot is lost after the upgrade.
If the source instance has a subscriber (subscribing end of a replication slot), the upgrade may cause data synchronization issues due to replication slot preemption. See the FAQ section for details.

Parameter changes

Parameters not supported by the destination version are automatically deleted.
Parameters whose values fall outside the valid range for the destination version are reset to the default values of the destination version's parameter template.
statement_timeout is temporarily set to 0 during the upgrade and restored to its original value afterward.

DTS tasks

If the instance is a source or destination for Data Transmission Service (DTS), recreate the DTS task after the upgrade.

Plugin compatibility

The upgrade automatically updates the instance to the latest minor engine version, which may cause plugin compatibility issues.

Backups

A full backup is performed both before and after the upgrade to support clone-based recovery.

Impact at different upgrade stages

Upgrade stage	Impact
Start the major version upgrade	DDL operations are prohibited.
Create replication slots and publications	DDL operations are prohibited. WAL logs start to accumulate.
Start the subscriber and establish a logical replication relationship	DDL operations are prohibited. WAL logs start to be consumed and no longer accumulate. Logical replication generates a certain resource payload; the payload is closely related to the number of databases and traffic.
Start the switchover	DDL operations are prohibited. Logical replication generates a certain resource payload; the payload is closely related to the number of databases and traffic. The instance is read-only; the read-only duration is related to the number of sequences.
Complete the switchover (upgrade complete)	The logical replication slot is deleted and the resource payload generated by logical replication is eliminated. The instance resumes normal read and write operations.

After the upgrade task starts, go to the Upgrade History tab. In the Upgrade Log column of the target upgrade task, click View Information to view the detailed upgrade process.

Step 1: Run a pre-upgrade check

Log on to the ApsaraDB RDS console. In the top navigation bar, select the region where the instance resides, find the instance, and click its ID.
In the left navigation pane, click Major Version Upgrade.

If Major Version Upgrade does not appear, check the version and configuration of your instance. For details, see Prerequisites.
On the Upgrade Check tab, click Create upgrade check report.
Select the destination version, set Upgrade Mode to Zero Downtime, and click OK. The instance status changes to Maintaining Instance. After the check completes, the status returns to Running.
Review the check result: For help interpreting check results, see Interpret an ApsaraDB RDS for PostgreSQL major version upgrade check report.
- Success or Warning: proceed to Step 2.
- Failed: click View Information, fix the reported issues, and run the check again.
Important
- If the result is Warning, fix all reported issues and rerun the check until the result is Success. - If you create a plugin on the primary instance after a successful check, run the check again before upgrading.

Step 2: Start the upgrade

Click the Upgrade Instance tab, read the warnings, select a version from Select The Destination Version, and click Create Upgrade Task.
In the dialog box, read the prompt and click OK.
In the Create Major Engine Version Upgrade Task section, set Upgrade Mode to Zero Downtime and click Create.

When the instance status changes to Migrating, the upgrade task has started.

The time required depends on the number of database objects in the instance — the more objects, the longer it takes. Track progress in Task Hub.

Important

Upgrade tasks cannot be modified or deleted after creation.
While the instance is in Migrating state, O&M (operations and maintenance) tasks such as modifying parameters, restarting, or releasing the instance are not supported.

Step 3: Verify and switch over

Verify the destination instance

When the instance status changes from Migrating to Migrating Data Out, logical replication is established and the destination instance is ready for verification.

Go to the Upgrade History tab and use the Later Version Verification URL from the upgrade record to connect to the destination instance and verify the data.

The destination instance is in read-only mode during this phase. Write operations are not supported.

Switch over to the new version

After confirming that the data meets your expectations and the Upgrade Result shows Synchronizing, click Switchover in the Upgrade Log column.

- If Upgrade Result shows a different status, see Upgrade result descriptions. - To abandon the upgrade, click Cancel in the Upgrade Log column. This deletes the logical replication slot, removes the replication overhead from the source instance, and re-enables DDL operations.
In the Switchover dialog box, set Write Downtime Tolerance (in seconds) and click OK. This setting tells the system to wait for replication lag to clear before completing the switchover, which ensures data consistency. During this wait, Upgrade Result shows Read-only. If the tolerance period is exceeded, the system returns to Synchronizing and removes the read-only restriction. When Upgrade Result changes to Read-only, the switchover is in progress and the instance status is Migrating. To cancel a switchover that has already started, click Interrupted in the Upgrade Log column.
When Upgrade Result changes to Succeeded, the switchover is complete and the instance status is Running. Verify the current version on the Basic Information page of the instance.

To see the exact read-only duration after the upgrade, go to the Upgrade History tab and click View Information in the Upgrade Log column. The read-only time is the interval between the Switching time and the Switching completion time, and does not include time when the instance is unreachable due to DNS cache propagation.

Upgrade result descriptions

The Upgrade Result column on the Upgrade History tab shows one of the following values during the upgrade.

Upgrade result	Instance status	Description	Available actions
Running	Migrating	The upgrade task is running.	None.
Synchronizing	Migrating Data Out	Logical replication is normal.	Switch to the new version, or cancel the upgrade.
Replication Interrupted	Migrating Data Out	Logical replication is abnormal.	View the upgrade log to determine the cause, or cancel the upgrade.
Read-only	Migrating	The switchover is in progress. The instance is read-only while sequences are being synchronized.	Break: cancel the switchover.
Switchover	Migrating	Sequence synchronization is complete; finalizing the switchover.	None.
Cancel	Running	The upgrade task was canceled.	None.
Succeeded	Running	The upgrade task completed successfully.	None.

API reference

API operation	Description
UpgradeDBInstanceMajorVersionPrecheck	Runs a pre-upgrade check for a major version upgrade.
DescribeUpgradeMajorVersionPrecheckTask	Queries the pre-upgrade check report.
UpgradeDBInstanceMajorVersion	Upgrades the major engine version.
DescribeUpgradeMajorVersionTask	Queries the history of major version upgrade tasks.

FAQ

Can I modify the instance during a major version upgrade?

No. Instance modifications — including changing the instance type — are not supported during an upgrade. Perform other operations only after the upgrade completes.

Are automatic major version upgrades supported?

No. Automatic upgrades to major engine versions are not supported.

Are major version downgrades supported?

No. Downgrading to a lower major version after an upgrade is not supported. To run a lower version, purchase a new instance running that version and use DTS to migrate the data.

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

This conflict can occur when the PostGIS version is earlier than 2.5.2 and the source instance runs PostgreSQL 10 or 11, and you upgrade to PostgreSQL 12.

Step 1: Upgrade the PostGIS plugin on the source instance.

Run the following command twice to make sure it succeeds.

SELECT PostGIS_Extensions_Upgrade();
SELECT PostGIS_Extensions_Upgrade();

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

If the PostGIS Raster plugin is in use:

Run the following on the source instance to detach the raster_overviews view from the extension:

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

Upgrade the PostgreSQL instance to at least PostgreSQL 12.

After the upgrade, recreate 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;

If the PostGIS Raster plugin is not in use:

Drop the plugin on the source instance:
```
DROP EXTENSION PostGIS_Raster;
```
Upgrade the PostgreSQL instance to at least PostgreSQL 12.

How do I prevent data synchronization issues caused by replication slot preemption?

To keep subscription data on the source instance: make sure the source instance does not go down due to excessive load during the upgrade. If it goes down, the destination instance may preempt the replication slot, causing data inconsistency. After the upgrade completes, disable the subscription on the destination database:

\c your_database
ALTER SUBSCRIPTION your_subscription_name DISABLE;

To keep subscription data on the destination instance: disable the subscription on the source instance before the upgrade, then re-enable it on the destination instance after the upgrade completes.

On the source instance (before the upgrade):

\c your_database
ALTER SUBSCRIPTION your_subscription_name DISABLE;

On the destination instance (after the upgrade):

\c your_database
ALTER SUBSCRIPTION your_subscription_name ENABLE;

For more information about using replication slots for data subscription, see Logical subscription and Change tracking. If subscription data is already inconsistent, see the next FAQ.

How do I handle subscription data inconsistency after an upgrade?

After the upgrade succeeds, delete the table data on the destination instance (running the higher version), then recreate the subscription with copy_data=true. For details, see ALTER SUBSCRIPTION.

Alternatively, use the ON CONFLICT clause to merge data consumed by the source instance into the destination. The following example shows how to handle conflicts by timestamp:

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');

-- Update if the incoming row has a newer timestamp
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;

-- Skip if the incoming row has an older timestamp
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;

-- Insert new rows normally
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;

ApsaraDB RDS:Upgrade a major version using zero-downtime mode