How to Install CockroachDB on Ubuntu 24.04 VPS — Distributed SQL with PostgreSQL Compatibility

CockroachDB is the database you pick when downtime is not an option and "call a DBA at 3 AM" is not a valid disaster-recovery plan. It speaks the PostgreSQL wire protocol, scales horizontally by adding nodes, survives full-node loss with zero manual failover, and gives you serializable ACID transactions across a geographically distributed cluster. This guide walks you from zero to a production-ready, TLS-secured three-node CockroachDB cluster on Ubuntu 24.04 VPS instances, plus a quick single-node dev setup for local experimentation.

Production-ready distributed SQL starts here. Deploy three CloudCore Business VPS instances and run a resilient CockroachDB cluster that survives node loss without dropping a single transaction.

Table of Contents

What is CockroachDB?

CockroachDB is an open-source, distributed SQL database built by Cockroach Labs. Named after the insect famous for being indestructible, it is engineered to survive anything short of total cluster annihilation and keep serving queries. Under the hood it combines ideas from Google's Spanner paper, the Raft consensus protocol, and a PostgreSQL-compatible SQL layer. The result is a database that looks like PostgreSQL to your application code but scales and self-heals like a cloud-native system.

Every table in CockroachDB is split into contiguous key-value slices called ranges, each replicated to three nodes (by default) via Raft. A write only commits once a quorum of replicas acknowledges it, so any single-node failure is transparent. Reads are served from the nearest leaseholder, and the cluster automatically rebalances ranges as you add or remove nodes. Because the data model is a single, globally consistent key-value store with SQL layered on top, you get serializable isolation by default — the strongest transactional guarantee the SQL standard defines.

CockroachDB is used for order management at transaction-heavy fintechs, user authentication services that can't afford to go down, multi-region SaaS products that need data residency per tenant, IoT ingestion at scale, and any workload where operational simplicity matters more than the last 5% of single-node OLTP throughput. It is less suited to analytical workloads (use a columnar store like ClickHouse for that) or to simple single-server CRUD apps where a standard PostgreSQL install would be both cheaper and simpler.

Why Self-Host CockroachDB vs. Cockroach Cloud?

Cockroach Labs offers a managed service (Cockroach Cloud / Serverless / Dedicated), but self-hosting on your own VPS fleet has concrete advantages for teams that already operate Linux infrastructure:

• Flat, predictable pricing — A three-node cluster on VPS costs the same whether you run 10 queries or 10 million. Cockroach Serverless bills by request units; Dedicated bills by the hour per vCPU. Both can surprise you.
• Full data residency control — You choose exactly which country, provider, and data center each replica lives in. This matters for GDPR, HIPAA-adjacent workloads, and contracts that prohibit third-party cloud storage.
• Your own networking and peering — Put the cluster on a private network alongside your application servers with zero egress fees, instead of paying cross-cloud bandwidth for managed-DB traffic.
• No vendor lock-in — CockroachDB is Apache 2.0 (CockroachDB Core) and BSL (Enterprise features). Your cluster keeps running even if Cockroach Labs changes pricing, ToS, or their managed offerings.
• Deeper operational access — You can tune OS-level parameters, mount specialty storage, run custom backup scripts, scrape Prometheus metrics directly, and patch the kernel on your schedule.
• Cost at scale — At production scale (100+ GB of data, a handful of vCPUs per node), self-hosting on three CloudCore Business instances typically runs 40–70% cheaper than equivalent managed dedicated clusters.

Self-Hosted vs. Cockroach Cloud Comparison

Prerequisites

Before you begin, make sure you have:

• Three VPS instances running Ubuntu 24.04 LTS (one node works for dev, three is the minimum for HA)
• SSH access to each server with sudo or root privileges
• At least 4 GB RAM, 2 vCPU, and SSD-backed storage per node (Cockroach Labs recommends 4+ vCPU, 8 GB RAM for real production)
• Low-latency network between nodes (same data center or private VLAN). Round-trip latency between nodes should be under 10 ms for good write performance.
• Open ports — 26257/tcp (SQL + inter-node), 8080/tcp (DB Console)

Recommended Plan: CloudCore Business ×3

For a resilient three-node CockroachDB cluster, deploy three CloudCore Business VPS instances. This gives you:

- 6 vCPU cores per node

- 16 GB RAM per node

- 200 GB NVMe SSD per node

- Unmetered bandwidth

- Private network between instances (zero egress within the data center)

Three nodes together comfortably handle several thousand QPS for typical OLTP workloads while surviving a full node loss with zero downtime.

For the rest of this guide we will assume three nodes with the following hostnames and private IPs:

• cockroach-1 — 10.0.0.11
• cockroach-2 — 10.0.0.12
• cockroach-3 — 10.0.0.13

sudo tee -a /etc/hosts > /dev/null <<EOF
10.0.0.11 cockroach-1
10.0.0.12 cockroach-2
10.0.0.13 cockroach-3
EOF

Connect to your first node to get started:

ssh [email protected]

Step 1: Update System Packages

Patch Ubuntu on every node before installing anything.

sudo apt update && sudo apt upgrade -y

Install a few utilities CockroachDB expects:

sudo apt install -y curl wget ntp ca-certificates

CockroachDB depends on accurate clocks across all nodes. Clock skew larger than 500 ms will cause nodes to be evicted from the cluster. Enable and start chrony (the modern NTP replacement):

sudo apt install -y chrony
sudo systemctl enable --now chrony
chronyc tracking

Expected output (abbreviated):

Reference ID    : A29FC87B (time.cloudflare.com)
Stratum         : 3
System time     : 0.000031221 seconds slow of NTP time
Last offset     : -0.000039188 seconds
RMS offset      : 0.000121547 seconds

Any offset well under 100 ms is fine. Repeat on all three nodes.

Step 2: Download and Install the CockroachDB Binary

CockroachDB ships as a single static Go binary — no runtime, no interpreter, no package manager dependencies. Grab the latest stable release from the official download URL.

On each node:

cd /tmp
wget https://binaries.cockroachdb.com/cockroach-v24.3.3.linux-amd64.tgz

Verify the checksum (Cockroach Labs publishes SHA-256 sums on their release page):

sha256sum cockroach-v24.3.3.linux-amd64.tgz

Extract and install:

tar -xzf cockroach-v24.3.3.linux-amd64.tgz
sudo cp cockroach-v24.3.3.linux-amd64/cockroach /usr/local/bin/
sudo chmod +x /usr/local/bin/cockroach

Copy the bundled GEOS libraries (needed for spatial queries):

sudo mkdir -p /usr/local/lib/cockroach
sudo cp cockroach-v24.3.3.linux-amd64/lib/libgeos.so /usr/local/lib/cockroach/
sudo cp cockroach-v24.3.3.linux-amd64/lib/libgeos_c.so /usr/local/lib/cockroach/

Verify the install:

cockroach version

Expected output:

Build Tag:        v24.3.3
Build Time:       2026/01/15 14:22:03
Distribution:     CCL
Platform:         linux amd64
Go Version:       go1.22.8

Repeat Step 2 on all three nodes.

Create a dedicated system user and data directory:

sudo useradd --system --home /var/lib/cockroach --shell /bin/false cockroach
sudo mkdir -p /var/lib/cockroach /etc/cockroach
sudo chown -R cockroach:cockroach /var/lib/cockroach /etc/cockroach

Step 3: Start a Single-Node Insecure Dev Cluster

Before wiring up TLS and three nodes, it is worth running a quick insecure single-node instance to verify everything works and give you a scratch database for local experimentation. Never use --insecure mode on the public internet — it disables all authentication and encryption.

On cockroach-1 (or a disposable dev VPS), run:

cockroach start-single-node \
    --insecure \
    --listen-addr=localhost:26257 \
    --http-addr=localhost:8080 \
    --store=/tmp/cockroach-dev \
    --background

Expected output:

*
WARNING: RUNNING IN INSECURE MODE!
*
- Your cluster is open for any client that can access localhost.
- Any user, even root, can log in without providing a password.
- Any user, connecting as root, can read or write any data in your cluster.
- There is no network encryption nor authentication, and thus no confidentiality.
*
CockroachDB node starting at 2026-04-16 10:00:00 UTC (took 1.5s)
build:               CCL v24.3.3 @ 2026/01/15 14:22:03 (go1.22.8)
webui:               http://localhost:8080
sql:                 postgresql://root@localhost:26257?sslmode=disable
RPC client flags:    cockroach <client cmd> --host=localhost:26257 --insecure
logs:                /tmp/cockroach-dev/logs

Open a SQL shell:

cockroach sql --insecure --host=localhost:26257

Run a quick sanity check:

CREATE DATABASE demo;
USE demo;
CREATE TABLE users (id SERIAL PRIMARY KEY, email STRING UNIQUE, created_at TIMESTAMP DEFAULT now());
INSERT INTO users (email) VALUES ('[email protected]'), ('[email protected]');
SELECT * FROM users;

Expected output:

id         |       email       |         created_at
---------------------+-------------------+----------------------------
 1023456789012345671 | [email protected] | 2026-04-16 10:01:15.12345
 1023456789012345672 | [email protected]   | 2026-04-16 10:01:15.12378
(2 rows)

Exit the shell and stop the dev node:

\q

cockroach quit --insecure --host=localhost:26257

Clean up the dev data directory before we set up the real cluster:

rm -rf /tmp/cockroach-dev

Good — the binary works. Now let's build the real thing.

Step 4: Generate TLS Certificates for a Secure Cluster

A secure CockroachDB cluster uses mutual TLS for all inter-node and client connections. You will generate a private certificate authority (CA), then issue one node certificate per server (which lets the node both serve TLS and connect to peers) and at least one client certificate per user.

Pick one node to act as the cert-generation host — we'll use cockroach-1. The CA private key must never leave that node. Only the generated node and client certs travel to the other servers.

On cockroach-1, create a safe home for the CA key:

mkdir -p ~/cockroach-ca-keys
mkdir -p ~/cockroach-certs

Create the Certificate Authority

cockroach cert create-ca \
    --certs-dir=~/cockroach-certs \
    --ca-key=~/cockroach-ca-keys/ca.key

This produces ~/cockroach-certs/ca.crt (distributable) and ~/cockroach-ca-keys/ca.key (keep secret).

Create Node Certificates

You need one node certificate per server. The certificate must list every address (hostname + IP) clients or other nodes might use to reach that node.

Node 1:

cockroach cert create-node \
    cockroach-1 \
    10.0.0.11 \
    localhost \
    127.0.0.1 \
    --certs-dir=~/cockroach-certs \
    --ca-key=~/cockroach-ca-keys/ca.key

This generates node.crt and node.key in the certs directory. Rename them before generating the next node's certs (otherwise they'll be overwritten):

mkdir -p ~/node1-certs
cp ~/cockroach-certs/ca.crt ~/node1-certs/
mv ~/cockroach-certs/node.crt ~/node1-certs/
mv ~/cockroach-certs/node.key ~/node1-certs/

Node 2:

cockroach cert create-node \
    cockroach-2 \
    10.0.0.12 \
    localhost \
    127.0.0.1 \
    --certs-dir=~/cockroach-certs \
    --ca-key=~/cockroach-ca-keys/ca.key
mkdir -p ~/node2-certs
cp ~/cockroach-certs/ca.crt ~/node2-certs/
mv ~/cockroach-certs/node.crt ~/node2-certs/
mv ~/cockroach-certs/node.key ~/node2-certs/

Node 3:

cockroach cert create-node \
    cockroach-3 \
    10.0.0.13 \
    localhost \
    127.0.0.1 \
    --certs-dir=~/cockroach-certs \
    --ca-key=~/cockroach-ca-keys/ca.key
mkdir -p ~/node3-certs
cp ~/cockroach-certs/ca.crt ~/node3-certs/
mv ~/cockroach-certs/node.crt ~/node3-certs/
mv ~/cockroach-certs/node.key ~/node3-certs/

Create a Client Certificate for root

cockroach cert create-client \
    root \
    --certs-dir=~/cockroach-certs \
    --ca-key=~/cockroach-ca-keys/ca.key

This creates client.root.crt and client.root.key in the certs directory.

Distribute the Node Certificates

Copy each node's bundle to the right server. From cockroach-1:

