[postgis-users] Upgrade 12->13 stuck due to postgis / raster issue

[Jim VanPeursem]

Roxanne, Allan and Regina,

Thanks so much for your help.

I created a snapshot backup, restored to a new test rds instance, and ran
the sql attachment "Spatial Types for PostgreSQL" that you highlighted.
That's a great find. My google sleuthing didn't uncover that one. Running
that script cleared up the postgis_full_version raster issues:

db=> SELECT postgis_full_version();
POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="120" (procs need upgrade for use
with PostgreSQL "130") GEOS="3.9.1-CAPI-1.14.2" PROJ="8.0.1" LIBXML="2.9.1"
LIBJSON="0.13.1" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)"
(1 row)


No more warning about raster being unbundled and requiring an upgrade, but
the PGSQL warning is new. I then did the following:

db=> SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis 3.1.7
NOTICE:  Extension postgis_raster is not available or not packagable for
some reason
NOTICE:  Extension postgis_topology is not available or not packagable for
some reason
NOTICE:  Extension postgis_tiger_geocoder is not available or not
packagable for some reason
                    postgis_extensions_upgrade
-------------------------------------------------------------------
 Upgrade completed, run SELECT postgis_full_version(); for details
(1 row)

db=> SELECT postgis_full_version();
POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="130" GEOS="3.9.1-CAPI-1.14.2"
PROJ="8.0.1" LIBXML="2.9.1" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2"
WAGYU="0.5.0 (Internal)"
(1 row)


Success!

Then I requested an aws upgrade to the test instance via the console and it
succeeded! I anticipate the same should work on the production db. I'll
give it a try during our next maintenance period.

Thanks again for everyone's help. Allan, I might need to borrow your hair
color after all of this. ;-)

->jvp



On Tue, Jan 24, 2023 at 12:07 AM Regina Obe <lr at pcorp.us> wrote:

> Jim,
>
>
>
> Sorry I had missed the thread Roxanne had written.
>
>
>
> So you already ran twice
>
>
>
> SELECT postgis_extensions_upgrade();
>
> SELECT postgis_extensions_upgrade();
>
>
>
> And still shows raster is UNPACKAGED.
>
> If you did delete some raster functions in panic, then I think that would
> prevent it from being upgraded.
>
>
>
> You want to first make sure you don’t have raster tables running the below
> query should return no records.
>
>
>
> SELECT *
>
> FROM raster_columns;
>
>
>
>
>
> Easy fix especially if you don’t need raster support, is to run the
> uninstall raster script I had attached in that mailing list thread Roxanne
> pointed to
>
>
>
>
> https://lists.osgeo.org/pipermail/postgis-users/attachments/20200529/e32c5d8c/attachment.obj
>
>
>
>
>
> That should cleanly get rid of all remnants of raster.
>
>
>
> Hope that helps,
>
> Regina
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Roxanne Reid-Bennett
> *Sent:* Monday, January 23, 2023 9:29 PM
> *To:* postgis-users at lists.osgeo.org
> *Subject:* Re: [postgis-users] Upgrade 12->13 stuck due to postgis /
> raster issue
>
>
>
> Jim,
>
> I did a search on this phrase:
>
> raster procs from "2.5.2 r17328" need upgrade
>
> I found a post from Regina from 2020 moving from 2.5.2 to 3. It might
> illumine a bit.
>
> https://lists.osgeo.org/pipermail/postgis-users/2020-May/044225.html
>
>
>
> And this one for someone who had 100% control of their PG hardware from
> last fall.
>
> https://groups.google.com/g/postgis-users/c/26iNuhXFHgg
>
>
>
> And this one from the AWS forums
>
>
> https://repost.aws/questions/QUEk9HG5x3Qq6sEB3lVmynUw/upgrade-from-postgres-12-7-r-1-to-13-3-r-1-stuck
>
> which references this:
>
>
> https://stackoverflow.com/questions/42634626/postgresql-upgrade-on-amazon-rds-blocked-by-postgis-version
>
> which uses
>
> ALTER EXTENSION postgis UPDATE;
>
> The observation in the last post about waiting overnight and things
> "magically being fixed", I saw during our dry run, not the production run,
> but definitely during the dry run.  Their observation about rebooting the
> PG server between upgrades isn't totally "off" either.
>
> Posting in the AWS Forums might get some help from inside Amazon.
>
> Or, if you have any level of support from AWS that would allow you get
> someone to "poke/prod" on disk, you might get a fix.
>
> Migrating the data is "of course" a last resort, but at least possible.
>
> Given my lack of knowledge of the upgrade steps, I've done what I can.
> Good luck.
>
> Roxanne
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
______________________________
Jim VanPeursem, PhD
http://jvp.llc <http://jvpcoaching.com/>/
us: +1 847 414 2759 (+ WhatsApp) --  skype: jimvanpeursem
*Bringing clarity and helping you go from where you are to where you want
to be*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230124/3741cc04/attachment.htm>