CockroachDB

Overview Copied

The CockroachDB Collection Agent plugin monitors CockroachDB clusters by polling two data sources on each collection cycle:

• HTTP node and store metrics — The collector publishes each node’s liveness status and an allowlist of per-node and per-store metrics. These are read from CockroachDB’s Cluster API v2 (GET /api/v2/nodes/) over HTTP (or HTTPS on a secure cluster), with an automatic fallback to the legacy v1 status endpoint (GET /_status/nodes) if the v2 call fails, for example on older clusters. The cluster UUID is still fetched from /_admin/v1/cluster. The allowlist is aligned with CockroachDB’s Essential Metrics for CockroachDB Advanced Deployments and Essential Alerts for CockroachDB Self-Hosted Deployments.
• SQL side-channel (JDBC against crdb_internal.*) — Over a separate PostgreSQL wire-protocol (JDBC) connection, the collector queries CockroachDB’s internal system views and publishes open-session counts and nonterminal jobs.

Every data point includes a cluster dimension set to the CockroachDB cluster UUID. The plugin fetches this UUID once from /_admin/v1/cluster at startup. If that call fails, the collector fails to start.

The HTTP and JDBC clients verify the server with a shared CA certificate and authenticate with a username and password. For TLS setup and credential requirements, see Security and TLS. For the minimum system privileges that the SQL user needs, see Collector user and privileges.

Prerequisites Copied

Before you deploy the plugin, make sure that:

• A CockroachDB cluster is reachable from the Collection Agent host over the HTTP API port and the SQL (PostgreSQL wire protocol) port.
• You have network access to the HTTP endpoint (default 8080) and the SQL port (default 26257).
• For a secure cluster, you have the PEM CA certificate that signed the CockroachDB node certificates.
• You have a dedicated SQL user with password authentication (LOGIN enabled) and the privileges described in Collector user and privileges.

CockroachDB version Copied

Secure clusters require CockroachDB 21.1 or later. The plugin obtains HTTP session tokens from POST /api/v2/login/, which is available from that release onward. Older versions are not supported when TLS and HTTP credentials are configured.

Geneos environment Copied

The latest version of the CockroachDB plugin requires:

• Gateway and Netprobe 7.11.x or higher.

• Collection Agent 6.6.x or higher.

  Note

  Starting from Collection Agent 5.x, Java 21 is the minimum required version.

The CockroachDB plugin binaries are packaged with Netprobe and are stored in the collection_agent > plugins folder. Alternatively, you can download separate binaries for the CockroachDB plugin from ITRS Downloads.

Collector user and privileges Copied

Create a dedicated SQL user for the plugin with password authentication and LOGIN enabled. For more information, see CockroachDB’s Manage Cluster User Authorization guide, CREATE USER for user creation, and GRANT for granting system privileges.

The plugin needs two system-level privileges. Membership in the admin role is not required:

Both the v2 (/api/v2/nodes/) and v1 (/_status/nodes) node endpoints require the same authorization: admin or the SYSTEM VIEWCLUSTERMETADATA privilege. Using v2 does not change the required access level.

Example (run as a user who can grant system privileges, for example root):

CREATE USER c2plugin WITH PASSWORD 'c2plugin_pw';
GRANT SYSTEM VIEWCLUSTERMETADATA TO c2plugin;
GRANT SYSTEM VIEWACTIVITY TO c2plugin;

The same SQL username is typically used for HTTP session login (http.username) and for JDBC (db.connectionConfig.username). With Kerberos JDBC authentication, HTTP still requires a password even when db.connectionConfig.kerberosConfig is configured. For setup details, see Kerberos authentication (JDBC).

Security and TLS Copied

TLS is controlled by the top-level tls key on the CockroachDB plugin configuration.

When tls is present, both the HTTP client and the JDBC driver use tls.caFile for server-certificate verification. Omit the entire tls key for an insecure cluster.

Client-certificate authentication is not supported. JDBC supports username and password, or Kerberos (GSSAPI). HTTP supports username and password only:

• HTTP — CockroachDB’s HTTP APIs accept a session token from POST /api/v2/login/. The plugin sends the token as the session cookie (required by the v1 status and admin endpoints) and as the X-Cockroach-API-Session header (required by the v2 API). Client certificates are not mapped to an identity. Kerberos is not available for the HTTP API.
• JDBC — pgjdbc’s sslkey property requires a PKCS#8 DER private key, while cockroach cert create-client emits a PEM key. To avoid that conversion, the plugin uses username and password, or Kerberos, for JDBC as well.

