[postgis-users] hard upgrade from 1.5

[Paul Ramsey]

> On Jan 10, 2022, at 10:01 AM, Nathan Wagner <nw at 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

> 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
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users