CockroachDB Installation: Distributed SQL

CockroachDB is a distributed SQL database that combines the simplicity of SQL with the scalability of NoSQL systems. It provides ACID transactions, multi-region replication, automatic failover, and horizontal scaling without sacrificing data consistency or operational simplicity. This comprehensive guide covers installation, cluster initialization, SQL operations, replication zones, monitoring, and best practices for deploying a production-grade CockroachDB cluster.

Table of Contents

Architecture and Design

CockroachDB uses a distributed architecture where data is automatically partitioned across nodes using consistent hashing. Each partition is replicated across multiple nodes for fault tolerance, with the Raft consensus algorithm ensuring agreement on data state. The system automatically balances data distribution, handles node failures, and rebalances data when the cluster topology changes.

CockroachDB provides strong ACID guarantees similar to PostgreSQL while scaling horizontally like distributed systems. It uses a multi-version concurrency control (MVCC) system for high concurrency and supporting snapshot isolation. Transactions can span multiple rows and tables without requiring distributed locks.

Installation

Install CockroachDB on Linux systems. Download the latest stable binary:

# Download CockroachDB (latest stable release)
cd /tmp
curl https://binaries.cockroachdb.com/cockroach-latest.linux-amd64.tgz | tar xz

# Copy binary to system path
sudo cp -i cockroach-latest.linux-amd64/cockroach /usr/local/bin/

# Verify installation
cockroach version
cockroach --help

Create a system user for running CockroachDB:

# Create CockroachDB user
sudo useradd -r -s /bin/false cockroachdb

# Create data directory
sudo mkdir -p /var/lib/cockroach
sudo chown cockroachdb:cockroachdb /var/lib/cockroach
sudo chmod 700 /var/lib/cockroach

# Create log directory
sudo mkdir -p /var/log/cockroach
sudo chown cockroachdb:cockroachdb /var/log/cockroach

Create a systemd service file for CockroachDB:

sudo nano /etc/systemd/system/cockroachdb.service

Add this configuration:

[Unit]
Description=CockroachDB
After=network.target

[Service]
Type=notify
User=cockroachdb
Group=cockroachdb

ExecStart=/usr/local/bin/cockroach start \
  --certs-dir=/etc/cockroach/certs \
  --store=/var/lib/cockroach \
  --listen-addr=192.168.1.10:26257 \
  --http-addr=192.168.1.10:8080 \
  --join=192.168.1.10:26257,192.168.1.11:26257,192.168.1.12:26257 \
  --cache=.25 \
  --max-sql-memory=.25 \
  --log-dir=/var/log/cockroach

StandardOutput=journal
StandardError=journal

Restart=always
RestartSec=5
KillMode=mixed
KillSignal=SIGTERM
TimeoutStopSec=60

[Install]
WantedBy=multi-user.target

Enable the service:

sudo systemctl daemon-reload
sudo systemctl enable cockroachdb

Cluster Initialization

Bootstrap a three-node CockroachDB cluster. First, create security certificates for secure communication:

# Create certificate directory
sudo mkdir -p /etc/cockroach/certs
sudo chmod 700 /etc/cockroach/certs
sudo chown cockroachdb:cockroachdb /etc/cockroach/certs

# Generate CA certificate
cockroach cert create-ca \
  --certs-dir=/etc/cockroach/certs \
  --ca-key=/etc/cockroach/certs/ca.key

# Generate node certificates for all three nodes
for i in 1 2 3; do
  cockroach cert create-node \
    --certs-dir=/etc/cockroach/certs \
    --ca-key=/etc/cockroach/certs/ca.key \
    192.168.1.1$i \
    node$i.local \
    node$i \
    localhost
done

# Create client certificate for administration
cockroach cert create-client \
  --certs-dir=/etc/cockroach/certs \
  --ca-key=/etc/cockroach/certs/ca.key \
  root

