
Alexander is a writer, traveler, and serial digital entrepreneur. He’s the founder of the Tushi, the Gen X personal growth newsletter Further, and offers small business advice at Unemployable.

Twitter

Linkedin

Facebook

Director and Chief Evangelist

Alexander Hipp


Anil is a content-obsessed marketer who loves driving organic traffic for outstanding companies. He is also the creator of Hack the Content, co-host of The Runner, and owner of several digital commerce sites.

Managing Partner

Anil Vugels


Monika is a writer, musician, and content strategist with a passion for language. When she isn’t helping clients, you can find her writing songs, hiking in a northern forest, or furiously journaling.

Director of Operations

Ann Monika

Mastodon

Github

Programmer / Founder / Student

Tim Schupp


I needed a PostgreSQL setup on Kubernetes that is:

- allows automatic creation of db instances
- allow configuration based pre-defining users, passwords and configurations
- can be included into other helm charts as depencency
- single configuration, one-install, full setup approach

For this, [CloudNativePG Helm Chart](TODO) is one of the cleanest approaches I've found so far ( after the [Bitnami Postgsql Helm Chart being moved closed source]() ).

So this is the exact setup I currently use as a base template.
Everything below is copy/paste-ready and end-to-end reproducible.

### What this creates

- namespace: `app-db`
- cluster: `app-postgres`
- database: `app`
- app user: `appuser`
- app password: `apppass`
- readonly user: `readonly`
- readonly password: `readonlypass`
- table: `public.items`

### 0) Move to your project and export kubeconfig

```bash
export KUBECONFIG=./kubeconfig.yaml
kubectl get nodes
```

### 1) Install the CloudNativePG operator

```bash
helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update
helm upgrade --install cnpg cnpg/cloudnative-pg --namespace cnpg-system --create-namespace
kubectl get pods -n cnpg-system
```

Wait until the operator pod is `Running` before continuing.

### 2) Create a manifest folder

```bash
mkdir -p cnpg-app-db
```

### 3) Create namespace manifest

```bash
cat > cnpg-app-db/namespace.yaml <<'EOF'
apiVersion: v1
kind: Namespace
metadata:
  name: app-db
EOF
```

### 4) Create app credentials secret

```bash
cat > cnpg-app-db/db-secret.yaml <<'EOF'
apiVersion: v1
kind: Secret
metadata:
  name: app-db-user
  namespace: app-db
type: kubernetes.io/basic-auth
stringData:
  username: appuser
  password: apppass
EOF
```

### 5) Create PostgreSQL cluster manifest

```bash
cat > cnpg-app-db/postgres-cluster.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: app-postgres
  namespace: app-db
spec:
  instances: 2
  imageName: ghcr.io/cloudnative-pg/postgresql:17
  storage:
    size: 10Gi
  bootstrap:
    initdb:
      database: app
      owner: appuser
      secret:
        name: app-db-user
      postInitApplicationSQL:
        - CREATE USER readonly WITH PASSWORD 'readonlypass';
        - GRANT CONNECT ON DATABASE app TO readonly;
        - CREATE TABLE IF NOT EXISTS public.items (id SERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());
        - ALTER TABLE public.items OWNER TO appuser;
        - GRANT ALL PRIVILEGES ON TABLE public.items TO appuser;
        - INSERT INTO public.items (name) VALUES ('seed-row-from-bootstrap');
        - GRANT USAGE ON SCHEMA public TO readonly;
        - GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
        - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
EOF
```

### 6) Create full Python test script

```bash
cat > cnpg-app-db/test_db_query.py <<'EOF'
#!/usr/bin/env python3
import argparse
import os
import sys
from urllib.parse import quote_plus

import psycopg


def build_dsn(args: argparse.Namespace) -> str:
    if args.dsn:
        return args.dsn

    user = quote_plus(args.user)
    password = quote_plus(args.password)
    host = args.host
    port = args.port
    dbname = args.database
    return f"postgresql://{user}:{password}@{host}:{port}/{dbname}"


def main() -> int:
    parser = argparse.ArgumentParser(description="Run a test query against CloudNativePG")
    parser.add_argument("--host", default=os.getenv("PGHOST", "127.0.0.1"), help="PostgreSQL host")
    parser.add_argument("--port", default=os.getenv("PGPORT", "5432"), help="PostgreSQL port")
    parser.add_argument("--database", default=os.getenv("PGDATABASE", "app"), help="Database name")
    parser.add_argument("--user", default=os.getenv("PGUSER", "appuser"), help="Database user")
    parser.add_argument(
        "--password",
        default=os.getenv("PGPASSWORD", "apppass"),
        help="Database password",
    )
    parser.add_argument(
        "--dsn",
        default=os.getenv("DATABASE_URL"),
        help="Full PostgreSQL DSN. Overrides host/user/password options when set.",
    )
    args = parser.parse_args()
    dsn = build_dsn(args)

    try:
        with psycopg.connect(dsn) as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT current_user, current_database(), version()")
                user_name, db_name, version = cur.fetchone()
                print(f"connected_as={user_name}")
                print(f"database={db_name}")
                print(f"version={version}")

                cur.execute("SELECT id, name, created_at FROM public.items ORDER BY id")
                rows = cur.fetchall()
                print(f"items_count={len(rows)}")
                for row in rows:
                    print(f"item id={row[0]} name={row[1]} created_at={row[2]}")
    except Exception as exc:
        print(f"connection/query failed: {exc}", file=sys.stderr)
        return 1

    return 0


if __name__ == "__main__":
    raise SystemExit(main())
EOF
```

