[YSQL] Postgis extension can't be installed using method from docs

[tlaguz]

Jira Link: DB-2997

Description

Yugabyte version: 2.15.0.1

I'm trying to install postgis on yugabyte running inside Debian 11 container. It turns out that postgis must be compiled to work with postgresql 11 in order to work with yugabyte. The only version available in postgresql repositories is 3.2.1.

1st problem: #11487

This can be easily fixed by removing pgaudit check that's causing the error:

sed -ie '/-- Check that pgaudit is disabled or not installed/,+18d' /home/yugabyte/postgres/share/extension/postgis--3.2.1.sql

2nd problem

It seems that postgis 3.2.1 is not compatible with libc bundled with yugabyte:

[XX000] ERROR: could not load library "/home/yugabyte/postgres/lib/postgis-3.so": /home/yugabyte/linuxbrew/lib/libm.so.6: version `GLIBC_2.29' not found (required by /home/yugabyte/postgres/lib/postgis-3.so)

I haven't found a workaround.

Dockerfile

FROM debian:bullseye

MAINTAINER tlaguz
ENV container=yugabyte-db

ENV YB_HOME=/home/yugabyte
WORKDIR $YB_HOME

ENV PATH="${YB_HOME}/bin:${PATH}"

RUN apt-get update \
    && apt-get install -y --no-install-recommends wget gnupg2 ca-certificates file python-is-python3 python3 \
    && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - \
    && echo "deb http://apt.postgresql.org/pub/repos/apt bullseye-pgdg main" > /etc/apt/sources.list.d/pgdg.list \
    && apt-get update \
    && apt-get upgrade -y \
    && apt-get install -y --no-install-recommends postgresql-11-postgis \
    && wget https://downloads.yugabyte.com/releases/2.15.0.1/yugabyte-2.15.0.1-b4-linux-x86_64.tar.gz -O /tmp/yuga.tar.gz \
    && tar -xvf /tmp/yuga.tar.gz --strip 1 \
    && rm -f /tmp/yuga.tar.gz \
    && ./bin/post_install.sh \
    && cp -v "$(pg_config --pkglibdir)"/*postgis*.so "$(./postgres/bin/pg_config --pkglibdir)" \
    && cp -v "$(pg_config --sharedir)"/extension/*postgis*.sql "$(./postgres/bin/pg_config --sharedir)"/extension \
    && cp -v "$(pg_config --sharedir)"/extension/*postgis*.control "$(./postgres/bin/pg_config --sharedir)"/extension \
    && ./bin/post_install.sh -e \
    && apt-get purge -y gnupg2 ca-certificates wget \
    && apt-get autoremove -y \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

# Patch fixing https://github.com/yugabyte/yugabyte-db/issues/11487
RUN sed -ie '/-- Check that pgaudit is disabled or not installed/,+18d' /home/yugabyte/postgres/share/extension/postgis--3.2.1.sql

[densone]

Just found this searching. I can confirm we have the same issue on Ubuntu.

yugabyte=# create extension postgis;
ERROR:  could not load library "/usr/yugabyte-2.14.0.0/postgres/lib/postgis-3.so": /usr/yugabyte-2.14.0.0/linuxbrew/lib/libm.so.6: version `GLIBC_2.29' not found (required by /usr/yugabyte-2.14.0.0/postgres/lib/postgis-3.so)
yugabyte=# 

Ubuntu 22.04 is running a much newer version of glibc

ldd (Ubuntu GLIBC 2.35-0ubuntu3) 2.35
Copyright (C) 2022 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.

[ddorian]

cc @svarnau ?

[FranckPachot]

Here are the steps that were successful for another user:

1. (Disabled pg audit) sed -ie '/-- Check that pgaudit is disabled or not installed/,+18d' /home/yugabyte/postgres/share/extension/postgis--3.2.1.sql
2. Downgraded Ubuntu from 22.04 and 20.04 and 18 now and repeated the step1
3. Now the GLIBC_2.27 with Ubuntu 18.
4. Rerun the postinstall.sh otherwise its not working.
5. Its working now and tested with couple of GIS data types

[jhg03a]

Requiring a downgrade to your OS isn't really a workaround

[FranckPachot]

Requiring a downgrade to your OS isn't really a workaround

Sure. But this is only in a staging environment to build the extension with the same GLIBC than the one used by YugabyteDB.

[jhg03a]

I found a workaround as well for Rocky 8.8 in #17747 where you don't have to deal with downgrading glibc. I don't know if it would work for ubuntu, but might be worth a shot or at least a similar pattern to attempt.