[postgis-users] hard upgrade from 1.5

[Nathan Wagner]

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?

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

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