[postgis-devel] Upgrade paths (again)

[Greg Troxel]

Sandro Santilli <strk at kbt.io> writes:

>> When I read
>>   https://postgis.net/docs/manual-3.2/postgis_administration.html#upgrading
>> I don't really understand
>>   - how that relates to this discussion
>
> This discussion is about how to implement the ability for users to
> upgrade their EXTENSION-based PostGIS code in their databases.
> We're talking about the procedure documented as "soft" upgrade (ie:
> not requiring dump/restore), that is:
>
>   https://postgis.net/docs/manual-3.2/postgis_administration.html#soft_upgrade_extensions

It would be great to add to the manual
 - that the previous section (non-extension) is basically obsolete
 - the command to run to tell you which mode you are in
 - how to move from old-way to extension

>>   - what packaging system should do, if anything, when
>>     * replacing postgis 3.1.x with 3.2.x
>>     * changing from pgsql 12 with postgis 3.2 to pgsql 13 with postgis 3.2
>>     * changing from pgsql 12 with postgis 3.2 to pgsql 13 with postgis 3.3
>
> This is a great question. So far getting rid of everything installed
> by old package and installing the new package seems to work, but
> requires the new package to install a file for each older postgis
> version from which a user is allowed to upgrade from. This currently
> means about 400 files if I'm not mistaken, you probably have a better
> idea as I assume you have a manifest of what gets installed ?

That's half the question.  331 files.  83 each for base, raster,
topology, and 82 for tiger_geocoder.

The other half is whether the package upgrade itself should do anything
to databases, but I am convinced that it can't because postgresql may
not be running.

> What I proposed above was NOT installing any of those files but
> provide an administration command that would install only the ones
> which could be needed (not easy to tell because it depends on what
> version of PostGIS extension is found installed in any database served
> by a any cluster using a specific PostgreSQL install directory).

So that command would have to be run by root, which can write the
extension dir, and root would have to be a pgsql superuser so it can
query the db, and that would have to happen while the db is running, and
after that, somebody could upgrade?

>>   - what a package manager should do if pgsql is not running when the
>>     above upgrades happen  (pkgsrc typically doesn't do a lot of this,
>>     and leaves the user to dump/restore across postgresql version
>>     changes, i.e. dump, nuke db dir, upgrade, restore.
>
> With my proposal it's all to be defined and your input is welcome.
> With current situation there's no need for pgsql to be running during
> system upgrades.

Yes, but that doesn't upgrade the dbs.  I think it just about has to be
that way.

>>   - how many people have postgis installed "without extensions" vs
>>     "with"
>
> We don't spy users so can't really tell

good answer!

> but I suspect 99.9% of users are using postgis "as an extension".

The upgrade docs don't make one think that.

>>   - how to change from "without" to "with"
>
> Calling `postgis_extensions_upgrade()' in a database changes from "without"
> to "with". This is a per-database setup.
>
> Going from "with" to "without" is a bit harder. There's a perl script
> in the repository to create an "unpackager" SQL script, that we use
> to unpackage "postgis_raster" when upgrading form PostGIS version <
> 3.0.0:
>
>   utils/create_extension_unpackage.pl

There is then the question about why, but I don't really care about
postgis < 3 any more.

>>   - what to type to find out which you have
>
> The postgis_full_version() output will contain an [EXTENSION]
> label if you're running in EXTENSION mode, and contain warnings
> in case you have a mix of extension and non-extension postgis provided
> modules (topology, raster, sfcgal, ...)
>
> Alternatively, and "manually" you can use system catalogs like:
>
>   select extname, extversion from pg_extension where extname like 'postgis%';
>
> The manual approach won't tell you which non-extension based modules
> of postgis you have installed so the recommended way is the
> `postgis_full_version()` call.

I see; a full "psql db -c 'foo bar'" in the upgrade docs would be nice.

> That's the first change I'm proposing, but I'm foreseeing a setup
> in which we STOP installing "an increasing number of files" but
> rather provide an administrative command to install them on demand.

It's not clear to me that this is a win.

>> I realize Windows doesn't really support symlinks in a reasonable way,
>> and presumably this "duplicated files" is a Windows-only concern.  If
>> that's off base, please straighten me out.
>
> You're right that the "duplicated files" is a windows only issue,
> but I'm also concerned about increasing number of symlinks...

It strikes me that this is really postgresql bug.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220801/4a0380c0/attachment.sig>