# Set proper permissions
sudo chown -R cockroachdb:cockroachdb /etc/cockroach/certs
sudo chmod 600 /etc/cockroach/certs/*.key
sudo chmod 644 /etc/cockroach/certs/*.crt

# Copy certificates to all nodes
for node in 192.168.1.11 192.168.1.12; do
  scp -r /etc/cockroach/certs/* $node:/etc/cockroach/certs/
done

Start the CockroachDB service on all nodes:

# On all nodes
sudo systemctl start cockroachdb
sudo systemctl status cockroachdb

# Monitor startup
sudo journalctl -u cockroachdb -f

Initialize the cluster on the first node:

# Run on first node to initialize cluster
cockroach init \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10:26257

# Verify cluster initialization
cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  --execute="SHOW DATABASES;"

Monitor cluster startup and node joining:

# Check node status via web UI or CLI
cockroach node status \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10

# Should show all three nodes with status "live"

Multi-Node Setup

Verify all nodes have joined the cluster:

# Connect to any node
cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10

-- Check cluster composition
SELECT node_id, address, sql_address FROM crdb_internal.gossip_nodes;

-- View replication status
SELECT range_id, start_key, end_key, replicas FROM crdb_internal.ranges LIMIT 5;

-- Check healthy status
SHOW CLUSTER SETTING cluster.organization;

Configure cluster settings for multi-node operation:

cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 << 'EOF'

-- Set cluster organization name
SET CLUSTER SETTING cluster.organization = 'My Company';

-- Enable location-aware replica placement
SET CLUSTER SETTING server.time_until_store_dead = '90s';

-- Configure distributed backup
SET CLUSTER SETTING server.shutdown.drain_wait = '5s';

-- Verify settings
SHOW CLUSTER SETTINGS;
EOF

SQL Shell and Queries

Connect to the CockroachDB cluster using the SQL shell:

# Connect as root user
cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10:26257 \
  --user=root

# Or via interactive shell with username/password
cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  --user=appuser

Create databases and users:

cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 << 'EOF'

-- Create database
CREATE DATABASE IF NOT EXISTS myapp;

-- Create user
CREATE USER IF NOT EXISTS appuser WITH PASSWORD 'secure_password';

-- Grant permissions
GRANT ALL ON DATABASE myapp TO appuser;

-- Switch to database
USE myapp;

-- Show databases
SHOW DATABASES;

-- Show users
SELECT user_name FROM system.users;
EOF

Create tables with appropriate schema:

cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  --database=myapp << 'EOF'

-- Create users table
CREATE TABLE users (
  id INT PRIMARY KEY DEFAULT unique_rowid(),
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now(),
  is_active BOOLEAN DEFAULT true
);

-- Create products table
CREATE TABLE products (
  id INT PRIMARY KEY DEFAULT unique_rowid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT now()
);

-- Create orders table with foreign key
CREATE TABLE orders (
  id INT PRIMARY KEY DEFAULT unique_rowid(),
  user_id INT NOT NULL REFERENCES users(id),
  order_date TIMESTAMP DEFAULT now(),
  total_amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create order items table
CREATE TABLE order_items (
  id INT PRIMARY KEY DEFAULT unique_rowid(),
  order_id INT NOT NULL REFERENCES orders(id),
  product_id INT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Create indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- Show tables
SHOW TABLES;

-- Show table schema
SHOW CREATE TABLE users;
EOF

Insert and query data:

cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  --database=myapp << 'EOF'

-- Insert users
INSERT INTO users (username, email) VALUES
  ('alice', '[email protected]'),
  ('bob', '[email protected]'),
  ('charlie', '[email protected]');

-- Insert products
INSERT INTO products (name, description, price, stock_quantity) VALUES
  ('Laptop', 'High-performance laptop', 999.99, 10),
  ('Mouse', 'Wireless mouse', 29.99, 100),
  ('Keyboard', 'Mechanical keyboard', 149.99, 50);

-- Insert orders
INSERT INTO orders (user_id, total_amount, status) VALUES
  (1, 1029.98, 'completed'),
  (2, 179.98, 'pending'),
  (3, 999.99, 'shipped');

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 999.99),
  (1, 2, 1, 29.99),
  (2, 3, 1, 149.99),
  (2, 2, 1, 29.99),
  (3, 1, 1, 999.99);

-- Query data
SELECT * FROM users;
SELECT * FROM products WHERE price > 50;
SELECT * FROM orders WHERE status = 'completed';

-- Complex query with joins
SELECT 
  u.username,
  o.id as order_id,
  o.total_amount,
  COUNT(oi.id) as item_count
FROM users u
JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY u.username, o.id, o.total_amount
ORDER BY o.total_amount DESC;
EOF

Replication Zones

CockroachDB uses replication zones to control data replication and placement. Configure replication zones:

cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  --database=myapp << 'EOF'

-- View default zone configuration
SHOW ZONE CONFIGURATION FOR RANGE default;

-- Configure zone for specific database (3x replication)
ALTER DATABASE myapp CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '[]',
  lease_preferences = '[]';

-- Configure zone for specific table (2x replication for less critical data)
ALTER TABLE products CONFIGURE ZONE USING
  num_replicas = 2;

-- Configure zone with placement constraints (requires node labels)
ALTER TABLE users CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '[+zone=us-east-1a, +zone=us-east-1b, +zone=us-east-1c]';

-- View zone configurations
SHOW ZONE CONFIGURATIONS;

-- Reset to defaults
ALTER TABLE products CONFIGURE ZONE USING
  num_replicas = COPY FROM PARENT,
  constraints = COPY FROM PARENT,
  lease_preferences = COPY FROM PARENT;
EOF

Database Schema

Design schema considering CockroachDB's strengths in distributed operations:

cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  --database=myapp << 'EOF'

-- Create audit table for tracking changes
CREATE TABLE audit_log (
  id INT PRIMARY KEY DEFAULT unique_rowid(),
  table_name VARCHAR(255),
  operation VARCHAR(50),
  record_id INT,
  old_values JSONB,
  new_values JSONB,
  changed_by VARCHAR(255),
  changed_at TIMESTAMP DEFAULT now()
);

-- Create indexes for audit efficiency
CREATE INDEX idx_audit_table_name ON audit_log(table_name);
CREATE INDEX idx_audit_changed_at ON audit_log(changed_at DESC);

-- Create view for active users
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = true;

-- Create view for revenue analysis
CREATE VIEW order_revenue AS
SELECT 
  DATE_TRUNC('day', o.order_date) as order_day,
  COUNT(DISTINCT o.user_id) as unique_customers,
  COUNT(*) as total_orders,
  SUM(o.total_amount) as total_revenue,
  AVG(o.total_amount) as avg_order_value
FROM orders o
WHERE o.status IN ('completed', 'shipped')
GROUP BY DATE_TRUNC('day', o.order_date);

-- Create materialized view (requires manual refresh)
CREATE MATERIALIZED VIEW product_sales AS
SELECT 
  p.id,
  p.name,
  COUNT(oi.id) as sales_count,
  SUM(oi.quantity) as total_quantity,
  SUM(oi.quantity * oi.unit_price) as total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

-- Show schema
SHOW TABLES;
SHOW VIEWS;
EOF

Transactions and Isolation

Leverage CockroachDB's ACID transaction support:

cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  --database=myapp << 'EOF'

-- Transaction for order processing
BEGIN TRANSACTION;

-- Check product availability
SELECT id, stock_quantity FROM products WHERE id = 1 FOR UPDATE;

-- Deduct from stock
UPDATE products SET stock_quantity = stock_quantity - 5 WHERE id = 1;

-- Create order
INSERT INTO orders (user_id, total_amount, status) VALUES (1, 4999.95, 'pending');

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) 
VALUES (LASTVAL(), 1, 5, 999.99);

-- Commit if all queries succeed, rollback if any fail
COMMIT;

-- Example: Use savepoints for conditional logic
BEGIN;
INSERT INTO orders (user_id, total_amount, status) VALUES (1, 99.99, 'pending');
SAVEPOINT sp1;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) 
VALUES (LASTVAL(), 999, 1, 99.99);

-- If previous insert fails (product doesn't exist), rollback to savepoint
ROLLBACK TO SAVEPOINT sp1;

COMMIT;

-- Set transaction isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Show current isolation level
SHOW TRANSACTION ISOLATION LEVEL;
EOF

Monitoring and Metrics

Monitor cluster health and performance:

# Access web UI
# Open browser to http://192.168.1.10:8080

# Check node status from CLI
cockroach node status \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10

# Run diagnostic commands
cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 << 'EOF'

-- Check cluster health
SELECT node_id, livenessStatus FROM crdb_internal.nodes;

-- View replication status
SELECT zone_id, zone_name, target FROM crdb_internal.zones;

-- Check query performance
SELECT 
  query,
  count,
  total_time,
  mean_time,
  max_time
FROM crdb_internal.statement_statistics
ORDER BY total_time DESC
LIMIT 10;

-- Monitor transactions
SELECT 
  session_id,
  query,
  progress,
  started
FROM crdb_internal.active_sessions
WHERE query NOT LIKE 'SELECT%' OR query LIKE 'INSERT%' OR query LIKE 'UPDATE%';

-- Check disk usage
SELECT 
  store_id,
  node_id,
  used,
  capacity
FROM crdb_internal.stores;
EOF

Backup and Recovery

Implement backup and recovery procedures:

# Create enterprise backup (requires license)
cockroach dump \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 \
  myapp > myapp-backup-$(date +%Y%m%d).sql

# Or use BACKUP statement
cockroach sql \
  --certs-dir=/etc/cockroach/certs \
  --host=192.168.1.10 << 'EOF'

-- Full database backup to external storage
BACKUP DATABASE myapp TO 's3://backup-bucket/myapp-full-backup?AUTH=implicit';

-- Table-level backup
BACKUP TABLE myapp.users, myapp.products 
  TO 's3://backup-bucket/myapp-tables-backup?AUTH=implicit';

-- Incremental backup
BACKUP DATABASE myapp 
  TO 's3://backup-bucket/myapp-incremental-backup?AUTH=implicit'
  WITH incremental_location = 's3://backup-bucket/myapp-full-backup?AUTH=implicit';

-- View backup status
SHOW BACKUP DETAILS 's3://backup-bucket/myapp-full-backup?AUTH=implicit';

-- Restore from backup
RESTORE DATABASE myapp FROM 's3://backup-bucket/myapp-full-backup?AUTH=implicit';

-- Restore specific tables
RESTORE TABLE myapp.users, myapp.products 
  FROM 's3://backup-bucket/myapp-tables-backup?AUTH=implicit';
EOF

Conclusion

CockroachDB delivers a distributed SQL database that combines ease of use with enterprise-grade scalability and reliability. Its architecture eliminates single points of failure, automatically handles failover, and seamlessly scales horizontally. By understanding replication zones, transaction semantics, and schema design considerations, you can build resilient, multi-region database infrastructure. The combination of strong ACID guarantees, automatic rebalancing, and SQL familiarity makes CockroachDB ideal for applications requiring geographic distribution, high availability, and data consistency without operational complexity.