http.username and http.password are required whenever http.endpointUrl uses https://. For JDBC, use either db.connectionConfig.username and db.connectionConfig.password, or Kerberos through db.connectionConfig.kerberosConfig (which takes precedence over password authentication).

The following table summarizes the settings for each cluster mode:

For Kerberos setup details, see Kerberos authentication (JDBC).

Kerberos authentication (JDBC) Copied

Kerberos applies only to the JDBC side-channel. The HTTP API still requires session login through http.username and http.password.

When you enable Kerberos:

• Set db.connectionConfig.kerberosConfig. It takes precedence over db.connectionConfig.password.
• Do not set password in the collector YAML. Validation fails if both are set.
• Set db.connectionConfig.username to the Kerberos principal.
• Prefer the fully qualified form user@REALM. A short name is supported only when default_realm is defined in the Collection Agent’s Kerberos configuration.

Requirements Copied

Before you enable Kerberos on the collector, make sure the following are in place on the server and the client:

JDBC properties applied by the plugin Copied

When Kerberos is enabled, the plugin sets these pgjdbc defaults unless they are already present in the JDBC URL:

The plugin never sends a password to pgjdbc when Kerberos is enabled.

JVM and credential cache notes Copied

• Set -Djava.security.krb5.conf=/absolute/path/to/krb5.conf on the Collection Agent JVM when not using the OS default.
• When ccachePath is omitted, the cache is resolved through KRB5CCNAME (for example, KRB5CCNAME=FILE:/tmp/krb5cc_1000) or the OS default (/tmp/krb5cc_<uid> on Linux).
• krb5.conf is JVM-global. All Kerberos-enabled collectors in the same Collection Agent process share one realm and KDC configuration.

Configure Geneos to deploy the CockroachDB plugin Copied

The plugin supports Collection Agent publication into Geneos using Dynamic Managed Entities. To set up the plugin in Geneos:

1. Set up the CockroachDB Collection Agent plugin.
2. Configure your mappings.
3. Configure other Dynamic Entities in the Gateway.

Set up the CockroachDB Collection Agent plugin Copied

Use one of the following options to configure the plugin:

• Set up your collector in the Gateway Setup Editor by adding the configuration in Dynamic Entities > Collectors. For more information, see Collectors in Dynamic Entities.
• Add the configuration to the collection-agent.yml file on your local machine.

collectors:
  - type: plugin
    className: CockroachDbCollector

    # How often (in milliseconds) to run a collection cycle. Optional, defaults to 60000.
    collectionInterval: 30000

    # Base URL of CockroachDB's HTTP API. Required. The collector appends /api/v2/nodes/
    # (with /_status/nodes as fallback), /api/v2/login/, and /_admin/v1/cluster to this prefix.
    # Use https:// against a secure cluster; the top-level tls.caFile is then required.
    http:
      endpointUrl: https://localhost:8080

      # Required whenever http.endpointUrl is https://. The plugin exchanges these for a session
      # token via POST /api/v2/login/ on first use and after any 401 response.
      username: c2plugin
      password: c2plugin_pw

    # Optional. Server-certificate verification for BOTH the HTTP client and the JDBC
    # driver. Omit the whole block for an insecure cluster.
    #   - HTTP: OkHttp is built with an SSLSocketFactory that trusts caFile only.
    #   - JDBC: pgjdbc is configured with sslmode=verify-full and sslrootcert=caFile.
    # No client-cert options are exposed; both clients authenticate via username/password
    # (see http.* and db.connection-config.* below).
    tls:
      caFile: /etc/c2/certs/ca.crt          # PEM CA that signed the CockroachDB node cert

    # JDBC side-channel settings.
    db:
      # JDBC connection credentials. Required.
      connectionConfig:
        # Full JDBC URL. Required. Must not embed managed JDBC parameters (user, password,
        # ssl, sslmode, sslrootcert, jaasLogin, gsslib); the
        # plugin sets these on the JDBC Properties object instead.
        url: jdbc:postgresql://localhost:26257/defaultdb

        # Optional. Defaults to "root".
        username: c2plugin

        # Password auth (omit when kerberosConfig is configured).
        password: c2plugin_pw

        # Optional. Kerberos (GSSAPI) authentication for the JDBC side-channel only; when set,
        # password authentication is disabled and password must be omitted from this block.
        # Set username to the collector's Kerberos client principal, preferably user@REALM.
        # A short principal name may be used only when default_realm is configured in the
        # Collection Agent host's krb5.conf (or via -Djava.security.krb5.conf).
        # kerberosConfig:
        #   keytabPath: /secure/path/c2plugin.keytab
        #   # ccachePath: /tmp/krb5cc_1000   # mutually exclusive with keytabPath

