PostgreSQL 9.6 – Error with pg_upgrade and PostGIS

postgispostgresql-9.6

I have a PostgreSQL 9.2 setup (with Postgis) and am trying to upgrade it to 9.6.

When performing the pg_upgrade, I get the following error:

ERROR:  could not find function "geography_analyze" in file "/usr/lib/postgresql/9.6/lib/postgis-2.0"
STATEMENT:  CREATE FUNCTION "public"."geography_analyze"("internal") RETURNS boolean
        LANGUAGE "c" STRICT
        AS '$libdir/postgis-2.0', 'geography_analyze';

    -- For binary upgrade, handle extension membership the hard way
    ALTER EXTENSION "postgis" ADD FUNCTION "public"."geography_analyze"("internal");

I've tried to upgrade Postgis on my 9.2 clusters, but I get an error as well:

postgres=# ALTER EXTENSION postgis UPDATE;
ERROR:  cannot drop function "geometry_gist_sel_2d"("internal","oid","internal",integer) because other objects depend on it
DETAIL:  operator &&("geometry","geometry") depends on function "geometry_gist_sel_2d"("internal","oid","internal",integer)
operator class "gist_geometry_ops_2d" for access method gist depends on operator &&("geometry","geometry")
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

How should I proceed?

Best Answer

How should I proceed?

I would try to load the newer version of PostGIS fresh on a 9.6 before you load your dataset. If successful, I would edit the dataset to remove anything to do with PostGIS's internal functions. I would then load my dataset without those offending functions (make sure whatever you're loading does not have PostGIS internal stuff).

Also worth mentioning PostGIS 2.0 is EOL already and unsupported.

Related Question