[postgis-devel] In Place Upgrade

[Paul Ramsey]

> On Sep 24, 2019, at 10:37 AM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> 
> 
> 
>> On Sep 24, 2019, at 10:20 AM, Regina Obe <lr at pcorp.us> wrote:
>> 
>> We just need to fix the view.  I think you can copy the definition from 2.5 and backport to 2.4.
> 
> Except I already had 2.5 in place… something is very odd here. I did replace both geometry_columns and raster_overviews, and then the pg_upgrade from 11/2.5.3 to 12/3.0.0alpha4 worked.
> 
> Still some broken bits though… full version noted the mismatch between function versions and extension version. Running extension upgrade appeared to work, but left a broken raster install lying around.

Ha ha, even maintainers don’t remember to read the error messages, so the need to run 

SELECT postgis_extensions_upgrade();

escaped me. That cleans things up and allows me to create the postgis_raster extension, leaving a finished upgrade.

There’s a lesson here about documentation and expectations, I guess. 

P

> 
> Unfortunately the packages aren’t currently available to test, since the pg11 postgis packages are borked right now and there is no 12/2.5 package to work with, but probably the “recommended” upgrade path will be to stairstep up either the postgis version first and then the pg or vice versa, rather than this 2-in-1 path I’m testing now.
> 
> P.
> 
> 
> 
> 
> postgis=# select postgis_full_version();
> 
>              postgis_full_version                                                                                                 
> 
> -----------------------------------------------------------------------------------------------------------------------------------
> -----------------------------------------------------------------------------------------------------------------------------------
> -------------------------------------------------
> POSTGIS="3.0.0alpha4 r17702" [EXTENSION] PGSQL="110" (procs need upgrade for use with "120") GEOS="3.7.1-CAPI-1.11.1 27a5e771" PRO
> J="6.2.0" GDAL="GDAL 3.0.1, released 2019/06/28" LIBXML="2.9.1" LIBJSON="0.11" (core procs from "2.5.1 r17027" need upgrade) RASTER
> (raster procs from "2.5.1 r17027" need upgrade)
> (1 row)
> 
> postgis=# alter extension postgis update;
> WARNING:  unpackaging raster
> WARNING:  PostGIS Raster functionality has been unpackaged
> HINT:  type `SELECT postgis_extensions_upgrade(); to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
> ALTER EXTENSION
> postgis=# \dx
>                                       List of installed extensions
>  Name   |   Version   |   Schema   |                             Description                             
> ---------+-------------+------------+---------------------------------------------------------------------
> plpgsql | 1.0         | pg_catalog | PL/pgSQL procedural language
> postgis | 3.0.0alpha4 | public     | PostGIS geometry, geography, and raster spatial types and functions
> (2 rows)
> 
> postgis=# create extension postgis_raster;
> ERROR:  PostGIS Raster is already installed in schema 'public'
> CONTEXT:  PL/pgSQL function inline_code_block line 10 at RAISE
> 
> 
> 
>> 
>> Looks like the new function pg_get_constraintdef we are using has existed at least since 9.2 so we should be okay with 2.4 fixing
>> 
>> https://www.postgresql.org/docs/9.2/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
>> 
>> 
>> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of Paul Ramsey
>> Sent: Tuesday, September 24, 2019 1:00 PM
>> To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
>> Subject: Re: [postgis-devel] In Place Upgrade
>> 
>> 
>> 
>> 
>>> On Sep 24, 2019, at 9:58 AM, rmrodriguez at carto.com wrote:
>>> 
>>>> Already at 2.5.3…
>>>> postgis25_11-2.5.3-3.rhel7.x86_64
>>> 
>>> That's the binary, are the scripts installed in the database at 2.5.3 too?
>> 
>> Yes, this isn’t a production system, this is a blank (with one spatial table) database created today just to test upgrading against packages.
>> 
>> 
>>> 
>>> 
>>>> What would fixing 2.4 entail? Is there a ticket I can use to track this down? I think it’s worthwhile, probably.
>>> 
>>> It was done for 3 and 2.5 in https://trac.osgeo.org/postgis/ticket/4231
>> 
>> Thanks,
>> 
>> P
>> 
>> 
>>> 
>>> 
>>> -- 
>>> Raúl Marín Rodríguez
>>> carto.com
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>> 
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>