High Availability Cockroach DB for selfhosters

I’ve decided to take a look at Cockroach DB to replace the Postgres instances I have. The main reason for that is the easier clustering options and the multi-active master deployment option.

The multi-master plays a large part in my infrastructure, as services are geographically dispersed and I’d like to have cross region HA capability. A multi-master setup means that I don’t need to worry about forwarding proxies going down - such as in a previous post where we were using HAProxy to redirect requests to an active Postgresql master.

Some links may be affiliate links that keep this site running.

Now this is not a comprehensive guide on setting up a multi region failover replicas and more, for that I recommend you reference the documentation - as of the writing of this post the version is v23.2 and the docs can be found here.

As I am not using this for anything critical, what we are going to do is create 3 regions with 1 instance in each and create global tables that will be available for reads across all nodes while writes will be slower as those will need to be replicated across all regions.

One caveat I have to mention, unless your instances are “close” one to another and are on powerful enough hosts, there is going to be a delay for write queries until those are propagated across all masters, while read queries would be the same as querying a single instance (as long as you are doing it in the same region).

For “proper” production installs you will be looking to have 3 instances under each region. You can find the explanation and requirements here.

I host majority of my cloud instances on HostHatch VPS (Virtual Private Server) Instance (In Asia) for a steal. Some of the other hosts I use are RackNerd (US) and WebHorizon (Asia+Europe) VPS, and decided that it is time to move away from Linode - which is a Great service, but I am looking to reduce the billing on instances. For comparison, I save more than 50% on HostHatch compared to Linode ($3.33 compared to $8) - Don't get me wrong, if this was an extremely (like REALLY) critical application, I would keep it on Linode.

Let’s get started

As usual we will create our directory structure

sudo mkdir -p /docker/cockroachdb
sudo chown $USER:$USER -R /docker
nano /docker/coackroachdb/docker-compose.yaml

There are two deployment options below, please choose the one that suits you, of course the recommended one is the secure deployment

Quick but not secure

Below is the configuration for a docker-compose.yaml file which will start in an insecure deployment - meaning no certificates to secure the connection:

version: '3.8'

name: cockroachDB
services:
    cockroach:
        container_name: roach1
        hostname: roach1
        ports:
            - 26257:26257
            # Optional for http console 
            #- 8080:8080
        volumes:
            - ./data:/cockroach/cockroach-data
        image: cockroachdb/cockroach:v23.2.0
        command: start --advertise-addr=roach1:26357 --http-addr=roach1:8080 --listen-addr=roach1:26357 --insecure --join=roach1:26357,roach2:26357,roach3:26357

You will need to adjust this compose file to all 3 of your servers and modify the corresponding --advertise-addr,--http-addr and --listen-addr. If you’d like to continue using roach# then you need to also adjust your /etc/hosts for that; Personally, I run my own DNS and so I use internal FQDN.

Secure deployment

To run in a secure mode (using certificates), CockroachDB docs do a great job in the technical writeup for it.

Below is the configuration for a secure connection:

version: '3.8'

name: cockroachdb
services:
    cockroach:
        container_name: roach1
        hostname: roach1
        volumes:
            - ./certs:/certs
            - ./data:/cockroach/cockroach-data
        ports:
            - 26257:26257
            # Optional for http console 
            #- 8080:8080
        image: cockroachdb/cockroach:v23.2.0
        command: start --advertise-addr=roach1:26357 --http-addr=roach1:8080 --listen-addr=roach1:26357 --certs-dir=certs --join=roach1:26357,roach2:26357,roach3:26357

You will need to replace roach# with the hostnames or IPs of the instances you are going to set up on your other servers.

To create the certificates, first lets create the directories:

mkdir /docker/cockroachdb/certs
mkdir /docker/cockroachdb/safe-dir

If you’d like a more secure deployment, you can generate all the certificates on your local machine (desktop/laptop), commands are the same as below.

OR you can download cockroachdb for your machine here and just drop the docker run component.

Creating CA Certificate

Lets run the docker container to generate the certificates for us, I recommend running this through a temporary docker container.

