It's my understanding that right now when you run pg_upgrade it creates the
extension from what exists in the to be upgraded databases.
Is there a reason why we can't have some sort of switch option that allows
the CREATE EXTENSION from the scripts instead of what is loaded in the db.
The reason I'm asking is for a couple of reasons.
1) For projects that version their libraries, sometimes the old versioned
library does not exist in the new install and not available for the new
install.
PostGIS project largely satisfied this issue by major versioning our
extension, but of cause this would still be an issue for folks going from
PostGIS 2.5 to PostGIS 3+.
2) Yes I know this is a no-no but the postgis spatial_ref_sys table is
assumed to be an essentially static table, and people build table
constraints using ST_Transform.
Also as we observed, for some reason the postgis.spatial_ref_sys table is
sometimes not created before user data is loaded or is empty and loaded
after user data.
As discussed here - https://trac.osgeo.org/postgis/ticket/5899
3) People often forget to run upgrade extension scripts after doing a
pg_upgrade so are left in an incomplete upgrade state after upgrade.
Having the extension be the latest version would largely solve this.
There are other issues with run into in the past where a users old extension
can not be upgraded cleanly, we've had this recently with BRIN.
Note I'm not asking to change the default behavior, just wondering if there
are obstacles to giving users an option to install from the scripts instead
of what's currently in the database.
Thanks,
Regina
