What is CockroachDB?

CockroachDB is a fault tolerant database that scales horiontally for reliability. It can self heal and recover from lost nodes. Its SQL is Postgresql compatible.

Why CockroachDB

It has been proven to be reliable and production ready. The Postgres compatibility means it can easily be dropped into application already using Postgres.

As a heavy user of Postgres it seems an easy transition for the architecture that I work with.

Kubernetes cluster

You can install CockroachDB on your own hardware and many other options but this guide covers running in a Kubernetes cluster.

Feel free create a cluster with one of the managed service by the big cloud providers, another 3rd party provider or roll your own.

Refer to the provider references for options

Kubernetes CLI

You need the CLI for Kubernetes, kubectl, installed locally, and configured to talk to your cluster.

Refer to the provider CLI references for easy provider credentials

brew install kubernetes-cli

sudo snap install kubectl --classic

Helm

This set up assumes Helm is used directly or via Flux to install packages into Kubernetes.

brew install helm

And then install the stable chart repository.

helm repo add stable \
   https:// kubernetes-charts.storage.googleapis.com/

You can install CockroachDB without Helm, but it requires more steps. Full details are available in the CockroachDB documentation.

FluxCD

Optional. FluxCd is a way to manage a cluster via GitOps.

For help setting up Flux have a look at my Lemmings repository.

Via Helm

Installing the CockroachDB package directly via Helm. (not using Flux)

kubectl apply -f cockroach/rbac-cockroach.yml

You need to specify some custom memory related values for the database.

vi cockroach/my-roach-values.yml

statefulset:
  replicas: 3
  resources:
    limits:
      memory: "4Gi"
    requests:
      memory: "4Gi"
conf:
  cache: "1024Mi"
  max-sql-memory: "1024Mi"

helm install myroach \
   --values cockroach/my-roach-values.yaml stable/cockroachdb

git add cockroach/rbac-cockroach.yml

vi cockroach/helm-cockroach.yml

apiVersion: helm.fluxcd.io/v1
kind: HelmRelease
metadata:
  name: cockroachdb
  annotations:
    fluxcd.io/automated: "false"
spec:
  releaseName: myroach
  chart:
    repository: https://kubernetes-charts.storage.googleapis.com
  name: cockroachdb
  version: 3.0.6
  values:
    statefulset:
      replicas: 3
      resources:
        limits:
          memory: "4Gi"
        requests:
          memory: "4Gi"
    conf:
      cache: "1024Mi"
      max-sql-memory: "1024Mi"
  tls:
    enabled: true

git add cockroach/helm-cockroach.yml

kubectl get pods

NAME                           READY STATUS                RESTARTS AGE
myroach-cockroachdb-0          0/1   Init:0/1              0        2m19s
myroach-cockroachdb-1          0/1   Init:0/1              0        2m19s
myroach-cockroachdb-init-j2dgc 0/1   Init:CrashLoopBackOff 4        3m3s

kubectl get pvc,pv,services

kubectl get csr

NAME                               AGE   REQUESTOR CONDITION
default.client.root                10m   s...b     Pending
default.node.myroach-cockroachdb-0 8m15s s...b     Pending
default.node.myroach-cockroachdb-1 8m29s s...b     Pending

kubectl describe csr default.node.myroach-cockroachdb-0

kubectl certificate approve default.node.myroach-cockroachdb-0

NAME                               AGE   REQUESTOR CONDITION
default.client.root                10m   s...b     Pending
default.node.myroach-cockroachdb-0 8m15s s...b     Approved,Issued
default.node.myroach-cockroachdb-1 8m29s s...b     Approved,Issued

Root connection

You need to set up a client connection for you to access the database as the root user. This uses the root client certificate.

apiVersion: apps/v1
kind: Pod
metadata:
  name: myroach-client-secure
  labels:
    app: myroach-client