Note

HTTP connect and read timeouts (5 seconds connect, 10 seconds read) and the JDBC connect timeout (5 seconds) are internal defaults and aren’t currently configurable.

For descriptions of all options, see Configuration reference.

Configure your mappings Copied

You can configure your dynamic mappings using a built-in or custom mapping in Dynamic Entities > Mapping:

• To use a built-in mapping, set Option to Built in, then select CockroachDB V1 in the Built in > Name field.
• To use a custom mapping, set Option to Custom.

For more information, see Mapping and mapping group in Dynamic Entities.

Configure other Dynamic Entities in the Gateway Copied

For a more detailed procedure, see Create Dynamic Entities.

Metrics collected Copied

Each collection cycle publishes data points from the HTTP API and from the SQL side-channel. SQL data-point names match their source column names. Dimensions identify which source table a given data point came from.

Each SQL query runs against its own short-lived JDBC connection and is handled independently, so a failure in one query doesn’t suppress the other SQL metrics or the HTTP metrics.

HTTP node and store metrics Copied

These metrics come from Cluster API v2 (GET /api/v2/nodes/), with the v1 GET /_status/nodes endpoint used as an automatic fallback. The v2 response is offset-paginated (limit / offset), and the plugin walks all pages. Both API shapes are normalized to the same data points:

The allowlist is split into a node-level set and a per-store set based on CockroachDB’s Essential Metrics and Essential Alerts documentation. Metrics that aren’t on the allowlist aren’t published.

SQL side-channel Copied

Sessions Copied

One bucket is published per (user_name, application_name) pair observed in crdb_internal.cluster_sessions. Per-session rows aren’t emitted — session_id is regenerated on every reconnect and isn’t a stable dimension.

Jobs Copied

One set of data points is published per row in crdb_internal.jobs whose status is nonterminal: failed, running, paused, pause-requested, cancel-requested, reverting, retry-running. No job_type filter is applied.

Note

The JDBC client sets timezone=UTC on each connection so that job timestamps aren’t shifted by the Collection Agent JVM’s local time zone offset.

Custom SQL querying Copied

The CockroachDB Collection Agent plugin publishes a fixed set of cluster telemetry from the HTTP API and from selected crdb_internal views. It doesn’t run arbitrary customer SQL.

To run your own SQL against CockroachDB and surface the results in Geneos, use the JDBC Collection Agent plugin. That plugin is separate from the CockroachDB collector. You can run both on the same Collection Agent — CockroachDB for built-in telemetry, and JDBC for custom queries.

CockroachDB speaks the PostgreSQL wire protocol, so configure the JDBC plugin with a PostgreSQL-style URL. For example:

jdbc:postgresql://<host>:26257/<database>

The JDBC plugin packages the PostgreSQL driver, so no extra driver JAR file is required for CockroachDB.

With the JDBC plugin, you can:

• Define one or more SQL queries and how each result column maps to Geneos data points (for example, Gauge, Counter, StatusMetric, or EntityAttribute).
• Publish results as Collection Agent data points for Dynamic Entities (not native Netprobe dataviews).
• Use the generic JDBC Dynamic Entity mapping, typically with dimensions such as database, table, and row_key.

For full configuration, data point translation, and mapping examples, see JDBC.

The following example shows a minimal JDBC collector that runs a custom query against CockroachDB:

collectors:
  - name: jdbc-cockroachdb
    type: plugin
    className: JdbcCollector
    collectionInterval: 60000
    workerThreads: 1

    connection:
      url: jdbc:postgresql://localhost:26257/defaultdb
      user: <sql_user>
      password: <sql_password>

    queryConfigs:
      - name: custom-query
        query: "SELECT id AS row_key, status FROM my_app.orders WHERE status != 'closed'"
        dimensions:
          dbColumns:
            - row_key
          static:
            database: defaultdb
            table: orders
        dataPoints:
          - column: status
            type: StatusMetric

Map the collector with the generic JDBC Dynamic Entity mapping so dimensions such as database, table, and row_key resolve correctly.

Note

Configure TLS on the JDBC collector (URL or propertiesFile). Don’t use the CockroachDB plugin tls block for JDBC connections.

Configuration reference Copied

Collection settings Copied

HTTP settings Copied

Configured under http.

TLS settings Copied

Configured under tls. When present, both the HTTP client and the JDBC driver verify the server certificate by using caFile. Omit the entire block for an insecure cluster.

Database connection settings Copied

Configured under db.connectionConfig.