[postgis-users] Postgis 3.0.3 extension for AWS RDS Postgres 13.3

417 views
Skip to first unread message

Allan Chase

unread,
Feb 14, 2022, 3:21:36 PM2/14/22
to PostGIS Users Discussion, PostGIS Development Discussion
Queston:

If there isn't a Postgis upgrade path when upgrading Postgres 11.12 (2.5.2) to 13.3 (3.0.3), can we just drop the extension, do the upgrade, then re-create the extension in the new database? I get an error message saying that it can't upgrade due to "no upgrade path" for 2.5.2 to 3.0.3.

Allan Chase
Data Engineer
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Regina Obe

unread,
Feb 14, 2022, 3:33:39 PM2/14/22
to PostGIS Users Discussion, PostGIS Development Discussion
No don't do that, you'll destroy data.

Are you able to hold off? I think Amazon should be releasing an update
soon.

If you can't wait, the workaround is below, but not sure it will work on AWS
RDS as updating system tables might be off limits.

UPDATE pg_extension SET extversion = 'ANY'
WHERE extname = 'postgis';


ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();

Sandro Santilli

unread,
Feb 14, 2022, 5:38:41 PM2/14/22
to PostGIS Users Discussion
On Mon, Feb 14, 2022 at 03:33:29PM -0500, Regina Obe wrote:

> the workaround is below, but not sure it will work on AWS
> RDS as updating system tables might be off limits.
>
> UPDATE pg_extension SET extversion = 'ANY'
> WHERE extname = 'postgis';

Allan: please let us know if AWS RDS allows or not the above
UPDATE, as we are planning to use that construct for
the postgis_extensions_upgrade() function in PostGIS-3.3.0

--strk;

Raj Talati

unread,
Feb 14, 2022, 6:07:47 PM2/14/22
to Allan Chase, PostGIS Users Discussion
If this AWS RDS you cannot upgrade directly from 11.x to 13.x . You will need to upgrade to 12.x and if you did like upgraded 11.x to 12.x and the. Upgraded fro 12.x to 13.3 then when you upgraded from 11.x to 12.x you need to upgrade Postgis too as AWS as that should allow you to upgrade postgis and then when you upgrade Postgres to 13 you should able to upgrade Postgis too. If we bypass Postgis upgrade while going from 11-> 12 and just so while doing 12->13 you will face this issue .

Hope this helps.

Allan Chase

unread,
Feb 14, 2022, 6:26:29 PM2/14/22
to Raj Talati, PostGIS Users Discussion

According to Upgrading the PostgreSQL DB engine for Amazon RDS - Amazon Relational Database Service, one can upgrade from 11.12 right to 13.3.

 

Allan Chase

Data Engineer

 

From: Raj Talati <rajt...@gmail.com>
Sent: Monday, February 14, 2022 4:08 PM
To: Allan Chase <allan...@bluestaq.com>; PostGIS Users Discussion <postgi...@lists.osgeo.org>
Subject: [External] Re: [postgis-users] Postgis 3.0.3 extension for AWS RDS Postgres 13.3

 

**************************************************************************
ATTENTION: External email
Please verify the sender before opening attachments or clicking links.
**************************************************************************

Raj Talati

unread,
Feb 14, 2022, 7:21:50 PM2/14/22
to Allan Chase, PostGIS Users Discussion
Allan,

So that might be issue if you jump from 11 to 13 in between Postgis is missing you can open AWS case and confirm. 

Fix is you upgrade to 12 do Postgis upgrade and then do PG 13 upgrade and Postgis Upgrade again . Issue is how they maintain all Postgis version prior they not allow skip in between version I mostly use Aurora so if they allowing this on RDS might have started recently, it will be good if they control Postgis upgrade or have some fix of issue you facing.

If you in urgency do upgrade the steps I mentioned should work. Upgrade to 12 and do Postgis Upgrade (you can bypass analyze step) and start upgrade to 13 and do Postgis upgrade and do post upgrade steps analyze etc … issue is Postgis 2.52 need to upgrade to Postgis 3.0  and then you can upgrade to Postgis 3.2 there is not direct jump from Postgis 2.5 to Postgis 3.2 hopefully they can handle if they allowing PG upgrades from11 to 13 directly they should handle this too.

Thanks 

Raj

Regina Obe

unread,
Feb 14, 2022, 8:46:20 PM2/14/22
to PostGIS Users Discussion, Allan Chase, Sandro Santilli

strk,

AWS doesn’t allow the below. But I think it should work as part of the ALTER EXTENSION as you have it in 3.3 as that runs under a higher level account.

 

Allan,

 

Can you output what your

 

SELECT * FROM pg_extension WHERE extname =’postgis’;

 

Shows and confirm it does say 2.5.2.

 

If it says something like 2.5.2next then do:

 

ALTER EXTENSION postgis UPDATE TO '2.5.2';

 

Also run:

 

SELECT postgis_full_version();

 

