How do I troubleshoot issues related to PostGIS extension when upgrading my RDS for PostgreSQL instance?

Last updated: 2022-09-29

The major version upgrade for my Amazon Relational Database Service (Amazon RDS) for PostgreSQL instance has failed due to issues with PostGIS extension.

Short description

The major version upgrade for your Amazon RDS for PostgreSQL instance might fail for multiple reasons. The most common reason might be that you didn't update the PostGIS extension or dependent extensions, such as address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, or postgis_raster, to the current supported version before performing the major version upgrade.

To check if your RDS PostgreSQL instance major version upgrade failed due to PostGIS or dependent extensions, do the following:

1. Check the events for the instance. You might see an event similar to the following:

Database instance is in a state that cannot be upgraded: PreUpgrade checks failed: The instance could not be upgraded because one or more databases have settings or usages that are not compatible with the target engine version. Please check the precheck log file for more details

2. Check the precheck log files for the database instance that was created during the upgrade process. During the major version upgrade, RDS for PostgreSQL performs certain precheck procedures on the instance to identify issues that might cause the upgrade process to fail. You can find information about any issues encountered during the precheck in the pg_upgrade_precheck.log file. Amazon RDS appends a timestamp to the file name.

If the precheck log file includes a message similar to the following, then the upgrade process failed due to PostGIS or dependent extensions.

------------------------------------------------------------------
Upgrade could not be run on Sun May 22 14:20:45 2022
------------------------------------------------------------------
The instance could not be upgraded from 9.6.22.R1 to 12.7.R1 because of following reasons.
Please take appropriate action on databases that have usages incompatible with requested major engine version upgrade and try again.
- Following usages in database 'test_db' need to be corrected before upgrade:
-- The instance could not be upgraded because the PostGIS extension and its dependent extensions (address_standardizer,
address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, postgis_raster) installation in one or more databases is
not compatible with your desired upgrade path. Please upgrade postgis and its dependent extensions to version supported in requested version.
----------------------- END OF LOG  ----------------------

To check the installed version of PostGIS and dependent extensions, run the following commands:

postgres=> select * FROM pg_available_extensions where name like '%postgis%';
postgres=> select * FROM pg_available_extensions where name like '%address_standardizer%';
postgres=> select probin from pg_proc where proname = 'postgis_raster_lib_version';

The value in the installed_version column shows the currently installed version of the extension. If this column has no value, then it means that the extension isn't installed.

Resolution

To troubleshoot the upgrade failure in your RDS for PostgreSQL instance, use one of the following methods based on your use case.

Remove the PostGIS extension

Drop the extension and recreate it after the upgrade is successful under the following conditions:

You don't need the PostGIS or dependent extensions in the instance.
An update path doesn't exist for the extension.

Note: Dropping the extension also removes the dependent columns.

1. To drop the extension, run the following command:

postgres=> DROP EXTENSION example_extension CASCADE;

2. After dropping the extensions in all the databases where they are installed, upgrade your RDS for PostgreSQL instance.

3. After upgrading the instance, create the extension, if needed, by running the following command:

postgres=> CREATE EXTENSION extension_name;

Update the PostGIS extension

Do the following:

Perform the upgrade in multiple steps.
Update the extension version in each step before upgrading the RDS for PostgreSQL instance.

Check for the next highest supported update path for the extension installed in your RDS for PostgreSQL instance. Run the following command:

postgres=> SELECT * FROM pg_extension_update_paths('extension_name') WHERE source='current_version' AND target NOT LIKE '%next%' AND source<target AND path LIKE '%--%';

Be sure to replace the following:

extension_name with the name of the installed extension
current_version with the currently installed version of the extension

If the command doesn't return an update path, but the installed_version and default_version values of the extensions are same, then the extension is already updated. In this case, you can perform the engine version upgrade.

For example:

postgres=> SELECT * FROM pg_extension_update_paths('postgis') WHERE source='2.3.7' AND target NOT LIKE '%next%' AND source<target AND path LIKE '%--%';

Example output:

source | target |     path
------+--------+--------------
2.3.7  | 2.5.2  | 2.3.7--2.5.2 (1 row)

If the values for the installed_version and default_version are different, upgrade the installed PostGIS and dependent extensions to the highest supported target version.

To upgrade the extension, run the following command:

postgres=> ALTER EXTENSION PostgreSQL-extension UPDATE TO 'new-version';

Note: Be sure to update the PostGIS extension along with the dependent extensions in the instance. These extensions are installed at the database level. Therefore, be sure to update the extensions in all the databases where they were installed.

For updating PostGIS and postgis_raster extension when you're on PostGIS version 2.5.0 or later, run the following command:

postgres=> SELECT postgis_extensions_upgrade();

In PostGIS version 3.0 and later, the raster functionality is broken out as a separate extension. If you don't use raster, then run the following command after updating the extension:

postgres=> DROP EXTENSION postgis_raster;

After updating the extensions, you can upgrade the RDS for PostgreSQL instance to the engine version in which these extension versions are supported.

After upgrading your instance, you can repeat the steps of updating the extension version followed by upgrading the RDS instance engine version until you install the target engine version.

Related information

How to perform a major version upgrade

Using PostgreSQL extensions with Amazon RDS for PostgreSQL

Did this article help?

Submit feedback

Do you need billing or technical support?

Contact AWS Support