RDS postgreSQL engine upgrade from 10.21 to 14.4 version

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.