[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] PostGIS raster data restore issue
From: "Regina Obe" <lr () pcorp ! us>
Date: 2016-07-27 20:15:27
Message-ID: 000b01d1e843$9df9c620$d9ed5260$ () 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.
1. I'd like it to be in September or some time before PostgreSQL 9,6,
but we'll see -
http://lists.osgeo.org/pipermail/postgis-devel/2016-July/025872.html (still
some loose ends to button up)
2. What sort of problems did you have? It should work if you create
extension first, run the set path script and restore the data. That's the
way I do it.
If you are in a rush you could hack your share/extensions/postgis-2.2.2.sql
file
Basically just copy the contents of share/contrib/postgis-2.2/
rtpostgis_proc_set_search_path.sql to the end of the extension
postgis-2.2.2.sql and postgis.sql extension scripts.
That way when the restore runs CREATE EXTENSION postgis, it will
automatically fix the paths of functions so the constraints don't fail when
they are called.
Then your create database and load data in one call should work.
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf
Of Kevin Berger
Sent: Wednesday, July 27, 2016 10:18 AM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] PostGIS raster data restore issue
Thanks Regina, that helps.
1. When will PostGIS 2.3 be available?
2. FYI - although custom might be preferred, we had problems restoring
custom backup. We were able to restore the backup in plain text format
without issue. Our database is approximately 30 GB.
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf
Of Regina Obe
Sent: July 25, 2016 15:42
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] PostGIS raster data restore issue
Unfortunately you have to create the database first. I'm working on fixing
this for PostGIS 2.3 so you can do without creating db first, but for
PostGIS 2.2.2 steps would be
1) Do your backup with pg_dump.exe (custom is preferred over plain
text)
2) Create new database, install postgis extension
3) Run the share/contrib/postgis-2.2/
rtpostgis_proc_set_search_path.sql (note this script is there for PostGIS
2.2.2+)
4) Restore your data as usual with pg_restore (if custom backup) or
psql if plain text.
Hope that helps,
Regina
<http://www.postgis.us> http://www.postgis.us
<http://postgis.net> http://postgis.net
From: postgis-users [ <mailto:postgis-users-bounces@lists.osgeo.org>
mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of Kevin Berger
Sent: Monday, July 25, 2016 1:06 PM
To: <mailto:postgis-users@lists.osgeo.org> postgis-users@lists.osgeo.org
Subject: [postgis-users] PostGIS raster data restore issue
Hi,
We want to test our PostGIS Version 2.2.2.1/PostgreSQL 9.5.2.1 restore. We
have raster data loaded into the database.
The backup command we are using is: pg_dump.exe -c -C (We specify -C as we
want the SQL for creating databases to be in the dump file - we want to
simplify the restore operation by having the database automatically created
as opposed to having to create an empty database then restore the data into
it). The dump file is 32 GB so modifying the dump file in Notepad++ is not
an option.
The restore command we are using is: psql -U postgres -f <dumpfile>.
However, we get several restore errors such as:
ERROR: function st_bandmetadata(public.raster, integer[]) does not exist
I am thinking that the only thing we can do is to create an empty database
then restore the data into it (which as I mentioned adds more steps to
restore). Is my rationale correct?
NOTE: the restore does work fine on a database that does not contain raster
data.
Kevin
[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:11.0pt;
font-family:"Calibri",sans-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.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle21
{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;}
/* List Definitions */
@list l0
{mso-list-id:756363447;
mso-list-type:hybrid;
mso-list-template-ids:-2065550312 269025295 269025305 269025307 269025295 269025305 \
269025307 269025295 269025305 269025307;} @list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1
{mso-list-id:1618217149;
mso-list-type:hybrid;
mso-list-template-ids:-68398790 67698703 67698713 67698715 67698703 67698713 \
67698715 67698703 67698713 67698715;} @list l1:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l2
{mso-list-id:1654719301;
mso-list-type:hybrid;
mso-list-template-ids:-815470770 67698705 67698713 67698715 67698703 67698713 \
67698715 67698703 67698713 67698715;} @list l2:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l2:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l2:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l2:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></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=MsoListParagraph \
style='text-indent:-.25in;mso-list:l1 level1 lfo5'><![if !supportLists]><span \
style='color:#1F497D'><span style='mso-list:Ignore'>1.<span style='font:7.0pt "Times \
New Roman"'> </span></span></span><![endif]><span \
style='color:#1F497D'>I'd like it to be in September or some time before PostgreSQL \
9,6, but we'll see – <a \
href="http://lists.osgeo.org/pipermail/postgis-devel/2016-July/025872.html">http://lists.osgeo.org/pipermail/postgis-devel/2016-July/025872.html</a> \
(still some loose ends to button up)<o:p></o:p></span></p><p class=MsoListParagraph \
style='text-indent:-.25in;mso-list:l1 level1 lfo5'><![if !supportLists]><span \
style='color:#1F497D'><span style='mso-list:Ignore'>2.<span style='font:7.0pt "Times \
New Roman"'> </span></span></span><![endif]><span \
style='color:#1F497D'>What sort of problems did you have? It should work if you \
create extension first, run the set path script and restore the data. That's \
the way I do it.<o:p></o:p></span></p><p class=MsoListParagraph><span \
style='color:#1F497D'>If you are in a rush you could hack your \
share/extensions/postgis-2.2.2.sql file<o:p></o:p></span></p><p \
class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoListParagraph><span style='color:#1F497D'>Basically just copy the contents \
of </span><span style='color:#1F497D'>share/contrib/postgis-2.2/</span> <span \
style='color:#1F497D'>rtpostgis_proc_set_search_path.sql to the end of the \
extension </span><span style='color:#1F497D'>postgis-2.2.2.sql and postgis.sql \
extension </span><span style='color:#1F497D'>scripts.<o:p></o:p></span></p><p \
class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoListParagraph><span style='color:#1F497D'>That way when the restore runs \
CREATE EXTENSION postgis, it will automatically fix the paths of functions so the \
constraints don't fail when they are called.<o:p></o:p></span></p><p \
class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoListParagraph><span style='color:#1F497D'>Then your create database and load \
data in one call should work.<o:p></o:p></span></p><p class=MsoListParagraph><span \
style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span \
style='color:#1F497D'><o:p> </o:p></span></p><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal style='margin-left:.5in'><b>From:</b> postgis-users \
[mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Kevin \
Berger<br><b>Sent:</b> Wednesday, July 27, 2016 10:18 AM<br><b>To:</b> PostGIS Users \
Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: \
[postgis-users] PostGIS raster data restore issue<o:p></o:p></p></div></div><p \
class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><p class=MsoNormal \
style='margin-left:.5in'><span lang=EN-CA style='color:#1F497D'>Thanks Regina, that \
helps.<o:p></o:p></span></p><p class=MsoListParagraph \
style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level1 lfo2'><![if \
!supportLists]><span lang=EN-CA style='color:#1F497D'><span \
style='mso-list:Ignore'>1.<span style='font:7.0pt "Times New \
Roman"'> </span></span></span><![endif]><span \
lang=EN-CA style='color:#1F497D'>When will PostGIS 2.3 be \
available?<o:p></o:p></span></p><p class=MsoListParagraph \
style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level1 lfo2'><![if \
!supportLists]><span lang=EN-CA style='color:#1F497D'><span \
style='mso-list:Ignore'>2.<span style='font:7.0pt "Times New \
Roman"'> </span></span></span><![endif]><span \
lang=EN-CA style='color:#1F497D'>FYI – although custom might be preferred, we \
had problems restoring custom backup. We were able to restore the backup in plain \
text format without issue. Our database is approximately 30 \
GB.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=EN-CA \
style='color:#1F497D'><o:p> </o:p></span></p><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal style='margin-left:.5in'><b>From:</b> postgis-users [<a \
href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] \
<b>On Behalf Of </b>Regina Obe<br><b>Sent:</b> July 25, 2016 15:42<br><b>To:</b> \
'PostGIS Users Discussion'<br><b>Subject:</b> Re: [postgis-users] PostGIS raster data \
restore issue<o:p></o:p></p></div></div><p class=MsoNormal \
style='margin-left:.5in'><span lang=EN-CA><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>Unfortunately \
you have to create the database first. I'm working on fixing this for PostGIS \
2.3 so you can do without creating db first, but for PostGIS 2.2.2 steps would \
be<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal \
style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l2 level1 \
lfo4'><![if !supportLists]><span style='color:#1F497D'><span \
style='mso-list:Ignore'>1)<span style='font:7.0pt "Times New \
Roman"'> </span></span></span><![endif]><span \
style='color:#1F497D'>Do your backup with </span><span lang=EN-CA>pg_dump.exe (custom \
is preferred over plain text)</span><span \
style='color:#1F497D'><o:p></o:p></span></p><p class=MsoListParagraph \
style='margin-left:1.0in;text-indent:-.25in;mso-list:l2 level1 lfo4'><![if \
!supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>2)<span \
style='font:7.0pt "Times New Roman"'> \
</span></span></span><![endif]><span style='color:#1F497D'>Create new database, \
install postgis extension<o:p></o:p></span></p><p class=MsoListParagraph \
style='margin-left:1.0in;text-indent:-.25in;mso-list:l2 level1 lfo4'><![if \
!supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>3)<span \
style='font:7.0pt "Times New Roman"'> \
</span></span></span><![endif]><span style='color:#1F497D'>Run the \
share/contrib/postgis-2.2/</span> <span \
style='color:#1F497D'>rtpostgis_proc_set_search_path.sql (note this script is \
there for PostGIS 2.2.2+)<o:p></o:p></span></p><p class=MsoListParagraph \
style='margin-left:1.0in;text-indent:-.25in;mso-list:l2 level1 lfo4'><![if \
!supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>4)<span \
style='font:7.0pt "Times New Roman"'> \
</span></span></span><![endif]><span style='color:#1F497D'>Restore your data as usual \
with pg_restore (if custom backup) or psql if plain \
text.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal \
style='margin-left:.5in'><span style='color:#1F497D'>Hope that \
helps,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span \
style='color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:.5in'><span lang=EN-CA><a href="http://www.postgis.us"><span \
lang=EN-US>http://www.postgis.us</span></a></span><span \
style='color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:.5in'><span lang=EN-CA><a href="http://postgis.net"><span \
lang=EN-US>http://postgis.net</span></a></span><span \
style='color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:.5in'><span \
style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal \
style='margin-left:.5in'><span \
style='color:#1F497D'><o:p> </o:p></span></p><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal style='margin-left:1.0in'><b>From:</b> postgis-users [<span \
lang=EN-CA><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span \
lang=EN-US>mailto:postgis-users-bounces@lists.osgeo.org</span></a></span>] <b>On \
Behalf Of </b>Kevin Berger<br><b>Sent:</b> Monday, July 25, 2016 1:06 \
PM<br><b>To:</b> <span lang=EN-CA><a \
href="mailto:postgis-users@lists.osgeo.org"><span \
lang=EN-US>postgis-users@lists.osgeo.org</span></a></span><br><b>Subject:</b> \
[postgis-users] PostGIS raster data restore issue<o:p></o:p></p></div></div><p \
class=MsoNormal style='margin-left:1.0in'><o:p> </o:p></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA>Hi,<o:p></o:p></span></p><p \
class=MsoNormal style='margin-left:1.0in'><span \
lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA>We want to test our PostGIS Version \
2.2.2.1/PostgreSQL 9.5.2.1 restore. We have raster data loaded into the \
database.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span \
lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA>The backup command we are using is: \
pg_dump.exe -c -C (We specify -C as we want the SQL for creating databases to be in \
the dump file - we want to simplify the restore operation by having the database \
automatically created as opposed to having to create an empty database then restore \
the data into it). The dump file is 32 GB so modifying the dump file in Notepad++ is \
not an option.<o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:1.0in'><span lang=EN-CA>The restore command we are \
using is: psql -U postgres -f <dumpfile>. However, we get several restore \
errors such as:<o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:1.0in'><span lang=EN-CA>ERROR: function \
st_bandmetadata(public.raster, integer[]) does not exist<o:p></o:p></span></p><p \
class=MsoNormal style='margin-left:1.0in'><span \
lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA>I am thinking that the only thing we can \
do is to create an empty database then restore the data into it (which as I mentioned \
adds more steps to restore). Is my rationale correct?<o:p></o:p></span></p><p \
class=MsoNormal style='margin-left:1.0in'><span \
lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA>NOTE: the restore does work fine on a \
database that does not contain raster data.<o:p></o:p></span></p><p class=MsoNormal \
style='margin-left:1.0in'><span lang=EN-CA><o:p> </o:p></span></p><p \
class=MsoNormal style='margin-left:1.0in'><span \
lang=EN-CA>Kevin<o:p></o:p></span></p></div></body></html>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://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