Hi, recently updated one of my servers to 7.3, and in doing so updated from psql 14 to 15. Went for the pg_upgrade path which requires postgresql-previous, all looked fine.. until it tried dumping one of my DBs which uses the postgis extension.
the general idea was that it failed accessing $libdir/postgis-3.so in the *source* database, which pg_upgrade starts using bin/postgresql-14 binaries. Ofc, pkg_add having run, postgis-3.so only exists in /usr/local/lib/postgresql/, not in /usr/local/lib/postgresql-14/. i quickly figured out that i needed two postgis installs for pg_upgrade to succeed, one for the version of the source database, one for the version of the target database. One cant simply copy them from lib/postgresql as they can only work for a matching psql db. For postgis i ended up building locally with the below diff, and manually copied the .so from fake to /usr/local/lib/postgresql-14/, that helped pg_upgrade succeed, and now all is fine. with that, postgis binaries end up in bin/postgresql-14, libs in lib/postgresql-14 and sql bits in share/postgresql-14, only manpages/docs conflict. Since things depend on postgis, i dont want to have a 'linked to the build' flavor. Or maybe only a flavor installing the minimum amount, eg only libs ? should we build (flavors?) extensions with both 'regular' and 'previous' versions of psql ? i suppose the same issue might also be present in other postgresql extensions ? What do postgresql users do when upgrading databases with extensions ? Use the manual dump/restore way ? Landry RCS file: /cvs/ports/geo/postgis/Makefile,v retrieving revision 1.79 diff -u -r1.79 Makefile --- Makefile 14 Nov 2022 07:03:36 -0000 1.79 +++ Makefile 18 Apr 2023 06:55:16 -0000 @@ -40,10 +40,11 @@ USE_LIBTOOL = Yes #for iconv.h and https://trac.osgeo.org/postgis/ticket/4573 CONFIGURE_ENV= LDFLAGS=-L${LOCALBASE}/lib \ + PG_CONFIG=/usr/local/bin/postgresql-14/pg_config \ CPPFLAGS=-I${LOCALBASE}/include CONFIGURE_STYLE=gnu
