[postgis-tickets] [PostGIS] #4483: Can't upgrade from PostGIS 3.0.0alpha3 to 3.0.0alpha4 or 3.0.0alpha5dev (ST_AsGeoJSON)

[PostGIS]

#4483: Can't upgrade from PostGIS 3.0.0alpha3 to 3.0.0alpha4 or 3.0.0alpha5dev
(ST_AsGeoJSON)
----------------------+---------------------------
  Reporter:  robe     |      Owner:  pramsey
      Type:  defect   |     Status:  reopened
  Priority:  medium   |  Milestone:  PostGIS 3.0.0
 Component:  postgis  |    Version:  trunk
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 Okay I just pg_upgraded my psuedo production system on Ubuntu 18.04
 running PostgreSQL 12 beta (something I think 3), PostGIS 3.0alpha4 to
 PostgreSQL 12.0 released, PostGIS 3.0.0rc1 and I ran into two stumbling
 blocks.  One id apt.postgresql.org bug which velix already warned about
 and another I think is our issue, but since it's a alpha4 (not really
 supported) probably not worth fixing.

 Issue one when apt.postgresql.org backs up the old cluster files and
 binaries, it does not backup lib and for some reason their
 pg_upgradecluster goes to fix it.

 These notes are mostly for posterity in case others run into the issue

 Steps I did to upgrade:
 First our union functions evidentally changed under the hood between
 alpha4 and rc1, so I ran into issue something like
 pgis_geometry_union_transfn not found.  So to prevent this in my second
 attempt I did this


 {{{
 su postgres
 psql -U postgres -p 5432 -d gisdb
 ALTER EXTENSION postgis DROP AGGREGATE ST_Union(geometry);
 DROP AGGREGATE ST_Union(geometry);
 ALTER EXTENSION postgis DROP AGGREGATE ST_Union(geometry);
 DROP AGGREGATE ST_Union(geometry);
 ALTER EXTENSION postgis DROP FUNCTION
 pgis_geometry_union_transfn(internal,geometry);
 ALTER EXTENSION postgis DROP FUNCTION
 pgis_geometry_union_finalfn(internal);
 DROP FUNCTION pgis_geometry_union_transfn(internal,geometry);
 DROP FUNCTION pgis_geometry_union_finalfn(internal);
 \q
 exit
 }}}


 #next run the upgrade as root
 {{{
 apt update && apt upgrade

 #for some reason upgrade does not copy lib files from old cluster but
 needs it
 cp /usr/lib/postgresql/12/lib/* /var/tmp/postgresql-12-201907221/lib/
 su postgres #I did this cause last time I attempted this as root it failed
 pg_renamecluster 12 main main.old
 pg_upgradecluster 12 main.old --rename main -m upgrade --old-
 bindir=/var/tmp/postgresql-12-201907221/bin

 #make sure it says success before moving on
 exit #back in as root
 service postgresql start

 su postgres
 psql -U postgres -p 5432 -d gisdb

 #in psql terminal
 SELECT postgis_extensions_upgrade();
 analyze (verbose);
 \q

 #back in shell
 pg_dropcluster 12 main.old

 exit
 #back in shell as root

 rm -rf /var/tmp/postgresql-12-201907221


 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4483#comment:21>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.