Upgrade PG 9.3 to 9.5 with PostGIS issue

classic Classic list List threaded Threaded
7 messages Options
SBL
Reply | Threaded
Open this post in threaded view
|

Upgrade PG 9.3 to 9.5 with PostGIS issue

SBL

Hi,

 

I am trying to update my PostgreSQL 9.3 to 9.5 with PostGIS 2.2.

 

Unfortunately I am facing similar problems like here:

https://trac.osgeo.org/postgis/ticket/2489

Actually, the functions listed in the relevant changeset were still present after upgrading the PostGIS to 2.2.2. So I dropped them manually, but I still get:

 

“Could not load library "$libdir/postgis-2.1" ERROR: could not access file "$libdir/postgis-2.1": No such file or directory”

 

The workaround posted here

http://serverfault.com/questions/742539/cannot-upgrade-postgresql-9-4-9-5beta2-after-postgis-2-1-6-2-2-0-upgrade

is not very tempting as I have no idea from which 2.1.x version the conflicting functions are... I had at least 2.1.4dev, 2.1.5 and 2.1.8 installed earlier...

 

Any ideas how to fix this or how to identify the conflicting functions?

 

Cheers

Stefan


_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PG 9.3 to 9.5 with PostGIS issue

Sandro Santilli-2
On Tue, Apr 05, 2016 at 06:22:32AM +0000, Blumentrath, Stefan wrote:

> “Could not load library "$libdir/postgis-2.1" ERROR: could not access file "$libdir/postgis-2.1": No such file or directory”

[...]

> Any ideas how to fix this or how to identify the conflicting functions?

SELECT format('%s(%s)', proname, pg_get_function_arguments(oid))
  FROM pg_proc
 WHERE probin = '$libdir/postgis-2.1';

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
SBL
Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PG 9.3 to 9.5 with PostGIS issue

SBL
Many thanks.

The query returns a list of 153, quite basic functions, e.g.:
"buffer(geometry, double precision)"
"buildarea(geometry)"
"centroid(geometry)"
"contains(geometry, geometry)"
"convexhull(geometry)"
"crosses(geometry, geometry)"
"difference(geometry, geometry)"
"dimension(geometry)"
"disjoint(geometry, geometry)"
"distance(geometry, geometry)"
"distance_sphere(geometry, geometry)"
"distance_spheroid(geometry, geometry, spheroid)"
"dump(geometry)"
"dumprings(geometry)"

Does that mean the upgrade of the extension failed locally?
Do I have to run some sql scripts from /usr/share/postgresql/9.5/extension/ manually?

Cheers
Stefan

-----Original Message-----
From: Sandro Santilli [mailto:[hidden email]] On Behalf Of Sandro Santilli
Sent: 5. april 2016 10:04
To: PostGIS Development Discussion <[hidden email]>
Cc: Blumentrath, Stefan <[hidden email]>
Subject: Re: [postgis-devel] Upgrade PG 9.3 to 9.5 with PostGIS issue

On Tue, Apr 05, 2016 at 06:22:32AM +0000, Blumentrath, Stefan wrote:

> “Could not load library "$libdir/postgis-2.1" ERROR: could not access file "$libdir/postgis-2.1": No such file or directory”

[...]

> Any ideas how to fix this or how to identify the conflicting functions?

SELECT format('%s(%s)', proname, pg_get_function_arguments(oid))
  FROM pg_proc
 WHERE probin = '$libdir/postgis-2.1';

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PG 9.3 to 9.5 with PostGIS issue

Sandro Santilli-2
On Tue, Apr 05, 2016 at 09:46:50AM +0000, Blumentrath, Stefan wrote:

> Many thanks.
>
> The query returns a list of 153, quite basic functions, e.g.:
> "buffer(geometry, double precision)"
> "buildarea(geometry)"
> "centroid(geometry)"
> "contains(geometry, geometry)"
> "convexhull(geometry)"
> "crosses(geometry, geometry)"
> "difference(geometry, geometry)"
> "dimension(geometry)"
> "disjoint(geometry, geometry)"
> "distance(geometry, geometry)"
> "distance_sphere(geometry, geometry)"
> "distance_spheroid(geometry, geometry, spheroid)"
> "dump(geometry)"
> "dumprings(geometry)"

All those signatures are missing an "st_" or "postgis_" prefixes,
meaning they probably come from pre-2.0 times, OR have been installed
via "legacy.sql".

> Does that mean the upgrade of the extension failed locally?

The upgrade procedure doesn't attempt to upgrade the legacy functions,
so their presence is not necessarely an indication of an upgrade failure.

If you can tell how those functions got in there, it might help
determining if it was an upgrade bug to keep them in or not.

> Do I have to run some sql scripts from /usr/share/postgresql/9.5/extension/ manually?

I dubt there's anything extension-specific about those functions.

This is a list of functions associated to the 'postgis' extension:

 SELECT pg_describe_object(d.classid, d.objid, 0)
   FROM pg_depend d, pg_extension e
  WHERE d.refobjid = e.oid
    AND d.refclassid = 'pg_extension'::regclass
    AND d.classid = 'pg_proc'::regclass
    AND deptype = 'e' AND e.extname = 'postgis';

A function associated to an extension cannot be removed with
DROP FUNCTION

