Installing POSTGIS locally

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

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();"