[postgis-users] How to migrate to new server when PostGIS generations differ

[Regina Obe]

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