CockroachDB: fast-start configuration on a fresh cluster

You frequently deploy CockroachDB clusters, and each time you need to create initial users, initial databases, some pre-loaded tables, adjustments to grants and privileges, and perhaps some custom zone-configurations based on your locality settings.

The pain is repeating this activity over, and over, and over again...

With this guide, after you deploy (or redeploy) your cluster, you can quickly configure it using a repeatable, reliable, and consistent pattern, encapsulated as a single Kubernetes job. This approach eliminates the error-prone and manual process of running your scripts to organize the database.

This configuration process prepares the database for your workloads, eliminating the need for application-managed configurations. These configs may include:

• defining database regions
• creation of named databases and schemas
• pre-creating some tables
• applying license keys
• creating initial users or accounts that apps require to operate
• defining permissions, grants, and roles-groups for the users
• backup schedules
• CDC/changefeed jobs
• etc, etc, etc...

Theory of operation

This process is defined as a Kubernetes Job object that runs once.

It initially connects using the root-account via certs. These are typically found in the related secrets object that contains the CA-certs, node-certs, and other cluster-applicable certificates.

Upon connecting to the cluster, the job calls the CockroachDB command line function __file=<some SQL file> which points to the mapped/mounted ConfigMap containing the SQL to apply.

When you create the job spec (code follows later in this blog), it instantly runs, and allows CockroachDB to issue the sequential SQL commands, as-defined in the ConfigMap. Upon completion, you can check the completed pod and review the console logging to ensure that all your SQL statements were successful.

zlamal-initial-sql config map

Step 1: Define your SQL embedded inside a ConfigMap

The ConfigMap spec is below. Note that it's much easier to do this using the OpenShift console UI rather than text-edit the below spec.

In this example I create an initial user, and an initial table with some inserted dummy-data. Here is where you can define your database properties such as regions/user-accounts/license-keys/etc to prepare the cluster for client/app connections.

I wish there was a way to syntax-highlight specific fields that you can focus on. I deliberately use zlamal and mz (my initials) to help with search/replace when you adopt these specs.

kind: ConfigMap
apiVersion: v1
metadata:
  name: zlamal-initial-sql
data:
  zlamal-run-sql-once: |
    create user mark with password 'zlamal';
    grant admin to mark;
    create table aaaa (c0 int, c1 string);
    insert into aaaa values (0, 'val0'),(1, 'val1'),(2, 'val2');
    -- ...
    -- ...
    -- ...

zlamal-prep-crdb job

Step 2: Job object to SQL embedded inside a ConfigMap

The Kubernetes job spec is below. Again, I wish there was a way to syntax-highlight specific fields that you can focus on, but the idea is that you'll need to adjust the connection-strings, and the names of the K8s resources to align this task to your cluster.

This job will mount the ConfigMap in a folder named "/zlamal-initial-sql". You can change this provided you're consistent with your naming convention.

apiVersion: batch/v1
kind: Job
metadata:
  name: zlamal-prep-crdb
spec:
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: zlamal-prep-crdb
          image: cockroachdb/cockroach:v24.2.5
          command:
            - /bin/bash
            - '-ecx'
            - >-
              exec /cockroach/cockroach
              sql
              --url
              'postgresql://mz-crdb-v11-cockroachdb-public:26257'
              --file
              /zlamal-initial-sql/zlamal-run-sql-once
              --certs-dir=cockroach-certs
          volumeMounts:
            - name: client-certs
              mountPath: /cockroach/cockroach-certs/
            - name: ca
              mountPath: /cockroach/ca
            - name: zlamal-initial-sql-configmap
              mountPath: /zlamal-initial-sql
      volumes:
        - name: zlamal-initial-sql-configmap
          configMap:
            name: zlamal-initial-sql
            defaultMode: 0777
        - name: client-certs
          projected:
              sources:
                - secret:
                    name: mz-crdb-v11-cockroachdb-client-secret
                    items:
                      - key: ca.crt
                        path: ca.crt
                      - key: tls.crt
                        path: client.root.crt
                      - key: tls.key
                        path: client.root.key
              defaultMode: 256
        - name: ca
          projected:
            sources:
              - secret:
                  name: mz-crdb-v11-cockroachdb-ca-secret
                  items:
                    - key: ca.key
                      path: ca.key
            defaultMode: 256

Of-course you'll need to adjust many of the fields in this YAML such as the cockroachDB version, the names, possibly permissions based on the K8s / OpenShift cluster characteristics.

You should create your own copy of these fragments, and after making the necessary changes, please test them against your environments!

Conclusion

This procedure is a quick reference into Kubernetes Jobs, ConfigMaps, volumes & mounts, and operating CockroachDB by applying some SQL using command-line arguments.