[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] pg_restore: error: could not execute query: ERROR: function public.st_accum(publ
From: "Regina Obe" <lr () pcorp ! us>
Date: 2020-10-15 8:28:32
Message-ID: 001e01d6a2cd$2bd20d70$83762850$ () pcorp ! us
[Download RAW message or body]
This is a multipart message in MIME format.
[Attachment #2 (multipart/alternative)]
This is a multipart message in MIME format.
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@lists.osgeo.org] On Behalf
Of Bekir Niyaz
Sent: Thursday, October 15, 2020 4:07 AM
To: postgis-users@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;
[Attachment #5 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type \
content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 \
(filtered medium)"><style><!-- /* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" \
vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>It could \
however be complaining about references in your code to \
those.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Do not drop \
postgis 2.5.2 as that would drop your geometry and raster \
columns.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If you \
can’t upgrade the old to 3.0, do the following on new<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE \
EXTENSION postgis_raster; -- raster was broken out of the postgis extension in \
3.0<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>That should \
fix the raster errors.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>ST_Accum was \
removed in 3.0 as it’s redundant and less performant than PostgreSQL built in \
array_agg<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that \
helps,<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div \
style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal><b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users \
[mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Bekir \
Niyaz<br><b>Sent:</b> Thursday, October 15, 2020 4:07 AM<br><b>To:</b> \
postgis-users@lists.osgeo.org<br><b>Subject:</b> [postgis-users] pg_restore: error: \
could not execute query: ERROR: function public.st_accum(public.geometry) does not \
exist<o:p></o:p></span></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'>I 'm trying restore backup from \
pg11 postgis 2.5.2 to pg12 postgis 3.0.0, but restore failing. \
<o:p></o:p></span></p><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>If I drop \
postgis 2.5.2 on source server and install 3.0.0 will It cause the problem \
?<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>details: \
<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>source db \
(where backup taken):<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
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<o:p></o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
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<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> 1.11.4, released \
2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" \
RASTER<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>target db \
(where restore):<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
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<o:p></o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
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<o:p></o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
.11" LIBPROTOBUF="1.0.2" WAGYU="0.4.3 (Internal)" \
TOPOLOGY<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> dump \
file:<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> <o:p></o:p></span></p></div><div><p \
class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
Dump Version: 1.14-0<o:p></o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
Format: CUSTOM<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> Integer: 4 \
bytes<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> Offset: 8 \
bytes<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> Dumped from \
database version: 11.1<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> Dumped by \
pg_dump version: 12.1<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> <o:p></o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>example \
errors:<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
pg_restore: error: could not execute query: ERROR: function \
public.st_accum(public.geometry) does not exist<o:p></o:p></span></p></div><div><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'> \
Command was: GRANT ALL ON FUNCTION public.st_accum(public.geometry) TO \
test_app;<o:p></o:p></span></p></div><div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> pg_restore: error: \
could not execute query: ERROR: type "public.raster" does not \
exist<o:p></o:p></span></p></div><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'> Command was: GRANT \
ALL ON FUNCTION public.st_countagg(public.raster, boolean) TO \
test_app;<o:p></o:p></span></p></div></div></div></body></html>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic