How to Install and Configure CockroachDB

How to Install and Configure CockroachDB

CockroachDB is a distributed SQL database that provides horizontal scalability, strong consistency, and survivability across data center failures. It is an excellent choice for applications running on multiple Breeze instances that need a PostgreSQL-compatible, resilient database layer.

Installing CockroachDB

Download and install CockroachDB on your Breeze instance:

curl https://binaries.cockroachdb.com/cockroach-v23.2.0.linux-amd64.tgz | tar -xz
sudo cp cockroach-v23.2.0.linux-amd64/cockroach /usr/local/bin/
sudo mkdir -p /usr/local/lib/cockroach
sudo cp -r cockroach-v23.2.0.linux-amd64/lib/* /usr/local/lib/cockroach/

Create a dedicated user and data directory:

sudo useradd -r -s /bin/false cockroach
sudo mkdir -p /var/lib/cockroach /var/log/cockroach
sudo chown cockroach:cockroach /var/lib/cockroach /var/log/cockroach

Generating Certificates

CockroachDB uses TLS certificates for inter-node and client communication. Generate the CA and node certificates:

mkdir -p certs my-safe-directory
cockroach cert create-ca --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach cert create-node localhost $(hostname) your-breeze-ip \
  --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key

Starting a Single-Node Cluster

For development or testing on a single Breeze instance:

cockroach start-single-node \
  --certs-dir=certs \
  --store=/var/lib/cockroach \
  --listen-addr=0.0.0.0:26257 \
  --http-addr=0.0.0.0:8080 \
  --background

Access the admin UI at http://your-breeze-ip:8080.

Starting a Multi-Node Cluster

For production deployments across multiple Breeze instances, start each node with a join flag:

cockroach start \
  --certs-dir=certs \
  --store=/var/lib/cockroach \
  --listen-addr=0.0.0.0:26257 \
  --http-addr=0.0.0.0:8080 \
  --join=breeze1-ip:26257,breeze2-ip:26257,breeze3-ip:26257

Initialize the cluster from any one node:

cockroach init --certs-dir=certs --host=breeze1-ip:26257

Creating a Database and User

Connect with the SQL shell and set up your application database:

cockroach sql --certs-dir=certs --host=localhost:26257

CREATE DATABASE myapp;
CREATE USER appuser WITH PASSWORD 'SecurePass!';
GRANT ALL ON DATABASE myapp TO appuser;

Connecting from Your Application

CockroachDB is PostgreSQL-compatible, so use any PostgreSQL driver. For example, with a Python application on your Breeze instance:

import psycopg2
conn = psycopg2.connect(
    host="your-breeze-ip",
    port=26257,
    dbname="myapp",
    user="appuser",
    password="SecurePass!",
    sslmode="verify-full",
    sslrootcert="certs/ca.crt"
)

CockroachDB handles distributed transactions, automatic rebalancing, and rolling upgrades, making it a strong fit for production workloads that require zero-downtime operations across your Breeze fleet.