spec:
  serviceAccountName: myroach
  initContainers:
  - name: init-certs
    image: cockroachdb/cockroach-k8s-request-cert:0.4
    imagePullPolicy: IfNotPresent
    command:
    - "/bin/ash"
    - "-ecx"
    - "/request-cert -user=root -namespace=${POD_NAMESPACE} -certs-dir=/cockroach-certs -type=client -symlink-ca-from=/var/run/secrets/kubernetes.io/serviceaccount/ca.crt"
    env:
    - name: POD_NAMESPACE
      valueFrom:
        fieldRef:
          fieldPath: metadata.namespace
    volumeMounts:
    - name: client-certs
      mountPath: /cockroach-certs
  containers:
  - name: myroach-client
    image: cockroachdb/cockroach:v19.2.4
    imagePullPolicy: IfNotPresent
    volumeMounts:
    - name: client-certs
      mountPath: /cockroach-certs
    command:
    - sleep
    - "2147483648" # 2^31
  terminationGracePeriodSeconds: 0
  volumes:
  - name: client-certs
    emptyDir: {}

Apply it to your cluster and check the CSRs and approve the certificate if not already approved.

kubectl get csr

You can now connect to the database with:

kubectl exec -it myroach-client-secure \
  -- ./cockroach sql \
  --certs-dir=/cockroach-certs \
  --host=myroach-public

Create database, user and grants

CREATE DATABASE myappdb;
CREATE USER myappuser WITH PASSWORD 'somerandompassword';
GRANT CREATE,DROP ON DATABASE myappdb TO myappuser;

If you later on want to use the Cockroach Admin UI with this user, you need to add a admin role to it:

INSERT INTO system.role_members (role, member, "isAdmin")
VALUES ('admin', 'myappuser', true);

If you get grant permission problems later on, consider this:

SHOW GRANTS ON DATABASE myappdb;
GRANT ALL ON TABLE myappdb.* TO myappuser;
GRANT ALL ON SEQUENCE myappdb.* TO myappuser;
SHOW GRANTS ON TABLE myappdb.*;

Client pod certificates

The certificate creation is done by inlining an init container per pod/deployment. Similar to the root connection earlier but with a different username, and no need for a CockroachDB client container.

apiVersion: apps/v1
kind: Deployment
...
spec:
  ...
    spec:
      serviceAccountName: myroach
      volumes:
      - name: client-certs
        emptyDir: {}
      initContainers:
      - name: init-certs
        image: cockroachdb/cockroach-k8s-request-cert:0.4
        imagePullPolicy: IfNotPresent
        command:
        - "/bin/ash"
        - "-ecx"
        - "/request-cert -user=myappuser -namespace=${POD_NAMESPACE} -certs-dir=/cockroach-certs -type=client -symlink-ca-from=/var/run/secrets/kubernetes.io/serviceaccount/ca.crt"
        env:
        - name: POD_NAMESPACE
          valueFrom:
            fieldRef:
              fieldPath: metadata.namespace
        volumeMounts:
        - name: client-certs
          mountPath: /cockroach-certs
      containers:
        ...

If you scroll along the /request-cert line you will eventually see -user=myappuser. Replace the myappuser with the database user you created.

Then in the same file, with your normal container you then add a volume mount

...
containers:
- name: myapp-container
   ...
   volumeMounts:
   - name: client-certs
      mountPath: /cockroach-certs
   ...

Once applied you can then approve the CSR

kubectl get csr

Database connection

Once the certificate is approved we can use it with the client connection to the database. This is mostly done via environment variables in the same Kubernetes deployment file used above.

In the app myapp it is listening to the environment variables DB_DRIVER, DB_URL, DB_USERNAME and DB_PASSWORD. This specifics of this will depend on your own application stack.

...
containers:
- name: myapp-container
   ...
   env:
   - name: DB_DRIVER
      value: org.postgresql.Driver
   - name: DB_URL
      value: jdbc:postgresql://myroach-public:26257/myappdb?sslmode=require&sslcert=/cockroach-certs/client.myappuser.crt&sslkey=/cockroach-certs/client.myappuser.key"
   - name: DB_USERNAME
     value: myappuser
     - name: DB_PASSWORD
     value: somerandompassword
   ...

If you scroll along the Postgres URL you will see it connects to myroach-public service, the myappdb database, and uses the myappuser specific crt and key files.

If you apply this, then this should be it. Your CockroachDB is now up and running. Hopefully.

How did I find setting up CockroachDB?

Pros

Cons

* possibly a typo or missing step I am repeating over and over again...

Cockroach references