Setup Three Node CockroachDB Cluster on CentOS 8

Scale, scale, scale! Scaling everything is a term you will hear several times as you walk through corporate corridors. Whether you are in the business development department or in the technical department, scaling the business or scaling your servers of containers is something we know you are well acquainted with.

For those tasked with managing databases, CockroachDB is here to sort your database scaling needs. CockroachDB is a master scaler with minimum effort and powerful results. In this guide, we are going to install and configure CockroachDB on three nodes and demonstrate how data in one node can be replicated and available in the three nodes. So that is CockroachDB?

CockroachDB is the cloud-native, distributed SQL database that provides next-level consistency, ultra-resilience, data locality, and massive scale to modern cloud applications. Companies like Comcast, Lush, and Bose are building their cloud data architectures on CockroachDB. Cockroach Labs was founded by a team of engineers dedicated to building cutting edge systems infrastructure, and has investments from Benchmark, G/V, Index Ventures, and Redpoint.

CockroachDB distributes a single binary that contains both core and enterprise features. You can use core features without any license key. However, to use the enterprise features, you need either a trial or an enterprise license key.

In this guide, we are going to deploy a three node CockroachDB database on CentOS 8 servers together with an optional HAProxy load balancer server then poke around to see what it is made of. Before we begin, let us clear all dependencies and system requirements.

System Requirements

  • The CockroachDB binary for Linux requires glibc, libncurses, and tzdata, which are found by default on nearly all Linux distributions, with Alpine as the notable exception.
  • 3 CentOS 8 servers with at least 2GB of RAM
  • You will need to allow these two ports on each server: 26257 for inter-node and application communication and 8080 for the Admin UI
  • An optional load balancer server

Install CockroachDB Cluster on CentOS 8 Servers

It should be noted that each node in your cluster needs to have a copy of the cockroach binary. To get CockroachDB installed on each node, follow the following steps.

Step 1: Update and install essential packages

Update your servers to get latest patches and software and install essential applications such as vim, wget and such.

sudo dnf -y update
sudo dnf -y install vim wget

Then add the following to each of the nodes’ “/etc/hosts” file in case you do not have an internal DNS to resolve domain names.

$ sudo vim /etc/hosts
172.28.182.170 cockroach-db01
172.28.182.246 cockroach-db02
172.28.187.217 cockroach-db03
172.28.190.34 loadbalancer

Before proceeding to the next step, create the following directories and create user cockroach on each database server node which will be responsible for managing cockroach service.

mkdir  ~/certs
sudo mkdir /var/lib/cockroach
sudo useradd cockroach

Step 2: Download and install CockroachDB Binary

Let us fetch the source code of CockroachDB and install it on our 3 servers. Download the CockroachDB archive for Linux, and extract the binary:

wget -qO- https://binaries.cockroachdb.com/cockroach-v20.1.7.linux-amd64.tgz | tar  xvz

After it has been extracted successfully, copy the binary into your PATH so it’s easy to execute cockroach commands from any shell:

$ sudo cp -i cockroach-v20.1.7.linux-amd64/cockroach /usr/local/bin/
$ cockroach version

Build Tag:    v20.1.7
Build Time:   2020/10/12 16:04:22
Distribution: CCL
Platform:     linux amd64 (x86_64-unknown-linux-gnu)  
Go Version:   go1.13.9
C Compiler:   gcc 6.3.0
Build SHA-1:  406792b58d32dc053008cc129b409252798f509c
Build Type:   release

Repeat the same for the other two remaining nodes.

Step 3: Generating Certificates for secure connections

On a node of your choice or even on a different server altogether, we are going to generate certificates that will be used by our cluster. Create two directories “certs” and “my-safe-directory“. Certs will keep your CA certificate and all node and client certificates and keys. my-safe-directory will keep your CA key and then reference the key when generating node and client certificates. This will be kept safe and secret; it is recommended not to upload it to your nodes. Create the two directories as follows:

mkdir {certs,my-safe-directory}

Next, Create the CA certificate and key:

cockroach cert create-ca 
--certs-dir=certs 
--ca-key=my-safe-directory/ca.key

