PostGIS Upgrade Issue

[rama]

I am facing an issue with PostGIS Upgrade from 2.5.0 to 3.3.3. Please check and help me out to fix the issue.

Environment Details

Os: Windows Environment (Windows 2016)
Postgresql Version: 11
Postgis Upgrade from 2.5.0 to 3.3.3
Download and installation done from stack builder

Current Version of Database and Postgis

  DATABASE_VERSION                      
------------------------------------------------------------
 PostgreSQL 11.0, compiled by Visual C++ build 1914, 64-bit
(1 row)
POSTGIS_VERSION                                                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.5.0 r16836" [EXTENSION] PGSQL="110" GEOS="3.7.0-CAPI-1.11.0 3.7.0" SFCGAL="1.3.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released 2018/03/19 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" TOPOLOGY RASTER
(1 row)

Command Execution Details

ALTER EXTENSION postgis UPDATE TO "3.3.3";
SELECT postgis_extensions_upgrade();

ERROR message:

ALTER EXTENSION postgis UPDATE TO "3.3.3";
WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
ERROR:  could not load library "R:/Database/11/lib/postgis-3.dll": The specified procedure could not be found.


 SELECT postgis_extensions_upgrade();
WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;

ERROR:  could not load library "R:/Database/11/lib/postgis-3.dll": The specified procedure could not be found.

CONTEXT:  SQL statement "ALTER EXTENSION postgis UPDATE TO "3.3.3";"
PL/pgSQL function postgis_extensions_upgrade() line 22 at EXECUTE
SQL state: XX000

[rama]

Upgrade command referred from Manual

[rama]

SELECT postgis_extensions_upgrade();

Error Message

WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;

ERROR:  could not load library "R:/Database/11/lib/postgis-3.dll": The specified procedure could not be found.

CONTEXT:  SQL statement "ALTER EXTENSION postgis UPDATE TO "3.3.3";"
PL/pgSQL function postgis_extensions_upgrade() line 22 at EXECUTE
SQL state: XX000

[rama]

ALTER EXTENSION postgis UPDATE; [without version]

Error Message

WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;

ERROR:  could not load library "R:/Database/11/lib/postgis-3.dll": The specified procedure could not be found.

SQL state: XX000

[rama]

Function:postgis_extensions_upgrade

-- FUNCTION: public.postgis_extensions_upgrade()

-- DROP FUNCTION IF EXISTS public.postgis_extensions_upgrade();

CREATE OR REPLACE FUNCTION public.postgis_extensions_upgrade(
	)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE rec record; sql text;
BEGIN
	-- if at a version different from default version or we are at a dev version,
	-- then do an upgrade to default version

	FOR rec in SELECT  name, default_version, installed_version
		FROM pg_available_extensions
		WHERE installed_version > '' AND name IN('postgis', 'postgis_sfcgal', 'postgis_tiger_geocoder', 'postgis_topology')
		AND ( default_version <> installed_version  OR
			( default_version = installed_version AND default_version ILIKE '%dev%' AND  installed_version ILIKE '%dev%'  )  ) LOOP

		-- we need to upgrade to next so our installed is different from current
		-- and then we can upgrade to default_version
		IF rec.installed_version = rec.default_version THEN
			sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || quote_ident(rec.default_version || 'next')   || ';';
			EXECUTE sql;
			RAISE NOTICE '%', sql;
		END IF;

		sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || quote_ident(rec.default_version)   || ';';
		EXECUTE sql;
		RAISE NOTICE '%', sql;
	END LOOP;

	RETURN public.postgis_full_version();

END
$BODY$;

ALTER FUNCTION public.postgis_extensions_upgrade()
    OWNER TO postgres;

COMMENT ON FUNCTION public.postgis_extensions_upgrade()
    IS 'Upgrades installed postgis packaged extensions (e.g. postgis_sfcgal, postgis_topology, postgis_sfcgal) to latest installed version. Reports full postgis version and build configuration infos after.';

Output of FOR loop select Query

name|default_version|installed_version
"postgis"|"3.3.3"|"2.5.0"

[strk]

Do you have access to the database system ? There should be a file called postgis—2.5.0—3.3.3.sql in the postgresql extensions directory containing all the statement taking you from 2.5.0 to 3.3.3, including the RAISE WARNING lines you seem to be able to see. What you could do is add more RAISE WARNING to spot the exact place in which the failure occurs.

[rama]

Based on our communication over chat postgis—2.5.0—3.3.3.sql file file is missing in the path xx\11\share\extension

Message from Team:

 it's not PostGIS official package doing that
didn't look at that package, but would ask the question "who makes those packages ?" and ask them for support

I have sent email to EDB team with details for further support.

Still awaiting for solution.

[robe]

I am the windows packager for PostGIS, not EDB, so this is the right channel to ask.

That error ERROR: could not load library "R:/Database/11/lib/postgis-3.dll" suggests that you have dependencies that are out of sync.

