PostGIS on YugabyteDB Alma8 (workarounds)

PostgreSQL database supports spatial functions with PostGIS. This extension was tested on CentOS 7. Here is an attempt to install it on the docker image based on Alma8.

Install on Alma8

I'm running this on a YugabyteDB 2.19 container started with:

docker run -it yugabytedb/yugabyte:2.19.2.0-b121 bash

As this version is PG11 compatible, I install postgresql11-devel and postgis33_11. I choose version 33 as the latest version (found with dnf search postgis | grep -E '^postgis[0-9]+_11[.]' | sort -n).

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
dnf update -y
dnf install -y dnf-plugins-core
dnf config-manager --set-enabled powertools
dnf -qy module disable postgresql
dnf install -y postgresql11-devel

dnf install -y postgis31_11

Once installed I copy the files to the YugabyteDB directory

alias pg_config=/usr/pgsql-11/bin/pg_config
alias yb_pg_config=$YB_HOME/postgres/bin/pg_config

cp "$(pg_config --pkglibdir)"/postgis* "$(yb_pg_config --pkglibdir)"
cp "$(pg_config --sharedir)"/extension/postgis* "$(yb_pg_config --sharedir)/extension"

$YB_HOME/bin/post_install.sh -e

Start YugabyteDB and test

yugabyted start
export PGHOST=$(hostname)
until $YB_HOME/postgres/bin/pg_isready ; do sleep 1 ; done | uniq
$YB_HOME/bin/ysqlsh -ec "create extension postgis"

Unfortunately, this fails with:

