[postgis-users] [postgis-devel] Upgrade issues

[Regina Obe]

Is this the first time you are running pg_upgrade?

1)      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

2)      This would not be an issue with regular PostGIS upgrade – ALTER EXTENSION postgis UPDATE;

3)        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.

4)      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).

 

 

From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of James Sewell
Sent: Tuesday, September 3, 2019 10:26 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Cc: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: [postgis-devel] [postgis-users] Upgrade issues

 

> Paul – any thoughts on how to fix this one?

None. Is it easily replicable? Does any non-standard geography srid trigger it?

 

Making some progress now.

 

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.

 

The failure can take one of two forms:

a) When the table is created spatial_ref_sys doesn't exist

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)

 

 

 I can't answer is how this has not been hit before???

 

  _____  

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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190903/42a74daf/attachment.html>