CloudNativePG with Citus

README.md

We wanted to transition to using the CloudNativePG for our in-clusted database management. Problem was that we were using Citus's columar for data storage. This extension is not available in the default Docker image.

One cannot simply use Citus Docker image as CNPG has some requirements for the image itself.

Installation

Prepare the image we want to use

docker build . -t registry.gitlab.com/ampiato/ampiato/postgresql-cnpg:16.6-11-bullseye
docker push registry.gitlab.com/ampiato/ampiato/postgresql-cnpg:16.6-11-bullseye

Setup CNPG to use the image.

kubectl create namespace postgres
kubectl apply -f cluster_image_catalog.yml

Then restart the cnpg manager such that it reloads the ConfigMap

kubectl delete pod -n cnpg-system cnpg-cloudnative-pg-77f488cf89-flbds

And finally create the cluster itself.

kubectl apply -f cluster.yml

Load the extension

❯ kubectl cnpg psql postgres-cluster                                                                                                                                                                    ─╯
psql (16.6 (Debian 16.6-1.pgdg110+1))
Type "help" for help.

postgres=# create extension citus;
CREATE EXTENSION
postgres=# 

Verify

❯ kubectl cnpg psql postgres-cluster                                                                                                                                                                    ─╯
psql (16.6 (Debian 16.6-1.pgdg110+1))
Type "help" for help.

postgres=# SELECT * FROM pg_extension;
  oid  |    extname     | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+----------------+----------+--------------+----------------+------------+-----------+--------------
 13541 | plpgsql        |       10 |           11 | f              | 1.0        |           | 
 16401 | citus_columnar |       10 |           11 | f              | 11.3-1     |           | 
 16457 | citus          |       10 |           11 | f              | 12.1-1     |           | 
(3 rows)```

## Secrets

You need to provide the pull-secret that can access the docker image you just built. CNPG will copy its content to `postgres-cluster-pull`. 
I guess you can delete the original secret after that.

cluster.yml

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-cluster
  namespace: postgres
spec:
  instances: 2

  storage:
    storageClass: hcloud-volumes
    size: 40Gi

  monitoring:
    enablePodMonitor: true

  imageCatalogRef:
    apiGroup: postgresql.cnpg.io
    kind: ClusterImageCatalog
    name: postgresql
    major: 16
    
  postgresql:
    shared_preload_libraries:
      - citus

  managed:
    roles:
    - name: ampiatostaging
      ensure: present
      comment: Ampiato Staging
      login: true
      superuser: false

cluster_image_catalog.yml

apiVersion: v1
data:
  .dockerconfigjson: <YOUR_KEY_GOES_HERE>
kind: Secret
metadata:
  name: ampiato-gitlab-docker-secret
  namespace: cnpg-system
type: kubernetes.io/dockerconfigjson
---

apiVersion: v1
kind: ConfigMap
metadata:
  name: cnpg-controller-manager-config
  namespace: cnpg-system
data:
  PULL_SECRET_NAME: ampiato-gitlab-docker-secret
---

apiVersion: postgresql.cnpg.io/v1
kind: ClusterImageCatalog
metadata:
  name: postgresql
spec:
  images:
    - major: 16
      image: registry.gitlab.com/ampiato/ampiato/postgresql-cnpg:16.6-11-bullseye

Dockerfile

FROM ghcr.io/cloudnative-pg/postgresql:16.6-11-bullseye@sha256:dc7bd9127c1b7055760a1201efda181ef16903326597935796591cbc5e13a73b

USER root

RUN apt-get update \
    && apt-get install -y --no-install-recommends \
        postgresql-server-dev-16 \
        build-essential \
        autoconf flex git libcurl4-gnutls-dev libicu-dev \
        libkrb5-dev liblz4-dev libpam0g-dev libreadline-dev \
        libselinux1-dev libssl-dev libxslt1-dev libzstd-dev \
        make uuid-dev \
    && rm -rf /var/lib/apt/lists/*


RUN git clone https://github.com/citusdata/citus.git \
    && cd citus \
    && git fetch --all --tags \
    && git checkout tags/v12.1.5 -b v12.1.5 \
    && ./configure \
    && make -j $(nproc) \
    && make install


RUN echo "shared_preload_libraries='citus'" >> /usr/share/postgresql/postgresql.conf.sample
COPY 001-create-citus-extension.sql /docker-entrypoint-initdb.d/

# Change to the uid of postgres (26)
USER 26