[postgis-users] hard upgrade from 1.5

[Paul Ramsey]

> On Jan 11, 2022, at 9:45 AM, Nathan Wagner <nw at hydaspes.if.org> wrote:
> 
> On Tue, Jan 11, 2022 at 01:18:27AM +0100, Sandro Santilli wrote:
>> On Mon, Jan 10, 2022 at 10:18:34AM -0800, Paul Ramsey wrote:
>>>> On Jan 10, 2022, at 10:01 AM, Nathan Wagner <nw at hydaspes.if.org> wrote:
>>> 
>>>> So, why exactly is a hard upgrade needed from 1.5 to 2.5?
>>> 
>>> Because the pg_dump, pre-2.0 would include all the function definitions
>> 
>> I think the correct answere here is: because the internal
>> representation of GEOMETRY type changed. That's really the only reason
>> why one would *need* the "hard upgrade" procedure.
> 
> So, what I guess I'm a bit confused about is what I get out of a select
> or copy?  What is the difference between the "internal representation"
> and what I get from a raw select or copy?
> 
> Suppose, for example, I have a table with a geometry column "geom".  If
> I do a "select geom from table", I get what looks like a hex
> representation of a binary value.  Is that a hex encoded internal
> representation, or some external representation that did not change
> between 1.5 and 2.5?  Will this value then be converted to the correct
> internal representation on the 2.5 side?

The internal representation is what is written on the disk.
The "canonical form" is what you get when you run "select geom from mytable", or just pg_dump the table.
The "canonical form" is unchanged from version 1.0 upwards. So you can dump a PostGIS 1.0 table and load it into PostGIS 3.2, because the form in the dump is understood (in fact you can load a table from PostGIS 0.5, since PostGIS 3.2 still accepts the old form on input).
The reason you need to "hard upgrade" between PostGIS 2 and 3, as Sandro noted, is that the on-disk format changed, so you cannot just replace the functions and leave the data in place (which is what the soft upgrade process does) you need to actually read it off disk, convert it into the canonical format (which is what pg_dump does) then send that data back into the new version of PostGIS to be written to disk in the new format.
As and end user, you never see the on-disk format. You're always getting some transformation of it, whether it's WKT, GeoJSON, WKB, or the HEXEWKB that comes out in the dump file or the raw "select geom from mytable" output.

> Another way to put this is will the following work?
> 
> psql -c '\copy (select geom from table) to stdout' -d postgis15 |
> psql -c '\copy table (geom) from stdin' -d postgis25

Yes, that will work. You're reading out the canonical form and writing it over to the new database which will happilty put it back on disk in the new on-disk format.

P.

> 
> The exact syntax is probably different as that is from memory, but I
> trust that the essence of what I'm trying to do is clear.
> 
>> Dropping old functions should be handled just fine by "soft upgrade"
>> procedure. Filtering out all the function definition is ONLY needed
>> during an "hard upgrade" of a database in which PostGIS was enabled
>> via the enabler script (postgis.sql) rather than the CREATE EXTENSION
>> syntax.
> 
> Could this have been done via 'create extension postgis from unpackaged'?
> I think that doesn't work for an in-place upgrade because it can't
> handle converting the internal representation.
> 
>> Out of curiosity: since you're going to copy the data, why do you stop
>> at 2.5 rather than going straight to 3.x ?
> 
> Client reluctance mostly.  The upgrade was also planned before v3 was
> out.  If it were my DB I'd go to 3.x on pg 14.
> 
> -- 
> nw
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users