How To Deploy CockroachDB on Multiple Servers on Ubuntu 16.04

Introduction

CockroachDB is an open source, distributed SQL database which offers strong consistency, simple scalability, and survivability.

Setting up CockroachDB is straightforward: you install it on multiple servers, each known as a "node," and join them together to act as if they were a single entity, known as a "cluster." All of the nodes in your cluster then behave symmetrically and have access to the same data.

With this kind of design, as your needs grow, you can easily increase your system's capacity by creating new nodes and joining them to your existing cluster. For greater detail, you can read up on CockroachDB's scalability model.

Goals

In this guide, you'll create a distributed and fault-tolerant database by deploying CockroachDB across multiple servers, which we describe as a "distributed multi-node cluster." You'll begin by installing CockroachDB on multiple servers, start them as nodes, and then have them work together as a cluster.

Additionally, we'll demonstrate data distribution and how the cluster survives failure, and show you how to connect your application to CockroachDB.

This guide covers setting up an insecure deployment without SSL encryption, which we don't recommend for production. However, CockroachDB also offers secure deployment instructions for Digital Ocean droplets.

<$>[note] Note: CockroachDB is currently in beta, so we recommend using this guide as a chance to familiarize yourself with interesting technology and not as a deployment guide for a piece of mission-critical software. <$>

Prerequisites

Before you begin, you'll need the following:

3 Ubuntu 16.04 servers with at least 2GB of RAM using private networking
A non-root user with sudo privileges
TCP traffic allowed on the following ports:
- 26257 for inter-node and application communication
- 8080 for the Admin UI
NTP installed and configured (though for brief tests, this is not a firm requirement)

To take your cluster beyond this tutorial, CockroachDB also offers recommended production settings.

Step 1 — Install CockroachDB

Each node in your cluster needs to have a copy of the cockroach binary (i.e., program).

To get started, SSH into your first server:

ssh <^>user<^>@<^>your-server's-IP-address<^>

Next, download and install the latest cockroach binary:

wget https://binaries.cockroachdb.com/cockroach-latest.linux-amd64.tgz?s=do

Now, extract the binary:

tar -xf cockroach-latest.linux-amd64.tgz?s=do --strip=1 cockroach-latest.linux-amd64/cockroach

Move the binary so it's easily accessible from the command line:

sudo mv cockroach /usr/local/bin

Make sure the binary's accessible by checking its version:

cockroach version

Finally, repeat these steps for the other two servers you plan to use as nodes.

Now that cockroach is available on all of your machines, you can set up your cluster.

Step 2 — Start Your Cluster

Your first CockroachDB node will start your cluster. There's nothing special about this first node; it's just that you have to start with one and then have others join it.

From your first machine, start a node without SSL encryption (--insecure), return control of the command prompt (--background), and have it communicate with other nodes using its internal IP address (--advertise-host):

cockroach start --insecure --background --advertise-host=<^>internal-IP-address<^>

<$>[note] Note: You can also set other flags to change the node's behavior, such as changing the directory data is stored in. Those flags are documented in cockroach start. <$>

Now that your node (and cluster is live), you can view its details by going to its Admin UI (which is a tool bundled into CockroachDB to show you information about your cluster). Go to http://<^>your-first-server's-IP-address<^>:8080.

Here you'll see that you have 1 node running.

You'll notice a warning message in the Node tile. If you mouse-over it, it indicates that your cluster has "Low Replication," which means your data isn't failure resistant because it's not sufficiently replicated to other sources. If the node goes down, your data will be lost.

We'll fix that in the next step by adding your other two servers as nodes to this cluster. By having 3 nodes, CockroachDB ensures there are at least 3 copies of your data, so it's possible to lose a node without irreparably losing data.

Step 3 – Add Nodes to Your Cluster

On your second machine, start the node like you did in step 2, but specify that you want it to join the first server's cluster through its internal IP address:

cockroach start --insecure --background \
--advertise-host=<^>this-server's-internal-IP-address<^> \
--join=<^>your-first-server's-internal-IP-address<^>:26257

Repeat these steps for your third machine, as well.

