Check report of a major engine version upgrade for an ApsaraDB RDS for PostgreSQL instance - ApsaraDB RDS

This topic describes the check report of a major engine version upgrade for an ApsaraDB RDS for PostgreSQL instance. This topic also describes the common errors included in the report and the solutions to these errors.

The check report is provided in Chinese and English.

Check items

If the check result in the upgrade check report is Failed, you can perform the following steps to view the details of the failure: Log on to the ApsaraDB RDS console, go to the Major Version Upgrade page, and then click View Information in the Report Content column. For more information, see Upgrade the major engine version.

The following section describes the information and common errors that a check report may contain.

Warnings

Content

During a major engine version upgrade of an RDS instance, the read-only time and the memory and disk resources of the RDS instance are affected by the number of its database objects. If the number of database objects is excessively large and the memory and disk resources are insufficient, the upgrade may fail. In this case, the system provides the recommended memory capacity, recommended minimum memory capacity, and recommended disk size on the Upgrade Check tab.

Recommended memory capacity: During a major engine version upgrade, the system can upgrade multiple databases of an RDS instance at the same time. If the memory capacity of an RDS instance is greater than or equal to the recommended memory capacity, the RDS instance is immediately upgraded to reduce the read-only time of the RDS instance.
Recommended minimum memory capacity: If the memory capacity of an RDS instance is greater than or equal to the recommended minimum memory capacity, the RDS instance is upgraded. However, the read-only time of the RDS instance may be extended because the system upgrades each database of the RDS instance in sequence.
Recommended disk size: During a major engine version upgrade, the system temporarily replicates all object definitions. This doubles the inode consumption. During the upgrade, if the disk size is less than the recommended disk size, the upgrade may fail.

Warnings and solutions

Disk warning

Format: Total disk size: {*} GB; Used disk size: {*} GB; Used inode: {*}; bytes-per-nodes: {*}; Minimum disk size required for the upgrade: {*} GB.
Possible cause: The number of database objects is excessively large, which requires a large disk size.
Solution:
- In blue-green deployment mode, you must specify a storage capacity for the new RDS instance that is greater than or equal to the minimum disk size required for an upgrade.
- In local upgrade mode, you must expand the storage capacity of the RDS instance that you want to upgrade before the upgrade. The new storage capacity must be greater than or equal to the minimum disk size required for an upgrade. For more information, see Change instance specifications.

Memory warning

Format: Current memory capacity: {*} GB; Recommended memory capacity: {*} GB; Minimum memory capacity: {*} GB.
Possible cause: The number of database objects is excessively large. Make sure that the memory resource of the RDS instance is sufficient. This helps reduce the read-only time of the RDS instance during an upgrade.
Solution:
- In blue-green deployment mode, you must specify a storage capacity for the new RDS instance that is greater than or equal to the minimum disk size required for an upgrade.
- In local upgrade mode, you must upgrade the specifications of the RDS instance if the memory capacity provided by the instance type is less than the recommended memory capacity. For more information, see Change instance specifications.

Subscription warning

Format: The subscriber of replication slots resides on the RDS instance. To prevent data inconsistency, we recommend that you view related Alibaba Cloud documentation.
Possible cause: The subscriber of replication slots resides on the RDS instance. Execute the SELECT * FROM pg_subscription; statement to view the subscriber.
Solution: For more information, see How do I handle the issue that the subscribed data is inconsistent after an upgrade?

Errors

Content

The system checks whether a redundant superuser account is created in the background or an invalid encryption method is configured for a standard account.
The system checks whether the upgrade check fails. If the upgrade check fails, the "pg_upgrade error log" error message is included in the check report.
The system checks whether the pgcrypto extension is installed in the pg_catalog schema.

Errors and solutions

Account error

Format: Invalid super account: {*}; Invalid account: {*}. View related Alibaba Cloud documentation.
Possible cause: A redundant superuser account or abnormal standard accounts exist.
Solution:
- Submit a ticket to Alibaba Cloud technical support to delete the redundant superuser account.
- Reset the passwords of the abnormal standard accounts.

Upgrade check error

Format: The pg_upgrade upgrade check fails. View "pg_upgrade error log" and "pg_upgrade-related files and errors".
Possible cause: The upgrade check fails.
Solution: Resolve the log issue. View "pg_upgrade error log".

pgcrypto extension error

Format: The pgcrypto extension is installed in the pg_catalog schema of the {*} database. View the related Alibaba Cloud documentation.
Possible cause: The major engine version upgrade fails because the pgcrypto extension is used to create functions that can be used only in later major engine versions in pg_catalog.
Solution: Delete the pgcrypto extension from each database and create the extension in a schema other than the pg_catalog schema.

pg_upgrade error log

Content

The system checks whether extensions and keywords that are incompatible with the new major engine version exist.

Common error

A list of problem libraries is in the file: loadable_libraries.txt

Possible cause

