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 that are included in the report and the solutions to these errors.
Check items
If the check result in the upgrade check report is Failed, you can log on to the ApsaraDB RDS console, go to the Major Version Upgrade page, and then click View Information in the Report Content column to view the details about the failure. For more information, see Upgrade the major engine version of an ApsaraDB RDS for PostgreSQL instance.
The report contains the following check items and common errors:
user_check_report
Content
This item is used to check whether an unnecessary superuser account is created in the background or an invalid encryption method is configured for a standard account.
Common errors
invalid superuser: ["user_01"]
Possible cause
An unnecessary superuser account is found. If you use this account to connect to your RDS instance, the read-only settings of your RDS instance are invalid. As a result, the data of your RDS instance changes after you perform an upgrade.
Solution
Submit a ticket to contact Alibaba Cloud technical support.
invalid user: ["user_02"]
Possible cause
A standard account is in an abnormal state. You cannot use this account to establish a connection after you perform an upgrade.
Solution
Reset the password of the abnormal account.
pg_upgrade_internal.log
Content
This item is used to check whether extensions and keywords that are incompatible with the new major engine version exist.
Common errors
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.
Solution
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 tables_with_oids.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_server.log
This item is used to check startup logs of your RDS instance.
other_log_info
Content
This item is used to record incompatibility between functions in the current major engine version and PostgreSQL 15 or later.
Common errors
[/data/pg_upgrade_data/pg_upgrade_output.d/20231109T142221.890/incompatible_polymorphics.txt]
In database: cti_link_conf_db
aggregate: public.group_concat(anyelement)
Possible cause
A function, such as group_concat(anyelement), in the original RDS instance is incompatible with PostgreSQL 15 or later. Determine whether to delete the function based on your business requirements. If you want to delete the function, we recommend that you delete the function before an upgrade. Before you delete the function, make sure that your RDS instance can run as expected without the function.
Solution
Execute the following statement to delete an incompatible function:
DROP FUNCTION IF EXISTS <Function to be deleted>;
Appendix
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. 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
This item is used to display the tables that are created with the WITH OIDS clause.