# CockroachDB Setup Rule ## Overview This rule provides comprehensive guidance for setting up CockroachDB in development and production environments. It covers initial cluster configuration, node setup, and essential security configurations to ensure optimal performance and reliability. ## Implementation ### 1. Initial Cluster Setup #### Single-Node Development Setup ```bash # Download and install CockroachDB curl https://binaries.cockroachdb.com/cockroach-v23.2.0.linux-amd64.tgz | tar -xz sudo cp -i cockroach-v23.2.0.linux-amd64/cockroach /usr/local/bin/ # Start single-node cluster for development cockroach start-single-node \ --insecure \ --listen-addr=localhost:26257 \ --http-addr=localhost:8080 \ --store=cockroach-data \ --background ``` #### Multi-Node Production Setup ```bash # Node 1 (initialize cluster) cockroach start \ --certs-dir=certs \ --store=node1 \ --listen-addr=node1.example.com:26257 \ --http-addr=node1.example.com:8080 \ --join=node1.example.com:26257,node2.example.com:26257,node3.example.com:26257 \ --background # Initialize the cluster (run once) cockroach init --certs-dir=certs --host=node1.example.com:26257 # Node 2 & 3 (join existing cluster) cockroach start \ --certs-dir=certs \ --store=node2 \ --listen-addr=node2.example.com:26257 \ --http-addr=node2.example.com:8080 \ --join=node1.example.com:26257,node2.example.com:26257,node3.example.com:26257 \ --background ``` ### 2. Security Configuration #### Generate Certificates ```bash # Create CA certificate cockroach cert create-ca \ --certs-dir=certs \ --ca-key=my-safe-directory/ca.key # Create node certificates cockroach cert create-node \ localhost \ node1.example.com \ node2.example.com \ node3.example.com \ --certs-dir=certs \ --ca-key=my-safe-directory/ca.key # Create client certificate for root user cockroach cert create-client \ root \ --certs-dir=certs \ --ca-key=my-safe-directory/ca.key ``` #### User Management ```sql -- Create application user CREATE USER app_user WITH PASSWORD 'strong_password'; -- Grant necessary privileges GRANT ALL ON DATABASE myapp TO app_user; GRANT ALL ON TABLE myapp.public.* TO app_user; -- Create read-only user for monitoring CREATE USER monitoring WITH PASSWORD 'monitoring_password'; GRANT SELECT ON DATABASE myapp TO monitoring; ``` ### 3. Database and Schema Setup #### Initial Database Configuration ```sql -- Create application database CREATE DATABASE myapp; -- Set default configurations ALTER DATABASE myapp SET sql_stats_automatic_collection_enabled = true; ALTER DATABASE myapp SET sql_stats_histogram_collection_enabled = true; -- Create schema USE myapp; CREATE SCHEMA IF NOT EXISTS public; ``` #### Connection Pool Configuration ```go // Go example with pgx import ( "github.com/jackc/pgx/v5/pgxpool" "context" ) config, err := pgxpool.ParseConfig("postgresql://app_user:password@localhost:26257/myapp?sslmode=require") if err != nil { log.Fatal(err) } // Configure connection pool config.MaxConns = 30 config.MinConns = 5 config.MaxConnLifetime = time.Hour config.MaxConnIdleTime = time.Minute * 30 pool, err := pgxpool.NewWithConfig(context.Background(), config) ``` ### 4. Environment-Specific Configurations #### Development Environment ```yaml # docker-compose.yml version: '3.8' services: cockroach: image: cockroachdb/cockroach:latest ports: - "26257:26257" - "8080:8080" command: start-single-node --insecure volumes: - cockroach-data:/cockroach/cockroach-data environment: - COCKROACH_DATABASE=myapp - COCKROACH_USER=app_user - COCKROACH_PASSWORD=dev_password volumes: cockroach-data: ``` #### Production Environment Variables ```bash # Essential environment variables export COCKROACH_CERTS_DIR=/var/lib/cockroach/certs export COCKROACH_STORE=/var/lib/cockroach/data export COCKROACH_HOST=node1.example.com:26257 export COCKROACH_DATABASE=myapp export COCKROACH_USER=app_user export COCKROACH_PASSWORD=production_password export COCKROACH_SSL_MODE=require ``` ## Best Practices ### 1. Cluster Configuration - **Minimum 3 nodes**: Always use odd numbers of nodes (3, 5, 7) for proper consensus - **Geographic distribution**: Spread nodes across different availability zones - **Resource allocation**: Ensure adequate CPU, memory, and storage for each node - **Network configuration**: Use dedicated network interfaces for inter-node communication ### 2. Security Hardening - **Never use --insecure**: Always use certificates in production - **Certificate rotation**: Implement automated certificate rotation - **Network isolation**: Use VPCs and security groups to restrict access - **Audit logging**: Enable audit logging for compliance requirements ### 3. Performance Optimization - **Storage configuration**: Use SSD storage with appropriate IOPS - **Memory allocation**: Set --cache to 25% of available memory - **Connection pooling**: Always use connection pooling in applications - **Query optimization**: Use EXPLAIN to analyze query plans ### 4. Monitoring Setup ```sql -- Enable important cluster settings SET CLUSTER SETTING server.time_until_store_dead = '1m30s'; SET CLUSTER SETTING kv.raft.log.disable_synchronization_unsafe = false; SET CLUSTER SETTING sql.metrics.statement_details.enabled = true; ``` ## Common Issues ### 1. Clock Synchronization **Problem**: "clock synchronization error" on cluster startup **Solution**: ```bash # Ensure NTP is configured on all nodes sudo apt-get install ntp sudo systemctl enable ntp sudo systemctl start ntp ``` ### 2. Certificate Issues **Problem**: SSL certificate verification failures **Solution**: ```bash # Verify certificate validity cockroach cert list --certs-dir=certs # Check certificate expiration openssl x509 -in certs/node.crt -text -noout | grep "Not After" ``` ### 3. Node Join Failures **Problem**: New nodes cannot join the cluster **Solution**: ```bash # Check node connectivity cockroach node status --certs-dir=certs --host=node1.example.com:26257 # Verify join addresses are correct cockroach node ls --certs-dir=certs --host=node1.example.com:26257 ``` ### 4. Storage Issues **Problem**: Node running out of disk space **Solution**: ```bash # Monitor storage usage cockroach node status --certs-dir=certs --host=node1.example.com:26257 # Clean up old logs find /var/lib/cockroach/logs -name "*.log" -mtime +7 -delete ``` ### 5. Connection Pool Exhaustion **Problem**: "connection pool exhausted" errors **Solution**: ```go // Increase pool size and add proper timeouts config.MaxConns = 50 config.HealthCheckPeriod = 1 * time.Minute config.ConnConfig.ConnectTimeout = 5 * time.Second ``` ### 6. Version Upgrade Issues **Problem**: Cluster upgrade failures **Solution**: ```bash # Always perform rolling upgrades cockroach node drain --certs-dir=certs --host=node1.example.com:26257 # Stop node, upgrade binary, restart cockroach start --certs-dir=certs --join=... --background ``` ## Validation Commands ```bash # Check cluster health cockroach node status --certs-dir=certs --host=node1.example.com:26257 # Verify replication cockroach sql --certs-dir=certs --host=node1.example.com:26257 -e "SHOW RANGES FROM TABLE myapp.public.users;" # Test connection cockroach sql --certs-dir=certs --host=node1.example.com:26257 -e "SELECT version();" ```