search

Homebrew / homebrew-core

🍻 Default formulae for the missing package manager for macOS (or Linux)
https://brew.sh
BSD 2-Clause "Simplified" License
13.79k stars 12.47k forks source link

PostGIS doesn't work with either PostgreSQL 13 or 12 #62929

Closed nertzy closed 4 years ago

nertzy commented 4 years ago

Bug report

Please note we will close your issue without comment if you delete, do not read or do not fill out the issue checklist below and provide ALL the requested information. If you repeatedly fail to use the issue template, we will block you from ever submitting issues to Homebrew again.

What you were trying to do (and why)

I have been running PostgreSQL 12 and PostGIS on my system. I want to either upgrade to PostgreSQL 13 and run PostGIS, or if this is not yet possible, I'd like to fall back to PostgreSQL 12 and PostGIS.

What happened (include command output)

Trying to install the PostGIS extension into the PostgreSQL 13 database fails, with the message:

ERROR:  PostGIS built for PostgreSQL 12.0 cannot be loaded in PostgreSQL 13.0
Command output 
❯ brew services start postgresql
❯ createdb testdb
❯ psql testdb -c "CREATE EXTENSION postgis;"
ERROR:  PostGIS built for PostgreSQL 12.0 cannot be loaded in PostgreSQL 13.0
CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE

What you expected to happen

brew install postgis should either set up a working installation of PostGIS targeted at either (or both) PostgreSQL 13 or PostgreSQL 12.

Step-by-step reproduction instructions (by running brew install commands)

For PostgreSQL 13:

For PostgreSQL 12:

ruurd commented 4 years ago

OK it seems we also need to update postgis to build it against PostgreSQL@13

nertzy commented 4 years ago

It's an issue with the bottle. I was able to get things working with PostgreSQL 13 by running:

brew reinstall -s postgis
jonchang commented 4 years ago

It's an issue with the bottle. I was able to get things working with PostgreSQL 13 by running:

brew reinstall -s postgis

Please open a pull request bumping the revision of postgis, thanks. Ideally also update the test block so our CI will catch this issue in the future.

nertzy commented 4 years ago

https://github.com/Homebrew/homebrew-core/pull/62940

nertzy commented 4 years ago

I took a stab at writing a test block but I'm not yet sure how to do that when I want to test that postgresql and postgis work together.

I got as far as coming up with the command:

❯ psql postgres --csv -c "BEGIN; CREATE EXTENSION postgis; SELECT PostGIS_version()"

But that assumes that a PostgreSQL server is up and running, which might not be true where the test is being run. I'm open to feedback about how to test something like this.

Zelnox commented 4 years ago

I have a related issue. I want to remain on postgresql@12, but installing postgis (3.0.2) will force the installation of postgresql 13. I have tried brew unlink postgresql && brew link --force postgresql@12 which will allow my existing pg12 db to load. However it doesn't have access to postgis, even by re-linking postgis. I’ve been trying to copy the files in libdir without success.

ruurd commented 4 years ago

If you want to stay at a certain version then use pinning:

Usage: brew pin formula

SMillerDev commented 4 years ago

Or use brew extract since your pinned version can and will break things and you likely won't have any idea why or where. While brew extract just transfers the formula to your own repo as new software. Making it pretty clear when things break.

pauldaustin commented 4 years ago

Please make postgis an option of the postgresql@xx and postgresql@xx rather than a separate formula. Then the auto upgrade would work as postgis would exist in the postgresql@xx version.

ruurd commented 4 years ago

Nope. This whole thing is just to get it to compile against a newer postgres version. PostGIS and PostgreSQL are really different products and someone that wants to use PostgreSQL is probably not going to use PostGIS unless he has that specific need. Merging them can lead to all other kinds of problems for example with licensing. In the case of PostgreSQL and PostGIS it is impossible to decide which one to use: the PostgreSQL license or the PostGIS license. They happen to be different.

pauldaustin commented 4 years ago

Or can you add a postgis@12 which would then be linked to the postgresql@12 binaries and build it in the correct place

Zelnox commented 4 years ago

I tried reinstalling postgis from source and I think my main issue right now is the path of PGXS (via pkg_config) is using postgresql instead of postgresql@12. I tried symlinking to have the location of the pgxs dir to match (they are in different places between 12 and 13), but that didn't work in the compilation.

ruurd commented 4 years ago

@pauldaustin Also nope there is no PostGIS version 12. If you want to run PostGIS against a certain version of PosgreSQL then pin that version of PostgreSQL and then install with --build-from-source against the version of PostgreSQL that you have installed. BTW that might also be the solution for running PostGIS in PostgreSQL 13. However keep in mind the caveat of @SMillerDev in this case.

pauldaustin commented 4 years ago

I know there is no postgis version 12, but there needs to be a way to say I want postgis that was built against version 12 of postgresql.

Having to understand the inner workings of homebrew so that you can upgrade from one version of Postgres + postgis to another isn't great.

I thought I had pinned Postgres to avoid this from the last upgrade.

The only way I managed to figure it out last time was to use another machine that I hadn't upgraded.

pauldaustin commented 4 years ago

Not a fully working solution, but this is how I got version 12 running

Update it doesn't work as postgis isn't linked

brew uninstall --ignore-dependencies postgresql
brew install postgresql@12
brew link postgresql@12 --force
pg_ctl -D /usr/local/var/postgres start
SMillerDev commented 4 years ago

My recommendation to have a version locked postgis would be to brew extract it and keep a postgis@12 version in a separate tap. That way you can point it wherever you like and you're unaffected by homebrews policy to always use the latest dependencies.

woodhull commented 4 years ago

I'd like to use a Brewfile to specify for colleagues how to install the dependencies for our app.

Postgres + Postgis combo causes headaches for colleagues that takes hours to unwind whenever either of them are updated. I'd like to lock both of them and update together only infrequently.

I don't think there is any way to do this with brew.

Just trying to explain why various workarounds proposed are not tenable in a team situation.

SMillerDev commented 4 years ago

Brewfiles can't pin versions, but if you maintain your own tap using brew extract you can just specify the tap and it's formula in the brewfile. It's what I do for my colleagues too.

nertzy commented 4 years ago

@Zelnox Does #62992 fix your issue with PGXS?

jonchang commented 4 years ago

Assuming that https://github.com/Homebrew/homebrew-core/pull/62995 fixes this issue.

Zelnox commented 4 years ago

@nertzy I brew extracted postgresql@12 and postgis@3.0.2 into my own tap. I was able to install postgresql@12.4, but after re-linking postgresql, and trying to install postgis from source, I keep getting a problem with a missing postgres.h header. It seems related to pg_config’s _includedirserver. The path sometimes contains an extra or a missing postgresql directory. Symlinking did not resolve the issue. 

---- Making all in libpgcommon
clang -I../liblwgeom  -Wall -Wmissing-prototypes -std=gnu99 -g -O2 -fno-math-errno -fno-signed-zeros -I/usr/local/opt/postgresql@12.4/include/server -I/usr/local/opt/proj/include  -fno-common -DPIC  -c -o gserialized_gist.o gserialized_gi
st.c
gserialized_gist.c:12:10: fatal error: 'postgres.h' file not found
#include "postgres.h"
         ^~~~~~~~~~~~
Zelnox commented 4 years ago

To conclude my saga, there were some directory that had to be renamed and to strip some postgresql path in the postgresql side such that pg_config will show correct information. I hit a wall trying to copy some files from postgis back to the postgresql opt_share, so that's a manual step for now. I'm able to make postgis queries with postgresql 12 now. :D