Table of contents

Reading Time: 7 minutes

This blog talks about CockroachDB, which is a distributed SQL database built on a transactional and strongly-consistent key-value store. It scales horizontally; survives disk, machine, rack, and even datacenter failures with minimal latency disruption and no manual intervention; supports strongly-consistent ACID transactions; and provides a familiar SQL API for structuring, manipulating, and querying data.

In this blog, we will go through the following:

Installing CockroachDB
Setting up an insecure cluster
Setting up a secure cluster

1. Installation

(i) Linux

Download the CockroachDB v1.0.1 archive for Linux from the official site.

Extract the binary using the command:

$ sudo tar xfz cockroach-v1.0.1.linux-amd64.tgz

Move the binary to your PATH so you can execute cockroach commands from any shell:

$ sudo cp -i cockroach-v1.0.1.linux-amd64/cockroach /usr/local/bin

Check that the cockroach command is working:

$ sudo cp -i cockroach-v1.0.1.linux-amd64/cockroach /usr/local/bin

(ii) Windows

Note: Native CockroachDB on Windows requires Windows 8 or higher, is experimental, and has not been extensively tested by Cockroach Labs. This prebuilt binary is provided as a convenience for local development and experimentation; production deployments of CockroachDB on Windows are strongly discouraged.

Download and extract the CockroachDB v1.0.1 archive for Windows

Open PowerShell, navigate to the directory containing the binary, and make sure the CockroachDB executable works:

PS C:\cockroach-v1.0.1.windows-6.2-amd64> .\cockroach.exe version

2. Setting up an insecure cluster

For beginners, it is easier to set up an insecure multi-node cluster locally. Although an insecure cluster is not recommended for production environments.

(i) Starting the first node

A node is an individual database in the cluster, and the nodes in a cluster may be running on the same machine or on different machine, but they all have unique socket addresses. You can run multiple nodes on a single machine by varying the port numbers and other credentials that we will discuss further.

$ sudo cockroach start --insecure \ --host=localhost

When we run the above command on terminal, a node is started with cockroachDB defaults and we should get the following response if the node is started correctly.

CockroachDB node starting at 2017-05-25 13:24:21.512411377 -0400 EDT
build:      CCL v1.0.1 @ 2017/05/25 15:15:48 (go1.8.1)
admin:      http://localhost:8080
sql:        postgresql://root@localhost:26257?sslmode=disable
logs:       cockroach-data/logs
store[0]:   path=cockroach-data
status:     initialized new cluster
clusterID:  {dab8130a-d20b-4753-85ba-14d8956a294c}
nodeID:     1

The --insecure flag makes communication unencrypted.
Since this is a purely local cluster, --host=localhost tells the node to listens only on localhost, with default ports used for internal and client traffic (26257) and for HTTP requests from the Admin UI (8080). If any other service (like Apache Tomcat) is already running on the port 8080, you need to specify the port specifically as we do below for running two other nodes on the same machine.
Node data is stored in the cockroach-data directory.
The standard output gives you helpful details such as the CockroachDB version, the URL for the admin UI, and the SQL URL for clients.

By default, each node’s cache is limited to 25% of available memory. This default is reasonable when running one node per host. When you run multiple nodes on a single host, however, this default may lead to out-of-memory errors, especially if you test in a serious way. To avoid such errors, you can limit each node’s cache size by setting the --cache flag in the start command.

(ii) Adding multiple nodes to cluster

As of now, our cluster is operational with only one node running on the http port 8080, with the default port for client traffic being 26257 and with node ID 1.

To add a second and third terminal to your cluster on the same machine, use the following command in separate terminals, specifying different--store, --port, and --http-port flags which are not being used by any other nodes.

$ cockroach start --insecure \ --store=node2 \ --host=localhost \ 
--port=26258 \ --http-port=8081 \ --join=localhost:26257

$ cockroach start --insecure \ --store=node3 \ --host=localhost \
--port=26259 \ --http-port=8082 \ --join=localhost:26257

Note: Do not close the terminal or press CTRL+C in any of the terminal on which a node is running. This will force shut down the node.

(iii) Executing SQL commands

In order to execute simple SQL commands, enter the SQL prompt of cockroachDB in a new terminal:

$ cockroach sql --insecure
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.

The above command uses the default port, i.e., 26257. Therefore, we are in the SQL prompt of first node.

Run some basic SQL operations and enter some data in your table.

> CREATE DATABASE bank;

> CREATE TABLE bank.accounts (id INT PRIMARY KEY, balance DECIMAL);

> INSERT INTO bank.accounts VALUES (1, 1000.50);

> SELECT * FROM bank.accounts;

+----+---------+
| id | balance |
+----+---------+
|  1 |  1000.5 |
+----+---------+
(1 row)

To exit the SQL prompt, enter:

> \q

Now, to connect to the second node in the cluster on the same machine that we started above, specify the port number as below:

$ cockroach sql --insecure --port=26258
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.

To enter the third node, specify the port number as 26259 on the port flag.

Note that all nodes have their own data stores, in which the data is replicated as and when an update is performed on any of the nodes. To test the same, run the same select query that we ran on the first node on either of the SQL prompt of the second or third node.

> SELECT * FROM bank.accounts;

+----+---------+
| id | balance |
+----+---------+
|  1 |  1000.5 |
+----+---------+
(1 row)

