Skip to content

Databases

CloudNative-PG (PostgreSQL)

CloudNative-PG runs a PostgreSQL 17.7 high-availability cluster with 3 instances.

Architecture

kubernetes/apps/database/cloudnative-pg/
├── app/                    # Operator deployment
│   ├── helmrelease.yaml
│   └── ocirepository.yaml
├── cluster/               # PostgreSQL cluster definition
│   ├── cluster.yaml       # Main cluster spec
│   ├── scheduledbackup.yaml
│   ├── objectstore.yaml   # S3 backup config
│   └── externalsecret.yaml
└── recovery/              # Disaster recovery configs
    └── cluster.yaml

Configuration

Setting Value
Instances 3 (HA with pod anti-affinity)
Storage 20Gi per instance (openebs-hostpath)
Max connections 200
Shared buffers 256MB
Effective cache size 512MB
Maintenance work mem 128MB
CPU request 100m
Memory request 512Mi
Memory limit 2Gi

Backups

  • WAL archiving to Garage S3 via barman-cloud plugin
  • Scheduled backups with configurable retention
  • Monitoring via PodMonitor for Prometheus

Connecting

Applications connect via the internal service:

postgres-rw.database.svc.cluster.local:5432

Recovery

A recovery cluster definition exists at kubernetes/apps/database/cloudnative-pg/recovery/cluster.yaml for disaster recovery scenarios.

Onboarding a new app

The repository's standard pattern is one shared cluster, per-app database + role, provisioned by an init-db initContainer using ghcr.io/home-operations/postgres-init. The init container reads INIT_POSTGRES_SUPER_PASS (from the cloudnative-pg 1Password item) and creates the database and a non-superuser role for the app.

initContainers:
  init-db:
    image:
      repository: ghcr.io/home-operations/postgres-init
      tag: 18@sha256:...
    envFrom:
      - secretRef:
          name: <app>-secret

The matching ExternalSecret template:

template:
  data:
    INIT_POSTGRES_DBNAME: "{{ .APP_POSTGRES_DB }}"
    INIT_POSTGRES_HOST: postgres-rw.database.svc.cluster.local
    INIT_POSTGRES_PORT: "5432"
    INIT_POSTGRES_USER: "{{ .APP_POSTGRES_USER }}"
    INIT_POSTGRES_PASS: "{{ .APP_POSTGRES_PASSWORD }}"
    INIT_POSTGRES_SUPER_PASS: "{{ .POSTGRES_SUPER_PASS }}"

Apps then connect with their own role (DATABASE_USER/DATABASE_PASS).

Apps that need PostgreSQL extensions

CREATE EXTENSION for "trusted" extensions like pgcrypto or uuid-ossp works as a regular role, but cube, earthdistance, postgis, pg_stat_statements, etc. require superuser. The per-app role created by init-db is intentionally not a superuser, so any migration that tries to install these extensions will fail with:

ERROR 42501 (insufficient_privilege) permission denied to create extension "earthdistance"
hint: Must be superuser to create this extension.

Pre-create the extensions in a second initContainer that connects as the postgres superuser. Example from TeslaMate (kubernetes/apps/observability/teslamate/app/helmrelease.yaml):

initContainers:
  init-db:
    image:
      repository: ghcr.io/home-operations/postgres-init
      tag: 18@sha256:...
    envFrom:
      - secretRef:
          name: teslamate-secret
  init-extensions:
    image:
      repository: ghcr.io/home-operations/postgres-init
      tag: 18@sha256:...
    command:
      - /bin/sh
      - -c
      - |
        PGPASSWORD="$INIT_POSTGRES_SUPER_PASS" psql \
          -h "$INIT_POSTGRES_HOST" \
          -p "$INIT_POSTGRES_PORT" \
          -U postgres \
          -d "$INIT_POSTGRES_DBNAME" \
          -v ON_ERROR_STOP=1 \
          -v app_user="$INIT_POSTGRES_USER" <<'SQL'
        -- 1. Install + upgrade the non-trusted geo extensions as superuser.
        CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
        CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA public;
        ALTER EXTENSION earthdistance UPDATE;

        -- 2. Transfer ownership of every cube/earthdistance member
        --    function to the app role so the migration's ALTER FUNCTION
        --    calls succeed when the app runs them as itself.
        SELECT 'ALTER FUNCTION ' || objid::regprocedure::text
               || ' OWNER TO ' || quote_ident(:'app_user') || ';'
        FROM pg_depend
        WHERE classid='pg_proc'::regclass AND deptype='e'
          AND refclassid='pg_extension'::regclass
          AND refobjid IN (SELECT oid FROM pg_extension
                           WHERE extname IN ('cube','earthdistance'))
        \gexec

        -- 3. Pre-create Ecto's schema_migrations table and stamp the
        --    superuser-only migrations as already applied so the app
        --    skips them. The DDL they would run has been done above.
        CREATE TABLE IF NOT EXISTS public.schema_migrations (
          version     bigint       PRIMARY KEY,
          inserted_at timestamp(0) WITHOUT TIME ZONE NOT NULL
        );
        ALTER TABLE public.schema_migrations OWNER TO :"app_user";

        INSERT INTO public.schema_migrations (version, inserted_at) VALUES
          (20240929084639, NOW()),  -- recreate_geo_extensions
          (20250407155134, NOW())   -- upgrade_earthdistance
        ON CONFLICT (version) DO NOTHING;
        SQL
    envFrom:
      - secretRef:
          name: teslamate-secret

