I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10. I'm using CentOS 7.X
Using pg_upgrade it should be quite straightforward, but the problem is PostGIS. Postgres 9.3 has PostGIS 2.1 available, whereas Postgres 10 has PostGis 2.4.4. So I compiled PostGIS 2.4 for Postgres 9.3 and installed it. Then for each database, I've made:
ALTER EXTENSION postgis UPDATE;
In this way, I've migrated to PostGIS 2.4.
But when I run
pg_upgrade, I receive this error:
Cannot access the file «$libdir/postgis-2.1»: It doesn't exist the file or the directory.
If I do this:
cd /usr/pgsql-10/lib/ ln -s postgis-2.4.so postgis-2.1.so
It doesn't work either, because if I run
pg_ugrade another time:
pg_restore: creating FUNCTION "public.geomfromewkb("bytea")" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 547; 1255 90291 FUNCTION geomfromewkb("bytea") ema pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "LWGEOMFromWKB" in file "/usr/pgsql-10/lib/postgis-2.1.so"<br> Command was: CREATE FUNCTION "public"."geomfromewkb"("bytea") RETURNS "public"."geometry" LANGUAGE "c" IMMUTABLE STRICT AS '$libdir/postgis-2.1', 'LWGEOMFromWKB';
Why does Postgres 9.3 reference PostGIS 2.1 after migrating to 2.4?
The other option I see is to compile PosGIS 2.1 for Postgres 10.
After reading this tip, I've done for each database I've done:
psql mydb -c 'ALTER EXTENSION postgis UPDATE;'
psql mydb -c 'ALTER EXTENSION postgis_topology UPDATE;'
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.1/uninstall_legacy.sql
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.4/legacy.sql
with identical results.
SELECT probin, COUNT(*)
WHERE probin LIKE '%/postgis%'
GROUP BY probin
ORDER BY probin;
probin | count ---------------------+------- $libdir/postgis-2.1 | 373
This guide explains the internals of PostGIS migration.
Clearly, PostGIS tells it migrates the extension, but doesn't apply the migration script to change the procedures.