Re: Sebastiaan Couwenberg > Options for a working postgis database after distribution upgrade > include recreating the databases by running your ETL process on the new > cluster after upgrade, or using symlink hacks to workaround the > version-in-extension-filename issue: > > http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html
This is the infamous "symlink" hack that was occasionally needed in the past, but I don't think it is necessary for this issue sind we are talking about proper "CREATE EXTENSION postgis" installed, not the old wild-west style with 1000 free-floating functions create in the database. > The hard upgrade procedure from the upstream docs may be an option too: > > http://postgis.net/docs/manual-3.1/postgis_administration.html#upgrading > > In my experience, recreating the database is the simplest solution. Before I answer this, I gave upgrading buster with postgresql-11-postgis-2.5{,-scripts} to sid with postgresql-13-postgis-3 a try. As expected, libgdal20 and postgresql-11-postgis-2.5 were removed during the process, and trying to access geometry data in the old postgresql-11 database fails: # select geom from country where geom is not null limit 1; FEHLER: 58P01: konnte nicht auf Datei »$libdir/postgis-2.5« zugreifen: Datei oder Verzeichnis nicht gefunden To see what would happen if we made libgdal20 and libgdal28 co-installable, I force-installed the old packages: $ sudo dpkg -i --force-depends /var/cache/apt/archives/libgdal20_2.4.0+dfsg-1+b1_amd64.deb /var/cache/apt/archives/postgresql-11-postgis-2.5_2.5.1+dfsg-1_amd64.deb [...] Entpacken von postgresql-11-postgis-2.5 (2.5.1+dfsg-1) ... dpkg: Abhängigkeitsprobleme verhindern Konfiguration von libgdal20: gdal-data (3.2.2+dfsg-1) beschädigt libgdal20 (<< 2.5.0~) und ist installiert. Zu konfigurierende Version von libgdal20 auf dem System ist 2.4.0+dfsg-1+b1. dpkg: Fehler beim Bearbeiten des Paketes libgdal20 (--install): Abhängigkeitsprobleme - verbleibt unkonfiguriert dpkg: postgresql-11-postgis-2.5: Abhängigkeitsprobleme, wird aber trotzdem wie gefordert konfiguriert: postgresql-11-postgis-2.5 hängt ab von libgdal20 (>= 2.0.1); aber: Paket libgdal20 ist noch nicht konfiguriert. postgresql-11-postgis-2.5 (2.5.1+dfsg-1) wird eingerichtet ... Trigger für libc-bin (2.31-12) werden verarbeitet ... Fehler traten auf beim Bearbeiten von: libgdal20 Ignoring these errors I proceeded to try reading the old geometry data: $ psql cb psql (13.3 (Debian 13.3-1), Server 11.12 (Debian 11.12-0+deb10u1)) 17:38 cbe@cb =# select geom from country where geom is not null limit 1; FEHLER: XX000: konnte Bibliothek »/usr/lib/postgresql/11/lib/postgis-2.5.so« nicht laden: /lib/x86_64-linux-gnu/libm.so.6: version `GLIBC_2.29' not found (required by /usr/lib/x86_64-linux-gnu/libSFCGAL.so.1) So there seems to be some additional incompatibility in libsfcgal1 -> libc6. At that point, I think fixing that isn't feasible, and we should instead put proper upgrade instructions into the release notes. My plan would be the following: sudo -u postgres pg_dumpall -f postgres11.dump ... do the upgrade sudo apt install postgresql-13-postgis-3 #sudo pg_createcluster 13 main --start # automatically created by postgresql-13 sudo -u postgres psql -p 5433 -Xf postgres11.dump # select geom from country where geom is not null limit 1; geom ────────────── 0106000020E610000001000000010.... Would such instructions in the release notes be an acceptable resolution for this bug? We can additionally point to the "hard" upgrade instruction mentioned above for people still using the non-extension installation methode. > > If I am not mistaken, Andreas proposed in another thread to introduce a > > postgis-2.5-built-against-postgresql-13 package to help with the > > upgrades. Would this be a viable option? > > No. I'm not going to maintain multiple versions of postgis. postgis-2.5-built-against-postgresql-13 wouldn't help since we need to get the data out of the old postgresql-11 first. > It will be one less package I have to maintain in Debian, I can just > chuck in my personal repo and not bother any further. Please don't, you are doing useful work here. I appreciate your efforts. Christoph