[postgis-users] Ynt: pg_restore: error: could not execute query: ERROR: function public.st_accum(public.geometry) does not exist

[Bekir Niyaz]

Thank you Regina,

>Did you install PostGIS on 2.5.2 using scripts or extension?  Those functions shouldn’t even be in your backup if you used an extension install.
Yes I used extension install

>It could however be complaining about references in your code to those.
There is no any code, or function created by developer, only postgis functions.

>Do not drop postgis 2.5.2 as that would drop your geometry and raster columns.
If there is columns depend on postgis, should be error on droping extension, right ? But I easily can drop extension. May it hint to solution?
ERROR:  cannot drop extension postgis because other objects depend on it
DETAIL:  column geo of table tbl_sapmle depends on type geometry
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

>CREATE EXTENSION postgis_raster;  -- raster was broken out of the postgis extension in 3.0

>That should fix the raster errors.

Yes, total errors had decreased. All errors about grants cannot find functions
Command was: GRANT ALL ON FUNCTION public._st_distance(public.geography, public.geography, double precision, boolean) TO cedyetrapor_app;GRANT ALL ON FUNCTION public._st_distance(public.geography, public.geography, double precision, boolean) TO test_user;

other errors:
pg_restore: error: could not execute query: ERROR:  function public._st_asgeojson(integer, public.geography, integer, integer) does not exist
pg_restore: error: could not execute query: ERROR:  function public._st_asgeojson(integer, public.geometry, integer, integer) does not exist
pg_restore: error: could not execute query: ERROR:  function public._st_asgml(integer, public.geography, integer, integer, text, text) does not exist
pg_restore: error: could not execute query: ERROR:  function public._st_askml(integer, public.geography, integer, text) does not exist
pg_restore: error: could not execute query: ERROR:  function public._st_askml(integer, public.geometry, integer, text) does not exist
pg_restore: error: could not execute query: ERROR:  function public._st_buffer(public.geometry, double precision, cstring) does not exist
pg_restore: error: could not execute query: ERROR:  function public._st_distance(public.geography, public.geography, double precision, boolean) does not exist
.......

>In your old database try to find in your views (I’m guessing might be where it’s coming from) reference to ST_Accum use  -- and change them to use array_agg instead.

You mean functions here ?, because there is only default postgis views
 public | geography_columns | view | postgres
 public | geometry_columns  | view | postgres
 public | raster_columns    | view | postgres
 public | raster_overviews  | view | postgres

Thank you again for your atention.
Best regards.

________________________________
Gönderen: Regina Obe <lr at pcorp.us> adına postgis-users <postgis-users-bounces at lists.osgeo.org>
Gönderildi: 15 Ekim 2020 Perşembe 11:28
Kime: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Konu: Re: [postgis-users] pg_restore: error: could not execute query: ERROR: function public.st_accum(public.geometry) does not exist


Did you install PostGIS on 2.5.2 using scripts or extension?  Those functions shouldn’t even be in your backup if you used an extension install.



It could however be complaining about references in your code to those.



Do not drop postgis 2.5.2 as that would drop your geometry and raster columns.



If you can’t upgrade the old to 3.0, do the following on new



CREATE EXTENSION postgis_raster;  -- raster was broken out of the postgis extension in 3.0



That should fix the raster errors.



In your old database try to find in your views (I’m guessing might be where it’s coming from) reference to ST_Accum use  -- and change them to use array_agg instead.



ST_Accum was removed in 3.0 as it’s redundant and less performant than PostgreSQL built in array_agg



Hope that helps,

Regina





From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Bekir Niyaz
Sent: Thursday, October 15, 2020 4:07 AM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] pg_restore: error: could not execute query: ERROR: function public.st_accum(public.geometry) does not exist



I 'm trying restore backup from pg11 postgis 2.5.2 to pg12 postgis 3.0.0, but restore failing.



If I drop postgis 2.5.2 on source server and install 3.0.0 will It cause the problem ?



details:



source db (where backup taken):



     PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

     POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL

    1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER



target db (where restore):



     PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

     POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " SFCGAL="1.3.1" PROJ="6.2.1" LIBXML="2.9.1" LIBJSON="0

    .11" LIBPROTOBUF="1.0.2" WAGYU="0.4.3 (Internal)" TOPOLOGY



 dump file:





      Dump Version: 1.14-0

      Format: CUSTOM

      Integer: 4 bytes

      Offset: 8 bytes

      Dumped from database version: 11.1

      Dumped by pg_dump version: 12.1





example errors:



    pg_restore: error: could not execute query: ERROR:  function public.st_accum(public.geometry) does not exist

    Command was: GRANT ALL ON FUNCTION public.st_accum(public.geometry) TO test_app;

    pg_restore: error: could not execute query: ERROR:  type "public.raster" does not exist

    Command was: GRANT ALL ON FUNCTION public.st_countagg(public.raster, boolean) TO test_app;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20201016/65b59f83/attachment.html>