You could tweak the function you used to _list_ those functions
to add a DROP FUNCTION and then run the resulting sql. Or, if
you installed those functions via loading "legacy.sql", you could
try loading "uninstall_legacy.sql" but it should be coming from
the same version the "legacy.sql" came from.

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
SBL
Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PG 9.3 to 9.5 with PostGIS issue

SBL
Hi again,

And thank you so much for all your assistance! The library issue is now solved by means of removing the legacy functions (where I could not remember when they were added).

I got following error message:
pg_restore: creating MATERIALIZED VIEW "nofa_tmp.stasjons_innsjoer"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2384; 1259 30178832 MATERIALIZED VIEW stasjons_innsjoer stefan
pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: public.geometry && public.geometry
LINE 1: SELECT $1 && $2 AND _ST_Intersects($1,$2)
                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT $1 && $2 AND _ST_Intersects($1,$2)
CONTEXT:  SQL function "st_intersects" during inlining
    Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('30178834'::pg_catal...

This occurred also after running:
postgis-2.2/postgis_proc_set_search_path.sql in my old database...

After removing the (luckily few) Materialized Views in my database pg_upgrade proceeds (and is now copying the data which I guess will take some time...

Cheers
Stefan


-----Original Message-----
From: Sandro Santilli [mailto:[hidden email]] On Behalf Of Sandro Santilli
Sent: 5. april 2016 12:59
To: Blumentrath, Stefan <[hidden email]>
Cc: PostGIS Development Discussion <[hidden email]>
Subject: Re: [postgis-devel] Upgrade PG 9.3 to 9.5 with PostGIS issue

On Tue, Apr 05, 2016 at 09:46:50AM +0000, Blumentrath, Stefan wrote:

> Many thanks.
>
> The query returns a list of 153, quite basic functions, e.g.:
> "buffer(geometry, double precision)"
> "buildarea(geometry)"
> "centroid(geometry)"
> "contains(geometry, geometry)"
> "convexhull(geometry)"
> "crosses(geometry, geometry)"
> "difference(geometry, geometry)"
> "dimension(geometry)"
> "disjoint(geometry, geometry)"
> "distance(geometry, geometry)"
> "distance_sphere(geometry, geometry)"
> "distance_spheroid(geometry, geometry, spheroid)"
> "dump(geometry)"
> "dumprings(geometry)"

All those signatures are missing an "st_" or "postgis_" prefixes, meaning they probably come from pre-2.0 times, OR have been installed via "legacy.sql".

> Does that mean the upgrade of the extension failed locally?

The upgrade procedure doesn't attempt to upgrade the legacy functions, so their presence is not necessarely an indication of an upgrade failure.

If you can tell how those functions got in there, it might help determining if it was an upgrade bug to keep them in or not.

> Do I have to run some sql scripts from /usr/share/postgresql/9.5/extension/ manually?

I dubt there's anything extension-specific about those functions.

This is a list of functions associated to the 'postgis' extension:

 SELECT pg_describe_object(d.classid, d.objid, 0)
   FROM pg_depend d, pg_extension e
  WHERE d.refobjid = e.oid
    AND d.refclassid = 'pg_extension'::regclass
    AND d.classid = 'pg_proc'::regclass
    AND deptype = 'e' AND e.extname = 'postgis';

A function associated to an extension cannot be removed with DROP FUNCTION

You could tweak the function you used to _list_ those functions to add a DROP FUNCTION and then run the resulting sql. Or, if you installed those functions via loading "legacy.sql", you could try loading "uninstall_legacy.sql" but it should be coming from the same version the "legacy.sql" came from.

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PG 9.3 to 9.5 with PostGIS issue

Sandro Santilli-2
On Wed, Apr 06, 2016 at 10:25:29AM +0000, Blumentrath, Stefan wrote:

> pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: public.geometry && public.geometry

Did you add postgis to the target database prior to restore
the data from the dump ?
Did you use postgis_restore.pl ?

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel
SBL
Reply | Threaded
Open this post in threaded view
|

Re: Upgrade PG 9.3 to 9.5 with PostGIS issue

SBL
Hi Sandro,

And thanks for your reply. I use:

/usr/lib/postgresql/9.5/bin/pg_upgrade -d /data/postgresql/9.3/main -D /data/postgresql/9.5/main -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.5/bin -p 5432 -P 5433 -v -j 10

So I do not dump/restore. pg_upgrade complains when the target cluster is not empty, so I don`t think it is possible to add postgis to the "target database", in fact there is no target database, so I did not read the FAQ carefully enough - sorry...

Cheers
Stefan


-----Original Message-----
From: Sandro Santilli [mailto:[hidden email]] On Behalf Of Sandro Santilli
Sent: 6. april 2016 14:16
To: Blumentrath, Stefan <[hidden email]>
Cc: PostGIS Development Discussion <[hidden email]>; Åström, Jens <[hidden email]>
Subject: Re: [postgis-devel] Upgrade PG 9.3 to 9.5 with PostGIS issue

On Wed, Apr 06, 2016 at 10:25:29AM +0000, Blumentrath, Stefan wrote:

> pg_restore: [archiver (db)] could not execute query: ERROR:  operator
> does not exist: public.geometry && public.geometry

Did you add postgis to the target database prior to restore the data from the dump ?
Did you use postgis_restore.pl ?

--strk;
_______________________________________________
postgis-devel mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-devel