Extensions that are incompatible with the new major engine version exist, and the relevant extensions are recorded in the loadable_libraries.txt file.

Solution

Check the extensions that are recorded in the loadable_libraries.txt file and determine whether the extensions need to be deleted based on your business requirements. If the extension needs to be deleted, we recommend that you delete the extension before an upgrade. Before you delete the extension, make sure that your RDS instance can run as expected without the extension. For more information about the extensions that are supported by ApsaraDB RDS for PostgreSQL, see Supported extensions.

A list of tables with the problem is in the file: tables_with_oids.txt

Possible cause

Some tables are created with the WITH OIDS clause. This clause is not supported in PostgreSQL 12 or later, and the relevant tables are recorded in the tables_with_oids.txt file.

Solutions

Solution 1: Upgrade the major engine version of your RDS instance to PostgreSQL 11. PostgreSQL 11 supports the WITH OIDS clause. This solution is recommended.
Solution 2: Check the tables that are recorded in the loadable_libraries.txt file and determine whether the business code depends on the objects that are specified in the WITH OIDS clause. If the business code does not depend on the specified objects, execute the following statement:
```
ALTER TABLE {table_name} SET WITHOUT OIDS;
```

pg_upgrade -related files and errors

loadable_libraries.txt

This file contains the libraries that are incompatible with the new major engine version. You can identify incompatible extensions based on these libraries.

The following information describes the common incompatible extensions and solutions to the incompatibility issues:

pgrouting extension

Possible cause

The pgrouting extension is incompatible with the new major engine version.

Solution

Determine whether to delete the extension based on your business requirements. If you want to delete the extension, we recommend that you delete the extension before an upgrade. Before you delete the extension, make sure that your RDS instance can run as expected without the extension. For more information about the extensions that are supported by ApsaraDB RDS for PostgreSQL, see Supported extensions.

jsonbx extension

Possible cause

Some JSON data types are not supported in PostgreSQL 9.4. To support all JSON data types, you must enable the jsonbx extension. PostgreSQL 10 and later versions support all JSON data types. If your RDS instance runs PostgreSQL 10 or a later version, you do not need to enable the jsonbx extension.

Solution

Check the functions that are used by the jsonbx extension in the new major engine version and determine whether to delete the extension based on your business requirements. If you want to delete the extension, we recommend that you delete the extension before an upgrade. Before you delete the extension, make sure that your RDS instance can run as expected without the extension.

Some functions that are used by the jsonbx extension may return different results in different PostgreSQL versions. The following table describes the differences in the results.

Function	Return result in PostgreSQL 9.4	Return result in PostgreSQL 10 and later versions
select '{"a":1, "b":2, "c":3}'::jsonb - 2;	{"a": 1, "b": 2}	ERROR: cannot delete from object using integer index
select jsonb_delete('{"a":1, "b":2, "c":3}'::jsonb, '{b}'::text[]);	{"a": 1, "c": 3}	ERROR: function jsonb_delete(jsonb, text[]) does not exist
select '{"a":{"c":1, "d":2}, "b":3}'::jsonb - '{a, c}'::text[];	{"a": {"d": 2}, "b": 3}	{"b": 3}

PostGIS and postgis_topology extensions

Possible cause

The version of the PostGIS extension that is used is outdated and is incompatible with the specified libraries in the new major engine version. As a result, your RDS instance failed the upgrade check.

Solution

Important

The PostGIS extension varies in different major engine versions. For example, this extension reports different parsing errors that are related to the well-known text (WKT) format in different major engine versions. Before you update this extension, we recommend that you clone your RDS instance. You can use the cloned RDS instance to test the compatibility of this extension with the new major engine version. After you verify that this extension is compatible with the new engine version, you can update this extension in your original RDS instance to the new major engine version. For more information about how to clone an RDS instance, see Back up an ApsaraDB RDS for PostgreSQL instance and Restore data of an ApsaraDB RDS for PostgreSQL instance.

Update the minor engine version of an instance. For more information, see Update the minor engine version.
Update the PostGIS extension that reports errors. For more information, see How do I update the plug-ins of Ganos?
Run the \dx command to query the version of the PostGIS extension. Make sure that the version of the PostGIS extension is 3.3.2 or later.
Perform an upgrade check again.
Important
If the PostGIS extension, postgis_topology extension, or pgrouting extension is installed on your RDS instance, take note of the following limits:
- If your RDS instance runs PostgreSQL 9.4, you can upgrade the major engine version of your RDS instance only to PostgreSQL 10 or PostgreSQL 11.
- If your RDS instance runs PostgreSQL 10, you can upgrade the major engine version of your RDS instance only to PostgreSQL 11.
- If your RDS instance runs PostgreSQL 11, PostgreSQL 12, or PostgreSQL 13, you cannot upgrade the major engine version of your RDS instance.

tables_with_oids.txt

The system displays the tables that are created with the WITH OIDS clause.