PostGIS releases a new minor version of PostGIS every one or two years. Each minor version of postgis has a different libname suffix.
In PostGIS 2.1 you'll find files in your PostgreSQL lib folder called postgis-2.1.*, rtpostgis-2.1.*, postgis-topology-2.1.*, address-standardizer-2.1.* etc.
and in a PostGIS 2.2 you'll find similar files but with 2.2 in the name. I believe PostGIS and pgRouting are the only extensions that stamp the lib with a version number.
Most other extensions you will find are just called extension.so e.g. hstore is always called hstore.dll /hstore.so even if the version changed from 9.6 to 10.
On the bright side this allows people to have two versions of PostGIS installed in a PostgreSQL cluster, though a database can use at most one version.
So you can have an experimental database running a very new or unreleased version of PostGIS and a production database running a more battery tested version.
On the sad side this causes a lot of PostGIS users frustration trying to use pg_upgrade to upgrade from an older version of PostGIS/PostgreSQL to a newer version of PostGIS/PostgreSQL;
as their pg_upgrade often bails with a message in the loaded_libraries.txt log file something to the affect:
This is also a hassle because we generally don't support a newer version of PostgreSQL on older PostGIS installs because the PostgreSQL major version changes tend to break our code
often and backporting those changes is both time-consuming and dangerous. For example the DatumGetJsonb change
and this PostgreSQL 11 crasher we haven't isolated the cause of yet. There are several changes like this that have already made the PostGIS 2.4.0
we released recently incompatible with the PostgreSQL 11 head development.
We've had some very heated arguments
about this of which others in PostGIS PSC don't want to do anything about the situation until PostGIS 3.0 which is probably years away.
That said, if you want to upgrade to PostgreSQL 10 / PostGIS 2.4 from an earlier PostGIS without having to install PostGIS 2.3 on your new PostgreSQL 10
or PostGIS 2.4 on your old PostgreSQL version, there is a way to do this that is much simpler than installing an older PostGIS in your newer PostgreSQL or newer PostGIS in your older PostgreSQL.
Although my examples are with core PostGIS extension, they should work just the same with address-standardizer, postgis_topology, and postgis_sfcgal extensions that often come packaged as part of postgis.
You don't need to bother doing this with postgis_tiger_geocoder extension since it doesn't contain any C-lib references, it's a pure sql/plpgsql extension.
This trick should work for pgRouting 2.4 and 2.5 as well. Going farther back because Vicky
is doing major cleanup and SQL api refactoring, you might be better off just DROP EXTENSION your pgrouting
in your older PostgreSQL, pg_upgrade and then install the newer pgrouting after upgrade. This works for pgrouting because pgrouting is not generally tied to any data like PostGIS is.
All types in pgrouting are only intermediary helpers and will be removed in pgRouting 3.0.
So unless you've made functions relying on really old signatures, you are safe to just drop and recreate pgrouting extension.
Step 1: Copy or Link your new lib to the old name
Say you want to upgrade from PostgreSQL 9.5 PostGIS 2.2 install. You just copy the postgis related file e.g. "postgis-2.4" in your /pg10/lib/ to postgis-2.2.
I'm saying copy instead of link because linking doesn't work on windows easily and Linux/Unix copy or link will do the same.
You can do this manually if you feel squeamish around commandlines, or you can do this:
On Ubuntu, if you got your install from apt.postgresql.org, your files are probably installed in /usr/lib/postgresql/<postgres version number here>/lib/
To be sure, you can run:
dpkg --listfiles postgresql-10
Once you've determined the location of postgresql lib installs, do this
cp /usr/lib/postgresql/10/lib/postgis-2.4.so /usr/lib/postgresql/10/lib/postgis-2.2.so
cp /usr/lib/postgresql/10/lib/rtpostgis-2.4.so /usr/lib/postgresql/10/lib/rtpostgis-2.2.so
If you are on Windows, and you have an EDB install, you'd do something like this, though Windows tries to protect you so copying might fail unless you launch cmd.exe as administrator
copy "C:\Program Files\PostgreSQL\10\lib\postgis-2.4.dll" "C:\Program Files\PostgreSQL\10\lib\postgis-2.2.dll"
copy "C:\Program Files\PostgreSQL\10\lib\rtpostgis-2.4.dll" "C:\Program Files\PostgreSQL\10\lib\rtpostgis-2.2.dll"
Big SQL Windows install would be, if you took the default paths
copy "C:\PostgreSQL\pg10\lib\postgresql\postgis-2.4.dll" "C:\PostgreSQL\pg10\lib\postgresql\postgis-2.3.dll"
copy "C:\PostgreSQL\pg10\lib\postgresql\rtpostgis-2.4.dll" "C:\PostgreSQL\pg10\lib\postgresql\rtpostgis-2.3.dll"
People often get confused by my instructions and thought I meant to say copy the postgis-2.2 from your old install to your new 10 install
. That would seem like the more logical thing to do.
Don't ever copy an extension lib from an older major PostgreSQL to a newer major PostgreSQL (for 9 series it's minor version, for 10 and on it's major version). NEVER.
A PostGIS compiled against say PostgreSQL 9.6 will not work with any other PostgreSQL Minor version or Major version. An extension compiled for PostgreSQL 9.5 will not work for PostgreSQL 9.6.
They may have the same name, but they are incompatible with each other.
That goes for every other PostgreSQL extension in existence. e.g a PL/v8 compiled against PostgreSQL 9.6 will not work against PostgreSQL 10.
It will never work.
So why does copying a PostgreSQL 10 compiled PostGIS 2.4 to a name PostGIS 2.3 or PostGIS 2.2 work?
Because between PostGIS 2.2-2.4, the 2.4 has all the functions that a PostGIS 2.2 or PostGIS 2.3 is looking for
plus some additional ones. PostGIS 2.1 I think we might have made a tiny break. I need to retest that out so may not work with 2.1 without dropping the offending function in your old cluster before upgrade.
When pg_upgrade tries to move over the data, it tries to install each function in the old extension and if the function that the SQL function is bound to doesn't exist in the new PostgreSQL lib
it will fail. However if you simply copied over your old PostGIS 2.2 lib to your new install, it will fail right away because it will recognize your PostGIS 2.2 was compiled against a lower version of PostgreSQL and will not be able to load the library at all.
Step 2: Upgrade your databases that have PostGIS right away
Once you have successfully migrated, do the following, do this for all the postgissy extensions you've got in each databaase
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_sfcgal UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_tiger_geocoder UPDATE;
Once you are done with your upgrade process, run the query
SELECT postgis_full_version();
And you should see an output something like:
POSTGIS="2.4.0 r15853" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER
Once you have confirmed you have ugpraded all your databases, you can then delete the postgis-2.2 and rtpostgis-2.2 that you created.
Coming in PostGIS 2.5
We have a new helper function postgis_extensions_upgrade you can call as follows:
SELECT postgis_extensions_upgrade();
Which will check which extensions that are part of postgis install you have installed in your database and will upgrade them to the latest (what's defined in the .control files).
That way if you have different postgis extensions installed in different database, you can just run without checking.
