[postgis-devel] Upgrade PostGIS from 2.5.4 to 3.1.2

Wed Jan 26 09:16:24 PST 2022

It's not that much easier in Windows when you need to guarantee no downtime or very little downtime and you have a big database or many databases.  It's the same issue.

Both apt.postgresql.org and yum.postgresql.org  support multiple postgresql versions running. Just like windows, they expect all PostgreSQL 11 to share the same installs, all PostgreSQL 9.6 to share same installs. Sure on windows, you could tell it to install elsewhere, but that causes other complications.  You can have as many clusters as you want running on both Windows and Linux.  I haven't had any need to run more the one version on BSDs  so haven't explored what is possible there in packaging.

In fact apt has this cool commandline which I think all systems should have

pg_lsclusters

That lists all your PostgreSQL clusters and the data path for each, the port, and if they are running or not.

The issue here is because packaging won't allow you to overwrite some extension files, which is more precautionary than anything which is why I think packagers have come up with workarounds like the postgis-3.1.control etc.

For what it's worth, on Linux on both Apt and Yum systems (I'm talking about apt.postgresql.org, yum.postgresql.org -- I think primary repos are much more restrictive), I do the upgrades all at once.

So for the last Yum upgrade I did, I was going from PostgreSQL 9.6/PostGIS 2.4  to PostgreSQL 11/3.1

