K8S PostGIS h3-pg installation

[iaguerri]

Hello! I'm trying to establish a docker image with a PostgreSQL and a PostGIS with the h3-pg installation and then deploy it in kubernetes with CloudNativePG as operator.

The dockerfile I have is this:

# Use the official PostgreSQL image as a parent image
FROM postgis/postgis:16-3.4

USER root

# Install cmake >3.20
RUN apt-get update && \
    apt-get install -y \
    wget && \
    wget -qO- "https://cmake.org/files/v3.21/cmake-3.21.3-linux-x86_64.tar.gz" | tar --strip-components=1 -xz -C /usr/local && \
    apt-get remove -y wget && \
    apt-get autoremove -y && \
    rm -rf /var/lib/apt/lists/*

# Install dependencies required for building h3-pg
RUN apt-get update && \
    apt-mark hold locales && \
    apt-get install -y \
    build-essential \
    cmake \
    postgresql-server-dev-16 \
    postgresql-16-h3 \
    postgresql-16-postgis-3 \
    pgxnclient

RUN pgxn install h3
RUN pgxn load h3

RUN apt-get remove -y build-essential postgresql-server-dev-16 cmake && \
    apt-get autoremove -y && \
    apt-mark unhold locales && \
    rm -rf /var/lib/apt/lists/*

Build the image:

docker build -t postgis-h3 .

And then deploy with this yaml

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgis-h3
spec:
  instances: 1
  imageName: docker.io/iaguerri/pgh3:16

  storage:
    size: 1Gi

The first error happens in this line: RUN pgxn load h3 and the error is

=> ERROR [5/7] RUN pgxn load h3                                                                                                                                     1.0s
------                                                                                                                                                                    
 > [5/7] RUN pgxn load h3:                                                                                                                                                
0.702 INFO: best version: h3 4.1.3                                                                                                                                        
0.891 psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
0.891   Is the server running locally and accepting connections on that socket?
0.894 ERROR: psql returned 2 running command
------

I did a image with that line commented and deployed the kubectl but when starting up and entering in psql:

kubectl exec -ti postgis-h3-1 -- psql app
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
psql (16.2 (Debian 16.2-1.pgdg110+2))
Type "help" for help.

app=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

app=# \l
                                                  List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+---------+-------+------------+-----------+-----------------------
 app       | app      | UTF8     | libc            | C       | C     |            |           | 
 postgres  | postgres | UTF8     | libc            | C       | C     |            |           | 
 template0 | postgres | UTF8     | libc            | C       | C     |            |           | =c/postgres          +
           |          |          |                 |         |       |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C     |            |           | =c/postgres          +
           |          |          |                 |         |       |            |           | postgres=CTc/postgres

but listing the packages installed in the container, they do appear:

i  postgresql-16                      16.2-1.pgdg110+2               amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-16-h3                   4.1.3-3.pgdg110+1              amd64        PostgreSQL bindings for H3, a hierarchical hexagonal geospatial indexing system
ii  postgresql-16-pg-failover-slots    1.0.1-2.pgdg110+1              amd64        High-availability support for PostgreSQL logical replication
ii  postgresql-16-pgaudit              16.0-1.pgdg110+1               amd64        PostgreSQL Audit Extension
ii  postgresql-16-pgrouting            3.6.1-1.pgdg110+1              amd64        Routing functionality support for PostgreSQL/PostGIS
ii  postgresql-16-pgrouting-scripts    3.6.1-1.pgdg110+1              all          Routing functionality support for PostgreSQL/PostGIS - SQL scripts
ii  postgresql-16-postgis-3            3.4.2+dfsg-1.pgdg110+1         amd64        Geographic objects support for PostgreSQL 16
ii  postgresql-16-postgis-3-scripts    3.4.2+dfsg-1.pgdg110+1         all          Geographic objects support for PostgreSQL 16 -- SQL scripts

How is possible to install h3-pg in a kubernetes pod?

Thanks,

[zachasme]

Hi @iaguerri!

I'm not that familiar with kubernetes. But I do know that the error

=> ERROR [5/7] RUN pgxn load h3                                                                                                                                     1.0s
------                                                                                                                                                                    
 > [5/7] RUN pgxn load h3:                                                                                                                                                
0.702 INFO: best version: h3 4.1.3                                                                                                                                        
0.891 psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
0.891   Is the server running locally and accepting connections on that socket?
0.894 ERROR: psql returned 2 running command
------

is because you are trying to run pgxn load during the docker build step. This is not possible because at that stage the postgres server is not yet running (which the error message is also hinting at).

Have you tried loading the extension on a running container (Using either CREATE EXTENSION or pgxn load.)?