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.
- 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 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-v21.2.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-*.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:
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
@cockroach-db01:~/certsca.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
@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.
$ 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|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= ##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.
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/config
Install HAProxy on CentOS 8|Rocky Linux 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. # 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 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 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.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.
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.
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.