Install CockroachDB cluster in a Virtual Machine

Photo by Maksym Kaharlytskyi on Unsplash

CockroachDB is a distributed SQL database that provides:

• Scale
• Consistency
• Resiliency
• SQL

CockroachDB supports the PostgreSQL wire protocol, so you can use any available PostgreSQL client drivers.

In this article we will install a local cluster of 3 instances on a VM running ubuntu focal (20.04 LTS), we will use vermin to make installation easy.

We use a Virtual Machine here so we can play with cockroachDB and try different things and differnt setups while keeping our host machine clean. And with vermin — as you will see — it is both easy and powerful to spin up and provision new VMs and clear them after we finish.

vermin is a simple virtual machines program that uses Virtualbox as the underlying hypervisor.

To follow this tutorial, you will have to install vermin.

First, you need to install Virtualbox, then for Linux and macOS, in a terminal window:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/mhewedy/vermin/master/install.sh)"

And for Windows, Open a PowerShell terminal as an administrator then use:

iex ((New-Object System.Net.WebClient).DownloadString('https://raw.githubusercontent.com/mhewedy/vermin/master/install.ps1'))

vermin is a single binary, you can still download it yourself and do a manual installation or even build it from source, see Github page for more information.

mhewedy/vermin

Step 1: Create the provision script

let's create the provision script that will download and install and start and initialize CockroachDB. Let’s name the file script.sh.

wget -qO- https://binaries.cockroachdb.com/cockroach-v19.2.6.linux-amd64.tgz | tar  xvz
sudo cp -i cockroach-v19.2.6.linux-amd64/cockroach /usr/local/bin/


for node in 0 1 2
do
 cockroach start \
 --insecure \
 --store=node_$node \
 --listen-addr=localhost:$((26257+node)) \
 --http-addr=localhost:$((8080+node)) \
 --join=localhost:26257,localhost:26258,localhost:26259 \
  --background >> ~/cockroach.log 2>&1
done

cockroach init --insecure

You can find the script on GitHub as well.

Step 2: Create and provision the VM:

Now open a new terminal and run the following command:

$ vermin create ubuntu/focal /path/to/script.sh
✔ Creating vm_01 from image ubuntu/focal
✔ Setting bridged network adapter
✔ Starting vm_01
✔ Establishing connection
Provisioning vm_01
cockroach-v19.2.6.linux-amd64/cockroach
Cluster successfully initialized
Your VM is ready. To connect to the VM use:
$ vermin ssh vm_01

This will download basic ubuntu/focal VM then will download and install cockroachDB inside the VM and start 3 instances of cockroachDB in cluster mode and initialize the cluster.

It will take a while especially the first time to use vermin as it will need to download the ubuntu VM.

Step 3: make sure the CockroachDB is running

Now let's check the VM we have so far, use the following command to check the VMs:

$ vermin ps
VM NAME        IMAGE           CPUS      MEM       DISK         TAGS
vm_01          ubuntu/focal    1         1024      2.9GB

vm_01 is our VM name that we have setted-up.

Now, Let’s make sure the CockroachDB is running by using vermin exec command as following:

$ vermin exec vm_01 "ps aux | grep [c]ockroach"
vermin      1154  0.5  8.5 366684 85800 ?        Sl   01:21   0:00 cockroach start --insecure --store=node_0 --listen-addr=localhost:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259
vermin      1208  0.4  8.4 358488 84436 ?        Sl   01:21   0:00 cockroach start --insecure --store=node_1 --listen-addr=localhost:26258 --http-addr=localhost:8081 --join=localhost:26257,localhost:26258,localhost:26259
vermin      1264  0.5  8.4 366684 85156 ?        Sl   01:21   0:00 cockroach start --insecure --store=node_2 --listen-addr=localhost:26259 --http-addr=localhost:8082 --join=localhost:26257,localhost:26258,localhost:26259

Admin UI

Before connecting to the VM, lets first open a new terminal and forward port 8080 from inside the VM to the local machine to access the Admin UI on the local machine.

$ vermin port vm_01 8080
Connected. Press CTRL+C anytime to stop

Now open http://localhost:8080 in the browser on your the host machine, and notice the 3 nodes listed

CockroachDB cluster Admin UI

Working with CockroachDB

Now let’s ssh into the VM and work with the database

$ vermin ssh vm_01

Now we are inside the VM, let’s initialize the database

vermin@verminbox:~$ cockroach workload init movr

Then let’s use cockroach sql command to work with the data:

vermin@verminbox:~$ cockroach sql --insecure
#
# Welcome to the CockroachDB SQL shell.
.......
root@:26257/defaultdb>

Now we can do switch the database

root@:26257/defaultdb> use movr;

Then let’s issue this query:

root@:26257/movr> EXPLAIN
 SELECT
  u.name,
  u.city,
  r.start_address,
  r.end_address,
  r.revenue
 FROM
  users AS u JOIN rides AS r ON u.id = r.rider_id
 WHERE
  u.name = 'Ryan Hickman'

Now let’s add two indexes:

create index on users (name);
create index on rides (rider_id);

Run the same query again and notice the difference in the execution plan.

You can even connect from any external client to the DB such as DataGrip by using the PostgreSQL client but first, you need to port forward the 26257 port:

$ vermin port vm_01 8080 26257
Connected. Press CTRL+C anytime to stop

IntelliJ (DataGrip) connecting to CockroachDB using PostgreSQL driver

In the end, you can stop the VM or even remove it completely.

To stop the VM and use it later, you can use:

$ vermin stop vm_01

To remove the VM completely to reclaim its HD space, use:

$ vermin rm -f vm_01

References:

Start a Local Cluster (Insecure)

Learn CockroachDB SQL