PostgreSQL is loading wrong PostGIS library

I am attempting to upgrade my version of PostgreSQL (from 9.2 to 9.6) on a Mac running 10.13.5.

I used pg_dumpall to backup the 9.2 database as per the instructions in the postgres documentation stopped the 9.2 instance and started the 9.6 one and then piped the backup file into the 9.6 version of psql.

As soon as it comes to create the tables I get these errors:

CREATE TYPE
psql:/Library/PostgreSQL/backups/9.2.3-dumpall:250: ERROR:  incompatible library "/Library/PostgreSQL/9.2/lib/postgis-2.0.so": version mismatch

DETAIL:  Server is version 9.6, library is version 9.2.

I have tried reinstalling a couple of times without making any difference.

I can't figure out why the loader is loading the 9.2 library instead of the 9.6. I have tried adding LD_LIBRARY_PATH to the environment but it made no difference.

Answer

Your database dump should not have this stuff to begin with. When you dump the database it should put a single line inside the dump says,

CREATE EXTENSION postgis;

This tells the database to load PostGIS from a SQL script in the extension directory. That SQL Script has commands just like the one you pasted that link to a compiled shared object (so).

In your case what has happened is that either

1. You installed PostGIS without using CREATE EXTENSION such as by manually running the SQL file yourself. This is bad. You should remove PostGIS entirely and use CREATE EXTENSION,


2. Some one explicitly yanked a copy of a BOX2D_in from that location. This can happen if someone knows what they're doing and they want to "back-port" a function to PostGIS by hacking it in. If they leave, it sure makes things fun for the next guy though.

As a special aside, it's possible that your database predates the EXTENSION infrastructure. You're on 9.2. That EXTENSION infrastructure was created in 9.1. If you started before 9.1 (like with 9.0 or 8.4) then it's quite possible you started with PostGIS 1.x and that you simply never migrated to use the EXTENSION properly (though you should).