<div dir="ltr"><div><div><div>Darrel,<br><br></div>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.<br><br></div>Best of luck,<br></div>Rich<br><br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Nov 11, 2015 at 2:56 PM, Darrel Maddy <span dir="ltr"><<a href="mailto:darrel.maddy@newcastle.ac.uk" target="_blank">darrel.maddy@newcastle.ac.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div link="blue" vlink="purple" lang="EN-GB">
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Dear Regina,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span 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). <u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span 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!<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span 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 style="font-size:11.0pt;font-family:Wingdings;color:#1f497d">J</span><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span 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.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Best wishes<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Darrel<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<div>
<div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US"> 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></p><div><div class="h5"><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<u></u><u></u></div></div><p></p>
</div>
</div><div><div class="h5">
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">Darrel,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">Sadly you are not the only one.
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">I have the issue documented here:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><a href="https://trac.osgeo.org/postgis/ticket/2485" target="_blank">https://trac.osgeo.org/postgis/ticket/2485</a><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">and I haven't come up with a generic enough fix I feel comfortable including in PostGIS code.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">The work-around is to do this<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><span>1)<span style="font:7.0pt "Times New Roman"">
</span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">Create your database and do CREATE EXTENSION postgis;<u></u><u></u></span></p>
<p><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><span>2)<span style="font:7.0pt "Times New Roman"">
</span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">Then add search paths to the functions used in check constraints<u></u><u></u></span></p>
<p>
<span lang="EN-US">raster_constraint_info_regular_blocking <u></u><u></u></span></p>
<p>
<span lang="EN-US">_raster_constraint_nodata_values <u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:18.0pt">
<span lang="EN-US">_raster_constraint_out_db <u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:18.0pt">
<span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:18.0pt">
<span style="font-size:10.0pt;font-family:"Courier New"" lang="EN-US">_raster_constraint_pixel_types</span><span lang="EN-US"><u></u><u></u></span></p>
<p><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">e.g.<u></u><u></u></span></p>
<p><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"" lang="EN-US">ALTER FUNCTION _raster_constraint_pixel_types(raster)<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"" lang="EN-US"> SET search_path=pg_catalog,public,postgis;<u></u><u></u></span></p>
<p><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><span>3)<span style="font:7.0pt "Times New Roman"">
</span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">Then restore your data.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">I'll try to write a full script up in the ticket<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">Hope that helps,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US">Regina<u></u><u></u></span></p>
<p><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d" lang="EN-US"><u></u> <u></u></span></p>
<div>
<div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US"> 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<u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span 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?<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span 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.<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span 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?<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span 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?
<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span 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.<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span 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?<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Darrel<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><a name="150f88d5442e5e75__MailEndCompose"></a><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<div>
<div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US"> 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<u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><u></u> <u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span 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.<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Darrel<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US"> 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<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><u></u> <u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">Odd. Looking at your error message again, it looks like the constraint is already in place by the time the data is getting loaded...<u></u><u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">What are you restoring from? Tar, custom or text?<u></u><u></u></p>
</div>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><u></u> <u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">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:<u></u><u></u></p>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Dear Bborie,</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span 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><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Darrel</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US"> 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><u></u><u></u></p>
<div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><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<u></u><u></u></p>
</div>
</div>
<div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
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.<u></u><u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
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.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
You could also try unchecking the box "Exit On Error" of the "Restore Options #2" tab.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
-bborie<u></u><u></u></p>
</div>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
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:<u></u><u></u></p>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Dear Bborie,</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span 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><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span 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 style="font-size:11.0pt;font-family:Wingdings;color:#1f497d">L</span><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">
</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Anyone care to give me an idiots guide?</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Sorry</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Darrel</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif" lang="EN-US"> 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><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
This problem arises because PostgreSQL sets the search path during the restore process. Unfortunately, the search path is quite restrictive.<u></u><u></u></p>
<div>
<div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
You should be able to do your restore in three sections using pg_restore's --section flag.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
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.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
-bborie<u></u><u></u></p>
</div>
</div>
</div>
</div>
<div>
<div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
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:<u></u><u></u></p>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">
Dear all,<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
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.<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
pg_restore: processing data for table "dems"<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
pg_restore: [archiver (db)] Error while PROCESSING TOC:<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
pg_restore: [archiver (db)] Error from TOC entry 3459; 0 94054 TABLE DATA dems postgres<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
pg_restore: [archiver (db)] COPY failed for table "dems": ERROR: function st_bandmetadata(public.raster, integer[]) does not exist<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
LINE 1: SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1...<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
^<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
HINT: No function matches the given name and argument types. You might need to add explicit type casts.<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
QUERY: SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1, ARRAY[]::int[]);
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
CONTEXT: SQL function "_raster_constraint_pixel_types" during inlining<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
COPY dems, line 1: "1 0100000100000000000000344000000000000034C0E0CCCCCCB1D517418066666692F80C41000000000000000000000000..."<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
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 .
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
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?<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
Once again any help would be gratefully received.<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
Best wishes<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="color:#888888"> </span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<span style="color:#888888">Darrel</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36.0pt">
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.<u></u><u></u></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt">
<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><u></u><u></u></p>
</blockquote>
</div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt">
<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><u></u><u></u></p>
</blockquote>
</div>
<p class="MsoNormal" style="margin-left:36.0pt">
<u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><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><u></u><u></u></p>
</blockquote>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><u></u> <u></u></p>
</div>
</div></div></div>
</div>
<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" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature"><div dir="ltr">Richard W. Greenwood, PLS<br><a href="http://www.greenwoodmap.com" target="_blank">www.greenwoodmap.com</a></div></div>
</div>