### 7) Fresh redeploy (safe reset for this cluster)

If you've tested a few times and want a clean restart, run the block below.
This recreates the setup from the manifests above.

```bash
kubectl delete cluster app-postgres -n app-db --ignore-not-found=true
kubectl delete pvc -n app-db --all --ignore-not-found=true
kubectl delete secret app-db-user -n app-db --ignore-not-found=true
kubectl apply -f cnpg-app-db/namespace.yaml
kubectl apply -f cnpg-app-db/db-secret.yaml
kubectl apply -f cnpg-app-db/postgres-cluster.yaml
```

### 8) Wait until cluster is ready

```bash
kubectl wait --for=condition=Ready cluster/app-postgres -n app-db --timeout=600s
kubectl get cluster -n app-db
kubectl get pods -n app-db
kubectl get svc -n app-db
```

Expected result:

- `app-postgres` shows healthy and ready instances
- Pods `app-postgres-1` and `app-postgres-2` are `Running`
- Services exist: `app-postgres-rw`, `app-postgres-ro`, `app-postgres-r`

### 9) Optional direct `psql` test in the pod

```bash
kubectl exec -it -n app-db app-postgres-1 -- psql -U appuser -d app
```

Then run:

```sql
SELECT current_user, current_database();
SELECT * FROM public.items;
\q
```

### 10) Install Python dependency (`psycopg`)

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install psycopg[binary]
```

### 11) Test app user (`appuser`) via RW service

Terminal 1:

```bash
kubectl port-forward -n app-db svc/app-postgres-rw 5432:5432
```

Terminal 2:

```bash
source .venv/bin/activate
python cnpg-app-db/test_db_query.py --host 127.0.0.1 --port 5432 --database app --user appuser --password apppass
```

### 12) Test readonly user (`readonly`) via RO service

Terminal 1:

```bash
kubectl port-forward -n app-db svc/app-postgres-ro 5433:5432
```

Terminal 2:

```bash
source .venv/bin/activate
python cnpg-app-db/test_db_query.py --host 127.0.0.1 --port 5433 --database app --user readonly --password readonlypass
```

### 13) Useful connection strings

```text
postgresql://appuser:apppass@app-postgres-rw.app-db.svc.cluster.local:5432/app
postgresql://readonly:readonlypass@app-postgres-ro.app-db.svc.cluster.local:5432/app
postgresql://appuser:apppass@127.0.0.1:5432/app
postgresql://readonly:readonlypass@127.0.0.1:5433/app
```

### Notes from my side

- Keeping bootstrap SQL in the cluster manifest is very convenient for small/medium setups.
- Splitting `rw` and `ro` service usage early helps avoid accidental write paths later in app code.
- For production, definitely swap the demo passwords and move secrets handling to your preferred secret manager flow.

That's it.
From here you have a reproducible CloudNativePG Postgres setup with:

- predefined users
- schema bootstrap
- seed data
- python connectivity test
- read/write and readonly validation paths

## Optional Add-On: DNS-01 cert flow

If you want to keep using your domain-based access (`test-db.<your-domain>.com`) with stronger trust setup across clients, a DNS-01 cert-manager flow is a good optional add-on.

At minimum:

1. install your DNS webhook solver
2. create a `ClusterIssuer`
3. issue certificate(s) and use them in your service exposure stack

I still keep this optional here, because CNPG already gives working Postgres TLS out of the box.

## Optional Add-On: tiny tailscale gimmick (cluster-to-cluster)

If you have tailscale operator on both clusters, you can expose this DB quickly via tailnet and consume it from another cluster using a simple `ExternalName` service.

High-level mini flow:

1. annotate exposed DB service with `tailscale.com/expose=true`
2. use tailnet hostname (`test-db.<tailnet>.ts.net`)
3. in cluster 2, create `ExternalName` -> that hostname

Useful when you just want quick private cross-cluster connectivity without extra public routing work.

Cheers Tim


A full copy/paste-ready walkthrough to install CloudNativePG, deploy PostgreSQL on Kubernetes, bootstrap users and tables, and verify with Python.

CloudNativePG PostgreSQL End-to-End Setup Guide


Deploying Postgres on Kubernetes need not be complex. With automation in place, you can deploy a secure and isolated instance of PostgreSQL within your Microk8s cluster using Bitnami's Helm chart. The updated script covers everything from provisioning TLS certificates to configuring the database for secure connections.

### Preparing the Environment

The process starts with some initial setup tasks. We create a Kubernetes namespace for the PostgreSQL instance and establish the baseline for our commands:

```bash
#!/bin/bash

KUBECMD_PREFIX="${KUBECMD_PREFIX:=microk8s}"
CERT_MANAGER_NAMESPACE="cert-manager"

$KUBECMD_PREFIX kubectl create namespace $K8_NAMESPACE || true
```

### Configuring SSL/TLS Encryption

With cert-manager installed in our cluster, we can automatically provision and manage TLS certificates. This is a crucial step in ensuring that our database communication is encrypted:

```bash
cat <<EOF | $KUBECMD_PREFIX kubectl apply -f -
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: $RELEASE_NAME-postgresql-tls
  namespace: $K8_NAMESPACE
spec:
  secretName: $RELEASE_NAME-postgresql-tls
  issuerRef:
    kind: ClusterIssuer
    name: letsencrypt-prod
  dnsNames:
  - $DB_HOSTNAME
EOF
```

It's imperative to wait for the certificate to become ready. This script assures that by incorporating a timeout for the process:

```bash
$KUBECMD_PREFIX kubectl -n $K8_NAMESPACE wait --for=condition=ready certificate $RELEASE_NAME-postgresql-tls --timeout=300s
```

### Installing PostgreSQL with Helm

Having confirmed that the TLS certificate is in place, we proceed with the Helm chart installation:

```bash
$KUBECMD_PREFIX helm install $RELEASE_NAME oci://registry-1.docker.io/bitnamicharts/postgresql \
    -n $K8_NAMESPACE \
    --set global.postgresql.auth.username="$DB_USERNAME" \
    --set global.postgresql.auth.password="$DB_PASSWORD" \
    ...
    --set tls.certificatesSecret="$RELEASE_NAME-postgresql-tls" \
    --set tls.certFilename="tls.crt" \
    --set tls.certKeyFilename="tls.key"
```

The script passes the appropriate values to Helm, signaling it to deploy Postgres with TLS enabled.

### Networking Configuration

The `ConfigMap` and `Service` definitions are applied to route external traffic to the Postgres instance:

```bash
read -r -d '' INGRESS_CONFIG << EOM
apiVersion: v1
kind: ConfigMap
metadata:
  namespace: ingress
  name: nginx-ingress-tcp-microk8s-conf
data:
  5432: "$K8_NAMESPACE/$RELEASE_NAME-postgresql:5432"
---
apiVersion: v1
kind: Service
metadata:
  name: $RELEASE_NAME-nodeport
  namespace: $K8_NAMESPACE
spec:
  type: NodePort
  ...
  nodePort: $TARGET_PORT
  ...
EOM

$KUBECMD_PREFIX kubectl apply -f - <<< "$INGRESS_CONFIG"
```

### Testing the Database Connection

Once deployed, verify the secure connection using Python and the `psycopg2` library. The script aims to establish a connection, execute a command, and then gracefully terminate the connection:

```python
import psycopg2

conn = None
try:
    conn = psycopg2.connect(
        dbname='...',
        user='...',
        password='...',
        host='...',
        port='...',
        sslmode='require'
    )

    print("Success: Connected to the database!")
    cur = conn.cursor()
    cur.execute("SELECT version();")
    record = cur.fetchone()
    print("You are connected to - ", record, "\n")
    cur.close()
except (Exception, psycopg2.DatabaseError) as error:
    print("Error: ", error)
finally:
    if conn is not None:
        conn.close()
        print("Database connection closed.")