when I build the packages, I assume you have the latest minor version of PostgreSQL and latest dependencies cause there is a overlap e.g the ssl and libxml that are often shared with the PostgreSQL EDB ships.

I see you are running PostgreSQL 11.0, which is really really old. I think libs have changed since then, so the newer libs might be incompatible with what you had installed.

My suggestion:

1. Using Stack Builder, upgrade the PostgreSQL 11. This should bring you up to PostgreSQL 11.21

alternatively you can download from ​https://www.postgresql.org/download/windows/

2. Reinstall PostGIS again

Then try to run the upgrade step.

[rama]

As recommended I have updated the PostgreSQL version to 11.21

The good news is that Postgis is upgraded to 3.3.3!! But Postgis_raster doesn't exist in the extension list

Currently facing issues with raster, sfcgal, and pgrouting Could you please suggest what could we do for the same

ALTER EXTENSION postgis UPDATE TO "3.3.3";
WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
ALTER EXTENSION

SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis 3.3.3
NOTICE:  Updating extension postgis_sfcgal 2.5.0
ERROR:  aggregate st_3dunion(geometry) does not exist
CONTEXT:  SQL statement "UPDATE pg_catalog.pg_extension SET extversion = 'ANY' WHERE extname = 'postgis_sfcgal';ALTER EXTENSION postgis_sfcgal UPDATE TO "3.3.3""
PL/pgSQL function postgis_extensions_upgrade() line 104 at EXECUTE

SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis 3.3.3
NOTICE:  Updating extension postgis_sfcgal 2.5.0
ERROR:  aggregate st_3dunion(geometry) does not exist
CONTEXT:  SQL statement "UPDATE pg_catalog.pg_extension SET extversion = 'ANY' WHERE extname = 'postgis_sfcgal';ALTER EXTENSION postgis_sfcgal UPDATE TO "3.3.3""
PL/pgSQL function postgis_extensions_upgrade() line 104 at EXECUTE

When I try to upgrade pgrouting and postgis_sfcgal extension i get an Error

##postgis_sfcgal
ALTER EXTENSION postgis_sfcgal UPDATE TO "3.3.3";
ERROR:  aggregate st_3dunion(geometry) does not exist

##pgrouting
ALTER EXTENSION pgrouting UPDATE TO "3.5.0";
ERROR:  function pgr_dijkstra(text, bigint, anyarray, boolean) does not exist

[robe]

strange - I'm surprised about postgis_sfcgal as that function does exist and I thought it had existed in 2.5 version as well.

Well the easy fix for both pgrouting and postgis_sfcgal is to uninstall them and reinstall.

Generally pgrouting and postgis_sfcgal don't have any data or user functions tied to them, so they are safe to drop. If you do have data and functions tied to them, then the below will fail.

DROP EXTENSION postgis_sfcgal;
CREATE EXTENSION postgis_sfcgal;
DROP EXTENSION pgrouting;
CREATE EXTENSION pgrouting;

I'll have to test with a clean postgis 2.5.0 install to rule out if its an issue with our upgrade script

Regarding the postgis_raster, I assume you below twice? It might have failed because of the postgis_sfcgal issue. Try rerunning again after you do the drop/create of postgis_sfcgal in above script.

SELECT postgis_extensions_upgrade();

[rama]

Thank you @robe it worked. Thank you for your extended support.

[robe]

I'll leave this open for now until I've confirmed there is a issue with postgis_sfcgal and pgrouting upgrade we missed. Thanks for the report.

[rama]

Please keep the ticket open I will close the ticket once my production upgrade is over

I have come across couple more issue could please help me to fix the same

while Upgrading Extension [SELECT postgis_extensions_upgrade();]

Issue 1: Some Extension function is owned by the user and the extension is owned by Postgres. Should we change ownership of function postgres or we have to run extension upgrade from the user.

ERROR:  Function postgis_noop(raster) is owned by xyz but extension is owned by postgres

Issue 2:  While Dropping and creating extension pgrouting i am getting some views are depend on it. Should I take  backup and delete the view before upgrading and recreate after it? Or do we have some other option?

ERROR:  cannot drop extension pgrouting because other objects depend on it
DETAIL:  materialized view o17_transportation.isochrone_nodes_walk depends on function pgr_drivingdistance(text,bigint,double precision,boolean,boolean)
materialized view o17_transportation.isochrone_edges_walk depends on materialized view o17_transportation.isochrone_nodes_walk
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

[rama]

@strk

Could you please Provide suggestion for user dependent objects for pgrouting and postgis_sfcgal

Some of our database have user object depency please provide solution for the same

Issue 2: While Dropping and creating extension pgrouting i am getting some views are depend on it. Should I take backup and delete the view before upgrading and recreate after it? Or do we have some other option?

ERROR: cannot drop extension pgrouting because other objects depend on it DETAIL: materialized view o17_transportation.isochrone_nodes_walk depends on function pgr_drivingdistance(text,bigint,double precision,boolean,boolean) materialized view o17_transportation.isochrone_edges_walk depends on materialized view o17_transportation.isochrone_nodes_walk HINT: Use DROP … CASCADE to drop the dependent objects too.

