3

As there are two ways to solve this question, I asked for another approach here.

Whenever a major release of PostgreSQL appears, homebrew will happily install it right away, unless one pins a certain version or installs a specific version from Homebrew/core.

So far so good, but the problem is that PostGIS also keeps updating and eventually will be incompatible with pinned or Homebrew/core versions of PostgreSQL. Again, one can pin PostGIS, yet Homebrew/core does not offer previous versions. From time to time I do brew cleanup and somehow I am now left with a broken PostGIS database. Ok, so "time to jump to PostgreSQL 10.1", I though, but pg_upgrade now gets stuck with the error

ERROR: could not access file "$libdir/postgis-2.3": No such file or directory

That means I can't back nor forth: I cannot figure out how to get PostGIS working with postgresql@9.6 and I cannot pg_upgrade to the new version of PostgreSQL (10.1).

Therefore my question can be answered in two ways. Here I am asking:

How to upgrade PostgeSQL with a broken PostGIS installation ("$libdir/postgis-2.3" error)?

The second approach is described at Installing PostGIS on older homebrew versions of PostgreSQL (e.g. postgresql@9.6)?


What I tried so far:

  • DROP EXTENSION postgis; does not work because the geo-spatial tables depend on it. I could DROP CASCADE but this would probably delete my tables and I would like to preserve them.

closed as off-topic by Vince, BBG_GIS, whyzar, aldo_tapia, MaryBeth Nov 13 '17 at 19:39

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – Vince, BBG_GIS, whyzar, aldo_tapia, MaryBeth
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Using PostGIS can certainly be a GIS topic, but trying to defeat installation/upgrade tools seems more like an OS task, unrelated to our charter. – Vince Nov 12 '17 at 12:56
  • 3
    @Vince Funny you say that. I keep getting such comments here, although GIS SE is full with such questions. There is even a homebrew tag. Anyway, please move it if you think this is appropriate. – n1000 Nov 12 '17 at 12:59
  • To the people in favor of closing the question, please participate in the discussion if installation-related questions are on-topic. Dear downvoters, please give me an indication how to improve my question! – n1000 Nov 12 '17 at 20:26
2

After a whole day of trial & error, I found one possible solution.

The PostGIS website describes the hard upgrade path, which boils down to the following steps:

  1. Dump the entire database

    pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb

  2. Initialize and enable PostGIS on the new database.

  3. Run the restore utility

    perl utils/postgis_restore.pl "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt

In my case the dump (in step 1) would not work at first because the upgrade to PostGIS 2.4 had apparently failed at some point. The problem is apparently that the PostGIS releases are specific to a PostgreSQL database release as discussed here. Luckily, there was for a short time a release of PostGIS 2.4.0 that I could re-enable by means of brew switch postgis 2.4.0 (the current version is called 2.4.0_1). In case you have a similar problem, you may be able to pull the correct version from github. This gist may also give you some food for thought.

I am still not sure what the regular upgrade procedure would look like.

1

I covered this issue in this article.

http://www.bostongis.com/blog/index.php?/archives/268-Using-pg_upgrade-to-upgrade-PostGIS-without-installing-an-older-version-of-PostGIS.html

In a nutshell, PostGIS 2.4 only adds in new functions, so you can simply copy your postgis-2.4., rtpostgis-2.4. and call it postgis-2.3., rtpostgis-2.3. in your new instance.

and then pg_upgrade will work fine because it will find the postgis-2.3 and rtpostgis-2.3 libs with all the functions present in the old postgis-2.3 / rtpostgis-2.3 libs.

  • should that approach still work for postgis 2.3.2 -> 2.5.0 on mac os x? I've run into the same problem having running brew install to go from postgres 9.6.1 to 10.5 as my question here stackoverflow.com/questions/53253267/… – mapping dom Nov 12 '18 at 8:12

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