Create the certificate and key for the first node, issued to all common names you might use to refer to the node as well as to the load balancer instances. Enter hostnames and IP addresses of node 1 (cockroach-db01 in my case) and of the loadbalancer as well:

cockroach cert create-node 
172.28.182.170 
cockroach-db01  
localhost 
127.0.0.1 
172.28.190.34 
loadbalancer  
--certs-dir=certs 
--ca-key=my-safe-directory/ca.key

Once the certificates are ready, upload the CA certificate, node certificate and key to the first node:

scp certs/ca.crt certs/node.crt certs/node.key <server-username>@cockroach-db01:~/certs

ca.crt                                                                                 100% 1111   873.8KB/s   00:00    
node.crt                                                                               100% 1208     1.4MB/s   00:00    
node.key                                                                               100% 1679     1.9MB/s   00:00 

Our keys are copied over to the first node and we now have three more servers remaining. We shall issue the same commands on this server you have set aside to create the certificates. I personally used cockroach-db03 to execute these commands but being careful to copy its own certificates in the right place. Before repeating the commands, delete the local copy of the node certificate and key which now belong to cockroach-db01.

rm certs/node.crt certs/node.key

Next, just like above, create the certificate and key for the second node (cockroach-db02), issued to all host names you might use to refer to the node as well as to the load balancer instances then copy the certificate files to the cockroach-db02 node.

cockroach cert create-node 
172.28.182.246 
cockroach-db02  
localhost 
127.0.0.1 
172.28.190.34 
loadbalancer  
--certs-dir=certs 
--ca-key=my-safe-directory/ca.key

Copy the CA certificate and node certificate and key to the second node (cockroach-db02) and delete the local copy of the node certificate and key which now belong to cockroach-db02.

scp certs/ca.crt certs/node.crt certs/node.key <server-username>@cockroach-db02:~/certs
rm certs/node.crt certs/node.key

Next, create the certificate and key for the third node (cockroach-db03).

cockroach cert create-node 
172.28.187.217 
cockroach-db03  
localhost 
127.0.0.1 
172.28.190.34 
loadbalancer  
--certs-dir=certs 
--ca-key=my-safe-directory/ca.key

Since I am using cockroach-db03 as the generator of the certificates, I am going to leave the generated certificates as they are produced inside certs directory.

After all the certificates have been generated and copied to their respective nodes, log into each of the CockroachDB servers you are using and move the “certs” directory to the “/var/lib/cockroach” directory we created in Step 1. This is where Cockroach will be reading certificates from.

Move the certs directory to the cockroach directory as follows on each CockroachDB node, that is cockroach-db01, cockroach-db02, and cockroach-db03.

sudo cp -rv certs /var/lib/cockroach/

Once done copying “certs” directory to “/var/lib/cockroach” change the ownership of Cockroach directory to the user cockroach:

sudo chown -R cockroach.cockroach /var/lib/cockroach/

Step 4: Configure CockroachDB Systemd service

One of the most amazing ways to manage your services in your Systemd-enabled server is taking advantage of its clean way of starting, stopping and viewing their statuses. For that reason, we are going to add a CockroachDB to Systemd as follows:

sudo vim /etc/systemd/system/cockroachdb.service

[Unit]
Description=Cockroach Database cluster node
Requires=network.target
[Service]
Type=notify
WorkingDirectory=/var/lib/cockroach
ExecStart=/usr/local/bin/cockroach start --certs-dir=certs --advertise-addr=cockroach-db03 --join=cockroach-db01,cockroach-db02,cockroach-db03 --cache=.25 --max-sql-memory=.25
TimeoutStopSec=60
Restart=always
RestartSec=10
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=cockroach
User=cockroach
[Install]
WantedBy=default.target

Repeat creating the systemd file on each node making sure that you change the “–advertise-addr” to fit each node’s name or IP accordingly then start cockroachdb service.

sudo systemctl start cockroachdb

Then check its status on each node.

sudo systemctl status cockroachdb