[robe]

Replying to rama:

@strk

Could you please Provide suggestion for user dependent objects for pgrouting and postgis_sfcgal

Some of our database have user object depency please provide solution for the same

Issue 2: While Dropping and creating extension pgrouting i am getting some views are depend on it. Should I take backup and delete the view before upgrading and recreate after it? Or do we have some other option?

ERROR: cannot drop extension pgrouting because other objects depend on it DETAIL: materialized view o17_transportation.isochrone_nodes_walk depends on function pgr_drivingdistance(text,bigint,double precision,boolean,boolean) materialized view o17_transportation.isochrone_edges_walk depends on materialized view o17_transportation.isochrone_nodes_walk HINT: Use DROP … CASCADE to drop the dependent objects too.

@rama. The SELECT postgis_extensions_upgrade() does not touch pgrouting. So to fix those.

Try first doing, the below, it may not work because the pgrouting pgr_drivingdistance has undergone quite a few changes over the years, so sadly a drop your materialized view and rebuild after you run ALTER EXTENSION pgrouting UPDATE or DROP and create extension pgrouting might be your only options.

` ALTER EXTENSION pgrouting UPDATE; `

[robe]

Slight correction I forgot you already said you can't upgrade pgrouting, so yes the solution sadly is to

DROP EXTENSION pgrouting; -- it will fail, not all the views it complains about, save the definitions and drop them

CREATE EXTENSION pgrouting;

-- recreate your views

regarding Issue 1, I think we have a fix for this in upcoming PostGIS release. But what you said, change the ownership of the functions being complained about to postgres should work.

A simpler solution, I think the check only complains if they owner of the function is not a super user. So what you could try is make xyz a super user just before you run the upgrade. You can get rid of the super user status after.

[rama]

@robe

As recommended in ticket 5592. i am trying to upgrade Postgres from V11 to V16. similarly, PostGIS 3.3.3 to 3.4.1

During the Restore process getting below Error

:M:/Database/bakup_01DEC2023.dump:13254: ERROR:  relation "o17_transportation.isochrone_edges_walk" does not exist
psql:M:/Database/bakup_01DEC2023.dump:25576759: ERROR:  no entry in "public.pointcloud_formats" for pcid = 1
CONTEXT:  COPY dfra_tpg_pcld_lidar2015leedsuni_dfra_bsln, line 1, column pa: "01010000000100000090010000026401000007000000E57006FC5AED68378AC1EDF63499D96677A35A4E62B3ABE5EE55AD9A..."
psql:M:/Database/bakup_01DEC2023.dump:25715376: ERROR:  no entry in "public.pointcloud_formats" for pcid = 1
CONTEXT:  COPY dfra_tpg_pcld_lidar2018bristolwestbury_dfra_bsln, line 1, column pa: "0101000000010000009001000002C801000009000000061B6856022448AC16204409D0A190B0193809120186E0905A701126..."
psql:M:/Database/bakupuk_01DEC2023.dump:25805846: ERROR:  no entry in "public.pointcloud_formats" for pcid = 1
CONTEXT:  COPY dfra_tpg_pcld_lidarpoints50cm_dfra_bsln, line 1, column pa: "010100000001000000900100000296010000080000006E5B69616E6671627E638262654F69667C5A74436B467262855A7866..."
psql:M:/Database/bakup_01DEC2023.dump:26591952: ERROR:  no entry in "public.pointcloud_formats" for pcid = 1
CONTEXT:  COPY dfra_tpg_pcld_lidarpointswarwickuni_dfra_bsln, line 1, column pa: "01010000000100000090010000036302000078DA1D927B4C8D7118C7579FC765186AFD61F1C7E10F0C9BF15763366BCC94DC..."

[rama]

@robe it is for Production. Could you please respond as soon as possible

[robe]

Replying to rama:

@robe it is for Production. Could you please respond as soon as possible

Hmm the pointcloud_formats issue, I recall someone complaining about it not properly backing up pointcloud_formats table. There were two such instances

​https://github.com/pgpointcloud/pointcloud/issues/331 (still marked as open)

and ​https://github.com/pgpointcloud/pointcloud/issues/197

marked as closed.

So my guess is your pointcloud_formats table is empty, try backing up just that from your PostgreSQL 11 and restoring just that table first before you restore your isochrone tables.

You may need to upgrade your PostgreSQL 11 pointcloud to latest version — binaries here -

​https://winnie.postgis.net/download/windows/pg11/buildbot/extras/pointcloud-pg11-binaries-1.2.4w64gcc81.zip

[rama]

@Rena

We have completed the Upgrade Activity. Your support has been invaluable to me. Thank you

I would say all your recommendations worked and helped to Fix all the issues that I have faced throughout the Upgradation Process.

This ticket can be Closed I Would give you 5 stars for your great support

[pramsey]

@Rama, you should give more than stars. High touch upgrade support usually costs thousands of dollars.

​https://www.patreon.com/reginaobe