Unable to 'CREATE EXTENSION' due to 'ERROR: postgis is already installed in schema 'public';'

Products

• VMware Tanzu Greenplum

Issue/Introduction

When trying to create the postgis extension in a DB, it fails stating that it already exists:

gpadmin=# create extension postgis; 
ERROR: extension "postgis" already exists

However, it is not listed in the table pg_extension:

select a.extname, b.nspname, a.extversion from pg_catalog.pg_extension a, pg_namespace b where a.extname='postgis'; 
extname | nspname | extversion 
---------+---------+------------ 
(0 rows) 

The function postgis_version() exists:

select postgis_version();
postgis_version 
--------------------------------------- 
2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 
(1 row) 

The following output may be seen when trying to upgrade the extension:

SELECT postgis_extensions_upgrade(); 
NOTICE: Function postgis_gdal_version() not found. Is raster support enabled and rtpostgis.sql installed?
CONTEXT: SQL statement "SELECT postgis_full_version()" PL/pgSQL function postgis_extensions_upgrade() line 26 at RETURN 
NOTICE: Function postgis_raster_scripts_installed() not found. Is raster support enabled and rtpostgis.sql installed? 
CONTEXT: SQL statement "SELECT postgis_full_version()" PL/pgSQL function postgis_extensions_upgrade() line 26 at RETURN 
NOTICE: Function postgis_raster_lib_version() not found. Is raster support enabled and rtpostgis.sql installed? 
CONTEXT: SQL statement "SELECT postgis_full_version()" PL/pgSQL function postgis_extensions_upgrade() line 26 at RETURN 
postgis_extensions_upgrade 
------------------------------------------------------------------------------------------------------------------------ 
POSTGIS="2.5.4" PGSQL="94" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.9.1" LIBJSON="0.12"

Environment

Product Version: 6.15

Resolution

It is likely that this is caused by one of the following reasons:
 

• Postgis was installed at a pre-GPDB 6 level and the DB is now upgraded to GPDB 6
• Postgis was installed at a GPDB 6 level but was using the pre-GPDB 6 method with the postgis_manager.sh script.

To get the Postgis extension installed correctly in GPDB, follow the steps below.

Make sure the Postgis is fully installed using the pre-GPDB6 method, as only some of the Postgis might be installed:

psql -d DB_name -f $GPHOME/share/postgresql/contrib/postgis-X.X/install/postgis.sql 
psql -d DB_name -f $GPHOME/share/postgresql/contrib/postgis-X.X/install/postgis_comments.sql 
psql -d DB_name -f $GPHOME/share/postgresql/contrib/postgis-X.X/install/rtpostgis.sql 
psql -d DB_name -f $GPHOME/share/postgresql/contrib/postgis-X.X/install/raster_comments.sql 

Note: Replace the DB_name with the database name and X.X with the Postgis version.

Use the postgis_manager script to upgrade the Postgis:

$GPHOME/share/postgresql/contrib/postgis-X.X/postgis_manager.sh DB_name upgrade 

Note: Replace the DB_name with the database name and X.X with the Postgis version.

The command should give the following output:

Postgis version 2.5 already installed. 
CREATE EXTENSION 

Now the Postgis is correctly installed. There is no need to run the CREATE EXTENSION command in the DB.