Cockroach DB is a scalable clustered database that Intergreatme makes use of for a significant amount of our data storage needs. While we still currently use PostreSQL for our mobile apps and Cassandra for document storage, the engineering team is also working towards replacing both with Cockroach.
I have also been using Cockroach DB as the go-to database for my own projects at Intergreatme. I make extensive use of it with the customer-facing portal I created as part of the KYC demo - which I ultimately re-worked to provide a front end for Telkom, Lalela and Angelshack to make use of as part of a delivery work-from-home validation program.
|Note: I use this to setup a single instance node of Cockroach DB on Linux. A lot of what is mentioned here can also be used when setting up a cluster.|
1. Create a Cockroach user
It's best to have Cockroach DB run as its own user. Use this command to setup the user. If you are following the Cockroach DB manual, remember that you will need to make use of sudo -u cockroach prior to running any additional configuration commands when interacting with Cockroach DB.
sudo adduser --system --disabled-password --disabled-login --no-create-home --shell /bin/false --group cockroach
2. Download Cockroach DB
use the following command to both download and extract Cockroach DB.
Note: you might need to install cURL first by running: sudo apt install curl
curl https://binaries.cockroachdb.com/cockroach-v22.1.7.linux-amd64.tgz | tar -xz && sudo cp -i cockroach-v22.1.7.linux-amd64/cockroach /usr/local/bin/
If you don't have or want to install cURL, you could replace it with wget but it becomes a two-step process:
wget https://binaries.cockroachdb.com/cockroach-v22.1.7.linux-amd64.tgz | tar -xz
sudo cp -i cockroach-v22.1.7.linux-amd64/cockroach /usr/local/bin/
3. Create your certificate files
Note: the Cockroach DB manual says its best to create these files on your local machine and to only copy the certificates that are necessary to the actual host machine. I would recommend following the prompts for a secure single-node instance as you would want any code you write on your local / dev environment to also be the same as in production.
Follow steps 1 to 4 but do not start the node. We will use our systemd service file to accomplish this.
Important! Since we created the cockroach user in step 1, you probably want to prepend all of the commands in the steps above using sudo -u cockroach command
This will ensure any working directories and files have the correct user applied to them. The assumption here is that you are going to be working from where you are going to be storing your files (based on the systemd configuration file outlined below) as in /var/lib/cockroach/
i. Create two directories for certificates.
sudo -u cockroach mkdir certs my-safe-directory
ii. Create the CA (Certificate Authority) certificate and key pair:
sudo -u cockroach cockroach cert create-ca --certs-dir=certs --ca-key=my-safe-directory/ca.key
iii. Create the certificate and key pair for the node:
sudo -u cockroach cockroach cert create-node localhost $(hostname) --certs-dir=certs --ca-key=my-safe-directory/ca.key
iv. Create a client certificate and key pair for the root user
sudo -u cockroach cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key
4. Create a cockroach.service file
I generally make use of systemd to provide a mechanism of controlling Cockroach DB. This allows me to more easily start/stop/restart and enable it to run as a service.
First, define the cockroach.service file. I tend to create this in my home directory first before copying / moving it to /etc/systemd/system/
Description=Cockroach Database cluster node
ExecStart=/usr/local/bin/cockroach start-single-node --certs-dir=/var/lib/cockroach/certs --advertise-addr=localhost --cache=.25 --max-sql-memory=.25
Update the file in a way that best fits your environment. For instance, you might want to store your working directory in a different location to where your certs are stored. You might also want to mount a different drive and have Cockroach persist data there to reduce IOP on the operating system disk, but also in case you ever need to detach or expand the drive (we use Azure to run our infrastructure.)
In certain environments, I also include the following to my cockroach.service file which delays the start up of Cockroach - particularly useful when the Azure agent is busy cleaning up swap memory at first boot when resource allocation can be quite high until everything has fully initialised.
Once you are happy with your file, move it to sudo mv cockroach.service etc/systemd/system/
Try and start the service to see if it will run: sudo systemctl start cockroach
It's also useful to remember to use sudo systemctl status cockroach and sudo journalctl -xe when things go wrong.
5. Create a new user
You should not be connecting to your database using the root user. Use the following steps to create a new user.
Start the built-in SQL shell
sudo -u cockroach cockroach sql --cert-dir=/var/lib/cockroach/certs
Create the user, any database(s) you want to work with, and grant permissions for the user. For instance, if you want your user to be db_user, you'd use:
CREATE USER IF NOT EXISTS db_user;
CREATE DATABASE mydb;
GRANT ALL ON DATABASE mydb TO db_user;
Then, you want to create a cert file for the user. Asssuming you are running the following command from /var/lib/cockroach/
sudo -u cockroach cockroach cert create-client db_user --certs-dir=certs --ca-key=my-safe-directory/ca.key
I use the process above as part of a fairly quick deployment mechanism when we create new instances that require standalone databases. The installation process is quick, and setting up an instance for single node access is pretty easy.
If you do encounter problems, these are usually to do with file permissions, which can either be on a directory, or with the access on a particular file.
You can use the following to recursively change the folder owner
sudo chown -R cockroach: .
Or make use of the following to change the access permissions:
sudo chmod 600 node.key
sudo chmod 600 client.node.key