PostGIS for PostgreSQL 15

[boris-glumpler]

It seems that the current version of PostGIS still requires v14 of Postgres. Is there a way to force it to install for v15? I tried to update the dependency with brew edit postgis. This seemed to work in that it did not install Postgres 14 and didn't throw any errors. Trying to then add the extension to a database doesn't work, tho. It says that PostGIS is not installed. Would greatly appreciate any pointers! Thanks!

[boris-glumpler]

I ended up compiling PostGIS from source against Postgres 15. Cheers!

[prachil007]

Thank you @funyx for the script.

Updated Script for:

Postgres 16.2 (homebrew)
Postgis 3.4.2

$ sudo mkdir /opt/postgis
$ sudo chown $(whoami):admin /opt/postgis
$ cd /opt
$ wget http://postgis.net/stuff/postgis-3.4.2.tar.gz
$ tar -xvzf postgis-3.4.2.tar.gz -C postgis
$ cd postgis/postgis-3.4.2
$ ./configure --with-projdir=/opt/homebrew/opt/proj --with-protobufdir=/opt/homebrew/opt/protobuf-c --with-pgconfig=/opt/homebrew/opt/postgresql@16/bin/pg_config --with-jsondir=/opt/homebrew/opt/json-c "LDFLAGS=$LDFLAGS -L/opt/homebrew/Cellar/gettext/0.22.5/lib" "CFLAGS=-I/opt/homebrew/Cellar/gettext/0.22.5/include"
$ make 
$ make install

psql $
SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%';
          name          | default_version | installed_version 
------------------------+-----------------+-------------------
 postgis                | 3.4.2        | 
 postgis_tiger_geocoder | 3.4.2        | 
 postgis_topology       | 3.4.2        | 
 postgis_raster         | 3.4.2        | 
 postgis_sfcgal         | 3.4.2        | 
(6 rows)

Enable / Install extention in Postgressql 16

PostGIS

CREATE EXTENSION postgis;

PostGIS Topology

CREATE EXTENSION postgis_topology;

Tips for issues related to dependencies

1. Install postgressql@16 via homebrew using brew install postgressql@16
2. Install postgis via homebrew using brew install postgis
3. Check the version of gettext at /opt/homebrew/Cellar/gettext/ before you proceed and make appropriate changes according to the versions available for gettext, postgressql and postgis.

[sagadave]

Trying to build postgis from source using the script above but make fails with an error:

Built-in generator --c_out specifies a maximum edition PROTO3 which is not the protoc maximum 2023.
make[1]: *** [vector_tile.pb-c.c] Error 1

This is on macOS Sonoma 14.6.1 with brew packages postgresql@16 (16.4) and protobuf-c 1.5.0_8 and postgis 3.4.3 from source, but the same error occurs with other recent versions of postgis and postgresql. Does anyone have any insights in to this error?

make completes successfully if I specify --without-protobuf, but protobuf is needed of course.

The same error is also reported at https://stackoverflow.com/questions/77149123/enable-postgis-extension-for-postgresql-16-on-mac-m2, but no solution is described there, sadly.

[ozeias]

Try this https://github.com/ozeias/homebrew-postgresql

brew rm postgresql@16
brew install ozeias/postgresql/postgis@16

[sagadave]

Thanks, that works great. I see there is a protobuf version update in the homebrew pipeline but not clear yet if it will fix this issue.

[sagadave]

The release of protobuf-c 1.5.0_9 appears to have fixed the PROTO3 incompatibility.

[svoop]

I'm having the same problem here trying to upgrade our infra to Pg15. The dependency on a specific version of Pg was introduced not long ago:

Homebrew/homebrew-core@69e7ca8

@MikeMcQuaid Would you have a minute for some context as to why – and maybe how to work around it? Thanks a bunch!

[SMillerDev]

Doesn't this mean in order to use Postgis you are forced to use the Pg Version it is currently pinned to?

Yes, that is and always has been the case for postgis through Homebrew.

[svoop]

IC. Slotting Postgis as well is not really an option since its versions are not in sync with Pg at all. Other distro face the same dilemma, Gentoo allows for more relaxed dependencies like postgis -> [email protected]. Is something similar possible for Homebrew as well or are the @version postfixes merely conventions with no code parsing them for dependency resolution?

[MikeMcQuaid]

@SMillerDev is correct here.

Is something similar possible for Homebrew as well

No, not really. You'd need to create your own formulae in a tap (brew tap-new and brew extract may be useful) where you change the version used here. We do not support swapping them in and out.

[svoop]

Bummer 😄 I'll try to brew edit Postgis to install against Pg15 first. If it works, a PR might follow. Thanks for your help!

[mschilde]

Does it make sense to version the Postgis formula then as well (postgis@14 etc) or should it always be hard coded and fixed to the latest Postgres version?

[vdavez]

FWIW, @svoop, I'm in the exact same boat. I've tried a bunch of different things to try and get things to make nice, without any luck. If someone figures this out, I'd be grateful!

[fbonzon]

I'm in the exact same boat. I've tried a bunch of different things to try and get things to make nice, without any luck. If someone figures this out, I'd be grateful!

@vdavez I was able to build PostGIS against PostgreSQL 15 (via brew install --build-from-source postgis) with the changes from this commit: fbonzon/homebrew-core@05c672d

Changes are only postgresql@14 → postgresql@15 2x (3x if you include a comment) and HOMEBREW_PREFIX → postgresql.prefix on line 90.

