Deploy CockroachDB

# Update package lists and upgrade existing packages
sudo apt update && sudo apt upgrade -y

# Install required dependencies
sudo apt install -y curl wget gnupg2 software-properties-common

# Set timezone (recommended for log consistency)
sudo timedatectl set-timezone UTC

# Download the latest CockroachDB binary
curl https://binaries.cockroachdb.com/cockroach-v24.3.0.linux-amd64.tgz | tar -xz

# Move binary to system path
sudo cp cockroach-v24.3.0.linux-amd64/cockroach /usr/local/bin/

# Copy the spatial libraries (required for geospatial features)
sudo mkdir -p /usr/local/lib/cockroach
sudo cp -r cockroach-v24.3.0.linux-amd64/lib/* /usr/local/lib/cockroach/

# Verify installation
cockroach version

# Create cockroach system user
sudo useradd -r -s /bin/false cockroach

# Create data and certificate directories
sudo mkdir -p /var/lib/cockroach
sudo mkdir -p /etc/cockroach/certs

# Set ownership
sudo chown -R cockroach:cockroach /var/lib/cockroach
sudo chown -R cockroach:cockroach /etc/cockroach

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

# Create node certificate (replace YOUR_VPS_IP with actual IP)
cockroach cert create-node \
    localhost \
    $(hostname) \
    YOUR_VPS_IP \
    --certs-dir=/etc/cockroach/certs \
    --ca-key=/etc/cockroach/certs/ca.key

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

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

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

[Unit]
Description=CockroachDB Server
After=network.target

[Service]
Type=simple
User=cockroach
Group=cockroach
ExecStart=/usr/local/bin/cockroach start-single-node \
    --certs-dir=/etc/cockroach/certs \
    --store=/var/lib/cockroach \
    --listen-addr=0.0.0.0:26257 \
    --http-addr=0.0.0.0:8080 \
    --cache=.25 \
    --max-sql-memory=.25
ExecStop=/usr/local/bin/cockroach quit --certs-dir=/etc/cockroach/certs
Restart=on-failure
RestartSec=10
LimitNOFILE=65535

[Install]
WantedBy=multi-user.target

sudo nano /etc/security/limits.d/cockroach.conf

cockroach soft nofile 65535
cockroach hard nofile 65535
cockroach soft nproc 65535
cockroach hard nproc 65535

# Reload systemd configuration
sudo systemctl daemon-reload

# Enable service to start on boot
sudo systemctl enable cockroachdb

# Start CockroachDB
sudo systemctl start cockroachdb

# Check status
sudo systemctl status cockroachdb

# Enable UFW if not already enabled
sudo ufw enable

# Allow SSH (important - do this first!)
sudo ufw allow 22/tcp

# Allow CockroachDB SQL port
sudo ufw allow 26257/tcp

# Allow Admin UI (restrict to specific IPs in production)
sudo ufw allow 8080/tcp

# Verify firewall rules
sudo ufw status verbose

# Remove general rule
sudo ufw delete allow 8080/tcp

# Allow only from specific IP (replace with your IP)
sudo ufw allow from YOUR_ADMIN_IP to any port 8080 proto tcp

cockroach sql --certs-dir=/etc/cockroach/certs

-- Create admin user
CREATE USER admin_user WITH PASSWORD 'YourSecurePassword123!';

-- Grant admin privileges
GRANT admin TO admin_user;

-- Verify user creation
SHOW USERS;

-- Create application database
CREATE DATABASE myapp_production;

-- Create application user
CREATE USER app_user WITH PASSWORD 'AppSecurePassword456!';

-- Grant database privileges
GRANT ALL ON DATABASE myapp_production TO app_user;

-- Connect to the new database
USE myapp_production;

-- Create sample table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT current_timestamp()
);

-- Grant table privileges
GRANT ALL ON TABLE users TO app_user;

# Start first node
cockroach start \
    --certs-dir=/etc/cockroach/certs \
    --store=/var/lib/cockroach \
    --listen-addr=NODE1_IP:26257 \
    --http-addr=NODE1_IP:8080 \
    --join=NODE1_IP:26257,NODE2_IP:26257,NODE3_IP:26257 \
    --cache=.25 \
    --max-sql-memory=.25 \
    --background

# Initialize the cluster (run only once on first node)
cockroach init --certs-dir=/etc/cockroach/certs --host=NODE1_IP:26257

# Start additional nodes
cockroach start \
    --certs-dir=/etc/cockroach/certs \
    --store=/var/lib/cockroach \
    --listen-addr=NODE_IP:26257 \
    --http-addr=NODE_IP:8080 \
    --join=NODE1_IP:26257,NODE2_IP:26257,NODE3_IP:26257 \
    --cache=.25 \
    --max-sql-memory=.25 \
    --background

cockroach node status --certs-dir=/etc/cockroach/certs

# Check node status
cockroach node status --certs-dir=/etc/cockroach/certs

# View cluster settings
cockroach sql --certs-dir=/etc/cockroach/certs -e "SHOW CLUSTER SETTINGS;"

# Check database sizes
cockroach sql --certs-dir=/etc/cockroach/certs -e \
    "SELECT database_name, sum(range_count) as ranges, \
     sum(approximate_disk_bytes) as disk_bytes \
     FROM crdb_internal.tenant_span_stats() GROUP BY database_name;"

/var/lib/cockroach/logs/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 cockroach cockroach
}

-- Backup to local filesystem
BACKUP INTO 'nodelocal://1/backups/full_backup'
WITH revision_history;

-- Backup to S3 (requires AWS credentials)
BACKUP INTO 's3://bucket-name/cockroach/backups?AWS_ACCESS_KEY_ID=xxx&AWS_SECRET_ACCESS_KEY=xxx'
WITH revision_history;

-- Create daily full backup schedule
CREATE SCHEDULE daily_backup
FOR BACKUP INTO 'nodelocal://1/backups'
RECURRING '@daily'
FULL BACKUP ALWAYS
WITH SCHEDULE OPTIONS first_run = 'now';

-- View scheduled backups
SHOW SCHEDULES;

-- List available backups
SHOW BACKUPS IN 'nodelocal://1/backups';

-- Restore specific database
RESTORE DATABASE myapp_production
FROM LATEST IN 'nodelocal://1/backups'
WITH new_db_name = 'myapp_restored';

-- Enable zone configuration for specific tables
ALTER TABLE users CONFIGURE ZONE USING
    gc.ttlseconds = 86400,
    num_replicas = 3;

-- View zone configurations
SHOW ZONE CONFIGURATION FOR TABLE users;

-- Enable query tracing
SET tracing = on;
SELECT * FROM users WHERE email = 'test@example.com';
SET tracing = off;
SHOW TRACE FOR SESSION;

-- View query statistics
SELECT * FROM crdb_internal.node_statement_statistics
ORDER BY count DESC LIMIT 10;