# Node 1 — install locally
sudo mkdir -p /etc/cockroach/certs
sudo cp ~/node1-certs/* /etc/cockroach/certs/
sudo cp ~/cockroach-certs/client.root.crt /etc/cockroach/certs/
sudo cp ~/cockroach-certs/client.root.key /etc/cockroach/certs/
sudo chown -R cockroach:cockroach /etc/cockroach/certs
sudo chmod 700 /etc/cockroach/certs
sudo chmod 600 /etc/cockroach/certs/*.key
Node 2
scp ~/node2-certs/* [email protected]:/tmp/
scp ~/cockroach-certs/client.root.crt ~/cockroach-certs/client.root.key [email protected]:/tmp/
Node 3
scp ~/node3-certs/* [email protected]:/tmp/
scp ~/cockroach-certs/client.root.crt ~/cockroach-certs/client.root.key [email protected]:/tmp/

On cockroach-2 and cockroach-3, move the files into place:

sudo mkdir -p /etc/cockroach/certs
sudo mv /tmp/node.crt /tmp/node.key /tmp/ca.crt /tmp/client.root.* /etc/cockroach/certs/
sudo chown -R cockroach:cockroach /etc/cockroach/certs
sudo chmod 700 /etc/cockroach/certs
sudo chmod 600 /etc/cockroach/certs/*.key

Step 5: Initialize a Three-Node Secure Cluster

Now we create a systemd unit on each node and start CockroachDB, pointing each process at the certs directory and telling it which peers to join.

Create the systemd Unit

On each node, create /etc/systemd/system/cockroach.service:

sudo tee /etc/systemd/system/cockroach.service > /dev/null <<'EOF'
[Unit]
Description=CockroachDB
Requires=network.target
After=network.target
[Service]
Type=notify
User=cockroach
Group=cockroach
WorkingDirectory=/var/lib/cockroach
LimitNOFILE=35000
Restart=always
RestartSec=10
ExecStart=/usr/local/bin/cockroach start \
    --certs-dir=/etc/cockroach/certs \
    --store=/var/lib/cockroach \
    --listen-addr=0.0.0.0:26257 \
    --advertise-addr=NODE_ADVERTISE_ADDR:26257 \
    --http-addr=0.0.0.0:8080 \
    --join=10.0.0.11:26257,10.0.0.12:26257,10.0.0.13:26257 \
    --cache=.25 \
    --max-sql-memory=.25
TimeoutStopSec=300
[Install]
WantedBy=default.target
EOF

Replace NODE_ADVERTISE_ADDR with that node's advertised IP (10.0.0.11 on node 1, .12 on node 2, .13 on node 3):

# On cockroach-1
sudo sed -i 's/NODE_ADVERTISE_ADDR/10.0.0.11/' /etc/systemd/system/cockroach.service
On cockroach-2
sudo sed -i 's/NODE_ADVERTISE_ADDR/10.0.0.12/' /etc/systemd/system/cockroach.service
On cockroach-3
sudo sed -i 's/NODE_ADVERTISE_ADDR/10.0.0.13/' /etc/systemd/system/cockroach.service

Quick explanation of the key flags:

• --certs-dir — Path with ca.crt, node.crt, node.key, and client certs.
• --store — Where CockroachDB stores its data. Use a dedicated SSD-backed volume in production.
• --listen-addr — Address to accept both SQL and inter-node RPC traffic.
• --advertise-addr — The address other nodes should use to reach this one. Critical when listening on 0.0.0.0.
• --http-addr — Address for the DB Console (admin UI on port 8080).
• --join — Comma-separated list of peer addresses. Listing all nodes here is fine; Cockroach deduplicates.
• --cache=.25 — Uses 25% of system RAM for the cache. 25–40% is typical for dedicated DB nodes.
• --max-sql-memory=.25 — Another 25% of RAM for SQL query execution.

Start CockroachDB on All Nodes

Reload systemd and start the service on each node:

sudo systemctl daemon-reload
sudo systemctl enable --now cockroach
sudo systemctl status cockroach

Expected output (abbreviated):

● cockroach.service - CockroachDB
     Loaded: loaded (/etc/systemd/system/cockroach.service; enabled)
     Active: active (running) since Wed 2026-04-16 10:15:00 UTC; 20s ago
   Main PID: 2345 (cockroach)
      Tasks: 42 (limit: 18923)
     Memory: 420.0M
        CPU: 2.5s

At this point each node is running but waiting for cluster initialization. The logs will say something like node is waiting for cluster initialization. Run cockroach init exactly once, from any node, to bootstrap the cluster:

cockroach init \
    --certs-dir=/etc/cockroach/certs \
    --host=10.0.0.11:26257

Expected output:

Cluster successfully initialized

Within a few seconds all three nodes join the cluster. Confirm:

cockroach node status \
    --certs-dir=/etc/cockroach/certs \
    --host=10.0.0.11:26257

Expected output (abbreviated):

id |     address      |  sql_address   |  build  |         started_at         |         updated_at          | locality | is_available | is_live
-----+------------------+----------------+---------+----------------------------+-----------------------------+----------+--------------+---------
   1 | 10.0.0.11:26257  | 10.0.0.11:26257| v24.3.3 | 2026-04-16 10:15:02.123... | 2026-04-16 10:16:30.456...  |          | true         | true
   2 | 10.0.0.12:26257  | 10.0.0.12:26257| v24.3.3 | 2026-04-16 10:15:10.234... | 2026-04-16 10:16:30.567...  |          | true         | true
   3 | 10.0.0.13:26257  | 10.0.0.13:26257| v24.3.3 | 2026-04-16 10:15:18.345... | 2026-04-16 10:16:30.678...  |          | true         | true
(3 rows)

You now have a live, TLS-secured, three-node CockroachDB cluster.

Step 6: Use the SQL Client

Connect to any node with the cockroach sql built-in client:

cockroach sql \
    --certs-dir=/etc/cockroach/certs \
    --host=10.0.0.11:26257

Expected prompt:

# Welcome to the CockroachDB SQL shell.
All statements must be terminated by a semicolon.
To exit, type: \q.
#
Server version: CockroachDB CCL v24.3.3
Cluster ID: 8b0c9f1c-a4a3-4b4a-9f3f-1234567890ab
#
[email protected]:26257/defaultdb>

Create a user and a database:

CREATE USER app WITH PASSWORD 'change-me-strong-password';
CREATE DATABASE orders;
GRANT ALL ON DATABASE orders TO app;
USE orders;
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING UNIQUE NOT NULL,
    name STRING NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO customers (email, name) VALUES
    ('[email protected]', 'Alice'),
    ('[email protected]', 'Bob');
SELECT * FROM customers;

Check replication — every range should have 3 replicas by default:

SHOW RANGES FROM TABLE customers;

Exit with \q.

Step 7: Explore the DB Console (Admin UI) on Port 8080

CockroachDB ships a built-in web-based admin UI (the "DB Console") on port 8080. From a workstation with access to the cluster's network, open:

https://10.0.0.11:8080

Because the UI uses the self-signed CA, your browser will warn about the certificate. Import ca.crt from the certs directory into your OS/browser trust store, or click through the warning for internal use.

Log in with a SQL user that has admin privileges. To promote the app user:

GRANT admin TO app;

Inside the DB Console you can inspect:

• Overview — cluster health, replicas under-replicated, live nodes, capacity
• Metrics — per-node CPU, memory, storage, SQL queries/sec, replication latency
• Databases — schema browser with table sizes and range distribution
• Statements — slow-query profiling with aggregated execution stats
• Transactions — retries, rollbacks, contention analysis
• Network Latency — inter-node ping matrix, essential for multi-region setups
• Jobs — running backups, schema changes, imports, and restores

Exposing the DB Console Publicly

For remote access, don't open port 8080 to the internet — put it behind a reverse proxy with authentication. The cleanest pattern is an Nginx reverse proxy with Let's Encrypt TLS and basic auth or OAuth2 on top.

Minimal Nginx block (add to /etc/nginx/sites-available/cockroach-ui):

server {
    listen 443 ssl;
    server_name cockroach.yourdomain.com;
    ssl_certificate     /etc/letsencrypt/live/cockroach.yourdomain.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/cockroach.yourdomain.com/privkey.pem;
    location / {
        auth_basic "CockroachDB Admin";
        auth_basic_user_file /etc/nginx/.cockroach-htpasswd;
        proxy_pass https://10.0.0.11:8080;
        proxy_ssl_verify off;  # self-signed node cert
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

Step 8: Configure Backups

CockroachDB's BACKUP statement writes consistent point-in-time snapshots to cloud object storage or an NFS share. The Enterprise license unlocks incremental and scheduled backups; the free tier supports full backups to userfile or cloud.

Full Backup to S3-Compatible Object Storage

Connect with the SQL shell and run:

BACKUP INTO 's3://my-cockroach-backups/prod?AWS_ACCESS_KEY_ID=AKIA...&AWS_SECRET_ACCESS_KEY=...'
    AS OF SYSTEM TIME '-10s';

Expected output:

job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+----------
 892345678901234567  | succeeded |                  1 | 1024 |             0 | 2048576

Scheduled Daily Backup

With an Enterprise license you can schedule backups directly in SQL:

CREATE SCHEDULE nightly_full
    FOR BACKUP INTO 's3://my-cockroach-backups/prod'
    WITH revision_history
    RECURRING '@daily'
    FULL BACKUP ALWAYS
    WITH SCHEDULE OPTIONS first_run = 'now';

Inspect scheduled backups:

SHOW SCHEDULES;

Restoring from Backup

To restore a full cluster from the latest backup:

RESTORE FROM LATEST IN 's3://my-cockroach-backups/prod';

To restore a single database:

RESTORE DATABASE orders FROM LATEST IN 's3://my-cockroach-backups/prod';

Backup to a Local NFS/Filesystem Mount

For air-gapped or simple setups, mount a shared volume (NFS, SSHFS, or rsync target) at /mnt/backups on all nodes and back up to it:

BACKUP INTO 'nodelocal://1/backups/full-2026-04-16';

The nodelocal://1/ prefix writes to node 1's extern directory (/var/lib/cockroach/extern by default). All three nodes must have that path writable.

Step 9: Perform a Rolling Upgrade

CockroachDB supports zero-downtime rolling upgrades between adjacent minor versions. You take nodes down one at a time, upgrade the binary, and bring them back — the cluster keeps serving traffic on the remaining nodes throughout.

Preconditions

• All nodes must be on the same minor version before upgrading.
• You can upgrade at most one minor version at a time (e.g. 24.2.x → 24.3.x is fine; 24.1.x → 24.3.x requires two hops).
• Take a full backup before starting.

Rolling Upgrade Procedure (per node)

cockroach node drain \
    --certs-dir=/etc/cockroach/certs \
    --host=10.0.0.11:26257 \
    --drain-wait=60s

sudo systemctl stop cockroach

sudo systemctl start cockroach

cockroach node status \
    --certs-dir=/etc/cockroach/certs \
    --host=10.0.0.11:26257

Wait until the upgraded node reports is_live: true and the cluster shows no under-replicated ranges in the DB Console. Then repeat for the next node.

Finalize the Upgrade

After all nodes run the new version, finalize the cluster version bump:

SET CLUSTER SETTING version = '24.3';

Before this command runs, the upgrade is reversible — you can downgrade nodes back. After finalization, the new version is locked in.

Step 10: Enable Prometheus Metrics

CockroachDB exposes a Prometheus-compatible metrics endpoint at /_status/vars on the DB Console port. Any scraper can pull thousands of time-series counters and gauges covering SQL throughput, storage, Raft, replication, and GC.

Scrape Endpoint

https://10.0.0.11:8080/_status/vars
https://10.0.0.12:8080/_status/vars
https://10.0.0.13:8080/_status/vars

Sample output:

# HELP sql_query_count Number of SQL queries executed
TYPE sql_query_count counter
sql_query_count{node_id="1"} 48923
HELP sql_mem_root_current Current sql statement memory usage for root
TYPE sql_mem_root_current gauge
sql_mem_root_current{node_id="1"} 0
HELP ranges Number of ranges
TYPE ranges gauge
ranges{node_id="1"} 42

Prometheus Scrape Config

If you already run a Prometheus server (see Install Prometheus on Ubuntu), add a job to /etc/prometheus/prometheus.yml:

scrape_configs:
  - job_name: 'cockroachdb'
    metrics_path: '/_status/vars'
    scheme: 'https'
    tls_config:
      ca_file: /etc/prometheus/cockroach-ca.crt
      insecure_skip_verify: false
    static_configs:
      - targets:
          - 10.0.0.11:8080
          - 10.0.0.12:8080
          - 10.0.0.13:8080
        labels:
          cluster: 'prod'

Copy the cluster CA to the Prometheus node first:

sudo cp ca.crt /etc/prometheus/cockroach-ca.crt
sudo chown prometheus:prometheus /etc/prometheus/cockroach-ca.crt
sudo systemctl reload prometheus

Key Metrics to Alert On

Step 11: Connect Apps via the PostgreSQL Wire Protocol

The payoff of the whole setup: CockroachDB speaks the PostgreSQL wire protocol (pgwire), so any driver that talks to PostgreSQL can talk to CockroachDB with only a connection string change.

Connection String Format

postgresql://app:[email protected]:26257/orders?sslmode=verify-full&sslrootcert=/path/to/ca.crt

For password-authenticated users:

postgresql://app:[email protected]:26257/orders?sslmode=verify-full&sslrootcert=ca.crt

For certificate-authenticated users (more secure, no passwords on the wire):

postgresql://[email protected]:26257/orders?sslmode=verify-full&sslrootcert=ca.crt&sslcert=client.app.crt&sslkey=client.app.key

psql

psql "postgresql://app:[email protected]:26257/orders?sslmode=verify-full&sslrootcert=ca.crt"

psql works the same way you use it with PostgreSQL — \dt, \d+ table, and most metacommands behave identically.

Node.js (pg)

import { Pool } from 'pg';
import fs from 'fs';
const pool = new Pool({
    host: '10.0.0.11',
    port: 26257,
    user: 'app',
    password: 'change-me-strong-password',
    database: 'orders',
    ssl: {
        ca: fs.readFileSync('/etc/ssl/cockroach-ca.crt').toString(),
        rejectUnauthorized: true,
    },
});
const { rows } = await pool.query('SELECT * FROM customers LIMIT 10');
console.log(rows);

Python (psycopg / SQLAlchemy / Django)

import psycopg
conn = psycopg.connect(
    host="10.0.0.11",
    port=26257,
    user="app",
    password="change-me-strong-password",
    dbname="orders",
    sslmode="verify-full",
    sslrootcert="/etc/ssl/cockroach-ca.crt",
)
with conn.cursor() as cur:
    cur.execute("SELECT id, email FROM customers")
    for row in cur.fetchall():
        print(row)

Django works out of the box — just use django.db.backends.postgresql and the appropriate OPTIONS for SSL. For full Django compatibility, install Cockroach Labs' django-cockroachdb adapter which handles the few SQL differences automatically.

Prisma

DATABASE_URL="postgresql://app:[email protected]:26257/orders?sslmode=verify-full&sslrootcert=./ca.crt"

In schema.prisma:

datasource db {
    provider = "cockroachdb"
    url      = env("DATABASE_URL")
}

Prisma ships first-class CockroachDB support — use the cockroachdb provider rather than postgresql so it picks correct default types (e.g. INT8 instead of INT4).

Connection Pooling and Load Balancing

Each node can serve reads and writes. For production, put a TCP load balancer (HAProxy or your cloud LB) in front of all three nodes on port 26257 and point your apps at the LB's address. Cockroach nodes are symmetric — any of them can accept any query.

A minimal HAProxy snippet:

frontend cockroach_sql
    bind *:26257
    default_backend cockroach_nodes
    mode tcp
backend cockroach_nodes
    mode tcp
    balance roundrobin
    option pgsql-check user haproxy_check
    server c1 10.0.0.11:26257 check
    server c2 10.0.0.12:26257 check
    server c3 10.0.0.13:26257 check

Troubleshooting

Viewing Logs

sudo journalctl -u cockroach -f

Structured CockroachDB logs also live under /var/lib/cockroach/logs/.

FAQ

Is CockroachDB fully compatible with PostgreSQL?

CockroachDB speaks the PostgreSQL wire protocol (pgwire) and supports a large subset of PostgreSQL SQL syntax, so most libpq-based drivers and ORMs like Prisma, SQLAlchemy, Django, GORM, and Hibernate work with minimal changes. It is not 100% identical: stored procedures in PL/pgSQL, some PostgreSQL extensions, sequences with certain gap guarantees, and a handful of built-in functions behave differently. In practice, most applications need only small adjustments for transaction retry logic (CockroachDB can return serialization errors that clients should retry) and sequence handling. If you're migrating from PostgreSQL, run your test suite against a CockroachDB instance and iterate — the changes are usually under a day of work for medium-size apps.

What is the minimum VPS size for a production CockroachDB cluster?

Cockroach Labs recommends at least 3 nodes for production, each with a minimum of 4 vCPU, 8 GB RAM, and SSD-backed storage. A three-node CloudCore Business cluster on vps-server.host (6 vCPU, 16 GB RAM, 200 GB NVMe per node) gives you comfortable headroom for small-to-medium workloads — several thousand QPS of mixed OLTP traffic is realistic. For larger production workloads, scale horizontally by adding nodes rather than enlarging a single node. CockroachDB is designed to spread load linearly across nodes, so going from 3 nodes to 6 nodes roughly doubles throughput.

Why would I self-host CockroachDB instead of using Cockroach Cloud?

Self-hosting on your own VPS gives you flat-rate, predictable pricing regardless of query volume, full control over data residency and networking, the ability to place nodes on any provider, and no per-request serverless billing surprises. Cockroach Cloud is an excellent managed option, especially for teams without ops capacity, but for teams that already operate VPS infrastructure and want to own the entire stack, self-hosting is typically 40–70% cheaper at equivalent scale. You also avoid the managed-service networking premium, which adds up fast if your application servers live somewhere other than the managed-DB's host cloud.

How does CockroachDB handle high availability?

CockroachDB replicates every range (a contiguous slice of a table) to three nodes by default using the Raft consensus protocol. A write is acknowledged once a majority of replicas (2 of 3) have durably stored it. This means a three-node cluster survives the loss of one node with zero data loss and no manual failover — reads and writes continue against the remaining two. When the failed node returns (or is replaced), CockroachDB automatically backfills missing replicas. Adding nodes increases both capacity and fault tolerance; with a five-node cluster and the default replication factor of three, you can lose two nodes simultaneously and stay online.

Can I run CockroachDB with my existing PostgreSQL client libraries?

Yes. Connect psql, pgx, node-postgres, Prisma, Django, Rails, and almost every other PostgreSQL driver to port 26257 using a standard postgresql:// connection string. The only common changes are (a) adding sslmode=verify-full plus the client certificate and CA paths for secure clusters, and (b) wrapping transactions in retry logic to handle serialization errors — Cockroach Labs publishes retry helpers for most popular languages. Tools that expect PostgreSQL-specific features like logical replication slots, LISTEN/NOTIFY, or PL/pgSQL stored procedures won't work unchanged; Cockroach offers change data capture (CDC) as a first-class replacement for streaming changes out.

Does CockroachDB support spatial queries and JSON like PostgreSQL?

Yes. CockroachDB ships with built-in spatial support via the bundled GEOS library (hence the libgeos.so files we installed in Step 2), including geometry/geography types, GiST-style spatial indexes, and most PostGIS-compatible functions. JSONB is supported natively with the same operators (->, ->>, @>, ?) and inverted indexes for fast containment queries. Full-text search has a more limited implementation than PostgreSQL's tsvector — for serious FTS workloads consider pairing CockroachDB with a dedicated search engine like OpenSearch.

Next Steps

Now that your CockroachDB cluster is live, here's what to tackle next:

• Set up automated backups to object storage — Configure daily full backups plus hourly incrementals to S3/MinIO/Backblaze B2. Use CREATE SCHEDULE with revision_history so you can restore to any second within your retention window.

• Build Grafana dashboards on top of Prometheus — Import Cockroach Labs' official Grafana dashboards (IDs 12483, 12991, 13154) and wire up alerts on liveness_livenodes, ranges_underreplicated, and sql_exec_latency_p99.

• Add a TCP load balancer in front of the cluster — HAProxy or your cloud LB on port 26257 lets applications use a single stable endpoint and automatically routes around node outages.

• Deploy a second cluster in another region for DR — Use CockroachDB's native cross-cluster replication (PCR) to stream changes to a standby cluster in a geographically separate data center.

• Rotate the root certificate before it expires — Node certs default to 5 years, client certs to 1 year, and the CA to 10 years. Put certificate expiry on your ops calendar and use cockroach cert create-node --overwrite to rotate without downtime.

• Harden network ACLs — Only your application servers, the LB, and your monitoring host should be able to reach port 26257. Lock it down with UFW or your cloud provider's security groups.

• Read the official tuning guide — Cockroach Labs maintains an excellent production checklist covering ulimits, kernel parameters, and storage tuning for high-throughput workloads.

Launch a Production-Ready CockroachDB Cluster in Minutes

Three CloudCore Business VPS instances give you the foundation for a highly available, PostgreSQL-compatible distributed SQL cluster:

- 6 vCPU, 16 GB RAM, 200 GB NVMe per node

- Unmetered bandwidth with private networking between instances

- SSD storage with low-latency writes ideal for Raft quorum

- Deploy three nodes, follow this guide, and be serving queries in under an hour

Deploy Your CockroachDB Cluster — from EUR 29.99/month per node.

Related install guides

• How to Install PostgreSQL on Ubuntu 24.04 — When you need the reference single-node relational database instead of distributed SQL
• How to Install Prometheus on Ubuntu 24.04 — Scrape the /_status/vars endpoint and build alerting on top of your cluster
• How to Install Nginx on Ubuntu 24.04 — Put the DB Console and SQL load balancer behind TLS and authentication

• CockroachDB Official Documentation
• CockroachDB Production Checklist
• CockroachDB SQL Feature Support