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.

If the report indicates that your RDS instance fails the upgrade check, 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. 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 is configured for your RDS instance is invalid. As a result, the data of your RDS instance changes after you perform an upgrade. To delete the superuser account, you must submit a ticket to contact Alibaba Cloud technical support.
invalid user: ["user_02"] A standard account is in an abnormal state. You cannot use this account to establish a connection after you perform an upgrade. 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 some plug-ins need to be deleted. If a plug-in needs to be deleted, we recommend that you delete the plug-in before an 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 Supported plug-ins.
A list of tables with the problem is in the file: tables_with_oids.txt Some tables are created with the WITH OIDS clause specified. This clause is not supported in PostgreSQL 12 or later versions.
  • 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 for 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 some plug-ins need to be deleted. If a plug-in needs to be deleted, we recommend that you delete the plug-in before an 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 Supported plug-ins.
could not load library "$libdir/jsonbx": ERROR: could not access file "$libdir/jsonbx": No such file or directory Some JSON data types are not supported in PostgreSQL 9.4. To support all JSON data types, you must enable the jsonbx plug-in. 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 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 an 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 failed 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 failed the upgrade check.
Notice The postgis plug-in varies in different database engine versions. For example, this plug-in reports different parsing errors that are related to the WKT format in different database engine versions. Before you update this plug-in, we recommend that you clone your RDS instance. You can use the cloned RDS instance to test the compatibility of this plug-in with the new major engine version. After you verify that this plug-in is compatible with the new engine version, you can update this plug-in in your original RDS instance to the new major engine version. For more information, see Back up an ApsaraDB RDS for PostgreSQL instance and Restore the data of an ApsaraDB RDS for PostgreSQL instance.
Perform the following steps:
  1. Update 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. Update the plug-in that reports errors.
    • postgis
      ALTER EXTENSION postgis UPDATE;
    • postgis_topology
      ALTER EXTENSION postgis_topology 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 an upgrade check again.
    Notice If the postgis plug-in, postgis_topology plug-in, or pgrouting plug-in 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.
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 an 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 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} null

tables_with_oids.txt

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