<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=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
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: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-family:"Calibri",sans-serif;}
@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:853106933;
mso-list-type:hybrid;
mso-list-template-ids:961938694 -2050735478 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-number-format:alpha-lower;
mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:.75in;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:1.25in;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:1.75in;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:2.25in;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:2.75in;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:3.25in;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:3.75in;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:4.25in;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:4.75in;
text-indent:-9.0pt;}
@list l1
{mso-list-id:1165245836;
mso-list-type:hybrid;
mso-list-template-ids:-1560619814 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l1:level1
{mso-level-text:"%1\)";
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;}
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=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Just a slight clarification on what Rich just said<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=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>1)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If you installed PostGIS with CREATE EXTENSION -- postgis functions, views, and included tables (except custom entries in spatial_ref_sys) aren't backed up anyway regardless what you do. Only thing that is included in the backup is the command<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; and custom spatial_ref_sys rows if you added any.<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'>So it actually always ends up picking whatever the default postgis (that listed in share/extensions/postgis.control) you have in your 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'>If for whatever reason, you have multiple versions of PostGIS installed in your PostgreSQL instance, and you want your restore to not use the default PostGIS version (that listed in postgis.control),<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'>Then you would need to do <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 version "2.1.8"; --for example<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'>And then restore your data non-cleanly.<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=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>2)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>This issue is only an issue if your postgis extension is not in the same schema as your data – so best practices screws you in this regard.<o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The reason why it's not an issue with same schema is that restore always adds the schema of the table being restored in search path.<o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>3)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>This issue only affects data where either constraints or indexes defined on the table utilize functions that utilize other PostGIS functions or other non-pgcatalog functions.<o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So Darrel to answer your question. You do not need it for all 16 constraint functions, just the 4 I mentioned that call out to other functions. The reason for that is that on a table, the index functions and constraint functions, are always fully-qualified, so it's only when that full-qualified function then makes an unqualified call to another function that the issue ensues.<o:p></o:p></span></p><p class=MsoListParagraph><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><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>This issue isn't isolated to raster restores, but it affects raster much more that geometry/geography proper, since raster utilizes constraint functions that call other postgis functions and these get triggered as part of the restore to validate the constraints.<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><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If you were to for example create an index with any postgis function that happens to call another function and your data happens not to be in the same schema as where postgis is installed, then you will have a restore problem.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>In case of indexes it's especially annoying because you don't notice it until you notice things are much slower. E.g. if you have a spatial index that uses ST_Transform (that's the common case I run into), this issue crops up.<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'>So I also out of habit set the search_path on ST_Transform as well.<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'>Thanks,<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 style='margin-left:.5in'><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>Richard Greenwood<br><b>Sent:</b> Wednesday, November 11, 2015 5:41 PM<br><b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-users] restore problem<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:0in;margin-right:0in;margin-bottom:12.0pt;margin-left:.5in'>Darrel,<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:0in;margin-right:0in;margin-bottom:12.0pt;margin-left:.5in'>A couple people have already pointed out that creating the postgis extension in the target database before you restore is advisable. Going along with that I also recommend excluding postgis from your backup. Postgis is put in the "public" schema so I avoid putting anything else in the "public" schema and then exclude it from my backups. The backups are a little smaller, there are fewer notices when you restore, and upgrading the postgis versions is a breeze.<o:p></o:p></p></div><p class=MsoNormal style='margin-left:.5in'>Best of luck,<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:0in;margin-right:0in;margin-bottom:12.0pt;margin-left:.5in'>Rich<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><p class=MsoNormal style='margin-left:.5in'>On Wed, Nov 11, 2015 at 2:56 PM, Darrel Maddy <<a href="mailto:darrel.maddy@newcastle.ac.uk" target="_blank">darrel.maddy@newcastle.ac.uk</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Dear Regina,</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Many thanks for this. A least now it looks like this is nothing I have done (my first assumption with all problems is that I did something wrong – this assumption is usually correct). </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I had seen that ticket (this was the chatter I referred to) but assumed with the Milestone set at 2.0.8 I was hoping this had been fixed by 2.2 – clearly I do not understand this terminology!</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I will attempt to use this workaround tomorrow – hopefully it will resolve the restore issue and I will be back tomorrow full of praise </span><span lang=EN-GB style='font-size:11.0pt;font-family:Wingdings;color:#1F497D'>J</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Santa is bringing me a copy of your “PostrgreSQL: Up and Running book”, so hopefully once I am good to go with the basics I can really start to see what this combo can do.</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Best wishes</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><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='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><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:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> 11 November 2015 21:43</span><span lang=EN-GB><o:p></o:p></span></p><div><div><p class=MsoNormal style='margin-left:.5in'><span lang=EN-GB><br><b>To:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] restore problem<o:p></o:p></span></p></div></div></div></div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel,</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Sadly you are not the only one. </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I have the issue documented here:</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a href="https://trac.osgeo.org/postgis/ticket/2485" target="_blank">https://trac.osgeo.org/postgis/ticket/2485</a></span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>and I haven't come up with a generic enough fix I feel comfortable including in PostGIS code.</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The work-around is to do this</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>1)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Create your database and do CREATE EXTENSION postgis;</span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>2)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Then add search paths to the functions used in check constraints</span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'>raster_constraint_info_regular_blocking <span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'>_raster_constraint_nodata_values <span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.75in'>_raster_constraint_out_db <span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.75in'> <span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.75in'><span style='font-size:10.0pt;font-family:"Courier New"'>_raster_constraint_pixel_types</span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>e.g.</span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:10.0pt;font-family:"Courier New"'>ALTER FUNCTION _raster_constraint_pixel_types(raster)</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:10.0pt;font-family:"Courier New"'> SET search_path=pg_catalog,public,postgis;</span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>3)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Then restore your data.</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I'll try to write a full script up in the ticket</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina</span><span lang=EN-GB><o:p></o:p></span></p><p style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><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='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><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 [<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> Wednesday, November 11, 2015 2:03 PM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] restore problem</span><span lang=EN-GB><o:p></o:p></span></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>OK can I come at this from a different direction? Are there any backup options I should be selecting to eliminate this problem?</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I am really struggling to understand why I cannot backup and restore a raster table – surely others have had this issue? At present while my databases are small (i.e. <13GB) I can live with rebuilding if things fall over but these tables will grow to TB sizes soon (if I deploy this for real) and I cannot contemplate having no reliable backup.</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I have been selecting only the default options for backup – should I be doing something differently?</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Could this be a problem with the raster tables themselves? They seem to work with my SQL queries (including putting the tiles together and viewing in QGIS) but is there anything I should be doing to them before I select backup? If the backup does not report errors does this mean there are no errors in the backup? </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I realise I have much to learn using postgres but I see little point in putting in that much effort if backup/restore does not work for me.</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I really do appreciate the helpful suggestions that have been made so far but are there any further suggestions?</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><a name="150f88d5442e5e75__MailEndCompose"></a><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><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='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><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 [<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Darrel Maddy<br><b>Sent:</b> 10 November, 2015 10:25 PM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] restore problem</span><span lang=EN-GB><o:p></o:p></span></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The default appears to be custom. I did not change anything when I did this. I just accepted the defaults.</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><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 [<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Bborie Park<br><b>Sent:</b> 10 November 2015 22:20<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] restore problem</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>Odd. Looking at your error message again, it looks like the constraint is already in place by the time the data is getting loaded...<o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>What are you restoring from? Tar, custom or text?<o:p></o:p></span></p></div></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>On Tue, Nov 10, 2015 at 2:16 PM, Darrel Maddy <<a href="mailto:darrel.maddy@newcastle.ac.uk" target="_blank">darrel.maddy@newcastle.ac.uk</a>> wrote:<o:p></o:p></span></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Dear Bborie,</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Apologies, I was obviously not clear – I was not asking about the problem itself, I can accept this is a difficult problem to resolve. I was simply asking for instruction on how to use your work around. This you kindly provided. Alas, however, even with the boxes checked, I get the same error and the raster table (dems) is empty.</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><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:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Bborie Park<br><b>Sent:</b> 10 November 2015 21:57</span><span lang=EN-GB><o:p></o:p></span></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB><br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] restore problem<o:p></o:p></span></p></div></div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>Given that the core problem has to do with the restore process' search_path, no amount of advice will help you get around the restrictions in place with the restore process without jumping into that process.<o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>In pgAdmin, you can check the boxes "Pre-data" and "Data" of the "Restore Options #1" tab. This won't restore the post-data portion of the backup as restoring post-data should be causing your error.<o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>You could also try unchecking the box "Exit On Error" of the "Restore Options #2" tab.<o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>-bborie<o:p></o:p></span></p></div></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>On Tue, Nov 10, 2015 at 1:29 PM, Darrel Maddy <<a href="mailto:darrel.maddy@newcastle.ac.uk" target="_blank">darrel.maddy@newcastle.ac.uk</a>> wrote:<o:p></o:p></span></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Dear Bborie,</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Yikes – that makes little sense to me. I am new to both postgres and postgis and would have hoped that backup and restore would have been simple push-button exercise!</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>As I am using PgAdmin I have no idea how to follow your instructions </span><span lang=EN-GB style='font-size:11.0pt;font-family:Wingdings;color:#1F497D'>L</span><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Anyone care to give me an idiots guide?</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Sorry</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darrel</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><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:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Bborie Park<br><b>Sent:</b> 10 November 2015 21:22<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] restore problem</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>This problem arises because PostgreSQL sets the search path during the restore process. Unfortunately, the search path is quite restrictive.<o:p></o:p></span></p><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>You should be able to do your restore in three sections using pg_restore's --section flag.<o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>The error looks like it has to be the post-data section, which you should output to a text file, change the "SET search_path" statements and then run through psql.<o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>-bborie<o:p></o:p></span></p></div></div></div></div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>On Tue, Nov 10, 2015 at 1:11 PM, Darrel Maddy <<a href="mailto:darrel.maddy@newcastle.ac.uk" target="_blank">darrel.maddy@newcastle.ac.uk</a>> wrote:<o:p></o:p></span></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>Dear all,<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>Like many here I suspect I want to keep a copy of my main databases held on my workstation, on my laptop. I used PgAdmin to backup the databases concerned (the backup files look about the size I was expecting) but restore on my laptop did not successfully complete. All my shp file tables restored without issues but my raster tables (these are tiled rasters) would not. Looking at the error in the restore window of PgAdmin this appears to be a problem with a function/constraint.<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>pg_restore: processing data for table "dems"<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>pg_restore: [archiver (db)] Error while PROCESSING TOC:<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>pg_restore: [archiver (db)] Error from TOC entry 3459; 0 94054 TABLE DATA dems postgres<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>pg_restore: [archiver (db)] COPY failed for table "dems": ERROR: function st_bandmetadata(public.raster, integer[]) does not exist<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>LINE 1: SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1...<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> ^<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>HINT: No function matches the given name and argument types. You might need to add explicit type casts.<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>QUERY: SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1, ARRAY[]::int[]); <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>CONTEXT: SQL function "_raster_constraint_pixel_types" during inlining<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>COPY dems, line 1: "1 0100000100000000000000344000000000000034C0E0CCCCCCB1D517418066666692F80C41000000000000000000000000..."<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>I did a quick search and found some old chatter on this issue but from the messages I read I would have expected this issue to have been cleared up in postgis 2 . <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>Obviously I cannot contemplate having mission critical data in a database which does not backup/restore correctly so I am assuming there is a fix which avoids this issue or there is another way to make a copy of the database for transfer elsewhere?<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>Once again any help would be gratefully received.<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>Best wishes<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='color:#888888'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB style='color:#888888'>Darrel</span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB>ps. Apologies for the string of questions I have asked lately – I will go silent once more shortly as I must move on to other things.<o:p></o:p></span></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></span></p></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div></div></div></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></span></p></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div></div></div></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></span></p></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span lang=EN-GB> <o:p></o:p></span></p></div></div></div></div></div><p class=MsoNormal style='margin-left:.5in'><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></blockquote></div><p class=MsoNormal style='margin-left:.5in'><br><br clear=all><br>-- <o:p></o:p></p><div><div><p class=MsoNormal style='margin-left:.5in'>Richard W. Greenwood, PLS<br><a href="http://www.greenwoodmap.com" target="_blank">www.greenwoodmap.com</a><o:p></o:p></p></div></div></div></div></body></html>