```

By requiring `sslmode='require'`, the script confirms that the connection is not only secure but also compliant with best practices.

### Wrapping Up

The combination of Microk8s's simplicity and Helm's power simplifies the deployment of a robust PostgreSQL database. TLS encryption, managed by cert-manager, makes sure the data remains secure in transit. For developers and administrators looking to setup PostgreSQL on Kubernetes, the process is now streamlined, automated, and immeasurably more secure than before.

Do check out the full deployment script and accompanying resources on the [GitHub repository](https://github.com/tbscode/tims-blog-posts/blob/main/assets/postgress_create_dynamic_k8s.sh) and ensure your databases are secure from setup to daily operations.

This guide provides a step-by-step approach to installing a PostgreSQL database using Bitnami's Helm chart on a Microk8s cluster, including TLS configuration for enhanced security.

Setting Up a Secure Bitnami PostgreSQL Instance on Microk8s


Every now and then, when setting up a simple staging environment, the need for a quick-setup database that retains state consistently arises. In such situations, I often lean towards MariaDB, specifically utilizing the [Bitnami Helm Chart](https://github.com/bitnami/charts/tree/main/bitnami/mariadb), with slight tweaks to achieve the following:

- An auto-set, namespace setup
- Accessibility through a single host subdomain + port
- Easy hourly backups to a specified host path
- Exposure through TCP to the cluster host

### Prerequisites

This post is operating under the assumption that you have a `microk8s` Kubernetes cluster already set up, complete with `cert-manager`, `ingress`, `dns` and an active and configured `letsencrypt-prod` cluster issuer.

> Note: To jumpstart your setup, you might find [this Blog Post on Microk8s Private Cluster Setup](/blog/microk8s-on-vps) quite helpful.

### TL;DR 

For a swift deployment of this configuration:

1. [Grab this script](https://github.com/tbscode/tims-blog-posts/blob/main/assets/create_mariadb.sh)
2. Then run it as follows:

```bash
./create_mariadb.sh \
  RELEASE_NAME="<release-name>" \
  K8_NAMESPACE="<installation-namespace>" \
  DB_USERNAME="<db-username>" \
  DB_PASSWORD="<your-super-secure-password>" \
  DB_NAME="<db-name>" \
  TARGET_PORT="<desired-port>" \
  HOST_BACKUP_PATH="<your host backup volume>"
```


> Remember to expose the desired port through your firewall with a `sudo ufw allow $TARGET_PORT` command.
  
#### Uninstallation Process:

Execute the following command: 

```
microk8s helm uninstall $RELEASE_NAME
```

#### Connecting to the Database

Use this command to establish a connection to the database:

```bash
docker run -it --rm  mysql mysql --host <your-host-domain> --port <your-port> --user <your-user> --password --protocol=TCP
```

Or use this with `--network="host"` if your testing a local server.
Remember to enter the admin password when prompted.

#### Backing up the Database

Here's a simple docker command for accomplishing this:

```bash
docker run --network="host" --rm mysql:5.7 mysqldump -h localhost -P 30004 -u <your-user> -p<your-admin-password> --protocol=TCP <your-db-name> > backup.sql
```

#### Host Backup Strategy

A straightforward example of a cron job that allows your database to be backed up regularly is created as follows:

1. First, create a secret to hold your MariaDB credentials:

```bash
microk8s kubectl create secret generic mariadb-creds --from-literal=password='$DB_PASSWORD' --from-literal=username='$DB_USER' -n $K8_NAMESPACE
```

2. Then, define a CronJob in a yaml file:

```yaml
apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: backup-job
spec:
  schedule: "0 * * * *"
  jobTemplate:
    spec:
      template:
        spec:
          volumes:
            - name: backup-volume
              hostPath:
                path: "$HOST_BACKUP_PATH"
          containers:
            - name: backup-container
              image: mysql:5.7
              volumeMounts:
                - name: backup-volume
                  mountPath: /backup
              env:
                - name: MYSQL_PWD
                  valueFrom:
                    secretKeyRef:
                      name: mariadb-creds
                      key: password
                - name: MYSQL_USER
                  valueFrom:
                    secretKeyRef:
                      name: mariadb-creds
                      key: username
              command: ["sh", "-c", 'mysqldump -h database-host -P 30004 -u $MYSQL_USER --protocol=TCP database-name > /backup/backup-$(date +%Y%m%d-%H%M%S).sql']
          restartPolicy: OnFailure
```

With this configuration in place, you would have a CronJob that executes hourly backups of the database.



This straightforward guide will walk you through deploying and exposing a basic MariaDB database on your microk8s Kubernetes cluster.

Postgresql

CloudNativePG PostgreSQL End-to-End Setup Guide

Setting Up a Secure Bitnami PostgreSQL Instance on Microk8s

MariaDB on Kubernetes with Backup Cron-job