At any rate, Raj might be right that upgrading from 11 to 13 for PostGIS is not supported on AWS.  Most people I’ve known on AWS first upgrade to 12 first.

 

Thanks,

Regina

 

 

 

 

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Allan Chase

Regina Obe

unread,
Feb 14, 2022, 9:10:01 PM2/14/22
to PostGIS Users Discussion, Allan Chase

Allan,

 

Looking at this chart, it looks like you can upgrade from PostGIS 2.5 to 3.1 on PostgreSQL 11 right?

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.11x

Says PostGIS 3.1 is available for 11.14.

 

That would be a safer bet to upgrade your PostgreSQL 11 first to 3.1.

PostgreSQL 13 removed CREATE EXTENSION from unpackaged, which I think the 3.0 release relies on, so that would be one reason why you can’t go from 2.5.2 to 3.0.3.

 

I think we introduced the work-around for that in PostGIS 3.1.

 

So options that theoretically should work

 

Go from your PG 11-  PostGIS 2.5.2 to PostGIS 3.1.4

Then upgrade from

PG 11 PostGIS 3.1.4 -> PG 13 PostGIS 3.1.4

 

Or if you really badly want to go straight to 13

 

PG 11 2.5.2 -> PG 13.5 (which does have PostGIS 3.1.4)

 

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.13x

 

 

Hope that helps,

Regina

 

 

 

From: Regina Obe [mailto:l...@pcorp.us]
Sent: Monday, February 14, 2022 8:46 PM
To: 'PostGIS Users Discussion' <postgi...@lists.osgeo.org>; 'Allan Chase' <allan...@bluestaq.com>
Cc: 'Sandro Santilli' <st...@kbt.io>
Subject: RE: [postgis-users] [External] Re: Postgis 3.0.3 extension for AWS RDS Postgres 13.3

 

strk,

AWS doesn’t allow the below. But I think it should work as part of the ALTER EXTENSION as you have it in 3.3 as that runs under a higher level account.

 

Allan,

 

Can you output what your

 

SELECT * FROM pg_extension WHERE extname =’postgis’;

 

Shows and confirm it does say 2.5.2.

 

If it says something like 2.5.2next then do:

 

ALTER EXTENSION postgis UPDATE TO '2.5.2';

 

Also run:

 

SELECT postgis_full_version();

 

At any rate, Raj might be right that upgrading from 11 to 13 for PostGIS is not supported on AWS.  Most people I’ve known on AWS first upgrade to 12 first.

 

Thanks,

Regina

 

 

 

 

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Allan Chase
Sent: Monday, February 14, 2022 6:26 PM

Allan Chase

unread,
Feb 14, 2022, 10:52:09 PM2/14/22
to Regina Obe, PostGIS Users Discussion

Thanks Regina,

 

Yeah, I saw that link that you referenced (a few days back) and I kind of went…hmmmm.  From that chart, you can upgrade to 3.1.X if you just go from 11.12 to 11.13 (or 14), but the thing that caught me a little off guard was:

 

PostgreSQL on Amazon RDS - Amazon Relational Database Service

 

I will definitely try your suggestions below.  All this is technically for a system that currently only goes as high as 13.3, so I’m currently stuck with 3.0.3 (this is all on “Gov Cloud”).

 

The main driver for me to want to jump right to 13 was to minimize our down time since it is a production system.  If the best answer is to jump from 11 to 12 to 13, I’ll just need to make sure I quantify the downtime (probably 45 minutes – hour) which is fine.

 

Allan Chase

Data Engineer

 

From: Regina Obe <l...@pcorp.us>
Sent: Monday, February 14, 2022 7:10 PM
To: 'PostGIS Users Discussion' <postgi...@lists.osgeo.org>; Allan Chase <allan...@bluestaq.com>
Subject: [External] RE: [postgis-users] [External] Re: Postgis 3.0.3 extension for AWS RDS Postgres 13.3

 

**************************************************************************
ATTENTION: External email
Please verify the sender before taking any actions or clicking any links.
**************************************************************************

Regina Obe

unread,
Feb 15, 2022, 1:24:14 PM2/15/22
to Allan Chase, PostGIS Users Discussion

So I’m curious is 11.14 not supported on GovCloud.

 

Doing micro updates should not require any downtime or very miniscule downtime because the data doesn’t need to be fixed to go up to 11.14.

So it really should be just flipping the switch.

 

So 11.14 is really where you want to be for security /bugfix patches anyway.

Raj Talati

unread,
Feb 15, 2022, 2:22:50 PM2/15/22
to PostGIS Users Discussion
Yes that will be better option if you can upgrade to 11.14 it’s available on RDS not sure for Gov cloud and it’s not available for Aurora too.
Minor upgrade will take some time to but not long as major upgrade as I think minor upgrade they bring up new binaries and stuff still much faster and better option to not go through 2 major version upgrade and hopefully with Postgis upgraded to 3.01 you should able to go direct from PG11 to PG13 .

Raj 
Reply all
Reply to author
Forward
0 new messages