Update to CockroachDB

{
  "description": "Performs a comprehensive health check of a CockroachDB cluster. Gathers deployment context first, then provides tier-appropriate diagnostics. Self-Hosted uses SQL against node-level system tables and CLI. Advanced/BYOC use Cloud Console and SQL with node visibility. Standard monitors provisioned compute and workload via Cloud Console. Basic monitors Request Unit consumption and connectivity. Use for daily checks, pre-maintenance validation, post-incident verification, or production readiness assessment.",
  "included_files": [
    {
      "relative_path": "references/production-readiness.md",
      "size_in_bytes": 6865
    }
  ],
  "name": "reviewing-cluster-health",
  "skill_md_contents": "---\nname: reviewing-cluster-health\ndescription: Performs a comprehensive health check of a CockroachDB cluster. Gathers deployment context first, then provides tier-appropriate diagnostics. Self-Hosted uses SQL against node-level system tables and CLI. Advanced/BYOC use Cloud Console and SQL with node visibility. Standard monitors provisioned compute and workload via Cloud Console. Basic monitors Request Unit consumption and connectivity. Use for daily checks, pre-maintenance validation, post-incident verification, or production readiness assessment.\ncompatibility: Self-Hosted requires SQL access with admin or VIEWCLUSTERMETADATA privilege. Advanced/BYOC require Cloud Console and SQL connectivity. Standard requires Cloud Console and SQL. Basic requires Cloud Console.\n---\n\n# Reviewing Cluster Health\n\nPerforms a comprehensive health check of a CockroachDB cluster. Before running diagnostics, this skill gathers deployment context to provide the right queries and tools for the operator's tier.\n\n## When to Use This Skill\n\n- Daily or shift-start operational health checks\n- Before starting maintenance (Self-Hosted, Advanced, BYOC)\n- After incidents to confirm recovery\n- Verifying production readiness\n- Monitoring capacity and performance\n\n**For live query issues:** Use [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md).\n**For background jobs:** Use [monitoring-background-jobs](../monitoring-background-jobs/SKILL.md).\n**For range analysis:** Use [analyzing-range-distribution](../analyze-range-distribution/SKILL.md).\n\n---\n\n## Step 1: Gather Context\n\n### Required Context\n\n| Question | Options | Why It Matters |\n|----------|---------|----------------|\n| **Deployment tier?** | Self-Hosted, Advanced, BYOC, Standard, Basic | Determines available diagnostics and operator responsibilities |\n| **Reason for health check?** | Daily check, Pre-maintenance, Post-incident, Pre-upgrade | Prioritizes which dimensions to check first |\n\n### Additional Context (by tier)\n\n**If Self-Hosted:**\n\n| Question | Options | Why It Matters |\n|----------|---------|----------------|\n| **Access available?** | SQL + CLI, SQL only | Determines which tools can be used |\n| **Cloud provider?** | AWS, GCP, Azure, On-Premises | Affects infrastructure-level checks |\n| **Kubernetes deployment?** | Yes (Operator, Helm, manual), No | Changes CLI commands and monitoring |\n| **Node count and regions?** | e.g., 9 nodes, 3 regions | Sets expectations for query results |\n\n**If Advanced or BYOC:**\n\n| Question | Options | Why It Matters |\n|----------|---------|----------------|\n| **Cloud provider?** (BYOC only) | AWS, GCP, Azure | For infrastructure-level monitoring in your cloud account |\n\n**If Standard:**\n\n| Question | Options | Why It Matters |\n|----------|---------|----------------|\n| **Current provisioned vCPUs?** | Number | Context for compute utilization assessment |\n\n**If Basic:** No additional context needed.\n\n### Context-Driven Routing\n\n| Tier | Go To |\n|------|-------|\n| Self-Hosted | [Self-Hosted Health Check](#self-hosted-health-check) |\n| Advanced | [Advanced Health Check](#advanced-health-check) |\n| BYOC | [BYOC Health Check](#byoc-health-check) |\n| Standard | [Standard Health Check](#standard-health-check) |\n| Basic | [Basic Health Check](#basic-health-check) |\n\n---\n\n## Self-Hosted Health Check\n\n**Applies when:** Tier = Self-Hosted\n\nSelf-Hosted node-level health is read primarily through `cockroach node status` (CLI) and the DB Console. Cluster settings and jobs are read through public SQL (`SHOW ALL CLUSTER SETTINGS`, `SHOW JOBS`). The `crdb_internal` virtual tables for cluster topology, storage, and certificates are not for production use — see the [docs](https://www.cockroachlabs.com/docs/stable/crdb-internal) for the production-safe table list.\n\n### Check 1: Node Liveness, Version, and Replication\n\n```bash\ncockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>\n```\n\nKey columns:\n- `is_live` — `false` requires immediate investigation\n- `is_draining`, `is_decommissioning`, `membership` — flag in-progress lifecycle operations\n- `started_at` — compare across runs to spot flapping (node restarts)\n- `build` — version per node; should be a single value (or two during a rolling upgrade)\n- `ranges_underreplicated` — non-zero indicates ranges below the zone's `num_replicas`\n\nFor finer-grained range breakdown, use the DB Console **Replication** page.\n\n### Check 2: Storage Capacity\n\nNo production-safe SQL view exposes per-store capacity. Use:\n- DB Console **Overview** → **Storage** for per-node usage\n- The Prometheus metric endpoint on each node: `curl -ks https://<node>:8080/_status/vars | grep '^capacity'` (`capacity`, `capacity_used`, `capacity_available`)\n\n### Check 3: Certificate Expiration\n\nNo SQL view exposes node certificate expiration. Use one of:\n- `cockroach cert list --certs-dir=<certs-dir>` to inspect certs locally on each node\n- `openssl x509 -in <cert.crt> -noout -enddate` for a single cert file\n- The Prometheus metric endpoint: `curl -ks https://<node>:8080/_status/vars | grep '^security_certificate_expiration_'` (UNIX-timestamp seconds; `node`, `ca`, `client_ca`, `ui_ca`)\n\nTreat anything within 90 days as `EXPIRING_SOON`.\n\n### Check 4: Critical Settings\n\n```sql\nSELECT variable, value FROM [SHOW ALL CLUSTER SETTINGS]\nWHERE variable IN (\n  'kv.rangefeed.enabled', 'sql.stats.automatic_collection.enabled',\n  'server.time_until_store_dead', 'admission.kv.enabled',\n  'cluster.preserve_downgrade_option'\n) ORDER BY variable;\n```\n\n`gc.ttlseconds` is a zone-config parameter, not a cluster setting; check the effective value with `SHOW ZONE CONFIGURATION FOR ...` against the relevant table/database/range.\n\n### Check 5: Consolidated Summary\n\nThe DB Console **Cluster Overview** page consolidates live/dead node count, version distribution, range counts, and storage. From the CLI:\n\n```bash\ncockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>\n```\n\nthen aggregate the columns of interest in your shell. The cluster's logical version comes from SQL:\n\n```sql\nSELECT value AS cluster_version FROM [SHOW CLUSTER SETTING version];\n```\n\n**If reason = Pre-maintenance**, also check for running jobs:\n```sql\nWITH j AS (SHOW JOBS)\nSELECT job_type, COUNT(*) FROM j WHERE status = 'running' GROUP BY job_type;\n```\n\n### Check 6: Production Readiness Assessment\n\nUse when verifying a cluster is ready for production workloads or during periodic operational reviews.\n\n```bash\n# Node count, liveness, and locality diversity\ncockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>\n```\n\nIn the output, count rows with `is_live = true` (production wants ≥ 3) and check that `locality` shows multiple regions/zones.\n\n```sql\n-- Critical production settings check\nSELECT variable, value,\n  CASE\n    WHEN variable = 'kv.rangefeed.enabled' AND value = 'true' THEN 'OK'\n    WHEN variable = 'kv.rangefeed.enabled' AND value = 'false' THEN 'WARN: should be true for CDC'\n    WHEN variable = 'sql.stats.automatic_collection.enabled' AND value = 'true' THEN 'OK'\n    WHEN variable = 'sql.stats.automatic_collection.enabled' AND value = 'false' THEN 'WARN: should be true'\n    WHEN variable = 'admission.kv.enabled' AND value = 'true' THEN 'OK'\n    WHEN variable = 'admission.kv.enabled' AND value = 'false' THEN 'WARN: recommended for production'\n    WHEN variable = 'cluster.preserve_downgrade_option' AND value != '' THEN 'INFO: finalization pending'\n    ELSE 'OK'\n  END AS assessment\nFROM [SHOW ALL CLUSTER SETTINGS]\nWHERE variable IN (\n  'kv.rangefeed.enabled', 'sql.stats.automatic_collection.enabled',\n  'admission.kv.enabled', 'cluster.preserve_downgrade_option',\n  'server.time_until_store_dead'\n) ORDER BY variable;\n\n-- Enterprise license status (Self-Hosted only)\nSELECT value AS organization FROM [SHOW CLUSTER SETTING cluster.organization];\n```\n\nSee [production-readiness reference](references/production-readiness.md) for the full production readiness checklist.\n\n---\n\n## Advanced Health Check\n\n**Applies when:** Tier = Advanced\n\nAdvanced clusters are dedicated single-tenant clusters managed by Cockroach Labs. You have node-level visibility via both Cloud Console and SQL.\n\n### Cloud Console Checks\n\n1. **Cluster Overview** — verify all nodes are live, check node count\n2. **Metrics** — CPU utilization, QPS, P99 latency, storage utilization\n3. **Alerts** — check for active alerts\n\n### CLI + SQL Checks\n\n```bash\n# Node liveness, version, and replication status\ncockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>\n```\n\nLook at `is_live`, `build`, and `ranges_underreplicated` per node.\n\n```sql\n-- Recent failed jobs\nWITH j AS (SHOW JOBS)\nSELECT job_type, status, COUNT(*) FROM j\nWHERE status IN ('running', 'failed') AND created > now() - INTERVAL '24 hours'\nGROUP BY job_type, status;\n```\n\n### Cloud API\n\n```bash\ncurl -s -H \"Authorization: Bearer $COCKROACH_API_KEY\" \\\n  \"https://cockroachlabs.cloud/api/v1/clusters/<cluster-id>\" | jq '.state, .cockroach_version'\n```\n\n---\n\n## BYOC Health Check\n\n**Applies when:** Tier = BYOC\n\nBYOC clusters are dedicated and run in your cloud account. You have the same CockroachDB visibility as Advanced, plus direct access to the underlying infrastructure.\n\n### CockroachDB Health\n\nRun all [Advanced Health Check](#advanced-health-check) steps.\n\n### Cloud Provider Infrastructure Checks\n\n**If AWS:**\n```bash\naws ec2 describe-instance-status --filters \"Name=tag:cockroach-cluster,Values=<cluster-name>\"\n```\n\n**If GCP:**\n```bash\ngcloud compute instances list --filter=\"labels.cockroach-cluster=<cluster-name>\"\n```\n\n**If Azure:**\n```bash\naz vm list --resource-group <rg> --query \"[?tags.cockroachCluster=='<cluster-name>']\"\n```\n\n### Additional BYOC Checks\n\n- Verify VPC/network connectivity (PrivateLink, PSC, VPC Peering)\n- Check IAM roles — CRL service account permissions still valid\n- Review cloud provider monitoring for infrastructure-level anomalies\n\n---\n\n## Standard Health Check\n\n**Applies when:** Tier = Standard\n\nStandard is a multi-tenant managed service. There are no individual nodes to monitor — Cockroach Labs manages all infrastructure, replication, and capacity. Health checking focuses on your workload performance and provisioned compute.\n\n### Cloud Console Checks\n\n1. **Cluster Overview** — verify cluster state is `RUNNING`\n2. **SQL Activity** — statement and transaction latency, error rates\n3. **Storage** — current usage\n4. **Compute** — provisioned vCPU utilization\n\n### SQL Checks\n\n```sql\n-- Verify connectivity\nSELECT 1;\n\n-- Current version\nSELECT version();\n\n-- Recent failed jobs\nWITH j AS (SHOW JOBS)\nSELECT job_type, status, description FROM j\nWHERE status = 'failed' AND created > now() - INTERVAL '24 hours';\n```\n\n### What to Monitor\n\n- **P99 SQL latency** — track via Cloud Console Metrics\n- **Error rates** — check for spikes in statement errors\n- **Storage growth** — plan based on usage trends\n- **Compute utilization** — increase provisioned vCPUs if utilization is consistently high\n\n**Note:** Node-level visibility is not available on Standard. Use Cloud Console for all infrastructure health monitoring.\n\n---\n\n## Basic Health Check\n\n**Applies when:** Tier = Basic\n\nBasic is a serverless offering that auto-scales. There are no nodes or provisioned compute to monitor. Cockroach Labs manages all infrastructure. Health checking focuses on connectivity, consumption, and spending.\n\n### Cloud Console Checks\n\n1. **Cluster Overview** — verify state is `RUNNING`\n2. **Request Units** — consumption rate and remaining budget\n3. **Storage** — current usage (10 GiB included free)\n4. **Spending Limits** — verify limits are configured to avoid unexpected charges\n\n### SQL Checks\n\n```sql\n-- Verify connectivity\nSELECT 1;\n\n-- Current version\nSELECT version();\n\n-- Recent failed jobs\nWITH j AS (SHOW JOBS)\nSELECT job_type, status, description FROM j\nWHERE status = 'failed' AND created > now() - INTERVAL '24 hours';\n```\n\n### What to Monitor\n\n- **Request Unit (RU) consumption** — track via Cloud Console to stay within spending limits\n- **Storage usage** — monitor growth relative to the 10 GiB free tier\n- **Query efficiency** — optimize queries that consume excessive RUs\n- **Cold start latency** — Basic clusters may scale to zero during inactivity; first connection after idle may have higher latency\n\n---\n\n## Safety Considerations\n\nAll checks in this skill are read-only. No data is modified.\n\n- **Self-Hosted:** `cockroach node status` requires CLI access (or admin SQL privilege if you need to fall back to internal tables). Most node-level health queries have no production-safe SQL alternative.\n- **Advanced/BYOC:** `cockroach node status` works the same way; certificate inspection is managed by Cockroach Labs.\n- **Standard/Basic:** No node-level visibility by design — use the Cloud Console.\n\n## Troubleshooting\n\n| Issue | Tier | Fix |\n|-------|------|-----|\n| `cockroach node status` errors with permission denied | SH | Use a cert with admin or `VIEWCLUSTERMETADATA` |\n| Node missing from `cockroach node status` output | SH | Check node process; verify `--join` address |\n| Standard/Basic SQL doesn't expose node tables | STD/BAS | Expected — use Cloud Console |\n| Cloud Console shows degraded | ADV/BYOC | Check Cloud status page; contact support |\n| High RU consumption | BAS | Profile queries; set spending limits |\n| Cloud API returns 401 | ADV/BYOC | Regenerate API key |\n| High latency on first connection | BAS | Expected cold start after idle period |\n\n## References\n\n**Skill references:**\n- [Production readiness checklist](references/production-readiness.md)\n\n**Related skills:**\n- [upgrading-cluster-version](../upgrading-cluster-version/SKILL.md)\n- [managing-cluster-capacity](../managing-cluster-capacity/SKILL.md)\n- [performing-cluster-maintenance](../perform-cluster-maintenance/SKILL.md)\n- [monitoring-background-jobs](../monitoring-background-jobs/SKILL.md)\n\n**Official CockroachDB Documentation:**\n- [Monitoring and Alerting](https://www.cockroachlabs.com/docs/stable/monitoring-and-alerting)\n- [cockroach node status](https://www.cockroachlabs.com/docs/stable/cockroach-node)\n- [Production Checklist](https://www.cockroachlabs.com/docs/stable/recommended-production-settings)\n- [Cloud Console Monitoring](https://www.cockroachlabs.com/docs/cockroachcloud/cluster-overview-page)\n- [Export Metrics (Advanced)](https://www.cockroachlabs.com/docs/cockroachcloud/export-metrics)\n"
}