ERROR:  could not load library "/home/yugabyte/postgres/lib/postgis-3.so": /home/yugabyte/linuxbrew/lib/libc.so.6: version `GLIBC_2.28' not found (required by /home/yugabyte/postgres/../lib/yb-thirdparty/libxml2.so.2)

The problem here is that YugabyteDB uses GLIBC 2.23:

sh-4.4# ls $YB_HOME/linuxbrew/lib/libc*so
/home/yugabyte/linuxbrew/lib/libc-2.23.so
sh-4.4#

But when running post_install.sh to link the new PostGIS libraries, some dependencies were taken from the system with higher GLIBC:

objdump -T $YB_HOME/lib/yb-thirdparty/* | awk '/^[/]/{f=$1} $0 ~ re{print gensub(re,"\\1 "f" \\2",1)}' re="^.* GLIBC_([0-9.]+) (.*)$" | sort -uV

...
2.23 /home/yugabyte/lib/yb-thirdparty/libquadmath.so.0:  __signgam
2.25 /home/yugabyte/lib/yb-thirdparty/libbsd.so.0:  __explicit_bzero_chk
2.25 /home/yugabyte/lib/yb-thirdparty/libcrypt.so.1:  getentropy
2.25 /home/yugabyte/lib/yb-thirdparty/libcrypt.so.1:  getrandom
2.25 /home/yugabyte/lib/yb-thirdparty/libcrypt.so.1:  __explicit_bzero_chk
2.25 /home/yugabyte/lib/yb-thirdparty/libexpat.so.1:  getrandom
2.26 /home/yugabyte/lib/yb-thirdparty/liburiparser.so.1:  reallocarray
2.27 /home/yugabyte/lib/yb-thirdparty/libIlmImf-2_2.so.22:  logf
2.27 /home/yugabyte/lib/yb-thirdparty/libarpack.so.2:  powf
2.27 /home/yugabyte/lib/yb-thirdparty/libgdal.so.30:  logf
2.27 /home/yugabyte/lib/yb-thirdparty/libgfortran.so.5:  expf
2.27 /home/yugabyte/lib/yb-thirdparty/libgfortran.so.5:  logf
2.27 /home/yugabyte/lib/yb-thirdparty/libhdf5.so.103:  powf
2.27 /home/yugabyte/lib/yb-thirdparty/liblapack.so.3:  logf
2.27 /home/yugabyte/lib/yb-thirdparty/liblapack.so.3:  powf
2.27 /home/yugabyte/lib/yb-thirdparty/libopenblaso.so.0:  expf
2.27 /home/yugabyte/lib/yb-thirdparty/libopenblaso.so.0:  logf
2.27 /home/yugabyte/lib/yb-thirdparty/libopenblaso.so.0:  powf
2.27 /home/yugabyte/lib/yb-thirdparty/libopenblasp.so.0:  expf
2.27 /home/yugabyte/lib/yb-thirdparty/libopenblasp.so.0:  logf
2.27 /home/yugabyte/lib/yb-thirdparty/libopenblasp.so.0:  powf
2.27 /home/yugabyte/lib/yb-thirdparty/libsatlas.so.3:  logf
2.27 /home/yugabyte/lib/yb-thirdparty/libsatlas.so.3:  powf
2.27 /home/yugabyte/lib/yb-thirdparty/libxerces-c-3.2.so:  logf
2.28 /home/yugabyte/lib/yb-thirdparty/libcfitsio.so.7:  fcntl64
2.28 /home/yugabyte/lib/yb-thirdparty/libsqlite3.so.0:  fcntl64
2.28 /home/yugabyte/lib/yb-thirdparty/libxml2.so.2:  fcntl64

Workaround1 add libraries from Centos7

This has been already troubleshooted by Giovanni Candido da Silva who shared how to build an image with YugabyteDB and PostGIS by adding the libraries libsqlite3.so.0 and libxml2.so.2 taken from Centos7:
https://github.com/giovannicandido/yugabytedb-postgis

I'm using a similar workaround here by taking them from the Centos7 image:

docker run -i centos:7 bash -c "tar -C /usr/lib64 -h -cf - libsqlite3.so.0 libxml2.so.2.9.1" |
  docker exec -i 597b5077f0b2 bash -c "tar -C /home/yugabyte/postgres/../lib/yb-thirdparty -xvf -"

Now, I can load the PostGIS extension:

[root@597b5077f0b2 yugabyte]# $YB_HOME/bin/ysqlsh -ec "create extension postgis"
create extension postgis
WARNING:  'analyze' is a beta feature!
HINT:  Set 'ysql_beta_features' yb-tserver gflag to true to suppress the warning for all beta features.
CREATE EXTENSION
[root@597b5077f0b2 yugabyte]#

This is a workaround, not supported. I've opened the following issue to get it solve in the YugabyteDB deployment:
https://github.com/yugabyte/yugabyte-db/issues/19389

Workaround 2: Install on Centos7 and copy to Alma8

Here is a Dockerfile that installs PostGIS on Centos7 (the YugabyteDB 2.15.3 image was the last one on this OS) and copies the files and dependent libraries to the Alma8 (the latest YugabyteDB image) image:

# get the latest YugabyteDB image that was deployed on Centos7
FROM yugabytedb/yugabyte:2.15.3.2-b1 as postgis-on-centos7
RUN yum update -y
# install PostGIS into PostgreSQL directory
RUN yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
RUN yum -y install postgresql11-server postgis31_11 postgis31_11-client
# store the names of existing files in YugabyteDB directory
RUN find /home/yugabyte -type f > /tmp/files-before.txt
# install PostGIS into YugabyteDB directory and relink with GLIBC
RUN cp /usr/pgsql-11/lib/postgis* /home/yugabyte/postgres/lib
RUN cp /usr/pgsql-11/share/extension/postgis* /home/yugabyte/postgres/share/extension
RUN /home/yugabyte/bin/post_install.sh -e
# keep only files added by PostGIS and dependencies and tar them
RUN rm $(cat /tmp/files-before.txt)
RUN tar -cvf /tmp/postgis.tar /home/yugabyte
# get the latest YugabyteDB image (on Alma8)
FROM yugabytedb/yugabyte:latest
# get PostGIS files without overriding anything
COPY --from=postgis-on-centos7 /tmp/postgis.tar /tmp/postgis.tar
RUN tar --skip-old-files -C / -xvf /tmp/postgis.tar
RUN /home/yugabyte/bin/post_install.sh -e

Again, this is a workaround, not supported, until #19389 is fixed.