Installing POSTGIS locally

Aarbormagna · Nov 15, 2023

Hi Sean,
Thanks for answering. I tried to set CPPFLAGS and LDFLAGS, but it doesn't help, CMake is always finding SQLite3 libraries in /usr/lib64 and INCLUDE_DIR in /usr/include.
Could the problem be that my SQLite3 libraries are installed in $HOME/opt/lib and not in $HOME/opt/lib64?

Nnick · Nov 15, 2023

If your SQLite3 library is located in $PREFIX/lib then I think you need to change this line in your GDAL install from -DCMAKE_INSTALL_RPATH=$PREFIX/lib64 to -DCMAKE_INSTALL_RPATH=$PREFIX/lib.

If you are not sure where it is, try locating the path to it with a command like this: find $HOME -name libsqlite3.so

Then try adding these to your script after you set the PREFIX and PATH variables, eg.

mkdir -p ~/opt/src
cd ~/opt/src
export PREFIX=$HOME/opt
export PATH=$PREFIX/bin:$PATH
export C_INCLUDE_PATH=$PREFIX/include:$C_INCLUDE_PATH
export CPLUS_INCLUDE_PATH=$PREFIX/include:$CPLUS_INCLUDE_PATH
export LD_LIBRARY_PATH=$PREFIX/lib:$LD_LIBRARY_PATH
export CPPFLAGS="-I$PREFIX/include $CPPFLAGS"
export LDFLAGS="-L$PREFIX/lib $LDFLAGS"
...

Note: you can also try clearing any CMAKE cache before each build by locating the CMakeCache.txt file in the build directories, and deleting it if you think there are any conflicts due to them.

Aarbormagna · Nov 17, 2023

I did all you suggested but nothing changed. I delete all files from the build directory before each build.
libsqlite3.so is in $PREFIX/lib and I changed -DCMAKE_INSTALL_RPATH=$PREFIX/lib64 to -DCMAKE_INSTALL_RPATH=$PREFIX/lib, however, CMakeCache.txt has these two lines:

//No help, variable specified on the command line.
CMAKE_INSTALL_RPATH:UNINITIALIZED=/home/zavod/opt/lib

It was the same with -DCMAKE_INSTALL_RPATH=$PREFIX/lib64 though. However, when -DCMAKE_INSTALL_RPATHis changed to $PREFIX/lib build exits with an error (near the end of the building) saying it could not find GEOS libraries, which is not the problem with $PREFIX/lib64 where build goes all the way to the end without problems.

So it's got to be something else, it is just beyond my understanding of compiling from the source.

Note: In CMakeCache fille there are two settings that are very odd because they are contradictory:

//Path to a library.
SQLite3_LIBRARY:FILEPATH=/home/zavod/opt/lib/libsqlite3.so

//Path to a library.
pkgcfg_lib_PC_SQLITE3_sqlite3:FILEPATH=/usr/lib64/libsqlite3.so

Nnick · Nov 17, 2023

arbormagna

I delete all files from the build directory before each build.

Make sure you're either completely deleting the build directory or explicitly removing CMakeCache.txt before re-running CMake.

You can also try something like this to attempt to bypass attempts CMAKE might be using to persist in using default system paths:

-DSQLite3_INCLUDE_DIR=$PREFIX/include \
-DSQLite3_LIBRARY=$PREFIX/lib/libsqlite3.so \

Or; try appending the cmake command with --verbose to try and get more details as to why it's using the system paths.

Otherwise, I'm going to try this out myself and get back to you with any findings.

Aarbormagna · Nov 17, 2023

arbormagna Sorry nick , although CMakeCache.txt is the same as it was in the time of my original post, when I ran ldd libgdal.so libsqlite3.so is referenced to correct location: libsqlite3.so.0 => /home/zavod/opt/lib/libsqlite3.so.0 (0x00007fb719b2f000), while ogrinfo -so --formats doesn't throw any sqlite3 errors or any errors for that matter.
All I did is adding those lines at the beggining of the script as you suggested:

export C_INCLUDE_PATH=$PREFIX/include:$C_INCLUDE_PATH
export CPLUS_INCLUDE_PATH=$PREFIX/include:$CPLUS_INCLUDE_PATH
export LD_LIBRARY_PATH=$PREFIX/lib:$LD_LIBRARY_PATH
export CPPFLAGS="-I$PREFIX/include $CPPFLAGS"
export LDFLAGS="-L$PREFIX/lib $LDFLAGS"