docker run --rm --name=roach-temp -v "ckdb-temp:/cockroach/cockroach-data" -v "/docker/cockroachdb/certs:/certs" -v "/docker/cockroachdb/safe-dir:/safe-dir" cockroachdb/cockroach:v23.2.0 cert create-ca --certs-dir=/certs --ca-key=/safe-dir/ca.key

The breakdown of the command is:

• docker run: This command is used to run a command in a new container.
• --rm: This option automatically removes the container when it exits. This is to prevent leftover containers from taking up space.
• --name=roach-temp: This option sets the name of the container to roach-temp.
• -v "ckdb-temp:/cockroach/cockroach-data": This is a volume mount. It creates a volume named ckdb-temp and mounts the directory /cockroach/cockroach-data directory that is in the container.
• -v "/docker/cockroachdb/certs:/certs": It mounts the directory "/docker/cockroachdb/certs" on the host to the /certs directory in the container.
• -v "/docker/cockroachdb/safe-dir:/safe-dir": Similarly, this volume mount maps /docker/cockroachdb/safe-dir on the host to the /safe-dir directory in the container.
• cockroachdb/cockroach:v23.2.0: This is the image that the container is based on. In this case, it’s version 23.2.0 of the CockroachDB image.
• cert create-ca --certs-dir=/certs --ca-key=/safe-dir/ca.key: These are arguments passed to the cockroach command in the container. It creates a new Certificate Authority (CA) using the /certs directory for storing certificates and /safe-dir/ca.key as the location of the CA key.

It might take sometime for the run to finish, but you can kill it once it throws the error Failed running start-single-node in the docker logs (docker logs -f roach-temp) to you. Run docker rm -f roach-temp, if the volume was not deleted, you’ll also need to run docker volume rm ckdb-temp.

Directory listing of safe-dir for CA key

Creating node certificates

Paste the command blocks one by one.

Replace the FQDNs above cockroachdb.selfhosted.club and cockroachdb.int.selfhosted.club with your FQDNs for every server, leave localhost,127.0.0.1 and the hostname of the container you are starting as you will need this later to initialise the cluster.

First Node:

docker run --rm --name=roach-temp -v "ckdb-temp:/cockroach/cockroach-data" -v "/docker/cockroachdb/certs:/certs" -v "/docker/cockroachdb/safe-dir:/safe-dir" cockroachdb/cockroach:v23.2.0 cert create-node roach1.selfhosted.club roach1.internal.selfhosted.club cockroachdb.selfhosted.club cockroachdb.internal.selfhosted.club localhost roach1 127.0.0.1 --certs-dir=/certs --ca-key=/safe-dir/ca.key

mv certs/node.key certs/node1.key
mv certs/node.crt certs/node1.crt

After running the first command, before the mv commands, you should see: Directory listing of certs for node certificates after running command once

Second Node:

docker run --rm --name=roach-temp -v "ckdb-temp:/cockroach/cockroach-data" -v "/docker/cockroachdb/certs:/certs" -v "/docker/cockroachdb/safe-dir:/safe-dir" cockroachdb/cockroach:v23.2.0 cert create-node roach2.selfhosted.club roach2.internal.selfhosted.club cockroachdb.selfhosted.club cockroachdb.internal.selfhosted.club localhost roach2 127.0.0.1 --certs-dir=/certs --ca-key=/safe-dir/ca.key

mv certs/node.key certs/node2.key
mv certs/node.crt certs/node2.crt

You can ignore the W240116 17:11:14.433285 1 security/certificate_loader.go:314 [-] 1 bad filename /certs/node1.crt: unknown prefix "node1" errors as cockroachDB does not expect the files to be in the directory.

Third Node:

docker run --rm --name=roach-temp -v "ckdb-temp:/cockroach/cockroach-data" -v "/docker/cockroachdb/certs:/certs" -v "/docker/cockroachdb/safe-dir:/safe-dir" cockroachdb/cockroach:v23.2.0 cert create-node roach3.selfhosted.club roach3.internal.selfhosted.club cockroachdb.selfhosted.club cockroachdb.internal.selfhosted.club localhost roach3 127.0.0.1 --certs-dir=/certs --ca-key=/safe-dir/ca.key


