[postgis-users] hard upgrade from 1.5

[Nathan Wagner]

I am working on an upgrade from postgis 1.5 on postgresql 9.0 to postgis
2.5 on postgresql 11.

The docs say that a hard upgrade is needed. Is that still true if we're
migrating the data from one database to another? Our migration strategy
is to do a copy out of the table data (as in a \copy psql command, or
equivalent) from the source and a copy in at the target. An examination
of the results seem to indicate that the data is copied correctly.

I had been under the impression that the binary format had changed, but
I am unable to find an example of this.

So, if you're not upgrading in place, but doing a copy, is a "hard" type
upgrade needed? My earlier strategy had been to select the data out of
the database wrapping the geometry columns in st_asewkt() on the way out
and similarly on the way in. This doesn't seem like it's needed and I
can just use the binary representation directly.

I had thought that postgis_restore.pl would have done some sort of
wrapper, but I think it just adjusts the manifest list of a pg_restore
-l, which we don't need in our case since we have the new version of
postgis already installed on the new database.

I can't find any mention of a change in the binary format in the git
logs, but if there weren't a binary format change, I would have thought
that a simple 'from unpackaged' script to convert to an extension would
have been sufficient and could have been done in place without needing
anything special.

So, why exactly is a hard upgrade needed from 1.5 to 2.5?

Can I do a copy to file from the 1.5 and then copy from that file to 2.5
and expect it to work? If not, why not, what sort of errors should I
expect? A specific example of something that doesn't work would be
ideal, since then I could then write a failing test.

--
nw
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Paul Ramsey]

> On Jan 10, 2022, at 10:01 AM, Nathan Wagner <n...@hydaspes.if.org> wrote:
>
> I am working on an upgrade from postgis 1.5 on postgresql 9.0 to postgis
> 2.5 on postgresql 11.
>
> The docs say that a hard upgrade is needed. Is that still true if we're
> migrating the data from one database to another? Our migration strategy
> is to do a copy out of the table data (as in a \copy psql command, or
> equivalent) from the source and a copy in at the target. An examination
> of the results seem to indicate that the data is copied correctly.

If you are going table-by-table and not doing a full pg_dump/pg_restore, then effecively you are doing a hard upgrade yourself. The hard upgrade is mostly about filtering the postgis function out of the pg_dump data stream, and since you're manually migrating all the tables, and thus avoiding all the functions, You Are A Winner.

> I had been under the impression that the binary format had changed, but
> I am unable to find an example of this.

The on-disk format has changed, which is why an in-place pg_upgrade is not possible, but the dump format has not changed, which is why you aren't seeing any difference examinging the pg_dump files.

> 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, and then splatting those onto the new fresh database would at a minimum generate a pile of errors and in a worst case would add some function end points that should have been dropped. But as noted above, your table-by-table method is avoid this issue. Post-2.0, assuming the postgis install was via 'create extension' the dump files no longer include all the guts of postgis, they just include a neat 'create extension postgis' call at the top, which makes inter-verion dump/restore MUCH more straightforward.

ATB,

P

[Sandro Santilli]

On Mon, Jan 10, 2022 at 10:18:34AM -0800, Paul Ramsey wrote:
> > On Jan 10, 2022, at 10:01 AM, Nathan Wagner <n...@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.

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.

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 ?

--strk;

Libre GIS consultant/developer
https://strk.kbt.io/services.html

[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 <n...@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

[Paul Ramsey]

> On Jan 11, 2022, at 9:45 AM, Nathan Wagner <n...@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 <n...@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.

[Sandro Santilli]

On Tue, Jan 11, 2022 at 05:45:16PM +0000, Nathan Wagner wrote:

> 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.

You are right: "create extension from unpackaged" is still a form of
"soft upgrade" so only works when "soft upgrade" is possible
(ie: when data doesn't need to be dumped *before* the upgrade).

--strk;