Working with CockroachDB as a Windows user

Published in

THG Tech Blog

5 min readJan 11, 2019

When I learned about CockroachDB, I couldn’t help but be intrigued. Like Google Spanner, CockroachDB claims to offer several challenging features for distributed SQL databases:

ACID compliance
High availability
Strong consistency

These claims warranted further investigation, and whilst reading whitepapers and reviews is good, nothing beats spinning up CockroachDB for yourself and trying to find where it breaks.

Cockroach Labs has some excellent guides on how to install a single node “cluster”, but that doesn’t allow me to test out their claims of how it supports high availability and strong consistency. Luckily there is a guide on how to set up a local cluster setup. Unfortunately, this is aimed at Linux developers (which I am not).

I needed to work out create a local Windows-based CockroachDB cluster. I also wanted to learn more about Kubernetes at the same time (why make life easy for myself?).

One thing I did decide to take easy on myself when creating my local Cockroach clusters is to create it as an insecure cluster --insecure as it removes the challenges of dealing with security certificates on minikube. When moving to test/dev clusters make sure you are setting them up as secure cluster!

The results follow!

Software you will need

I recommend following the excellent installation instructions from Assistanz on setting up Minikube, Kubectl on Windows 10 using Oracle VirtualBox (as your VM host service), the software I make use of can be seen below:

After installing the above components, you should have in a single directory the files:

Minikube.exe
Kubectl.exe

Note: you may need to add an additional PATH entry, this will be to reference where the “.minikube’ directory is installed within your local profile. e.g. Add “C:\Users\xxx” where xxx=user name to your own user profile.

To verify that minikube has been installed correctly open a command window and run:

# check that minikube responds
> minikube.exe status
# you should see empty response of
minikube:
cluster:
kubectl:

Resetting your minikube (so you can restart your test/if you broke it)

When starting minikube or verifying that VirtualBox is working correctly, if you get issues I have found following the below steps to reset usually fixes most issues:

# now stop and delete the minikube instance
minikube stop   # stop it if it's already running
minikube delete  # delete any kubernetes cluster (so we can start from a blank sheet)# if delete/clear down goes truly wrong, you can delete/rename folder in C:\user\<user>\.minikube# this will force a re-download of minikube .iso and recreate of minikube environment

The actual steps to setup CockroachDB

Open a terminal window (powershell or command prompt) in the same directory that you placed the yaml files (see Required Software above) and follow the below steps.

IMPORTANT: Run the below scripts as Administrator

1. Starting up your local kubernetes cluster

Create your first mini kubernetes cluster, by running:

Output from starting minikube should look like the above

At the end of this process you will have kubectl configured to talk to your local kubernetes cluster.

Verifying that your minikube cluster is working

You can then run minikube status to confirm that your local k8s cluster is up and running and ready to be used

Output from checking status will look like the above

You can also run:

Dashboard command will launch the interface

2. Define and create your CockroachDB nodes on your Minikube

First check to see if the Kubernetes cluster is currently empty (if you’ve previously used Minikube you may have more than one service up and running)

> kubectl.exe get services
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
kubernetes ClusterIP 10.96.0.1 <none> 443/TCP 5m

When requesting Kubernetes to spin up resources the recommended way is to use a YAML file, which is a common way of defining the configuration of your services/pods/jobs/etc. For a more detailed introduction please read Introduction to YAML: Creating a Kubernetes deployment

Now that we’re confident that Minikube is ready to host our CockroachDB cluster, we are going to ask our k8s cluster to configure the resources defined in the cockroachdb-statefulset.yaml file

First copy the cockroachdb-statefulset.yaml file into your working directory:

3. Validate that your cluster pods have been setup

It’s all very good to know that you’ve submitted a set of instructions to Kubernetes, but has it worked?

You may need to re-run that command several times while the pods are being spun up by Kubernetes

4. Validate that the persistent storage for your nodes has been setup

I find it reassuring to check that persistent volume claims have been allocated and bound to each of the Kubernetes pods / CockroachDB nodes.

Note: If the above nodes have not been built within 10 minutes, then something has gone wrong with your installation of Minikube/VirtualBox/your .yaml file

5. Is the cluster running yet?

You have defined and created the Cockroach pods and services on Kubernetes, but is it yet running? Let’s check…

To do this we try using the kubectl run command that will create a temporary pod cockroachdb that will launch the command cockroachdb/cockroach sql --insecure --host=cockroachdb-publicand attach a shell so we can interact with it.

We have setup all the required parts of a CockroachDB cluster, however we missed the important step of initialising the cluster, we do this using a once off Kubernetes Job. We will do this using another YAML file, please go ahead and download cluster-init.yaml. Then run the following to initialise the cluster:

Once the above returns that job has been successful we can move on

6. Is it actually running now?

Now that we have completed:

Creating 3 CockroachDB nodes
Initialised the CockroachDB cluster and joined those nodes together

We should be able to actually use it as a database service.

When querying from the command line there are some challenges in deciphering the resultset (depending on the number of columns). In a later blog post I will describe some of the GUI tools that we are using to query directly, but the short version is because CockroachDB is compatible with PostgreSQL, there are a lot of Postgres clients such as DBeaver, there are also Postgres extensions in VSCode that make querying CockroachDB very useful.

As we as using the run command to create a new pod to connect to the cluster, you can also open a terminal on any of the pods using exec -it [Name of POD] -- [terminal command]:

# first get your pod names and choose one of them
> kubectl get pods
NAME                 READY   STATUS      RESTARTS   AGE
cluster-init-t7qh9   0/1     Completed   0          30m
cockroachdb-0        1/1     Running     2          40m
cockroachdb-1        1/1     Running     2          40m
cockroachdb-2        1/1     Running     2          40m# then go and open /bin/bash on one of them
> kubectl exec -it cockroachdb-0 -- /bin/bash
root@cockroachdb-0:/cockroach## this will allow you to run any command and dig into the 
# status/logs of your individual cockroach nodes
root@cockroachdb-0:/cockroach# ./cockroach version
Build Tag:    v2.0.6
Build Time:   2018/10/01 13:59:40
Distribution: CCL
Platform:     linux amd64 (x86_64-unknown-linux-gnu)
Go Version:   go1.10
C Compiler:   gcc 6.3.0
Build SHA-1:  d30787727998a453fcae13c5a0c90f27f02a69f2
Build Type:   release# once finished you can go ahead and exit
root@cockroachdb-0:/cockroach# exit
exit
>

Conclusion

In this article I have taken the initial steps to get CockroachDB running on Windows using Minikube to the point where it can be queried using traditional Postgres tools. Future articles will go into the details of running CockroachDB.

We’re recruiting

Find out about the exciting opportunities at THG here:

https://www.thg.com/careers/

Inspiration

Special thanks to Alex Robinson from Cockroach Labs who put together a demonstration on how to get CockroachDB working on Kubernetes cluster: https://youtu.be/7P-nWJgqP7U

Useful documentation and blog posts from around the web: