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

If the report indicates that your RDS instance fails the upgrade check, you can click See Information in the Report content column to view the details about the failure. The report contains the following check items:
Note For more information about major engine version upgrades, see Upgrade the major engine version of an ApsaraDB RDS for PostgreSQL instance.

user_check_report

This item is used to check whether a superuser account is created in the background or an invalid encryption method is configured for a standard account.
Error message Possible cause Solution
invalid superuser: ["user_01"] A superuser account is found. If you use this account to connect to your RDS instance, the read-only attribute that you configure for your RDS instance is invalid. As a result, the data of your RDS instance changes after you perform the upgrade. To delete the superuser account, submit a ticket to Alibaba Cloud technical support.
invalid user: ["user_02"] A standard account is abnormal. You cannot use this account to establish a connection after the upgrade is complete. Reset the password of the abnormal account.

pg_upgrade_internal.log

This item is used to check whether the new major engine version is compatible with the plug-ins on your RDS instance.
Error message Possible cause Solution
A list of problem libraries is in the file: loadable_libraries.txt Plug-ins that are incompatible with the new major engine version are found in the loadable_libraries.txt file. Check the plug-ins that are listed in the loadable_libraries.txt file and evaluate whether the plug-ins need to be deleted. If a plug-in needs to be deleted, we recommend that you delete the plug-in before the upgrade. Before you delete a plug-in, make sure that your RDS instance can run as expected without the plug-in. For more information, see Plug-ins supported.
A list of tables with the problem is in the file: tables_with_oids.txt The WITH OIDS clause is specified when some tables are created. This clause is not supported in PostgreSQL 12 or later.
  • 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 listed in the tables_with_oids.txt file and evaluate 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 the types of logs that are enabled on your RDS instance.

loadable_libraries.txt

This item is used to check the libraries that are incompatible with the new major engine version. You can identify incompatible plug-ins based on these libraries.
Error message Possible cause Solution
could not load library "$libdir/pgrouting-2.6.2": ERROR: could not access file "$libdir/pgrouting-2.6.2": No such file or directory The pgrouting plug-in is incompatible with the new major engine version. Check the plug-ins that are listed in the loadable_libraries.txt file and evaluate whether the plug-ins need to be deleted. If a plug-in needs to be deleted, we recommend that you delete the plug-in before the upgrade. Before you delete a plug-in, make sure that your RDS instance can run as expected without the plug-in. For more information, see Plug-ins supported.
could not load library "$libdir/jsonbx": ERROR: could not access file "$libdir/jsonbx": No such file or directory PostgreSQL 9.4 does not support all JSON data types. To support all JSON data types, you must enable the jsonbx plug-in. PostgreSQL 10 and later support all JSON data types. If your RDS instance runs PostgreSQL 10 or later, you do not need to enable the jsonbx plug-in. Check the functions that are used by the jsonbx plug-in in the new major engine version and evaluate whether the plug-in needs to be deleted. If the plug-in needs to be deleted, we recommend that you delete the plug-in before the upgrade. Before you delete the plug-in, make sure that your RDS instance can run as expected without the plug-in. For more information, see Use differences of jsonbx plug-in functions.
  • could not load library "$libdir/postgis-2.5": ERROR: could not access file "$libdir/postgis-2.5": No such file or directory
  • could not load library "$libdir/postgis_topology-2.2": ERROR: could not access file "$libdir/postgis_topology-2.2": No such file or directory
  • The version of the postgis plug-in that is used is outdated and is incompatible with the specified libraries in the new major engine version. As a result, your RDS instance fails the upgrade check.
  • The version of the postgis_topology plug-in that is used is outdated and is incompatible with the specified libraries in the new major engine version. As a result, your RDS instance fails the upgrade check.
Perform the following steps:
  1. Upgrade the minor engine version of your RDS instance. For more information, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
  2. Run the following command to upgrade the postgis plug-in:
    ALTER EXTENSION postgis UPDATE;
  3. Run the \dx command to query the version of the postgis plug-in. Make sure that the version of the postgis plug-in is 2.5.4 or later.
  4. Perform the upgrade check again.
    Note If you installed Postgis or Postgis_topology in a PostgreSQL version 9.4 instance. For the version to be upgraded, please select PostgreSQL 10 version to upgrade. PostgreSQL 9.4 is not supported to upgrade to other versions with this plug-in.
Some functions that are used by the jsonbx plug-in may return different results in different PostgreSQL versions. The following table describes the differences in the results. Before you perform the upgrade, you must evaluate whether the jsonbx plug-in needs to be deleted.
Function Return result in PostgreSQL 9.4 Return result in PostgreSQL 10 and later
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}

tables_with_oids.txt

This item is used to display the tables that are created based on the WITH OIDS clause.