(why not 13 or 14, cause I got some fairly old code with old libraries running here so didn't want to risk it).

Then what I do is install a fresh PostgreSQL 11 with just 3.1 on it.  Do the symlink dance, which thankfully is not required anymore after everything is on PostGIS 3+

Create a replica of PostgreSQL 9.6 running, I promote the replica and upgrade it to PG 11/ 3.1
Test it out for a bit (e.g. I have the original PostgreSQL 9.6/2.4 running), I have the new PG 11/3.1 (with same data running)

When I feel comfortable, I tear down the old replica, rereplicate, / reupgrade it and then do the port switch.

In cases where I am short on disk space and don't have this luxury, I just do the upgrade (using pg_upgrade --link mode) and hope for the best.

Hope that helps,
Regina

> -----Original Message-----
> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
> Behalf Of Paul Ramsey
> Sent: Tuesday, January 25, 2022 2:04 PM
> To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> Subject: Re: [postgis-devel] Upgrade PostGIS from 2.5.4 to 3.1.2
> 
> 
> 
> > On Jan 25, 2022, at 10:58 AM, Bruce Rindahl <bruce.rindahl at gmail.com>
> wrote:
> >
> > Strange how this is so much easier on windows.  When I want to upgrade I
> simply install the new version of PG and PostGIS right alongside the existing
> versions. The EDB installer puts all the executables in a different directory,
> asks me where I want the data directory, and suggests an available port.  I
> then do a pg_dump of whatever database I want, pipe it to psql and specify
> the new port number.  Then I spend a while testing to see if everything
> works.  If it's good I simply shut down each instance, swap the port numbers
> in postgresql.conf and fire them up again.  Then I am on the new version.
> Not sure if this is easily done in linux.
> 
> Depends a lot on where you're getting your build from. If you're using PGDG
> packages, they tend to be set up to allow simultaneous installations of
> PostgreSQL, so setting up the new version in parallel and piping things over
> should work fine. The system level packages tend to assume "one true
> install" though, so adding a new postgresql wipes out the old one, which
> seems... less than super.
> 
> P
> 
> 
> > Bruce
> >
> > On Tue, Jan 25, 2022 at 10:44 AM Regina Obe <lr at pcorp.us> wrote:
> > Uninstalling will cause downtime if any postgis functions are in active use
> at the time of uninstall.
> >
> >
> >
> > However if you install and symlink fast enough, you shouldn�t have much
> downtime, if any at all. Probably under 10 minutes.
> >
> >
> >
> >
> >
> > From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
> Behalf Of Nikhil Shetty
> > Sent: Monday, January 24, 2022 2:17 PM
> > To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> > Cc: postgis-users at lists.osgeo.org
> > Subject: Re: [postgis-devel] Upgrade PostGIS from 2.5.4 to 3.1.2
> >
> >
> >
> > Hi Regina,
> >
> >
> >
> > Thank you for sharing the steps.Yes, we are upgrading the database as well
> from 11.7 to 13.4.
> >
> >
> >
> > I saw the steps you shared and I will give it a try.
> >
> >
> >
> > Does uninstalling postgis25_11 have any impact on a running Postgres
> database that is using postgis25_11 functions?? Wanted to know which step
> will cause a downtime to the application apart from the database upgrade
> step.
> >
> >
> >
> > Thanks and Regards,
> >
> > Nikhil
> >
> >
> >
> >
> >
> > On Tue, 25 Jan 2022 at 12:36 AM, Regina Obe <lr at pcorp.us> wrote:
> >
> > I think I ran into this issue too though I was doing a pg_upgrade from 9.6 to
> 11 at the same time.
> >
> >
> >
> > The issue is since both ship and overlapping set of files, they can�t cleanly
> coexist.
> >
> >
> >
> > The below should work.
> >
> >
> >
> > Uninstall postgis25_11 package
> >
> >
> >
> > Install the postgis31_11 package
> >
> >
> >
> > Symlink the old names to the new libraries
> >
> >
> >
> > ln -s /usr/pgsql-11/lib/postgis-3.so /usr/pgsql-11/lib/postgis-2.5.so
> >
> > ln -s /usr/pgsql-11/lib/postgis_raster-3.so  /usr/pgsql-11/lib/rtpostgis-
> 2.5.so
> >
> >
> >
> > #if you are not using these libraries, you can skip this step
> >
> > ln -s /usr/pgsql-11/lib/postgis_topology-3.so /usr/pgsql-
> 11/lib/postgis_topology-2.5.so
> >
> >
> >
> > ln -s /usr/pgsql-11/lib/address_standardizer-3.so /usr/pgsql-
> 11/lib/address_standardizer-2.5.so
> >
> > ln -s /usr/pgsql-11/lib/address_standardizer-3.so /usr/pgsql-
> 11/lib/address_standardizer.so
> >
> >
> >
> >
> >
> > #On your databases
> >
> >
> >
> > SELECT postgis_full_version();
> >
> >
> >
> > Should show something to the effect you have 2.5 scripts but are running
> postgis-3
> >
> >
> >
> > ALTER EXTENSION postgis UPDATE;
> >
> > SELECT postgis_extensions_upgrade();
> >
> > SELECT postgis_extensions_update();
> >
> >
> >
> > -- if not using raster, you can do this
> >
> > DROP EXTENSION postgis_raster;
> >
> >
> >
> >
> >
> > #after you are done upgrading your dbs, you can drop the symlinks e.g.
> >
> > rm /usr/pgsql-11/lib/rtpostgis-2.5.so
> >
> > rm /usr/pgsql-11/lib/postgis-2.5.so
> >
> > rm /usr/pgsql-11/lib/postgis_topology-2.5.so
> >
> >
> >
> > From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
> Behalf Of Nikhil Shetty
> > Sent: Monday, January 24, 2022 1:48 PM
> > To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>;
> postgis-users at lists.osgeo.org
> > Subject: [postgis-devel] Upgrade PostGIS from 2.5.4 to 3.1.2
> >
> >
> >
> > Hi Team,
> >
> >
> >
> > I am looking for a way to upgrade postgis on postgresql 11 from 2.5.4 to
> 3.1.2
> >
> > I am getting below error when doing though yum
> >
> >
> >
> > Transaction check error:
> >   file /usr/pgsql-11/share/extension/address_standardizer.control from
> install of postgis31_11-3.1.2-1.rhel7.x86_64 conflicts with file from package
> postgis25_11-2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/share/extension/address_standardizer.sql from install
> of postgis31_11-3.1.2-1.rhel7.x86_64 conflicts with file from package
> postgis25_11-2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/share/extension/address_standardizer_data_us.control
> from install of postgis31_11-3.1.2-1.rhel7.x86_64 conflicts with file from
> package postgis25_11-2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/share/extension/postgis.control from install of
> postgis31_11-3.1.2-1.rhel7.x86_64 conflicts with file from package
> postgis25_11-2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/share/extension/postgis_sfcgal.control from install of
> postgis31_11-3.1.2-1.rhel7.x86_64 conflicts with file from package
> postgis25_11-2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/share/extension/postgis_tiger_geocoder.control from
> install of postgis31_11-3.1.2-1.rhel7.x86_64 conflicts with file from package
> postgis25_11-2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/share/extension/postgis_topology.control from install
> of postgis31_11-3.1.2-1.rhel7.x86_64 conflicts with file from package
> postgis25_11-2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/bin/pgsql2shp from install of postgis31_11-client-3.1.2-
> 1.rhel7.x86_64 conflicts with file from package postgis25_11-client-2.5.4-
> 1.rhel7.x86_64
> >   file /usr/pgsql-11/bin/raster2pgsql from install of postgis31_11-client-
> 3.1.2-1.rhel7.x86_64 conflicts with file from package postgis25_11-client-
> 2.5.4-1.rhel7.x86_64
> >   file /usr/pgsql-11/bin/shp2pgsql from install of postgis31_11-client-3.1.2-
> 1.rhel7.x86_64 conflicts with file from package postgis25_11-client-2.5.4-
> 1.rhel7.x86_64
> >
> >
> >
> > I have seen the document for 'Hard Upgrade' but that is unfortunately a
> long route to take for critical databases.
> >
> >
> >
> > Postgresql Version - 11.7
> >
> > OS - RHEL 7.9
> >
> >
> >
> > Full Version Details -
> >
> > PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-39), 64-bit POSTGIS="2.5.4" [EXTENSION]
> PGSQL="110" GEOS="3.8.1-CAPI-1.13.3" PROJ="Rel. 7.0.0, March 1st, 2020"
> GDA
> >
> > L="GDAL 3.0.4, released 2020/01/28" LIBXML="2.9.1" LIBJSON="0.11"
> LIBPROTOBUF="1.0.2" RASTER
> >
> > Thanks and Regards,
> >
> > Nikhil
> >
> >
> >
> > _______________________________________________
> > 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
> > _______________________________________________
> > 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