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|Rocky Linux 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|Rocky Linux 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|Rocky Linux 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 wgetThen 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 loadbalancerBefore 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-v21.2.7.linux-amd64.tgz | tar xvzAfter 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-*.linux-amd64/cockroach /usr/local/bin/Confirm the version of CockroachDB locally available on the system.
$ cockroach version
Build Tag: v21.2.7
Build Time: 2022/03/14 16:37:26
Distribution: CCL
Platform: linux amd64 (x86_64-unknown-linux-gnu)
Go Version: go1.16.6
C Compiler: gcc 6.5.0
Build Commit ID: 37dee546a7c52870a8dc58826f0cffe2afa8d47a
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.keyCreate 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.keyOnce 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.keyCopy 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.keyNext, 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.keySince 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.targetRepeat 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 cockroachdbThen check its status on each node.
$ 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 --reloadStep 5: Initialize the CockroachDB cluster on CentOS 8|Rocky Linux 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 initializedStep 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-db03The 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 8080Once 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|Rocky Linux 8 (Optional)
Log into your loadbalancer server CentOS 8|Rocky Linux 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/configInstall HAProxy on CentOS 8|Rocky Linux 8
Install haproxy package as follows:
sudo dnf -y install haproxyAfter 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 haproxyConfirm 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.
#
root@:26257/defaultdb>On the prompt, proceed to issue the following query. You can use your username and password here.
root@:26257/defaultdb> CREATE USER geeksadmin WITH PASSWORD 'SafePassword';
CREATE ROLE
root@: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.
You should be ushered in.
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 certsThe 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.keyIt 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
root@:26257/defaultdb> SHOW DATABASES;
database_name
-----------------
defaultdb
postgres
startrek
system
(4 rows)
Time: 5.638902ms
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.638902msYou should be able to view the startrek database listed as above, which holds our example data. A screenshot of the same is shared below.
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.