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

[Regina Obe]

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/20201015/fbe436bb/attachment.html>