(iv) Monitoring the cluster

You can monitor the cluster using the Admin UI of cockroachDB by going to http://localhost:8080 on your browser.

As mentioned earlier, CockroachDB automatically replicates your data behind-the-scenes. To verify that data written in the previous step was replicated successfully, scroll down to the Replicas per Node graph and hover over the line.

The replica count on each node is identical, indicating that all data in the cluster was replicated 3 times (the default).

(v) Stopping the cluster

To stop a particular node, press CTRL+C on the terminal in which the particular node is running. If you terminate a node in the cluster that we created above, the cluster will still remain operational because majority of the replicas are still available. To verify that this failure was tolerated by the cluster, connect to either of the two nodes using SQL prompt and run the select query.

$ cockroach sql --insecure --port=26258
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.

> SELECT * FROM bank.accounts;

+----+---------+
| id | balance |
+----+---------+
|  1 |  1000.5 |
+----+---------+
(1 row

> \q

Now stop nodes 2 and 3 by switching to their terminals and pressing CTRL + C.

Note: For node 3, the shutdown process will take longer (about a minute) and will eventually force kill the node. This is because, with only 1 of 3 nodes left, a majority of replicas are not available, and so the cluster is no longer operational. To speed up the process, press CTRL + C a second time.

Even if you have now closed all the nodes, the data is still stored in the data stores of each of the nodes. So if you restart a node now, you will still be able to read the data. However, if you want to get rid of the cluster altogether, you need to now delete the data stores of all the nodes by executing the following command:

$ rm -rf cockroach-data node2 node3

3. Setting up a secure cluster

In production environment, we will be required to setup a secure cluster and an insecure cluster with no security certification won’t suffice. So let us now learn how to do the real thing!

(i) Create security certificates

After you have installed CockroachDB using step 1, open a new terminal and enter the following commands:

# Create a certs directory and safe directory for the CA key.
# If using the default certificate directory (`${HOME}/.cockroach-certs`), make sure it is empty.
$ mkdir certs
$ mkdir my-safe-directory

# Create the CA key pair:
$ cockroach cert create-ca \ --certs-dir=certs \ 
--ca-key=my-safe-directory/ca.key

# Create a client key pair for the root user:
$ cockroach cert create-client \ root \ --certs-dir=certs \  
--ca-key=my-safe-directory/ca.key 

# Create a key pair for the nodes: 
$ cockroach cert create-node \ localhost \ $(hostname) \ 
--certs-dir=certs \ --ca-key=my-safe-directory/ca.key

The first command makes a new directory for the certificates.
The second command creates the Certificate Authority (CA) certificate and key: ca.crt and ca.key.
The third command creates the client certificate and key, in this case for the root user: client.root.crt and client.root.key. These files will be used to secure communication between the built-in SQL shell and the cluster (see step 4).
The fourth command creates the node certificate and key: node.crt and node.key. These files will be used to secure communication between nodes. Typically, you would generate these separately for each node since each node has unique addresses; in this case, however, since all nodes will be running locally, you need to generate only one node certificate and key.

(ii) Starting the first node

$ cockroach start \ --certs-dir=certs \ --host=localhost \ 
--http-host=localhost

CockroachDB node starting at 2017-05-25 13:24:21.512411377 -0400 EDT
build:      CCL v1.0.1 @ 2017/05/25 15:15:48 (go1.8.1)
admin:      https://ROACHs-MBP:8080
sql:        postgresql://root@ROACHs-MBP:26257?sslcert=%2FUsers%2F...
logs:       cockroach-data/logs
store[0]:   path=cockroach-data
status:     restarted pre-existing node
clusterID:  {dab8130a-d20b-4753-85ba-14d8956a294c}
nodeID:     1

This command starts a node in secure mode, accepting the CockroachDB defaults as we discussed in the insecure setup.

The --certs-dir directory points to the directory holding certificates and keys.
Since this is a purely local cluster, --host=localhost tells the node to listens only on localhost, with default ports used for internal and client traffic (26257) and for HTTP requests from the Admin UI (8080).
The Admin UI defaults to listening on all interfaces. The --http-host flag is therefore used to restrict Admin UI access to the specified interface, in this case, localhost.
Node data is stored in the cockroach-data directory.
The standard output gives you helpful details such as the CockroachDB version, the URL for the admin UI, and the SQL URL for clients.

(iii) Adding multiple nodes to cluster

To add second and third nodes, you can specify different values of the --store, --port, and --http-port flags in the same way as we did in the insecure setup.

$ cockroach start \
--certs-dir=certs \
--store=node2 \
--host=localhost \
--port=26258 \
--http-port=8081 \
--http-host=localhost \
--join=localhost:26257

$ cockroach start \
--certs-dir=certs \
--store=node3 \
--host=localhost \
--port=26259 \
--http-port=8082 \
--http-host=localhost \
--join=localhost:26257

(iii) Executing SQL commands

To enter the secure cluster using any of the node, use the following command:

cockroach sql \
--certs-dir=certs \
--port=26257
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.

The rest of the procedure remains the same as in case of insecure cluster.

I hope this was of help for the beginners out their trying toget their hands on the budding technology that is CockroachDB. In case of any queries, be sure to leave a comment down below. Cheers 🙂

References: