Postgis - migration from 1.5 to 2.3 - workaround

Question

Fellows,

I already had a really hard time trying to make a migration from a postgis 1.5 to 2.3. Actually I had many attempts to do that with all version of postgis: 2.0, 2.1, 2.2 and now 2.3.

As I have spent a few weeks with that, and as I am ready to drop that I keep using my old version of postgresql and postgis, I hope my question finds a echo somewhere...

I only want to migrate one table field, a geometry field. So before trying to do that, I would like to hear if any of you have experience with that:

The idea is to select the field from the old postgis (postgresql 9.2, postgis 1.5), and update the table to the new postgis (postgresql 9.6, postgis 2.3).

Anyone can say anything about it?

EDIT---

I have just tryed to import the table I needed, but I got an error: violation of constraint "enforce_srid_the_geom".

:(

Thanks a lot.

Answer 1

If you need to migrate just a single table with a geometry column and you are getting troubles with the procedure described here http://www.postgis.org/docs/postgis_installation.html#hard_upgrade I'd suggest a workaround: create a text field in the old db, e.g. wkt_geom, then execute

update tablename set wkt_geom = st_astext(the_geom)

Now drop the geometry column, export the old db and import into the new one. Then, create the geometry column and

update tablename set the_geom = st_geomfromtext(wkt_geom, SRID_HERE)