[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