However, then PostgreSQL will not find the PostGIS installation and raise errors like:

ERROR: extension "postgis" is not available
ERROR: could not access file "$libdir/postgis-3": No such file or directory

I fixed those errors by adding missing symlinks inside the PostgreSQL directories, pointing to the PostGIS files.

For the first above error:

cd $(pg_config --sharedir)/extension
find ../../../../../postgis/3.3.2_2/share/postgresql@15/extension -mindepth 1 -maxdepth 1 -print0 | \
  xargs -0 -L 1 ln -s -f

For the second above error:

cd $(pg_config --libdir)/postgresql
ln -s -f /opt/homebrew/Cellar/postgis/3.3.2_2/lib/postgresql/address_standardizer-3.so
ln -s -f /opt/homebrew/Cellar/postgis/3.3.2_2/lib/postgresql/postgis-3.so
ln -s -f /opt/homebrew/Cellar/postgis/3.3.2_2/lib/postgresql/postgis_raster-3.so
ln -s -f /opt/homebrew/Cellar/postgis/3.3.2_2/lib/postgresql/postgis_sfcgal-3.so
ln -s -f /opt/homebrew/Cellar/postgis/3.3.2_2/lib/postgresql/postgis_topology-3.so

NOTE:

• 3.3.2_2 is the current version of PostGIS in Homebrew. In case PostGIS is updated in Homebrew, it will need to be upgraded accordingly.
• The above commands needs to be repeated every time a PostGIS or PostgreSQL 15 update happens in Homebrew.
• Adding those missing symlinks manually works for me but seems hacky. There is probably a more elegant solution involving some brew commands, but I'm not familiar enough with the Homebrew ecosystem to be able to find it.

[vdavez]

Amazing! Thank you! I was able to make it work! I really really appreciate the help!

[dpritchett]

Blundering my way through the same issue with postgresql@13 and postgis, thanks for sharing these notes!

I have a feeling the Formula syntax could allow for an install-time option to let a user specify which postgresql@version-goes-here package they wanted but I've not customized enough formulae in the past to know where to start.

[svoop]

@fbonzon I can only do brew edit postgis and then brew install --build-from-source postgis when postgis (and therefore postgres@14) is already installed. After uninstalling the two, brew edit postgis simply throws "Error: Invalid usage: postgis doesn't exist on disk". (Also, uninstalling only postgresql@14 and keeping the dependency postgis, then brew edit postgis and brew reinstall --build-from-source postgis doesn't do the trick, the thus installed postgis still references the ole 14 in postgis/3.3.4_1/share/postgresql@14/extension. Do you remember how you managed to get around this?

@boris-glumpler Soon, this discussion you've started will celebrate its first anniversary... and unfortunately, we're no bit closer to a real fix than on day one. 😢

[boris-glumpler]

I don't expect there to be a solution anymore, tbh. Installing PostGIS from source is fairly easy, tho, so that's what I've been doing.

[dpritchett]

I’m on an M2 Mac and I’ve been able to use prebuilt binaries from the Postgres.app “universal” installer to get pg13+postgis working: https://postgresapp.com/downloads.html Haven’t bothered with brew for this one thing but if y’all find a pg13+postgis formula that worked on ARM Macs I’d likely choose that over the installer I linked above.

On Tue, Jul 4, 2023 at 10:56 AM Marko Schilde ***@***.***> wrote: Does it make sense to version the Postgis formula then as well ***@***.*** etc) or should it always be hard coded and fixed to the latest Postgres version? — Reply to this email directly, view it on GitHub <#3987 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABEEDLTI6FCZO3GXBPP2Z3XOQ4MDANCNFSM6AAAAAASL5ERLE> . You are receiving this because you commented.Message ID: ***@***.***>

--

…

___________________________ Daniel Pritchett 📨 ***@***.*** |📱 901-871-0139 | ✍🏻 dpritchett.net | 🐦 @dpritchett <https://dpritchett.net/>

[b4stien]

I've taken a shot at writing a "postgresql@15-postgis" without luck so far. I can't understand how/where the symbolic links are done.

After installing postgresql@14 / postgis, you end up with various symbolic links like /opt/homebrew/share/postgresql@14/extension/address_standardizer--1.0--3.3.4.sql -> /opt/homebrew/Cellar/postgis/3.3.4_1/share/postgresql@14/extension/address_standardizer--1.0--3.3.4.sql. As far as I can tell, this links are not created in the postgis formula. Does anyone know where they come from?

I think it's the missing piece for me to make a postgresql@15-postgis formula.

[bahoo]

Ooh @b4stien — https://postgis.net/docs/manual-2.5/postgis_installation.html#installing_pagc_address_standardizer

The address_standardizer extension used to be a separate package that required separate download. From PostGIS 2.2 on, it is now bundled in.

[bahoo]

Actually that's not that interesting — I think this is closer to what you're looking for ?

https://github.com/postgis/postgis/blob/master/extensions/upgrade-paths-rules.mk#L22-L26

Noting too, that file's been through a number of changes the last several months.

[ozeias]

I have it working here, and once I have more time, I will do it for PostgreSQL 16.
https://github.com/ozeias/homebrew-postgresql

[jon-sully]

Thank you for this! 🙌 your pg16+postgis16 formula worked great for me 💯

[b4stien]

I concur: thanks a lot, it works flawlessly.