mv certs/node.key certs/node3.key
mv certs/node.crt certs/node3.crt

After all blocks are executed, your directory should look like this: Directory listing of certs for node certificates after running all commands

Securing instance to instance traffic

To secure your instances properly, I recommend to ONLY allow traffic from your other CockroachDB servers one to another over ports 26257, that can be achieved by running >the following commands:

UFW

sudo ufw allow from replace.with.instance.ip to any port 26257 proto tcp

IPTables

sudo iptables -A INPUT -p tcp -s place.with.instance.ip -dport 26257 -j ACCEPT
sudo iptables-save > /etc/iptables/rules.v4

Better yet, use an overlay network such as Wireguard mesh, Tailscale or Netbird.

Create a client certificate and key-pair

We need to run our temporary container again to generate a login key pair:

docker run --rm --name=roach-temp -v "ckdb-temp:/cockroach/cockroach-data" -v "/docker/cockroachdb/certs:/certs" -v "/docker/cockroachdb/safe-dir:/safe-dir" cockroachdb/cockroach:v23.2.0 cert create-client root --certs-dir=/certs --ca-key=/safe-dir/ca.key

Directory listing of certs after running command to create client certificate

Distributing Certificates

These compose files will need to be adjusted to EVERY node you are going to run the docker container on, meaning the regions and the --advertise-addr. Create on each host, the directory structure as we wrote previously, and transfer the certificates (node#.crt, node#.key & ca.crt) to those hosts. Rename those files to node.crt and node.key.

If you’re using a cloud provider (like AWS, GCP, Azure), you may need to configure the security group or firewall rules through their web console or CLI to allow traffic from the specified IP address over the given port.

Starting up

Once all the certificates are distributed, we are ready to start up the cluster! The first thing that we will do is bring up all the nodes before we initialise the cluster. The initialisation can be done from any node that holds the client certificates, in our case, I will be launching it from roach1 as the certs are already there.

The command we are going to run on all the nodes first is:

docker-compose up -d && docker compose logs -f

Once all the nodes are up, you should see the following lines:

*                                                                                                                                                                                                                                   
* WARNING: Running a server without --sql-addr, with a combined RPC/SQL listener, is deprecated.                                                    
* This feature will be removed in a later version of CockroachDB.
*
*
* INFO: initial startup completed.
* Node will now attempt to join a running cluster, or wait for `cockroach init`.
* Client connections will be accepted after this completes successfully.
* Check the log file(s) for progress.
*
*
* WARNING: The server appears to be unable to contact the other nodes in the cluster. Please try:
*
* - starting the other nodes, if you haven't already;
* - double-checking that the '--join' and '--listen'/'--advertise' flags are set up correctly;
* - running the 'cockroach init' command if you are trying to initialize a new cluster.
*
* If problems persist, please see https://www.cockroachlabs.com/docs/v23.1/cluster-setup-troubleshooting.html.
*

The lines above usually that means it will be ok, however if there is an issue, I would append --logtostderr in the docker compose command portion to see the output, and > you should be looking then for the lines:

‹roach2.internal.selfhosted.club:26257› is itself waiting for init, will retry

Initialising the database

On our first node where we generated the certificates (roach1), we will run the following command:

docker exec -it roach1 cockroach init --certs-dir=/clientcert --host=roach1

You should see Cluster successfully initialized on the screen and the following from your docker logs: Initialised database

Connecting and creating a user

We currently only have the root user with the certificate that is able to connect. While going to https://roach1:8080 we get a screen that asks us to create a user, the command is simple, though you will need to login to the database with a client to run the SQL queries.

One of my favorite ones is TablePlus which comes under the SetApp subsciption for Mac users.

Connecting to database using TablePlus and certificates

Once you are logged in (remember, you will need the client certificates we have generated earlier and the ca.crt file to connect), we will run the following SQL commands:

CREATE user ilia with password 'selfhosted.club';
GRANT admin TO ilia;

Voila! Login to your new replicated database and execute the queries you need.

Hooray

To see the view below, you need to enable the console in the docker-compose file, and connect over port 8080.

CockroachDB Dashboard