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

[Paul Ramsey]

> On Sep 3, 2019, at 6:20 AM, Regina Obe <lr at pcorp.us> wrote:
> 
> Oh didn’t think of that one.  That ‘s a bummer.  PostGIS has supported other long/lat spatial ref sys since PostGIS 2.2.
> I think if you use anything other than 4326 though it has to look in the spatial_ref_sys table for some things.  I’m guessing it’s checking to make sure 4283 is a valid entry in spatial_ref_sys.
>  
> I’m not sure why it doesn’t need to check for geometry or maybe we just never bother since geometry if you can’t transform it is not a huge deal and things like 0 srid aren’t in the spatial_ref_sys anyway.
>  
> Paul – any thoughts on how to fix this one?

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

P

>  
> Thanks,
> Regina
>  
>  
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of James Sewell
> Sent: Monday, September 2, 2019 8:32 PM
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Upgrade issues
>  
> Sadly that's not the case - the issue is with the actual Geometry column.
>  
> ERROR:  relation "public.spatial_ref_sys" does not exist
> LINE 21:     location_pt public.geography(Point,4283),
>  
> I'm curious how this ever worked?
>  
> James
>  
> On Tue, 3 Sep 2019 at 09:35, Regina Obe <lr at pcorp.us> wrote:
>> I recall Raúl  mentioning he triggered this.  I think he had something like a table constraint on ST_Buffer(geog.. ) or some other function that internally relies on spatial_ref_sys.
>>  
>> The issue is that since spatial_ref_sys is a table, pg_upgrade doesn’t populate before it populates other tables. pg_upgrade first makes the structure of all the tables and pg_upgrade doesn’t know it needs to populate the data in spatial_ref_sys before the other tables as it does a create extensions in non-standard way when migrating the data to maintain same exact functions/data etc as it had before.
>>  
>> I thought we fixed this issue like in 2.5.3, but I I can’t find the bug fix notice in the notices, so maybe not.  I also can’t remember what ticket it was but I think it is ticketed.
>>  
>> What you could do is drop the offending constraint before you pg_upgrade, and then create it after the upgrade is done.
>>  
>>  
>>  
>> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of James Sewell
>> Sent: Sunday, September 1, 2019 10:59 PM
>> To: postgis-users at lists.osgeo.org
>> Subject: [postgis-users] Upgrade issues
>>  
>> Hi all,
>>  
>> I'm upgrading from 9.6 -> 11. Both versions have PostGIS 2.5.1.
>>  
>> The upgrade starts then I see this in the logs:
>>  
>> pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.spatial_ref_sys" does not exist
>> LINE 39:     "location_pt" "public"."geography"(Point,4283),
>>  
>> Above this I can see:
>>  
>> pg_restore: creating EXTENSION "postgis"
>>  
>> This is created fine - it just doesn't make the PostGIS tables in any schema?
>>  
>> Has anyone seen anything like this before?
>>  
>> Cheers,
>> 
>> James Sewell,
>>  
>>  
>> 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.
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>  
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users