[postgis-users] pg_dumpall Fails: No $libdir/postgis-2.0

[Paul Ramsey]

Brian,
You can find the offending functions by

select oid, proname from pg_proc where proname like '%2.0%';

Probably a good thing to do in general if your database has been
around since 1.5 and progressively upgraded over time. If you've
upgraded to 2.1 and have functions around still pointing at 2.0, you
can probably safely just change their paths to expect 2.1 (update
pg_proc set probin =... ) and go from there.

P


On Fri, Feb 13, 2015 at 6:13 AM, Brian Allen <gzmarketing at gmail.com> wrote:
> I have a database that has had PostGIS in it since 1.5.  We've
> upgraded through various Postgres and PostGIS versions until I'm
> currently on:
>
> PostgreSQL 9.2.10 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit POSTGIS="2.1.5 r13152"
> GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012"
> GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSO
> N="UNKNOWN" TOPOLOGY RASTER
>
> Everything works fine.
>
> But I'm upgrading to Postgres 9.4, so I'm backing up the cluster using
> pg_dumpall.
>
> It fails with:
>
> pg_dump: [archiver (db)] query failed: ERROR:  could not access file
> "$libdir/postgis-2.0": No such file or directory
> pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname,
> a.atttypmod, a.attstattarget, a.attstorage, t.typstorage,
> a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign,
> a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname,
> array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN
> a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS
> attcollation, pg_catalog.array_to_string(ARRAY(SELECT
> pg_catalog.quote_ident(option_name) || ' ' ||
> pg_catalog.quote_literal(option_value) FROM
> pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name),
> E',
>     ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN
> pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid =
> '18040917'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY
> a.attrelid, a.attnum
>
> If I run that query in psql it succeeds, but returns no rows.
>
> What in my schema is directly tied to PostGIS 2.0?  This has happened
> before when upgrading and I have to track down the old version,
> downgrade, do the backup, upgrade, then restore. It's a major pain,
> and as time goes on it gets harder and harder to find the old versions
> or have them work with CentOS (currently 6.6).
>
> In my current case, I'll have to downgrade to postgis2_91-2.0.6
> because it complains that I don't have "rtpostgis-2.0".
>
> What in my schema is still pointing to or requiring rtpostgis-2.0?
> Why won't it work with the current version of PostGIS that is
> installed and working?
>
> I think I can figure it out for this upgrade, but what about next
> time, and the time after that?
>
> Thanks!
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users