CockroachDB Instalación: Distributed SQL

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

Architecture and Design

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

CockroachDB proporciona 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.

Instalación

Instala 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/

# Verifica installation
cockroach version
cockroach --help

Crea a system user for running CockroachDB:

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

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

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

Crea a systemd servicio file for CockroachDB:

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

Add this configuration:

[Unit]
Description=CockroachDB
After=red.target

[Servicio]
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

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

[Instala]
WantedBy=multi-user.target

Habilita the servicio:

sudo systemctl daemon-reload
sudo systemctl enable cockroachdb

Cluster Initialization

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

# Crea 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 nodo certificates for all three nodos
for i in 1 2 3; do
  cockroach cert create-nodo \
    --certs-dir=/etc/cockroach/certs \
    --ca-key=/etc/cockroach/certs/ca.key \
    192.168.1.1$i \
    nodo$i.local \
    nodo$i \
    localhost
done

# Crea 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 nodos
for nodo in 192.168.1.11 192.168.1.12; do
  scp -r /etc/cockroach/certs/* $nodo:/etc/cockroach/certs/
done

Inicia the CockroachDB servicio on all nodos:

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

# Monitorea startup
sudo journalctl -u cockroachdb -f

Initialize the cluster on the first nodo:

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

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

Monitorea cluster startup and nodo joining:

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

# Should show all three nodos with status "live"

Multi-Nodo Configuración

Verifica all nodos have joined the cluster:

# Conecta to any nodo
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, réplicas FROM crdb_internal.ranges LIMIT 5;

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

Configura cluster settings for multi-nodo 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';

-- Habilita location-aware réplica placement
SET CLUSTER SETTING server.time_until_store_dead = '90s';

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

-- Verifica settings
SHOW CLUSTER SETTINGS;
EOF

SQL Shell and Queries

Conecta to the CockroachDB cluster using the SQL shell:

# Conecta 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

Crea databases and users:

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

-- Crea database
CREATE DATABASE IF NOT EXISTS myapp;

-- Crea 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

Crea tables with appropriate schema:

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

-- Crea 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
);

-- Crea 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()
);

-- Crea 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
);

-- Crea 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)
);

-- Crea 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. Configura 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;

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

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

-- Configura zone with placement constraints (requires nodo 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'

-- Crea 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()
);

-- Crea 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);

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

-- Crea 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);

-- Crea 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;

-- Crea 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

Monitoreo and Metrics

Monitorea cluster health and performance:

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

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

# Ejecuta 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.nodos;

-- 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;

-- Monitorea 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

Respalda and Recovery

Implement backup and recovery procedures:

# Crea 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 almacenamiento
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';

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

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

Conclusión

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.

Instalación de CockroachDB SQL distribuido