Script for GDAL building was not changed and looks like this:

#Download and install gdal 3.6.4
cd $PREFIX/src
wget http://download.osgeo.org/gdal/3.6.4/gdal-3.6.4.tar.gz
tar xvf gdal-3.6.4.tar.gz
cd gdal-3.6.4
mkdir -p build
cd build
cmake .. -DCMAKE_BUILD_TYPE=Release \
-DCMAKE_C_COMPILER=$PREFIX/bin/gcc \
-DCMAKE_CXX_COMPILER=$PREFIX/bin/g++ \
-DCMAKE_INSTALL_PREFIX=$PREFIX \
-DSQLite3_INCLUDE_DIR=$PREFIX/include \
-DSQLite3_LIBRARY=$PREFIX/lib/libsqlite3.so \
-DSQLITE3EXT_INCLUDE_DIR=$PREFIX/include \
-DCMAKE_BUILD_WITH_INSTALL_RPATH=ON \
-DCMAKE_INSTALL_RPATH=$PREFIX/lib64
cmake --build . -j8
cmake --build . --target install

I will try to compile Postgres and Postgis now and let you know if everything goes well.

P.S. I would really like to know what made it successful this time even though nothing seems to be changed in the CMakeCache.txt file. Forgive me if I am missing something obvious.

Nnick · Nov 17, 2023