Now, when you access the Admin UI from any node (http://<^>your-server's-IP-address<^>:8080), you'll see that the cluster has 3 nodes.

Now that all of your nodes are connected through the cluster, they'll all have access to the same data.

Step 4 – Demonstrate Data Distrubtion

Whenever you write data to one node, all of the other nodes in the cluster have access to it. The easiest way to demonstrate this is to use generate CockroachDB's example data, and then view it from the built-in SQL client.

From your first node, generate the example data:

cockroach gen example-data | cockroach sql

You can now launch the SQL client and view which databases are in your cluster:

cockroach sql

SHOW DATABASES;

You'll see the startrek database listed, which holds our example data.

+--------------------+
|      Database      |
+--------------------+
| information_schema |
| pg_catalog         |
| startrek           |
| system             |
+--------------------+

<$>[note] Note: CockroachDB supports its own SQL dialect that offers different extensions of the SQL standard than other databases. <$>

Now, move to your second node's terminal and run the same commands:

cockroach sql

SHOW DATABASES;

You'll see that even though you generated the example data on another node, it's been distributed and is accessible from all of your other servers.

You can also view that the database exists from the Admin UI's Databases tab on any node.

Step 5 – Demonstrate Survivability

In addition to distributing data to all nodes in your cluster, CockroachDB also guarantees the availability and integrity of your data in case of server outages. CockroachDB's tolerance for node failure is (<^>n<^> - 1)/2 where <^>n<^> is the number of nodes in your cluster. So, in this example of 3 nodes, we can tolerate losing 1 node without losing any data.

To demonstrate this, we'll remove a node from the cluster and show that all of the cluster's data is still available. We'll then rejoin the node to the cluster and see that it receives all updates that happened while it was offline.

Remove a Node From the Cluster

From your second node, launch the SQL client if you aren't still in it:

cockroach sql

Count the number of rows in the quotes table of the example database:

SELECT COUNT(*) FROM startrek.quotes;

You'll see that the table has 200 rows. Exit the SQL client by pressing ctrl+C.

Now, we'll remove this node from the cluster and see that all of the data is still available from the other nodes.

From the same node you were using (node 2), stop the cockroach process:

cockroach quit

Now switch to the terminal of one of your other nodes (node 1 or 3) and launch the SQL client:

cockroach sql

Run the same command as before to count the number of rows in the quotes table:

SELECT COUNT(*) FROM startrek.quotes;

Despite losing one of the nodes in the cluster, you'll see that you still have 200 rows of data! This means CockroachDB has successfully tolerated a system failure and maintained the integrity of your data.

Rejoin the Node to the Cluster

We can also demonstrate that CockroachDB gracefully handles the server coming back online. First, we'll delete some data, then have the node we removed rejoin the cluster. Once it rejoins, we'll be able to see that CockroachDB automatically removes the same data from the resurrected node.

From one of your nodes that's currently running, delete all of the quotes where the episode is greater than 50.

DELETE FROM startrek.quotes WHERE episode > 50;

SELECT COUNT(*) FROM startrek.quotes;

You'll see there are now 131 rows of data.

Now, go back to the terminal of the node we removed from the cluster (node 2), and have it rejoin the cluster:

cockroach start --insecure --background \
--advertise-host=<^>this-server's-internal-IP-address<^> \
--join=<^>your-first-server's-internal-IP-address<^>:26257

Launch the built-in SQL client:

cockroach sql

Now, count the number of rows the quotes table contains:

SELECT COUNT(*) FROM startrek.quotes;

131! So, despite being offline when the update happened, the node is updated as soon as it rejoins the cluster.

If you'd like, you can now remove the example data:

DROP TABLE quotes;
DROP TABLE episodes;
DROP DATABASE startrek;

Step 6 – Connect an Application

With your cluster up and running, you can use it as the database for an application. To connect to CockroachDB, you can use any PostgreSQL client driver.

<$>[note] Note: Though CockroachDB supports the PostgreSQL wire protocol, its SQL syntax differs and is not a drop-in replacement for PostgreSQL. <$>

In your connection strings, connect to port 26257 on any node's IP address. This means your firewall must also allow connections on port 26257.

For example, here's a PHP/PDO connection string that connects the user maxroach to the database bank:

PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=disable',
    'maxroach', null, array(
      PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_EMULATE_PREPARES => true,
  ));

For more help using the PostgreSQL client drivers, CockroachDB has a number of code samples available.

Conclusion

At this point, you've created a 3-node cluster, seen CockroachDB's distributed and survivable features, and connected it to an application. If you wanted to scale your deployment horizontally by adding more nodes, you would just have to install the cockroach binary and have them join your existing cluster. For more tools to develop or deploy applications using CockroachDB, check out CockroachDB's documentation.

sploiselle/crdb-do.md