How to rewrite postgres functions which reference postgis lib?

Question

I recently upgraded my postgres version from 9.4 to 10.1. At the same time, I wanted to upgrade postgis from version 2.1 to version 3.

What I did is just dump my database and restore it on my new postgres. But here is the problem I face. Since a lot of functions reference the postgis library with AS '$libdir/postgis-2.1', I get an error saying it can't found $libdir/postgis-2.1.

Of course it can't since the libraries directory of my new postgres only contains postgis-3.so. But then how to tell my function to use this one?

I already found a trick, creating a link named postgis-2.1.so and pointing to postgis-3.so. But this is really an ugly way to do, and I don't want to have to create a new link every times I will upgrade postgis.

I also tried to dump functions, sed it and restore it like this:

pg_dump -U postgres -Fc -s -f db_schema my_db
sed -i -e 's/postgis-2.1/postgis-3/g' db_schema
pg_restore -l db_schema | grep FUNCTION > functions
pg_restore -U postgres -d my_db -L functions db_schema

But it seems that pg_restore does not like the fact I sed this file since I get this error:

pg_restore: [custom archiver] could not read from input file: end of file

Is there a clean way to do this upgrade or is my symlink the only solution here?

Giuseppe Schembri Giuseppe Schembri · Accepted Answer · 2020-07-08T13:55:56

Have you tried to follow the documentation?

It seems you're missing enabling PostGIS.

-- Enable PostGIS (as of 3.0 contains just geometry/geography)
CREATE EXTENSION postgis;
-- enable raster support (for 3+)
CREATE EXTENSION postgis_raster;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

How to rewrite postgres functions which reference postgis lib?

1 Answers