<div dir="ltr">Thanks!<div>Obvious typo, start with pg_dump not pg_restore...</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Feb 26, 2020 at 1:33 PM Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div lang="EN-US"><div class="gmail-m_1121325527455160561WordSection1"><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">The only thing to be cautious of is if you are going from 2.something to 3.0 and you are using postgis_raster, you will need to install postgis and postgis_raster in your new database first.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">If you are using pg_restore, since the functions are not part of the backup,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Just the line CREATE EXTENSION postgis;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Which doesn’t have the version number (defaults to whatever is your default as noted in postgis.control file) – So if your default is 3+ will not have raster support.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">What would happen is if you had raster tables in your dump, they would fail to restore, because the postgis_raster types and functions, would not get created by the new version of <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">CREATE EXTENSION postgis;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">However if you create your database fresh do CREATE EXTENSION postgis; CREATE EXTENSION postgis_raster;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">And then restore your data, all will be fine including your raster tables.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><div style="border-top:none;border-right:none;border-bottom:none;border-left:1.5pt solid blue;padding:0in 0in 0in 4pt"><div><div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank">postgis-devel-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Bruce Rindahl<br><b>Sent:</b> Wednesday, February 26, 2020 4:13 PM<br><b>To:</b> PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-devel] About EXTENSION from UNPACKAGED on PostgreSQL 13<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">When ever I upgrade, I simply install the new version side by side on the same machine but on a different port. Then I do:<u></u><u></u></p><div><p class="MsoNormal">pg_restore -p 5432 mydbase | psql -p 5433 mydbase<u></u><u></u></p></div><div><p class="MsoNormal">and see if anything throws an error. If everything works, I test it for a while and then finally shut them down and swap ports in postgresql.conf <u></u><u></u></p></div><div><p class="MsoNormal">Will this avoid the issue?<u></u><u></u></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Wed, Feb 26, 2020 at 12:11 PM Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<u></u><u></u></p></div><blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"><p class="MsoNormal">> Presumably 15 years out from the 1.x -> 2.x we can stop worrying about<br>> bundling unpackaged postgis into an extension, and just recommend a hard<br>> upgrade dump/restore to the hardy souls still running 1.x.<br>> <br>> P.<br>> <br><br>We don't need to worry about 1.x cause 1.x can only do a hard upgrade to 2 or 3. We never supported soft upgrade from 1.x<br>Easy solution there is just to install postgis extension and do pg_restore/postgis_restore of your data.<br><br>So it's really just the 2.1 -> 3 that are of concern.<br>I think now is a fine time to encourage everyone to upgrade to 3 if they can so they don't need to suffer any crazy solutions we come up with :)<br><br>Turn this into a convenient emergency.<br><br>Thanks,<br>Regina<br><br>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><u></u><u></u></p></blockquote></div></div></div></div>_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div>