How do I troubleshoot issues that are related to the PostGIS extension when I upgrade my RDS for PostgreSQL instance?

5 minute read
0

The major version upgrade for my Amazon Relational Database Service (Amazon RDS) for PostgreSQL instance failed because of issues with the PostGIS extension.

Short description

The major version upgrade for your Amazon RDS for PostgreSQL instance might fail for multiple reasons. The most common reason is an outdated PostGIS extension or dependent extensions. Before you perform the major version upgrade, you must update the PostGis and dependent extensions. Dependent extensions include address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, or postgis_raster.

First, determine if your RDS for PostgreSQL instance major version upgrade failed because of extensions. If the issue is with the extensions, then upgrade PostGIS and its dependent extensions.

Resolution

Check if your instance needs extension upgrades

To determine if your RDS for PostgreSQL instance major version upgrade failed because of extensions, complete the following tasks.

View the events for the instance. You might see an event similar to the following one:

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

View the precheck log files for the instance. In a major version upgrade, RDS for PostgreSQL performs precheck procedures on the instance. You can find issues that happen during the precheck in the pg_upgrade_precheck.log file.

If the precheck log file includes a message similar to the following example, then the upgrade process failed because of the 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 its 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 the extension isn't installed.

Prepare the PostGIS extensions upgrade

When you upgrade an RDS for PostgreSQL instance, take the following actions:

  • Perform the upgrade in multiple steps.
  • Before you upgrade the RDS for PostgreSQL instance, first upgrade the extension version in each step.

Run the SELECT postgis_full_version() command. In the output, check if there are extensions that are pending an upgrade.

Example output with extensions that must be upgraded:

postgres=>  SELECT postgis_full_version();
                                                                                                                        postgis_full_version                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
POSTGIS="2.2.5 r15298" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.4, released 2017/06/23" LIBXML="2.9.1" LIBJSON="0.12.1" (core procs from "2.2.2 r14797" need upgrade) RASTER (raster procs from "2.2.2 r14797" need upgrade)

Example output with no extensions that require an upgrade:

postgres=> SELECT postgis_full_version();
                                                                                postgis_full_version                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
POSTGIS="2.3.7 r16523" PGSQL="96" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.4, released 2017/06/23" LIBXML="2.9.1" LIBJSON="0.12.1" RASTER
(1 row)

Choose the version that's available for an upgrade

Confirm that the PostGIS installation and version information in the output of the pg_extension catalog table matches the SELECT postgis_full_version() command.

Run the following command to check for the next supported version for the extensions that are installed in your RDS for PostgreSQL instance:

postgres=> SELECT name,version,installed FROM pg_catalog.pg_available_extension_versions WHERE name LIKE 'postgis%' AND version NOT LIKE ALL (array['%next%','%unpackaged%']) order by 2,1;
postgres=> SELECT name,version,installed FROM pg_catalog.pg_available_extension_versions WHERE name LIKE 'address%' AND version NOT LIKE ALL (array['%next%','%unpackaged%']) order by 2,1;

Select the highest supported version that's available for an upgrade.

Note: Before a PostgreSQL engine upgrade, check the pg_extension table to confirm that there are no "next", "dev", or "unpackaged" extension versions. If you use any of these versions, then it's a best practice to first move to the standard extension version.

Upgrade the extensions

To upgrade the extensions, run one of the following commands.

If the installed version of PostGIS is 2.5 or later, then run the following command:

SELECT postgis_extensions_upgrade();


To upgrade PostGIS from version 2.5.x to 3.x, run the following command twice:

SELECT postgis_extensions_upgrade();

If the installed version of PostGIS is version 2.4 or earlier, then run the ALTER EXTENSION command:

ALTER EXTENSION PostgreSQL-extension UPDATE TO 'new-version';

If you installed multiple PostGIS modules, such as postgis_topology, then individually update each module. The following example shows the best practice to update multiple modules with the ALTER EXTENSION command:

ALTER EXTENSION postgis UPDATE TO 'new-version';

ALTER EXTENSION postgis_topology UPDATE TO 'new-version';

ALTER EXTENSION postgis_tiger_geocoder UPDATE TO 'new-version';


Note: Extensions are installed at the database level. Make sure that you update the extensions in all the databases where they're installed.

If the current installed PostGIS version is 3.0 or later, you must separately update the raster functions. If you don't use raster functions, then run the following command after you update the extensions:

postgres=> DROP EXTENSION postgis_raster;

After you update the extensions, upgrade the RDS for PostgreSQL engine version for the supported extension versions.

After you upgrade you instance, repeat the steps to check the installed version of PostGIS and its dependent extensions.

Note: It's s a best practice to upgrade PostGIS and its dependent extensions to the latest supported version on your current RDS for PostgreSQL version.

AWS OFFICIAL
AWS OFFICIALUpdated 2 months ago
2 Comments

I don't see any logs under Logs and Events, just events. I am using a serverless db.

Rob
replied 2 months ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 2 months ago