[postgis-users] pg_restore: error: could not execute query: ERROR: function public.st

Bekir Niyaz

unread,

Oct 15, 2020, 4:06:37 AM10/15/20

to postgi...@lists.osgeo.org

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;

Regina Obe

unread,

Oct 15, 2020, 4:28:40 AM10/15/20

to PostGIS Users Discussion

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

Bekir Niyaz

unread,

Oct 16, 2020, 4:21:17 AM10/16/20

to PostGIS Users Discussion

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 <l...@pcorp.us> adına postgis-users <postgis-us...@lists.osgeo.org>
Gönderildi: 15 Ekim 2020 Perşembe 11:28
Kime: 'PostGIS Users Discussion' <postgi...@lists.osgeo.org>
Konu: Re: [postgis-users] pg_restore: error: could not execute query: ERROR: function public.st_accum(public.geometry) does not exist

Reply all

Reply to author

Forward