የኮክሮች ዴቢ

cockroach start     --certs-dir=/etc/cockroach/certs     --store=/var/lib/cockroach     --listen-addr=0.0.0.0:26257     --http-addr=0.0.0.0:8080     --join=node1:26257,node2:26257,node3:26257     --cache=25% \                      # Memory for cache
    --max-sql-memory=25% \             # Memory for SQL queries
    --locality=region=us-east,zone=a   # Geo-locality

# Basic connection
postgresql://user:password@host:26257/database

# Multi-node with load balancing
postgresql://user:password@host1:26257,host2:26257,host3:26257/database

# With SSL verification
postgresql://user:password@host:26257/database?sslmode=verify-full&sslrootcert=/path/to/ca.crt

# Application failover
postgresql://user:password@lb.example.com:26257/database?application_name=myapp

-- Set up regions
ALTER DATABASE mydb PRIMARY REGION "us-east-1";
ALTER DATABASE mydb ADD REGION "us-west-1";
ALTER DATABASE mydb ADD REGION "eu-west-1";

-- Geo-partitioned table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING,
    region STRING,
    data JSONB,
    INDEX (region)
) LOCALITY REGIONAL BY ROW AS region;

-- Regional table (single region)
CREATE TABLE eu_customers (
    id UUID PRIMARY KEY,
    name STRING,
    address STRING
) LOCALITY REGIONAL IN "eu-west-1";

-- Default replication factor
ALTER RANGE default CONFIGURE ZONE USING num_replicas = 3;

-- Pin specific table to region
ALTER TABLE users CONFIGURE ZONE USING 
    num_replicas = 5,
    constraints = '{"+region=us-east": 2, "+region=us-west": 2, "+region=eu-west": 1}';

-- Fast reads with follower reads (slightly stale data acceptable)
SET CLUSTER SETTING kv.closed_timestamp.target_duration = '1s';

-- Increase SQL memory limit
SET CLUSTER SETTING sql.defaults.distsql.max_running_flows = 500;

-- Enable statement statistics
SET CLUSTER SETTING sql.metrics.statement_details.enabled = true;

-- Adjust range size for large datasets
ALTER RANGE default CONFIGURE ZONE USING range_max_bytes = 134217728;  -- 128MB

-- Connection pool settings (application side)
-- Max connections: 4 × num_cores per node
-- Min connections: num_cores per node

-- Full backup to S3
BACKUP DATABASE mydb TO 's3://bucket/backup?AWS_ACCESS_KEY_ID=xxx&AWS_SECRET_ACCESS_KEY=xxx';

-- Incremental backup
BACKUP DATABASE mydb TO 'latest' IN 's3://bucket/backups';

-- Restore
RESTORE DATABASE mydb FROM 'latest' IN 's3://bucket/backups';

-- Scheduled backups (Enterprise)
CREATE SCHEDULE backup_schedule
FOR BACKUP DATABASE mydb INTO 's3://bucket/backups'
RECURRING '@daily'
WITH SCHEDULE OPTIONS first_run = 'now';

-- Stream changes to Kafka
CREATE CHANGEFEED FOR TABLE users, orders
INTO 'kafka://broker:9092?topic_prefix=myapp_';

-- Stream to webhook
CREATE CHANGEFEED FOR TABLE events
INTO 'webhook-https://myapp.com/webhook?insecure_tls_skip_verify=true';

-- Cluster health
SHOW CLUSTER SETTING version;

-- Node status
SELECT node_id, address, is_live, is_available FROM crdb_internal.gossip_liveness;

-- Active queries
SELECT query_id, user_name, query, start FROM [SHOW QUERIES];

-- Slow queries
SELECT query, count, avg_latency FROM crdb_internal.statement_statistics
ORDER BY avg_latency DESC
LIMIT 10;

-- Range distribution
SELECT range_id, replicas, lease_holder FROM crdb_internal.ranges;