<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)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.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.EmailStyle17
{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:197205929;
mso-list-type:hybrid;
mso-list-template-ids:-498414166 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Is this the first time you are running pg_upgrade?<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 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'>I think most users don’t even know they can use an srid other than 4326 for geography, so this probably hasn’t been exercised by many users<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 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 would not be an issue with regular PostGIS upgrade – ALTER EXTENSION postgis UPDATE;<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 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'> It’s because pg_upgrade tries to replicate exactly what you had by first creating an empty PostGIS extension and then loading in all the extension parts from the database. In theory it should recognize that spatial_ref_sys is a part of PostGIS, so I’m very surprised you run into a situation where spatial_ref_sys table doesn’t exist. The spatial_ref_sys table being empty is a bit more understandable.<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>4)<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'>I think also there was a time when the geography SRIDs were cached in a secret place, so geography didn’t rely on spatial_ref_sys. I forget when this changed (might have been at 2.2 or 2.3).<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><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-devel [mailto:postgis-devel-bounces@lists.osgeo.org] <b>On Behalf Of </b>James Sewell<br><b>Sent:</b> Tuesday, September 3, 2019 10:26 PM<br><b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Cc:</b> PostGIS Development Discussion <postgis-devel@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-devel] [postgis-users] Upgrade issues<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><div><div><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><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal>> Paul – any thoughts on how to fix this one?<br><br>None. Is it easily replicable? Does any non-standard geography srid trigger it?<o:p></o:p></p></blockquote></div></div></blockquote><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Making some progress now.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>It looks like pg_upgrade from any (tested 9.6, 10, -> 11) version of Postgres will fail if any version of PostGIS (tested 2.5.1, 3.0) is installed and a table with a Geography, non 4326 SRID column exists.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>The failure can take one of two forms:<o:p></o:p></p></div><div><p class=MsoNormal>a) When the table is created spatial_ref_sys doesn't exist<o:p></o:p></p></div><div><p class=MsoNormal>b) When the table is created spatial_ref_sys exists but is empty (data isn't added till after the schema import using pg_upgrade)<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal> I can't answer is how this has not been hit before???<o:p></o:p></p></div></div></div><p class=MsoNormal><o:p> </o:p></p><div class=MsoNormal align=center style='text-align:center'><hr size=3 width="100%" noshade style='color:#222222' align=center></div><p class=MsoNormal><span style='font-size:7.5pt;font-family:"Arial",sans-serif;color:gray'>The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.</span><o:p></o:p></p></div></body></html>