If this is on AWS RDS, create a ticket with AWS as they have to perform what
they call an “under the hood fix”. Hopefully AWS had logged the steps they
took when they fixed my instance. Unfortunately I can’t give you the ticket
number because of “reasons” ☹. I created a snapshot of our instance, created
a DB out of that snapshot and allowed AWS to use that as their testing ground
for the fix; They fixed it and I was able to continue my upgrade path.
Afterwards, I had to use some hair color to cover up those greys for sure.
Allan Chase
Data Engineer
From: postgis-users <postgis-users-boun...@lists.osgeo.org> On Behalf Of Jim
VanPeursem
Sent: Monday, January 23, 2023 6:32 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: [External] Re: [postgis-users] Upgrade 12->13 stuck due to postgis /
raster issue
**************************
ATTENTION - External Email
**************************
Please verify the sender before taking any actions or clicking any links.
Please treat this email with caution, especially if you are requested to click
on a link, decrypt/open an attachment, or enable macros. If you determine this
email to be malicious, please report it to phishing.
Hi Roxanne,
Thanks for the help. Here's what I see when I follow the normal postgis
extension update path:
db=> SELECT PostGIS_Extensions_Upgrade();
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_full_version
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="120" GEOS="3.8.2-CAPI-1.13.4"
PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.4, released 2020/01/08"
LIBXML="2.9.1" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)"
RASTER (raster lib from "2.5.5 r0" need upgrade) [UNPACKAGED!] (raster procs
from "2.5.2 r17328" need upgrade)
(1 row)
No luck.
->jvp
On Mon, Jan 23, 2023 at 5:30 PM Roxanne Reid-Bennett
<r...@tara-lu.com<mailto:r...@tara-lu.com>> wrote:
Jim,
We did a series of updates from 10.17 through to 13+ with Postgis some time
back on AWS. I just pulled up the notes from the dry run
SELECT PostGIS_Extensions_Upgrade();
does apply to "all the pieces and parts". It wasn't uncommon for it to say
Raster (Topology, etc) wasn't available, especially when it wasn't installed.
There was one time where running
SELECT PostGIS_Extensions_Upgrade();
followed by
select postgis_full_version();
indicated we had to run the Extensions Upgrade AGAIN.
Based on your research, it does sound like something may have been deleted "not
using" the AWS/Postgis packaged tools. I don't know the internals of PostGis
enough to even guess.
Can you maybe create a new v 12 database with Postgis and Rastor installed..
and look in the extension there to see what might be missing in your main DB?
Roxanne
On 1/23/2023 4:03 PM, Raj Talati wrote:
You tried to do upgrade the extension it might be case that the current old
extension was not upgraded . Alter extension PostGis update and then you can
retry upgrade.
I guess whoever did PG 12 upgrade not did alter extension Postgis update and
when now you trying that missed prior is giving error.
Most likely this the case.
Good luck
On Mon, Jan 23, 2023 at 6:15 PM Jim VanPeursem
<j...@jvp.llc><mailto:j...@jvp.llc> wrote:
Greetings,
[originally posted on pgsql-admin, but referred here]
I recently took over the management of a postgresql + postgis db on aws rds.
Given the age of this project, the db itself is probably ~7-8 years old. It is
currently on v12.12 and I'm unable to upgrade it to 13+. The db does use
postgis, but as far as I can tell, no raster or topology or other
postgis-related fields/features.
When I try to upgrade on aws, I get the following error:
The instance could not be upgraded because there are one or more databases with
an older version of PostGIS extension or its dependent extensions
(address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder,
postgis_topology, postgis_raster) installed.
Please upgrade all installations of PostGIS and drop its dependent extensions
and try again.
SELECT postgis_full_version(); gives the following (reformatted for clarity):
POSTGIS="3.1.7 aafe1ff" [EXTENSION]
PGSQL="120"
GEOS="3.8.2-CAPI-1.13.4"
PROJ="Rel. 5.2.0, September 15th, 2018"
GDAL="GDAL 2.4.4, released 2020/01/08"
LIBXML="2.9.1"
LIBJSON="0.13.1"
LIBPROTOBUF="1.3.2"
WAGYU="0.5.0 (Internal)"
RASTER (raster lib from "2.5.5 r0" need upgrade) [UNPACKAGED!] (raster
procs from "2.5.2 r17328" need upgrade)
Note that it lists RASTER both as being unpackaged and needing an upgrade, even
though postgis_raster is apparently not installed. My thinking is that
somewhere along the way, postgis_raster and possibly topology were installed
and later uninstalled (perhaps after being unbundled?).
For more clues, I issued the following command. For clarity I replace the
account numbers with pseudo-usernames for clarity. Also note that schema_1 and
schema_2 are two schemas that the project uses.
db=> select a.extname, a.extowner, a.extnamespace, a.extversion, b.nspname,
b.nspowner from pg_catalog.pg_extension a, pg_namespace b where a.extname LIKE
'%postgis%';
extname | extowner | extnamespace | extversion | nspname |
nspowner
---------+------------+--------------+------------+--------------------+----------
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_1 |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_1 |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_catalog |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | information_schema |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | extensions |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | schema_1 |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | my_new_topo |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | tiger |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | tiger_data |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | topology |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | schema_2 |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | public |
<local_admin>
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_4 |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_4 |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_5 |
<rdsadmin>
postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_5 |
<rdsadmin>
I'm not familiar enough with postgresql nor postgis to understand whether the
nspname entries for tiger, topology, etc. are expected, or offer clues as to
the problem that I am encountering.
Some things that I've tried:
db=> SELECT postgis_extensions_upgrade();
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
Also:
db=> select * from pg_available_extensions where name like 'postgis%';
name | default_version | installed_version |
comment
------------------------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.1.7 | 3.1.7 | PostGIS
geometry and geography spatial types and functions
postgis_tiger_geocoder | 3.1.7 | | PostGIS tiger
geocoder and reverse geocoder
postgis_topology | 3.1.7 | | PostGIS
topology spatial types and functions
postgis_raster | 3.1.7 | | PostGIS raster
types and functions
(4 rows)
And:
db=> \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+---------------------------------------------------------------------
fuzzystrmatch | 1.1 | extensions | determine similarities and distance
between strings
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.1.7 | extensions | PostGIS geometry, geography, and raster
spatial types and functions
sslinfo | 1.2 | public | information about SSL certificates
(4 rows)
And:
db=> CREATE EXTENSION postgis_raster;
ERROR: PostGIS Raster is already installed in schema 'extensions'
CONTEXT: PL/pgSQL function inline_code_block line 10 at RAISE
And:
db=> DROP EXTENSION postgis_raster;
ERROR: extension "postgis_raster" does not exist
I also did a snapshot backup and restored to a new instance on aws, and this
resulted in exactly the same problem on the new instance.
Erik Weinhold from the pgsql-admin mailing list helpfully pointed out the
following:
Could be that someone "uninstalled" them by deleting from pg_extension.
Deleting from pg_extension reproduces the error:
test=# create extension postgis_raster;
CREATE EXTENSION
test=# delete from pg_extension where extname = 'postgis_raster';
DELETE 1
test=# drop extension postgis_raster;
ERROR: extension "postgis_raster" does not exist
test=# create extension postgis_raster;
ERROR: PostGIS Raster is already installed in schema 'public'
CONTEXT: PL/pgSQL function inline_code_block line 10 at RAISE
That last error message does not come from checking pg_extension but rather
postgis_raster itself[2].
I tried DROP SCHEMA extensions CASCADE at this point to get rid of the
remaining objects but that fails:
test=# drop schema extensions cascade;
ERROR: cache lookup failed for extension 27232
Is there a way to either restore or remove the unbundled / partial raster
support so that it can be upgraded? Does anyone have other suggestions on what
I could try? I'd like to get to postgresql v13+ with only postgis (no raster,
topology, etc.) installed without losing any data along the journey. Is my only
recourse to do a full data backup to sql followed by creating a new instance
and restoring data?
Thanks,
->jvp
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org<mailto:postgis-users@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
CONFIDENTIALITY NOTICE: This electronic message contains information from
Bluestaq LLC, which may be company sensitive, proprietary, privileged, or
otherwise protected from disclosure. The information is intended to be used
solely by the recipient(s) named above. If you are not an intended recipient,
be aware that any review, disclosure, copying, distribution, or use of this
transmission or its contents is prohibited. Please notify the sender
immediately if you have received this transmission in error.
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