Notes:

  • Reuse the postgres-init image — it ships psql and matches the existing pattern.
  • Connect as -U postgres, not as the app's role, so the CREATE EXTENSION succeeds.
  • The whole script is idempotent: CREATE EXTENSION IF NOT EXISTS, ALTER EXTENSION ... UPDATE (no-op when current), \gexec-driven ownership transfer (no-op when the role already owns it), CREATE TABLE IF NOT EXISTS, and INSERT ... ON CONFLICT DO NOTHING. Safe to re-run on every pod restart.
  • Set -v ON_ERROR_STOP=1 so a failed step aborts the init and surfaces the error in pod events instead of silently letting the app start with a broken schema.
  • Don't promote the app role to SUPERUSER. It looks tempting but PostgreSQL's SUPERUSER attribute is cluster-wide, not per-database — the app role would gain read/write access to every other CNPG-backed database in the same cluster (kguardian, teslamate, forgejo, …). In a shared-cluster setup that's an unacceptable blast radius.
  • GRANT postgres TO app doesn't help for CREATE EXTENSION. Role membership inherits privileges (table GRANTs, etc.) but not role attributes like SUPERUSER, and CREATE EXTENSION on non-trusted extensions checks the SUPERUSER attribute.
  • Why the fake-stamp pattern works: Ecto records applied migrations in schema_migrations. If a migration's version is already in that table when the migrator boots, Ecto skips it. Pre-create the table (matching the DDL Ecto would emit), do the superuser DDL yourself as postgres, and INSERT the version. The app then sees no work to do.
  • What you must keep in sync when the upstream app adds a new superuser-requiring migration: replicate its DDL in step 1/2 above, and add its version to the INSERT in step 3. The migration list as of TeslaMate 3.0.0:
  • 20190925152807_create_geo_extensions — not skipped; runs fine as the app role once function ownership is transferred (the inner CREATE EXTENSION IF NOT EXISTS short-circuits before its privilege check, the ALTER FUNCTION calls succeed because of the ownership transfer, and the CREATE INDEX runs as the table owner).
  • 20240929084639_recreate_geo_extensions — skipped; DROP EXTENSION cube CASCADE requires extension ownership which can't be granted.
  • 20250407155134_upgrade_earthdistance — skipped; we run the same ALTER EXTENSION earthdistance UPDATE as postgres in step 1.

MariaDB Operator (MariaDB Galera)

MariaDB Operator runs a MariaDB 11.7 high-availability Galera cluster with 3 instances.

Architecture

kubernetes/apps/database/mariadb-operator/
├── app/                       # Operator deployment
│   ├── helmrelease-crds.yaml  # CRDs HelmRelease
│   ├── helmrelease.yaml       # Operator HelmRelease
│   ├── helmrepository.yaml    # Helm repo source
│   └── kustomization.yaml
├── cluster/                   # MariaDB Galera cluster
│   ├── mariadb.yaml          # MariaDB CR (Galera)
│   ├── backup.yaml           # Scheduled S3 backup
│   ├── externalsecret.yaml   # 1Password credentials
│   └── kustomization.yaml
└── ks.yaml                   # Flux Kustomizations

Configuration

Setting Value
Instances 3 (Galera multi-master with pod anti-affinity)
Storage 20Gi per instance (openebs-hostpath)
Max connections 200
InnoDB buffer pool 256MB
Max allowed packet 256MB
CPU request 100m
Memory request 512Mi
Memory limit 2Gi

Backups

  • Scheduled backups to Garage S3 every 6 hours (0 */6 * * *)
  • Retention: 30 days
  • Compression: bzip2
  • S3 bucket: mariadb-backups (prefix galera)
  • Method: mysqldump with --single-transaction --all-databases

Connecting

Applications connect via internal services:

# All instances (load-balanced)
mariadb.database.svc.cluster.local:3306

# Primary only
mariadb-primary.database.svc.cluster.local:3306

# Read replicas
mariadb-secondary.database.svc.cluster.local:3306

Operator Installation

The operator is installed via two separate HelmReleases from the helm.mariadb.com Helm repository:

  1. mariadb-operator-crds — installs Custom Resource Definitions
  2. mariadb-operator — installs the controller (depends on CRDs)

The operator includes Prometheus metrics via ServiceMonitor and cert-manager webhook integration.

FreePBX Databases

The MariaDB cluster hosts FreePBX databases managed via operator CRs in kubernetes/apps/voip/freepbx/database/:

Resource Name Purpose
Database b1_asterisk Main Asterisk configuration
Database b1_asteriskcdrdb Call Detail Records
User freepbx Application user (max 100 connections)
Grant ALL PRIVILEGES Full access on both databases

Dragonfly

Dragonfly is a modern Redis-compatible in-memory datastore:

  • Deploys the Dragonfly Operator for managing instances
  • Higher performance alternative to Redis/Valkey
  • Used by applications requiring fast caching or session storage

DBGate

DBGate provides a web UI for database management:

  • Located in kubernetes/apps/database/dbgate/
  • Kanidm SSO integration for authentication
  • Accessible via Envoy Gateway