RDS postgreSQL engine upgrade from 10.21 to 14.4 version

0

Before upgrading we need to update certain extensions before major upgrade. So in our case also we have PostGis extension with version '2.5.2' which needs to be altered to '3.1.5'

I followed the steps as per the document i.e. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.PostGIS.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update:~:text=1%0A(1%20row)-,Step%206%3A%20Upgrade%20the%20PostGIS%20extension,-Each%20new%20release

1>PostGis was updated to 3.1.5 but not able to update the 'postgis_raster' extension which is one of the dependency of postgis. But this dependent extension is not showing from the following command output -> SELECT * FROM pg_extension; And to complete the upgrade of extension, run the upgrade command : SELECT postgis_extensions_upgrade();

2>But the above command was giving below error: ERROR: function postgis_extensions_upgrade() does not exist LINE 1: SELECT postgis_extensions_upgrade(); HINT: No function matches the given name and argument types. You might need to add explicit type casts.

3>And when I modified the engine version in RDS ,the precheck logs sent the below error: **The instance could not be upgraded from 10.21.R1 to 14.4.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 'DB_NAME' 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.**

4>Then I also tried deleting this extension but still RDS throwing the same error in precheck logs.

**Need help in upgrading the postgresql as the version 10 is deprecated and I'm not able to upgrade it because of this extension and AWS will also automatically upgrad to version 14 but they might also face issue this issue in upgrading our database.

1 Answer
1
Accepted Answer

Hello yanshul,

This issue can occur when an old version of postgis_raster (which was not supported on newer versions) exists in the database. These older versions were installed as functions and not extensions hence would not show up when you run "show rds.extensions”.

I was able to reproduce the issue, and here are steps to resolve it

  • First check if any function or libraries related to version 2 RASTER is still installed in database by

postgres=> select probin from pg_proc where proname = 'postgis_raster_lib_version';
probin
---------+-------
$libdir/rtpostgis-2.5

  • Check number of objects created in version 2

postgres=> SELECT probin, count(*) FROM pg_proc WHERE probin LIKE '%postgis%' GROUP BY probin;
probin | count
-----------------+-------
$libdir/rtpostgis-2.5 | 104

  • Now add PostGIS extension (if you don't have installed) to the database where you are having issue

CREATE EXTENSION postgis; (this will create extension with 3.1.5 version)

  • To check full PostGIS extension version with "RASTER lib" version and status

postgres=> SELECT postgis_full_version();
postgis_full_version
-----------------+-------
POSTGIS="3.1.5 c60e4e3" [EXTENSION] PGSQL="100" GEOS="3.7.3-CAPI-1.11.3 b50468f" 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" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" RASTER (raster lib from "2.5.5 r0" need upgrade) [UNPACKAGED!] (raster procs from "2.5.5 r0" need upgrade)

  • We have to drop the libraries related to version 2. To do that log in with master user and drop 'postgis_raster_lib_version'

postgres=> drop function postgis_raster_lib_version;
DROP FUNCTION

postgres=> select * from pg_proc where proname = 'postgis_raster_lib_version';
proname | pronamespace | proowner | prolang | procost | prorows | provariadic | protransform | proisagg | proiswindow | prosecdef | proleakproof | proisstrict | proretset | provolatile | proparallel | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | protrftypes | prosrc | probin | proconfig | proacl ---------+--------------+----------+---------+---------+---------+-------------+--------------+----------+- (0 rows)

Drop PostGIS extension (this will drop PostGIS version 3 but version 2.X will remain installed):

DROP EXTENSION postgis CASCADE;

Drop all raster related type from database:

DROP TYPE raster CASCADE;

Review number of objects in raster version 2

postgres=> SELECT probin,count(*) FROM pg_catalog.pg_proc WHERE probin LIKE '%postgis%' GROUP BY 1;
probin | count
-----------------------+-------
$libdir/rtpostgis-2.5 | 8

we have to drop all functions manually:

postgres=> SELECT proname FROM pg_catalog.pg_proc WHERE probin LIKE '%postgis%'; proname
-----------------------+-------
postgis_raster_lib_build_date
postgis_gdal_version
_st_summarystats_finalfn
_st_histogram
_st_quantile
_st_valuecount
st_gdaldrivers
st_minpossiblevalue

postgres=> drop function postgis_raster_lib_build_date;
DROP FUNCTION

postgres=> SELECT probin,count(*) FROM pg_catalog.pg_proc WHERE probin LIKE '%postgis%' GROUP BY 1;
probin | count
--------+-------
(0 rows)

Now you can go back to AWS Management console and upgrade RDS PostgreSQL instance.

AWS
answered 2 years ago
profile picture
EXPERT
reviewed 7 months ago
  • Thanks for the detailed explanation.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions