[postgis-devel] Upgrade PostGIS from 2.5.4 to 3.1.2

Regina Obe lr at pcorp.us
Wed Jan 26 09:32:16 PST 2022 

One more comment.  Regardless if I am on windows or Linux or whether or not I have enough disk space to support two of my data clusters, my final pg_upgrade is always done using link mode.

The reason is link mode is the fastest.  Even for a huge cluster, I have less than 5 minutes downtime since it just copies over the schemas and links the data.
Doing copy depends on how fast your disks are and how large your data.

In either case I don't bother with pg_dump / pg_restore anymore unless I've only got one fairly small database (custom backup is less than 10 GB), it's pre 2.0 (which thankfully I have none of those left).
Usually even for small installs I'm running 10 or more databases in a cluster, so pg_dump/pg_restore is way too painful.

To strk's point, yah you can use docker.  I just haven't warmed up to docker, though I know clients who happily use it and have upgraded databases using it.


> -----Original Message-----
> From: Regina Obe [mailto:lr at pcorp.us]
> Sent: Wednesday, January 26, 2022 12:16 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
> 
> 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

More information about the postgis-devel mailing list