arbormagna Following the version recommendations you linked earlier. I was able to install PostgreSQL 15 with PostGIS 3.3.3, and minimal dependencies (you'd have to install whatever you need that is missing).

You don't have to install a newer GCC version and all it's related dependencies, but rather use scl enable devtoolset-11 bash instead, which is installed on most servers. If you get an error when using that, open a support ticket and ask us to install it on your server as it might not be installed.

Here's the really rough set of steps I organized in my spare time that lumps everything (Yes, it could be cleaner ) in a single NGINX proxy app (~/apps/psql15) used for the PostgreSQL 15 install (that was inspired by another post I made in the past for PostgreSQL 12). Just replace all the relevant fields to match your app and dev environment on the server.

#Download, install, and configure PostgreSQL15
cd ~/apps/psql15
wget https://ftp.postgresql.org/pub/source/v15.0/postgresql-15.0.tar.gz
tar zxf postgresql-15.0.tar.gz 
export PGHOME=$HOME/apps/psql15/
export PGDATA=$HOME/apps/psql15/pgdata
./postgresql-15.0/configure --prefix=$PGHOME
make
make install
mkdir $PGDATA
$PGHOME/bin/initdb -D $PGDATA
sed -i 's/#port = 5432/port = XXXXX/g' $PGDATA/postgresql.conf

#Version check
 ~/apps/psql15/bin/postgres --version

#Immediately setup superuser **DO NOT SKIP**
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile start
~/apps/psql15/bin/psql -h localhost -p XXXXX -d postgres
ALTER USER newdbuser WITH PASSWORD 'mypasswd';
exit
sed -i 's/trust/md5/g' $PGDATA/pg_hba.conf
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile restart

#Commands to stop, start, restart, and connect
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata stop
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile start
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile restart
~/apps/psql15/bin/psql -h localhost -p XXXXX -d postgres -U newdbuser

# Install CMAKE
# make some dirs if they don't already exist, etc
mkdir -p ~/opt ~/bin ~/src && cd ~/src
export PATH=$HOME/opt/bin:$PATH
wget https://github.com/Kitware/CMake/archive/refs/tags/v3.27.8.tar.gz
tar zxf v3.27.8.tar.gz
cd CMake-3.27.8/
./bootstrap --prefix=$HOME/opt
make
make install
cd ~/bin
ln -s ~/opt/bin/* .

## Install other POSTGIS dependencies

# Set dev env stuff
scl enable devtoolset-11 bash
export PGHOME=$HOME/apps/psql15/
export PATH="$PGHOME/bin:$PATH"
export LD_LIBRARY_PATH="$PGHOME/lib:$LD_LIBRARY_PATH"

# Install GEOS
cd $PGHOME/
wget https://download.osgeo.org/geos/geos-3.12.0.tar.bz2
tar xjf geos-3.12.0.tar.bz2
cd geos-3.12.0
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++
make -j9
make install

#Download and install googletest
git clone https://github.com/google/googletest.git $PGHOME/googletest/
cd $PGHOME/googletest/
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
         -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
         -DBUILD_SHARED_LIBS=ON
make -j9
make install

#Download and install sqlite3
cd $PGHOME/
wget https://www.sqlite.org/2023/sqlite-autoconf-3440000.tar.gz
tar xzf sqlite-autoconf-3440000.tar.gz
cd sqlite-autoconf-3440000
./configure --prefix=$PGHOME
make -j9
make install

#Download and install PROJ9
cd $PGHOME/
wget https://download.osgeo.org/proj/proj-9.2.1.tar.gz
tar zxf proj-9.2.1.tar.gz 
cd proj-9.2.1/
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
         -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
make -j9
make install

# Install GDAL
cd $PGHOME/
wget https://github.com/OSGeo/gdal/releases/download/v3.7.0/gdal-3.7.0.tar.gz
tar zxf gdal-3.7.0.tar.gz
cd gdal-3.7.0/
mkdir build && cd build
cmake .. -DCMAKE_BUILD_TYPE=Release \
         -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
         -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so \
         -DGEOS_DIR=$PGHOME/lib64/cmake/GEOS
cmake --build .
cmake --build . --target install

# Install PostGIS
export LD_LIBRARY_PATH="$PGHOME/lib64:$PGHOME/lib:$LD_LIBRARY_PATH";
cd $PGHOME/
wget https://download.osgeo.org/postgis/source/postgis-3.3.3.tar.gz
tar zxf postgis-3.3.3.tar.gz
cd postgis-3.3.3/
export LDFLAGS="-L$PGHOME/lib64"
export CPPFLAGS="-I$PGHOME/include"
./configure --with-pgconfig=$PGHOME/bin/pg_config \
            --with-geosconfig=$PGHOME/bin/geos-config \
            --with-projdir=$PGHOME \
            --without-protobuf \
            --without-sfcgal
make -j9
make install

# Connect to PostgreSQL, create postgis extension
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
~/apps/psql15/bin/psql -h localhost -p XXXXX -d postgres -U newdbuser
CREATE EXTENSION postgis;

# Checking PostGIS in psql:
SELECT PostGIS_Version();

            postgis_version            
---------------------------------------
 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

postgres=#

I hope it helps your workflow in getting this working for your Opalstack served projects

Aarbormagna · Nov 22, 2023

Dear Nick,

Thank you for your help, and sorry for late reply. Last several days I was trying to get POstgis installed with the script you provided, but I ran into a few issues.
First, after installing GDAL, ogrinfowas missing so I 'fixed' it by adding

-DCMAKE_BUILD_WITH_INSTALL_RPATH=ON \
-DCMAKE_INSTALL_RPATH=$PGHOME/lib64

configure options.

Also, because I need SFCGALand PROTOBUF dependencies, I installed several dependencies related to those, and then ran into problems while configuring POSTGIS(it couldn't find PROJlibraries), which I managed to solve by setting variables:

export PROJ_CFLAGS="-I$PGHOME/include"
export PROJ_LIBS="-L$PGHOME/lib -lproj"

This helped the installation make it through, however, during runtime, when I would try to make postgis extension on the database it would throw an error that it couldn't find proj libraries.

I didn't want to bother you and wanted to crack this one myself but I am out of options at the moment.

Nnick · Nov 23, 2023

arbormagna Hi, can you please specify all the dependencies your project requires? I will have to revise and look at the build later.

Aarbormagna · Nov 23, 2023

nick Yes off course, I want to deploy Geodjango project with Django 4.2 and I need:

PostgreSQL 15  with OpenSSL
PostGIS 3.3
GEOS 3.10.5
GDAL 3.6.4
PROJ 6 - 9
json-c 0.17
protobuf-c 1.4.1
SFCGAL 1.5.0

Aarbormagna · Nov 24, 2023

arbormagna nick Hi, just an update from my side. Building upon your script I have now a script that brings the process to the end, i.e. I have a working PostGIS extension:

# Install CMAKE
# make some dirs if they don't already exist, etc
mkdir -p $HOME/opt/src $HOME/bin $HOME/src && cd $HOME/opt/src
export PATH="$HOME/opt/bin:$PATH"

wget https://github.com/Kitware/CMake/archive/refs/tags/v3.27.8.tar.gz
tar zvxf v3.27.8.tar.gz
cd CMake-3.27.8/
./bootstrap --prefix=$HOME/opt
make -j9
make install
cd ~/bin
ln -s ~/opt/bin/* .

## Install other POSTGIS dependencies

# Set dev env stuff
scl enable devtoolset-11 bash
export PATH="$HOME/opt/bin:$PATH"
export PGHOME=$HOME/apps/psql15
export PGDATA=$HOME/apps/psql15/pgdata
mkdir -p $HOME/apps/psql15/pgdata
mkdir -p $PGHOME/src
export MY_PORT=XXXXX
export PATH="$PGHOME/bin:$PATH"
export LD_LIBRARY_PATH="$PGHOME/lib:$LD_LIBRARY_PATH"
export C_INCLUDE_PATH=$PGHOME/include:$C_INCLUDE_PATH
export CPLUS_INCLUDE_PATH=$PGHOME/include:$CPLUS_INCLUDE_PATH
export CPPFLAGS="-I$PGHOME/include $CPPFLAGS"
export LDFLAGS="-L$PGHOME/lib $LDFLAGS"

# Install GEOS
cd $PGHOME/src
wget https://download.osgeo.org/geos/geos-3.10.5.tar.bz2
tar xvjf geos-3.10.5.tar.bz2
cd geos-3.10.5
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
		 -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
		 -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++
make -j9
make install

#Download and install googletest
git clone https://github.com/google/googletest.git $PGHOME/src/googletest/
cd $PGHOME/src/googletest/
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
         -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
         -DBUILD_SHARED_LIBS=ON
make -j9
make install

#Download and install sqlite3
cd $PGHOME/src
wget https://www.sqlite.org/2023/sqlite-autoconf-3440000.tar.gz
tar xvzf sqlite-autoconf-3440000.tar.gz
cd sqlite-autoconf-3440000
./configure --prefix=$PGHOME
make -j9
make install

#Download and install PROJ9
cd $PGHOME/src
wget https://download.osgeo.org/proj/proj-9.2.1.tar.gz
tar zvxf proj-9.2.1.tar.gz
cd proj-9.2.1/
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
         -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
make -j9
make install

#Download and install json-c-0.16
cd $PGHOME/src
git clone https://github.com/json-c/json-c.git
cd json-c
git checkout remotes/origin/json-c-0.16
mkdir ../json-c-build
cd ../json-c-build
cmake ../json-c -DCMAKE_INSTALL_PREFIX=$PGHOME \
				-DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
				-DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++
make -j9
make install

# Install GDAL
cd $PGHOME/src
wget https://github.com/OSGeo/gdal/releases/download/v3.6.4/gdal-3.6.4.tar.gz
tar zvxf gdal-3.6.4.tar.gz
cd gdal-3.6.4/
mkdir build && cd build
export LD_LIBRARY_PATH=$PGHOME/lib64:$LD_LIBRARY_PATH
cmake .. -DCMAKE_BUILD_TYPE=Release \
         -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc \
         -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++ \
         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
cmake --build . -j9
cmake --build . --target install

# Download and install postgresql 15.4
cd $PGHOME/src
wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz
tar xvf postgresql-15.4.tar.gz
cd postgresql-15.4
LDFLAGS=-lstdc++ ./configure CC=/opt/rh/devtoolset-11/root/usr/bin/gcc \
		--prefix=$PGHOME \
		--with-pgport=$MY_PORT \
		--with-ssl=openssl
make -j9
make install

# Download and install protobuf
cd $PGHOME/src
git clone https://github.com/protocolbuffers/protobuf.git
cd protobuf
git checkout remotes/origin/4.0.x
./autogen.sh
./configure --prefix=$PGHOME
make -j9
make install

# Download and install protobuf-c
cd $PGHOME/src
wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.4.0/protobuf-c-1.4.0.tar.gz
tar -xzvf protobuf-c-1.4.0.tar.gz
cd protobuf-c-1.4.0
export LD_LIBRARY_PATH=$PGHOME/lib64:$LD_LIBRARY_PATH
./configure --prefix=$PGHOME \
			CC=/opt/rh/devtoolset-11/root/usr/bin/gcc \
            protobuf_CFLAGS="-I$PGHOME/include" \
            protobuf_LIBS="-L$PGHOME/lib -lprotobuf"
make -j9
make install

#Download and install gmp 6.3.0 (needed for SFCGAL)
cd $PGHOME/src
wget https://ftp.gnu.org/gnu/gmp/gmp-6.3.0.tar.xz
unxz gmp-6.3.0.tar.xz
tar -xvf gmp-6.3.0.tar
cd gmp-6.3.0
./configure --enable-cxx \
			--prefix=$PGHOME
make -j9
make install

#Download and install mpfr 4.2.1 (needed for SFCGAL)
cd $PGHOME/src
wget https://ftp.gnu.org/gnu/mpfr/mpfr-4.2.1.tar.gz
tar -xvf mpfr-4.2.1.tar.gz
cd mpfr-4.2.1
./configure --prefix=$PGHOME
make -j9
make install

# Download and install boost 1.83.0 (needed for SFCGAL)
cd $PGHOME/src
wget https://boostorg.jfrog.io/artifactory/main/release/1.83.0/source/boost_1_83_0.tar.gz
tar -xzvf boost_1_83_0.tar.gz
cd boost_1_83_0
export LD_LIBRARY_PATH=$PGHOME/lib64:$LD_LIBRARY_PATH
./bootstrap.sh --prefix=$PGHOME
./b2 install -j9

# Download and install CGAL-5.6 (needed for SFCGAL)
cd $PGHOME/src
wget https://github.com/CGAL/cgal/releases/download/v5.6/CGAL-5.6.tar.xz
tar -xvf CGAL-5.6.tar.xz
cd CGAL-5.6
mkdir -p build
cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME -DCMAKE_BUILD_TYPE="Release"
make -j9
make install

# Download and install SFCGAL-1.5.0
cd $PGHOME/src
wget https://gitlab.com/SFCGAL/SFCGAL/-/archive/v1.5.0/SFCGAL-v1.5.0.tar.gz
tar -xvf SFCGAL-v1.5.0.tar.gz
cd SFCGAL-v1.5.0
mkdir -p build
cd build
export CXX=c++
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
-DCMAKE_BUILD_TYPE="Release"
make -j9
make install

# Install PostGIS
export LD_LIBRARY_PATH="$PGHOME/lib64:$PGHOME/lib:$LD_LIBRARY_PATH";
cd $PGHOME/src
wget https://download.osgeo.org/postgis/source/postgis-3.3.3.tar.gz
tar zvxf postgis-3.3.3.tar.gz
cd postgis-3.3.3/
export LDFLAGS="-L$PGHOME/lib64 -L$PGHOME/lib"
export CPPFLAGS="-I$PGHOME/include"
./configure --with-pgconfig=$PGHOME/bin/pg_config --with-gdalconfig=$PGHOME/bin/gdal-config --with-geosconfig=$PGHOME/bin/geos-config --with-projdir=$PGHOME --with-jsondir=$PGHOME
make -j9
make install

However, even though the PostGIS is built with all needed dependencies:

-------------- Dependencies --------------
  GEOS config:          /home/arbormagna/apps/psql15/bin/geos-config
  GEOS version:         3.10.5
  GDAL config:          /home/arbormagna/apps/psql15/bin/gdal-config
  GDAL version:         3.6.4
  SFCGAL config:        /home/arbormagna/apps/psql15/bin/sfcgal-config
  SFCGAL version:       1.5.0
  PostgreSQL config:    /home/arbormagna/apps/psql15/bin/pg_config
  PostgreSQL version:   PostgreSQL 15.4
  PROJ4 version:        92
  Libxml2 config:       /usr/bin/xml2-config
  Libxml2 version:      2.9.1
  JSON-C support:       yes
  protobuf support:     yes
  protobuf-c version:   1004000
  PCRE support:         Version 1
  Perl:                 /usr/bin/perl

 --------------- Extensions ---------------
  PostGIS Raster:                     enabled
  PostGIS Topology:                   enabled
  SFCGAL support:                     enabled
  Address Standardizer support:       enabled

GDAL and SFCGAL are not available at run time (I figure they are not installed):

                postgis_full_version

-------------------------------------------------------
-------------------------------------------------------
-----------------------------------------------------
 POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="
3.10.5-CAPI-1.16.2" PROJ="9.2.1" LIBXML="2.9.1" LIBJSON
="0.16" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)"
(1 row)

As a side note, if I add an option CC=/opt/rh/devtoolset-11/root/usr/bin/gcc to PostGIS configuration I can not create PostGIS extension at run time (postgis can not find libproj.so). Could this mean that it can be related to compiler? Any insights are highly appreciated.

Nnick · Nov 25, 2023

arbormagna Hi there, sorry for any delays - it took me a while to get this tested.

Couple things I noticed:

CGAL INSTALL.md notes that:

Since Version 5.0, CGAL is now header-only by default, meaning that you do not need to build and install CGAL.

So, camke/make/install won't do anything, you just have to extract it and tell SFCGAL where the headers are when you install it, eg. export CGAL_DIR=/home/user/src/CGAL-5.6

Assuming SFCGAL installed successfully, create it's related extension after the postgis one:

$PGHOME/bin/psql -h localhost -p XXXXX -d db_name -c "CREATE EXTENSION postgis_sfcgal;"

Then the $PGHOME/bin/psql -h localhost -p XXXXX -d db_name -c "SELECT PostGIS_Full_Version();" command should show something like this:

-------------------
 POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="3.10.5-CAPI-1.16.2" SFCGAL="SFCGAL 1.5.0
, CGAL 5.6, BOOST 1.83.0" PROJ="9.2.1" LIBXML="2.9.1" LIBJSON="0.17" LIBPROTOBUF="1.4.0" WAGYU=
"0.5.0 (Internal)"
(1 row)

In case you want to review the entire install process, please see below, which is an adapted version of your work and mine.

# Make NGINX proxy app (eg we'll use psql15 here) and note the port

# Create necessary directories if they don't already exist
mkdir -p ~/opt ~/bin ~/src ~/apps/psql15/pgdata

# Enable devtoolset and environment variables (need to set again if terminal session ever ends)
source /opt/rh/devtoolset-11/enable
export PATH=~/opt/bin:~/apps/psql15/bin:$PATH;
export PGHOME=~/apps/psql15;
export PGDATA=~/apps/psql15/pgdata;
export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib64:$LD_LIBRARY_PATH;
export C_INCLUDE_PATH=$PGHOME/include:$C_INCLUDE_PATH;
export CPLUS_INCLUDE_PATH=$PGHOME/include:$CPLUS_INCLUDE_PATH;
export CPPFLAGS="-I$PGHOME/include $CPPFLAGS";
export LDFLAGS="-L$PGHOME/lib64 -L$PGHOME/lib $LDFLAGS"

# Install CMake
cd ~/src
wget https://github.com/Kitware/CMake/archive/refs/tags/v3.27.8.tar.gz
tar zvxf v3.27.8.tar.gz
cd CMake-3.27.8/
./bootstrap --prefix=~/opt
make -j9
make install

# Install PostgreSQL 15 with OpenSSL (replace XXXXX with port)
cd ~/src
wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz
tar xvf postgresql-15.4.tar.gz
cd postgresql-15.4
LDFLAGS=-lstdc++ ./configure --prefix=$PGHOME --with-openssl
make -j9
make install
$PGHOME/bin/initdb -D $PGDATA
sed -i 's/#port = 5432/port = XXXXX/g' $PGDATA/postgresql.conf

# Immediately setup superuser password **DO NOT SKIP**
$PGHOME/bin/pg_ctl -D $PGDATA -l logfile start
$PGHOME/bin/psql -h localhost -p XXXXX -d postgres
ALTER USER shell_user WITH PASSWORD 'superuser_pwd';
exit
sed -i 's/trust/md5/g' $PGDATA/pg_hba.conf
$PGHOME/bin/pg_ctl -D $PGDATA -l logfile restart

# Create production DB/user
$PGHOME/bin/createuser new_user -h localhost -p XXXXX --no-createdb --no-superuser --no-createrole --pwprompt
$PGHOME/bin/createdb new_db -h localhost -p XXXXX --owner=new_user

#Commands to stop, start, restart, and connect
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata stop
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile start
~/apps/psql15/bin/pg_ctl -D ~/apps/psql15/pgdata -l logfile restart
~/apps/psql15/bin/psql -h localhost -p XXXXX -d new_db -U new_user

# Install GEOS 3.10.5
cd ~/src
wget https://download.osgeo.org/geos/geos-3.10.5.tar.bz2
tar xvjf geos-3.10.5.tar.bz2
cd geos-3.10.5
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME
make -j9
make install

# Install sqlite3
cd ~/src
wget https://www.sqlite.org/2023/sqlite-autoconf-3440000.tar.gz
tar xvzf sqlite-autoconf-3440000.tar.gz
cd sqlite-autoconf-3440000
./configure --prefix=$PGHOME
make -j9
make install

# Install PROJ9
cd ~/src
wget https://download.osgeo.org/proj/proj-9.2.1.tar.gz
tar zvxf proj-9.2.1.tar.gz
cd proj-9.2.1/
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
make -j9
make install

# Install json-c 0.17
cd ~/src
git clone https://github.com/json-c/json-c.git
cd json-c
git checkout json-c-0.17
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME
make -j9
make install

# Download and install googletest
cd ~/src
git clone https://github.com/google/googletest.git
cd googletest/
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DBUILD_SHARED_LIBS=ON
make -j9
make install

# Install GDAL 3.6.4
cd ~/src
wget https://github.com/OSGeo/gdal/releases/download/v3.6.4/gdal-3.6.4.tar.gz
tar zvxf gdal-3.6.4.tar.gz
cd gdal-3.6.4/
mkdir build && cd build
cmake .. -DCMAKE_BUILD_TYPE=Release \
         -DCMAKE_INSTALL_PREFIX=$PGHOME \
         -DSQLITE3_INCLUDE_DIR=$PGHOME/include \
         -DSQLITE3_LIBRARY=$PGHOME/lib/libsqlite3.so
make -j9
make install

# Download and install protobuf
cd ~/src
git clone https://github.com/protocolbuffers/protobuf.git
cd protobuf
git checkout remotes/origin/4.0.x
./autogen.sh
./configure --prefix=$PGHOME
make -j9
make install

# Download and install protobuf-c
cd ~/src
wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.4.0/protobuf-c-1.4.0.tar.gz
tar -xzvf protobuf-c-1.4.0.tar.gz
cd protobuf-c-1.4.0
./configure --prefix=$PGHOME \
            protobuf_CFLAGS="-I$PGHOME/include" \
            protobuf_LIBS="-L$PGHOME/lib -lprotobuf"
make -j9
make install

# Download and install gmp 6.3.0 (needed for SFCGAL)
cd ~/src
wget https://ftp.gnu.org/gnu/gmp/gmp-6.3.0.tar.xz
tar xf gmp-6.3.0.tar.xz
cd gmp-6.3.0
./configure --enable-cxx \
			--prefix=$PGHOME
make -j9
make install

#Download and install mpfr 4.2.1 (needed for SFCGAL)
cd ~/src
wget https://ftp.gnu.org/gnu/mpfr/mpfr-4.2.1.tar.gz
tar -xvf mpfr-4.2.1.tar.gz
cd mpfr-4.2.1
./configure --prefix=$PGHOME
make -j9
make install

# Download and install boost 1.83.0 (needed for SFCGAL)
cd ~/src
wget https://boostorg.jfrog.io/artifactory/main/release/1.83.0/source/boost_1_83_0.tar.gz
tar -xzvf boost_1_83_0.tar.gz
cd boost_1_83_0
./bootstrap.sh --prefix=$PGHOME
./b2 install -j9

# Download and extract CGAL-5.6 headers (needed for SFCGAL)
cd ~/src
wget https://github.com/CGAL/cgal/releases/download/v5.6/CGAL-5.6.tar.xz
tar -xvf CGAL-5.6.tar.xz

# Download and install SFCGAL-1.5.0
cd ~/src
wget https://gitlab.com/SFCGAL/SFCGAL/-/archive/v1.5.0/SFCGAL-v1.5.0.tar.gz
tar -xvf SFCGAL-v1.5.0.tar.gz
cd SFCGAL-v1.5.0
mkdir build && cd build
export CXX=c++
export CGAL_DIR=$HOME/src/CGAL-5.6
cmake .. -DCMAKE_INSTALL_PREFIX=$PGHOME \
-DCMAKE_BUILD_TYPE="Release"
make -j9
make install

# Install PostGIS 3.3
cd ~/src
wget https://download.osgeo.org/postgis/source/postgis-3.3.3.tar.gz
tar zvxf postgis-3.3.3.tar.gz
cd postgis-3.3.3/
./configure --with-pgconfig=$PGHOME/bin/pg_config \
            --with-gdalconfig=$PGHOME/bin/gdal-config \
            --with-geosconfig=$PGHOME/bin/geos-config \
            --with-projdir=$PGHOME \
            --with-jsondir=$PGHOME
make -j9
make install

# Connect to PostgreSQL and create PostGIS and SFCGAL extension
$PGHOME/bin/psql -h localhost -p XXXXX -d postgistest -c "CREATE EXTENSION postgis;"
$PGHOME/bin/psql -h localhost -p XXXXX -d postgistest -c "CREATE EXTENSION postgis_sfcgal;"

# Check PostgreSQL version: 
$PGHOME/bin/postgres --version

# Verify PostGIS installation within PostgreSQL: 
$PGHOME/bin/psql -h localhost -p XXXXX -d postgistest -c "SELECT PostGIS_Full_Version();"

Aarbormagna · Nov 25, 2023

nick Hi Nick,
Thank you very much for your time. This fixed SFCGAL dependency, but as you can see from SELECT PostGIS_Full_Version(); there is still no GDAL (very important library for raster support).

-------------------
 POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="3.10.5-CAPI-1.16.2" SFCGAL="SFCGAL 1.5.0
, CGAL 5.6, BOOST 1.83.0" PROJ="9.2.1" LIBXML="2.9.1" LIBJSON="0.17" LIBPROTOBUF="1.4.0" WAGYU=
"0.5.0 (Internal)"
(1 row)

Hopefully, there is a fix for this too as my project depends on it. Apologies for posting such a noxious problem

Nnick · Nov 25, 2023

arbormagna Try installing the raster extension:

CREATE EXTENSION postgis_raster;

And if you require topology support:

CREATE EXTENSION postgis_topology;

Confirmed:

                                                                                               
                           postgis_full_version                                                
                                                                          
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
 POSTGIS="3.3.3 2355e8e" [EXTENSION] PGSQL="150" GEOS="3.10.5-CAPI-1.16.2" SFCGAL="SFCGAL 1.5.0
, CGAL 5.6, BOOST 1.83.0" PROJ="9.2.1" GDAL="GDAL 3.6.4, released 2023/04/17" LIBXML="2.9.1" LI
BJSON="0.17" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
(1 row)

Note, you will probably want to export these variables before interacting with your PSQL server, or add them to your session via .bash_profile or .bashrc file in your base $HOME dir.

export PATH=~/opt/bin:~/apps/psql15/bin:$PATH;
export PGHOME=~/apps/psql15;
export PGDATA=~/apps/psql15/pgdata;
export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib64:$LD_LIBRARY_PATH;
export C_INCLUDE_PATH=$PGHOME/include:$C_INCLUDE_PATH;
export CPLUS_INCLUDE_PATH=$PGHOME/include:$CPLUS_INCLUDE_PATH;
export CPPFLAGS="-I$PGHOME/include $CPPFLAGS";
export LDFLAGS="-L$PGHOME/lib64 -L$PGHOME/lib $LDFLAGS"

Aarbormagna · Nov 26, 2023

nick Dear Nick, this finally did the trick . Thank you very much for your patience and time, it is highly appreciated .

Note: There is a typo in your intallation script: just add .xz to the end of tar xf gmp-6.3.0.tarcommand.

Nnick · Nov 26, 2023

arbormagna You're welcome

Note: There is a typo in your intallation script: just add .xz to the end of tar xf gmp-6.3.0.tarcommand.

All fixed, thanks for spotting that.

Let us know if there is anything else.