2

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.

1
  • PostGIS 9.2 is crap you need at least 9.3 so you can use parallel queries. – Evan Carroll Jan 9 '19 at 6:08
1

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

2
  • 1
    The database was migrated pre 9.1 and I had worked out that that was the fundamental issue. It isn’t clear to me is how to migrate to using EXTENSION. – Russell Fulton Jan 10 '19 at 16:44
  • @RussellFulton As a consultant, I would try to dump the database add the CREATE EXTENSION PostGIS at the top, then load it again. This will force the first creation of an extension-PostGIS. Upon load, lots of things will fail to install. because they already exist or are provided by the extension. After the DB is up, I would dump it again. Then I would manually clean it up looking for any PostGIS stuff that I didn't create -- manually deleting it. Then I would load that, and you should be good. – Evan Carroll Jan 10 '19 at 16:48
-1

Found the problem. After a lot of stuffing around I realised that the reference to the library must be in the database dump sql and a quick search with the editor found:

CREATE FUNCTION box2d_in(cstring) RETURNS box2d LANGUAGE c IMMUTABLE STRICT
   AS '/Library/PostgreSQL/9.2/lib/postgis-2.0', 'BOX2D_in';

But it turns out that simply hacking the sql to reflect the new library which is version 2.3 does not work because of library changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.