● cockroachdb.service - Cockroach Database cluster node
   Loaded: loaded (/etc/systemd/system/cockroachdb.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2020-10-15 22:06:51 UTC; 31s ago
 Main PID: 1699 (cockroach)
    Tasks: 17 (limit: 11068)
   Memory: 57.9M
   CGroup: /system.slice/cockroachdb.service
           └─1699 /usr/local/bin/cockroach start --certs-dir=certs --advertise-addr=cockroach-db03 

Before proceeding to the next step, it will be prudent to allow the recommended ports on the firewall on each node. You can do that using the commands below.

sudo firewall-cmd --permanent --add-port={8080,26257}/tcp
sudo firewall-cmd --reload

Step 5: Initialize the CockroachDB cluster on CentOS 8

On one of the nodes, run the cockroach init command to complete the node startup process and have them join together as a cluster:

cockroach init --certs-dir=certs --host=<address of any node>

##For Example

cockroach init --certs-dir=certs --host=cockroach-db03

Cluster successfully initialized

Step 6: Generate HAProxy configuration file

Finally we are going to generate the load balancer’s configuration file. We shall use HAProxy as the load balancer and CockroachDB has an inbuilt facility that auto-generates haproxy configuration file with certificates. Run the command below on the same computer we have been using to generate the certificates to first create a client certificate and key (client.root.crt, client.root.key) for the root user in your CockroachDB database.

cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key 

If you would wish to connect to the default database on each node, then you should copy the two client files (client.root.crt and client.root.key) to the certs directory in their home directories we created in Step 1. Without these files, you wont be able to login without CockroachDB root role’s password.

Run the cockroach gen haproxy command with the –host flag set to the address of any node and security flags pointing to the CA cert and the client cert and key:

cockroach gen haproxy --certs-dir=certs --host=cockroach-db03

The command above will create “haproxy.cfg” file with all the minimal configurations details we need as it has been shared below. You can see that all of our nodes are well displayed on the file.

global
  maxconn 4096

defaults
    mode                tcp
    # Timeout values should be configured for your specific use.
    # See: https://cbonte.github.io/haproxy-dconv/1.8/configuration.html#4-timeout%20connect
    timeout connect     10s
    timeout client      1m
    timeout server      1m
    # TCP keep-alive on client side. Server already enables them.
    option              clitcpka

listen psql
    bind :26257
    mode tcp
    balance roundrobin
    option httpchk GET /health?ready=1
    server cockroach1 cockroach-db03:26257 check port 8080
    server cockroach2 cockroach-db02:26257 check port 8080
    server cockroach3 cockroach-db01:26257 check port 8080

Once you have reviewed and you are satisfied with the file, upload the haproxy.cfg file to the load balancer server to be used to run HAProxy load balancer.

scp haproxy.cfg <username>@loadbalancer:~

Step 7: Install HAProxy on CentOS 8 (Optional)

Log into your loadbalancer server (CentOS 8) and proceed with the installation Steps as follows.

Enable Permissive mode in SELinux

Make SELinux permissive temporarily (for now) and permanently when it reboots by running the commands below:

sudo setenforce 0
sudo sed -i 's/enforcing/permissive/g' /etc/selinux/config

Install HAProxy on CentOS 8

Install haproxy package as follows:

sudo dnf -y install haproxy

After HAProxy installs, the default configuration file will be found at “/etc/haproxy/haproxy.cfg“. Simply backup this file and copy the one we uploaded into the server.

cd /etc/haproxy/
sudo mv haproxy.cfg haproxy.cfg.backup
sudo cp ~/haproxy.cfg /etc/haproxy/

After you are done with configuring HAProxy, start and enable haproxy service.

sudo systemctl enable --now haproxy

Confirm that haproxy service is running

$ systemctl status haproxy

● haproxy.service - HAProxy Load Balancer
   Loaded: loaded (/usr/lib/systemd/system/haproxy.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2020-10-15 22:30:40 UTC; 9min ago
  Process: 2838 ExecStartPre=/usr/sbin/haproxy -f $CONFIG -c -q (code=exited, status=0/SUCCESS)
 Main PID: 2840 (haproxy)
    Tasks: 2 (limit: 11070)

Clients can now be able to access database instances via the load balancer listening at port 26257.

At this point let us view the details of the single node by accessing its Admin UI dashboard. Point your browser to https://[domain-name/IP]:8080 of any node. You should see a dashboard as shared below:

You will notice that you are being asked for a username and password. To generate the credentials, login to the node we were using to generate certificates and issue the command below which should usher you to the defaultdb. We are being logged in directly because we have the client key and certificate of root user we generated in Step 6.

cockroach sql --certs-dir certs

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: q.
#
# Server version: CockroachDB CCL v20.1.7 (x86_64-unknown-linux-gnu, built 2020/10/12 16:04:22, go1.13.9) (same version as client)
# Cluster ID: 2b9244a4-e3b6-48d7-ba44-f0a21e0f778c
No entry for terminal type "xterm-256color";
using dumb terminal settings.
#
# Enter ? for a brief introduction.
#
[email protected]:26257/defaultdb>

On the prompt, proceed to issue the following query. You can use your username and password here.

[email protected]:26257/defaultdb> CREATE USER geeksadmin WITH PASSWORD 'SafePassword';
CREATE ROLE

[email protected]:26257/defaultdb>GRANT admin TO geeksadmin;

Note: The admin role is created by default and cannot be dropped. Users belonging to the admin role have all privileges for all database objects across the cluster.

Once done, go back to the Admin UI, refresh and key in the credentials you just created.Setup Three Node CockroachDB Cluster on CentOS 8

You should be ushered in.Setup Three Node CockroachDB Cluster on CentOS 8

Step 8: Using CockroachDB

Once the cluster is well set, we can test if creating data on one node distributes is to other nodes as well. In that case, all nodes should have access to the same set of data once it has been created. So that we can save on time, we can take advantage of CockroachDB’s example data to make sense of this. Log into cockroach-db03, the node we were using to generate certs together with client certificate and key for the root user and generate the sample data as follows:

cockroach gen example-data startrek | cockroach sql --certs-dir certs

The command above creates a sample database called startrek and we can now launch the SQL client and view which databases are in your cluster. Make sure the rest of the nodes have the certs directory we created in Step 1 with their respective certificates in them together with the two client files (client.root.crt and client.root.key) we created in Step 6. Log into cockroach-db02, and confirm the contents of certs folder:

ls certs

ca.crt  client.root.crt  client.root.key  node.crt  node.key

It looks good! Proceed to log into the defaultdb and check whether the sample example database is accessible from there as well

##On cockroach-db02

$ cockroach sql --certs-dir certs
[email protected]:26257/defaultdb> SHOW DATABASES;

  database_name
-----------------
  defaultdb
  postgres
  startrek
  system
(4 rows)

Time: 5.638902ms

Setup Three Node CockroachDB Cluster on CentOS 8

Just to ensure that the same database can be accessed from the third cockroach-db01 node, let us log into it and list the databases as above. Make sure you have copied (client.root.crt and client.root.key) into its certs directory.

##On cockroach-db01

$ cockroach sql --certs-dir certs
> SHOW DATABASES;

  database_name
-----------------
  defaultdb
  postgres
  startrek
  system
(4 rows)

Time: 5.638902ms

You should be able to view the startrek database listed as above, which holds our example data. A screenshot of the same is shared below.Setup Three Node CockroachDB Cluster on CentOS 8

If you would wish to increase your number of nodes, you simply have to do the same steps as it was done on nodes two and three on this example. That is, install CockroachDB Binaries then join them to the first node using the Systemd file. That way your cluster scales horizontally. Find out even more about this unique database by checking out CockroachDB’s official website.

To Conclude

It is now evident that we can have data spread across the number of nodes that you would wish to have. Our three-node CockroachDB’s cluster is distributed and survivable at the moment. You are free to connect create databases and connect your application to it. If you have been in a Postgres environment then you have nothing much to worry since CockroachDB works with PostgreSQL drivers.

Finally, we hope the guide was helpful and you got something out of it. Otherwise, thank you for staying till the end and we appreciate your continued support. Find other guides below that might arouse your interest.

Install Single Node TiDB database Cluster on CentOS 8

Install RavenDB NoSQL database on Ubuntu 20.04

Install PostgreSQL Database Server on Ubuntu 20.04 (Focal Fossa)

Manage MySQL / MariaDB & PostgreSQL Database Server with Adminer

Sidebar