> Troels, > > > I am working on getting rid of an old CentOS 7 server which runs > > Postgres > > 10+PostGIS 2.4.8, installed from the yum repository at > > https://download.postgresql.org/pub/repos/yum/ > > > > The server's databases are to be moved to a new Ubuntu 22 server > > running Postgres 15+PostGIS 3.4, installed from the apt repo at > > http://apt.postgresql.org/pub/repos/apt/ > > > > I was hoping I could simply perform a pg_dump on the old server and a > > pg_restore on the new one, but I run into some errors, example: > > > > pg_restore: error: could not execute query: ERROR: type > > "public.pgis_abs" > > does not exist > > Command was: CREATE AGGREGATE public.accum(public.geometry) ( > > SFUNC = public.pgis_geometry_accum_transfn, > > STYPE = public.pgis_abs, > > FINALFUNC = public.pgis_geometry_accum_finalfn > > ); > > > > Is my pg_dump->pg_restore plan simply not viable? If not, what other > > strategy should I employ? > > > > -- > > Regards, > > Troels Arvin > > Your plan of doing a pg_dump of your old Centos 7 PostGIS 2.4.8 and > pg_restore on PostgreSQL 15 + PostGIS 3.4 should work just fine. > > How did you install your PostGIS 2.4.8, if you installed using extensions, you > shouldn't even have CREATE AGGREGATE public.accum in your install. > My guess is maybe you have remnants of older postgis in there even pre- > 2.4.8, cause I vaguely recall we got rid of public.accum even before PostGIS > 2.4.8 > > The public.accum function we got rid of since it was supplanted by the built > in > PostgreSQL array_agg function. > So that error about public.accum you can ignore unless you built user > functions around it. If you have functions around it, you could create a > public.accum, which would be just a copy of the array_agg definition, perhaps > just forcing use of geometry, before you do the pg_dump > > That said, once you do all that, many people run into issues with how the > planner planned things back then and PostGIS use of new planner stuff from > PG 12 on. > So you'll probably want to test your apps for performance degradation, as you > may have to rewrite some of those queries. > > Hope that helps, > Regina >
I forgot to mention one more thing. In PostGIS 2.4, the postgis extension included both geometry and raster types. In PostGIS 3+, these were split into the postgis extension and the postgis_raster extension. Now if you are not using raster, functionality and you had created your postgis in your old version using CREATE EXTENSION postgis; there is nothing extra to do. Because your backup should just have a CREATE EXTENSION postgis; in it, and thus No raster stuff will be restored. However if you were using postgis raster functionality, then in your PostgreSQL 15 / 3.4, you should do CREATE EXTENSION postgis; CREATE EXTENSION postgis_raster; That way your raster tables will come back cleanly. _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
