CockroachDB

https://github.com/cockroachdb/docs
Admin
CockroachDB is a distributed SQL database designed for building global, scalable cloud services that...
Tokens:993,632
Snippets:7,020
Trust Score:8.2
Update:3 hours ago
Show doc for...
Tokens:
Raw
### ccloud quickstart output with connection string

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/setup/sample-setup-certs.md

Example output from running `ccloud quickstart` when the user selects 'General connection string'. It shows the connection string line starting with `postgresql://` that should be copied and saved securely.

```text
? How would you like to connect? General connection string
Retrieving cluster info: succeeded
 Downloading cluster cert to /Users/maxroach/.postgresql/root.crt: succeeded
postgresql://maxroach:ThisIsNotAGoodPassword@blue-dog-147.6wr.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=%2FUsers%2Fmaxroach%2F.postgresql%2Froot.crt
```

--------------------------------

### Run ccloud quickstart

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/cockroachcloud/ccloud/quickstart.md

Run this command to start the interactive CockroachDB Cloud quickstart. It guides you through logging in, creating a new CockroachDB Basic cluster, and connecting to it. The command opens a browser window for login and prompts for cluster name, cloud provider, and region, with default values available.

```shell
ccloud quickstart
```

--------------------------------

### Quick Start: Build and Serve Docs Locally

Source: https://github.com/cockroachdb/docs/blob/main/src/current/ci/README.md

Quick start for local development: build the Docker image, serve docs with live reload, and open the local URL. Requires Docker installed locally.

```bash
# 1. Build the Docker image (first time only)
make docker-build

# 2. Serve docs locally with live reload
make docker-serve

# 3. Open http://localhost:4000/docs/ in your browser
```

--------------------------------

### ccloud quickstart output showing connection parameters

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/setup/sample-setup-parameters-certs.md

Example output from the `ccloud quickstart` command, showing the interactive prompt and resulting connection parameters. The output includes the SQL user name, cluster ID, database, host, password, port, and username. This is used to retrieve connection parameters for the CockroachDB Cloud cluster.

```text
? How would you like to connect? Parameters only
Looking up cluster ID: succeeded
Creating SQL user: succeeded
Success! Created SQL user
 name: maxroach
 cluster: 37174250-b944-461f-b1c1-3a99edb6af32
Retrieving cluster info: succeeded
Connection parameters
 Database:  defaultdb
 Host:      blue-dog-147.6wr.cockroachlabs.cloud
 Password:  ThisIsNotAGoodPassword
 Port:      26257
 Username:  maxroach
```

--------------------------------

### SQL shell welcome text with connection info

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/setup/start-single-node-insecure.md

Example output from the SQL shell after starting the cluster, showing the sql connection string postgresql://root@localhost:26257?sslmode=disable.

```text
CockroachDB node starting at 2021-08-30 17:25:30.06524 +0000 UTC (took 4.3s)
build:               CCL v21.1.6 @ 2021/07/20 15:33:43 (go1.15.11)
webui:               http://localhost:8080
sql:                 postgresql://root@localhost:26257?sslmode=disable
```

--------------------------------

### Example response for GET backups-config

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/cockroachcloud/backups/cloud-api-backup-settings.md

Example response from a successful GET request to the backups-config endpoint. Shows the managed backup settings: enabled status, retention in days, and frequency in minutes.

```json
{
  "enabled": true,
  "retention_days": 30,
  "frequency_minutes": 240
}
```

--------------------------------

### cockroach demo command for bounded staleness example

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/known-limitations/follower-reads-limitations.md

Starts a demo cluster with the MovR dataset, required to run the subsequent bounded staleness read example.

```shell
cockroach demo
```

--------------------------------

### JWT token quickstart with OpenSSL

Source: https://github.com/cockroachdb/docs/blob/main/src/current/molt/molt-replicator-best-practices.md

A complete example of generating an EC key pair, uploading the public key to the _replicator.jwt_public_keys table, reloading Replicator, and generating a JWT token for the ycsb.public schema. Ensure the -a argument format matches your target database.

```shell
# Generate an EC private key using OpenSSL.
openssl ecparam -out ec.key -genkey -name prime256v1

# Write the public key components to a separate file.
openssl ec -in ec.key -pubout -out ec.pub

# Upload the public key for all instances of Replicator to find it.
cockroach sql -e "INSERT INTO _replicator.jwt_public_keys (public_key) VALUES ('$(cat ec.pub)')"

# Reload configuration, or wait one minute.
killall -HUP replicator

# Generate a token which can write to the ycsb.public schema.
# The key can be decoded using the debugger at https://jwt.io.
# Add the contents of out.jwt to the CREATE CHANGEFEED command:
# WITH webhook_auth_header='Bearer {out.jwt}'
replicator make-jwt -k ec.key -a ycsb.public -o out.jwt
```

--------------------------------

### Run Replicator with metrics snapshot flags (CockroachDB)

Source: https://github.com/cockroachdb/docs/blob/main/src/current/molt/replicator-metrics.md

Example replicator start command with metrics snapshotting configured. Requires the --metricsAddr, --metricsSnapshotPeriod, --metricsSnapshotCompression, and --metricsSnapshotRetentionTime flags. On success, the console output indicates the metrics snapshotter has started.

```shell
replicator start \
--targetConn postgres://postgres:postgres@localhost:5432/molt?sslmode=disable \
--stagingConn postgres://root@localhost:26257/_replicator?sslmode=disable \
--bindAddr 0.0.0.0:30004 \
--stagingSchema _replicator \
--stagingCreateSchema \
--disableAuthentication \
--tlsSelfSigned \
--stageMode crdb \
--bestEffortWindow 1s \
--flushSize 1000 \
--metricsAddr :30005 \
--metricsSnapshotPeriod 15s \
--metricsSnapshotCompression gzip \
--metricsSnapshotRetentionTime 168h \
-v
```

--------------------------------

### Start demo cluster with --global flag

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/sql/multiregion-example-setup.md

Starts a simulated multi-region cluster with 9 nodes using the cockroach demo command. Required before following the multi-region examples.

```shell
$ cockroach demo --global --nodes 9
```

--------------------------------

### Run Replicator with metrics snapshot flags (PostgreSQL)

Source: https://github.com/cockroachdb/docs/blob/main/src/current/molt/replicator-metrics.md

Example replicator pglogical command with metrics snapshotting configured. Requires the --metricsAddr, --metricsSnapshotPeriod, --metricsSnapshotCompression, and --metricsSnapshotRetentionTime flags. On success, the console output indicates the metrics snapshotter has started.

```shell
replicator pglogical \
--targetConn postgres://postgres:postgres@localhost:5432/molt?sslmode=disable \
--stagingConn postgres://root@localhost:26257/_replicator?sslmode=disable \
--slotName molt_slot \
--bindAddr 0.0.0.0:30004 \
--stagingSchema _replicator \
--stagingCreateSchema \
--disableAuthentication \
--tlsSelfSigned \
--stageMode crdb \
--bestEffortWindow 1s \
--flushSize 1000 \
--metricsAddr :30005 \
--metricsSnapshotPeriod 15s \
--metricsSnapshotCompression gzip \
--metricsSnapshotRetentionTime 168h \
-v
```

--------------------------------

### Start the SQL shell

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/app/create-maxroach-user-and-bank-database.md

Starts the built-in SQL shell with the specified certificate directory.

```shell
$ cockroach sql --certs-dir=certs
```

--------------------------------

### Start the cluster nodes

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/common/manage-certs-vault.md

Starts the cluster by executing the pre-provisioned start script on each node via gcloud beta compute ssh.

```shell
gcloud beta compute ssh --command './start_roach.sh' "${node1name}"
gcloud beta compute ssh --command './start_roach.sh' "${node2name}"
gcloud beta compute ssh --command './start_roach.sh' "${node3name}"
```

--------------------------------

### Install Node.js app dependencies

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/quickstart.md

Installs the required npm packages for the sample app.

```shell
$ npm install
```

--------------------------------

### Start core changefeed and initial output

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/cockroachcloud/cdc/create-core-changefeed.md

Starts the core changefeed for table foo and shows the initial output record.

```sql
> EXPERIMENTAL CHANGEFEED FOR foo;
```

```text
table,key,value
foo,[0],"{""after"": {""a"": 0}}"
```

--------------------------------

### Run Replicator with metrics snapshot flags (MySQL)

Source: https://github.com/cockroachdb/docs/blob/main/src/current/molt/replicator-metrics.md

Example replicator mylogical command with metrics snapshotting configured. Requires the --metricsAddr, --metricsSnapshotPeriod, --metricsSnapshotCompression, and --metricsSnapshotRetentionTime flags. On success, the console output indicates the metrics snapshotter has started.

```shell
replicator mylogical \
--targetConn postgres://postgres:postgres@localhost:5432/molt?sslmode=disable \
--stagingConn postgres://root@localhost:26257/_replicator?sslmode=disable \
--defaultGTIDSet '4c658ae6-e8ad-11ef-8449-0242ac140006:1-29' \
--bindAddr 0.0.0.0:30004 \
--stagingSchema _replicator \
--stagingCreateSchema \
--disableAuthentication \
--tlsSelfSigned \
--stageMode crdb \
--bestEffortWindow 1s \
--flushSize 1000 \
--metricsAddr :30005 \
--metricsSnapshotPeriod 15s \
--metricsSnapshotCompression gzip \
--metricsSnapshotRetentionTime 168h \
-v
```

--------------------------------

### Start Confluent platform

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/cockroachcloud/cdc/create-core-changefeed-avro.md

Start Confluent from the extracted confluent-<version> directory. Only zookeeper, kafka, and schema-registry are needed.

```shell
$ ./bin/confluent start
```

--------------------------------

### Start cockroach demo

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/sql/movr-statements.md

Run this command to start a temporary, in-memory cluster with the movr sample dataset preloaded.

```shell
$ cockroach demo
```

--------------------------------

### Start CockroachDB with systemctl

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/insecure-start-nodes.md

Starts the insecure CockroachDB service using systemd.

```shell
$ systemctl start insecurecockroachdb
```

--------------------------------

### Example startup details output

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/start-in-docker/mac-linux-steps-single-node.md

Example output from the grep command showing the startup details of a single-node cluster, including the web UI URL, SQL connection string, and cluster ID.

```shell
CockroachDB node starting at 2023-11-07 20:26:36.11359443 +0000 UTC m=+1.297365572 (took 1.0s)
build:               CCL {{ page.page_version }} @ 2023/09/27 02:36:23 (go1.19.10)
webui:               https://127.0.0.1:8080
sql:                 postgresql://root@127.0.0.1:26257/defaultdb?sslcert=certs%2Fclient.root.crt&sslkey=certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=certs%2Fca.crt
sql (JDBC):          jdbc:postgresql://127.0.0.1:26257/defaultdb?sslcert=certs%2Fclient.root.crt&sslkey=certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=certs%2Fca.crt&user=root
RPC client flags:    /cockroach/cockroach <client cmd> --host=127.0.0.1:26257 --certs-dir=certs
logs:                /cockroach/cockroach-data/logs
temp dir:            /cockroach/cockroach-data/cockroach-temp2611102055
external I/O path:   /cockroach/cockroach-data/extern
store[0]:            path=/cockroach/cockroach-data
storage engine:      pebble
clusterID:           60f29a4e-1c87-4b0c-805d-eb73460766b1
status:              initialized new cluster
nodeID:              1
```

--------------------------------

### Serve Documentation with Live Reload

Source: https://github.com/cockroachdb/docs/blob/main/src/current/ci/README.md

Serve documentation with live reload using the Makefile target, then open the local URL.

```bash
make docker-serve
# Then open http://localhost:4000/docs/
```

--------------------------------

### Get maintenance window with ccloud cluster maintenance get

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/ccloud-reference.md

Retrieves the current maintenance window for a cluster. The output shows the cluster name, window start, and duration.

```shell
ccloud cluster maintenance get blue-dog

```

```text
∙∙∙ Retrieving maintenance window...
Cluster: blue-dog
Window Start: Tuesday 02:00 UTC
Window Duration: 6h

```

--------------------------------

### Start cockroach demo with bank data

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/known-limitations/cannot-refresh-materialized-views-inside-transactions.md

Starts a CockroachDB demo cluster with the sample bank dataset, which is used to reproduce the limitation.

```shell
cockroach demo bank
```

--------------------------------

### PCR stream start response JSON

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/physical-cluster-replication.md

Example JSON response from the POST request to start a PCR stream. Contains the stream's job ID, status (STARTING), cluster IDs, and creation timestamp. The status will remain STARTING for about 5 minutes while VPC peering is set up and connectivity is validated.

```json
{
  "id": "c3d35c84-a4ea-41b3-8452-553c5ded3b85",
  "status": "STARTING",
  "primary_cluster_id": "3fabc29e-5ced-48d9-b31e-000000000000",
  "standby_cluster_id": "f9b1d580-9be3-47f8-ac28-000000000000",
  "created_at": "2025-05-01T18:57:50.038137Z"
}
```

--------------------------------

### Clone quickstart-code-samples repository

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/quickstart.md

Clones the quickstart-code-samples repository from GitHub to get the Java sample code.

```shell
git clone https://github.com/cockroachdb/quickstart-code-samples
```

--------------------------------

### List databases and view output

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/secure-test-cluster.md

Verify the creation of the database by listing all databases in the cluster.

```sql
> SHOW DATABASES;
```

```text
+--------------------+
|      Database      |
+--------------------+
| crdb_internal      |
| information_schema |
| securenodetest     |
| pg_catalog         |
| system             |
+--------------------+
(5 rows)
```

--------------------------------

### Start SQL client in secure cluster (Operator)

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/orchestration/monitor-cluster.md

For secure clusters deployed with the Operator, get a shell into the pod and start the CockroachDB built-in SQL client with certificates and host specified.

```shell
$ kubectl exec -it cockroachdb-client-secure \
-- ./cockroach sql \
--certs-dir=/cockroach/cockroach-certs \
--host=cockroachdb-public
```

--------------------------------

### ccloud jwt-issuer get

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/ccloud-reference.md

Retrieves the details of a specific JWT issuer by its ID. No output is shown in the example.

```shell
ccloud jwt-issuer get d6e7f8a9-b0c1-2345-6789-012345abcdef
```

--------------------------------

### Create table and check scan direction with EXPLAIN (OPT)

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/misc/force-index-selection.md

Create a sample table and use EXPLAIN (OPT) to verify the optimizer's chosen scan direction when using a forced index with DESC.

```sql
CREATE TABLE kv (K INT PRIMARY KEY, v INT);
```

```sql
EXPLAIN (opt) SELECT * FROM users@{FORCE_INDEX=primary,DESC};
```

```text
                 text
+-------------------------------------+
  scan users,rev
   └── flags: force-index=primary,rev
(2 rows)
```

--------------------------------

### Set up and run local docs server with Docker

Source: https://github.com/cockroachdb/docs/blob/main/LOCAL_DEVELOPMENT.md

Run these commands in the src/current directory to pull Docker images and start the local development server. Requires Rancher Desktop; Docker Desktop is not supported.

```bash
cd src/current
make docker-pull
make docker-serve
```

--------------------------------

### Example: console.disable() to toggle logging

Source: https://github.com/cockroachdb/docs/blob/main/src/current/molt/userscript-api.md

TypeScript example showing console.disable() at the start of a script to disable logging. Includes an onRowUpsert handler with console.debug and an onRowDelete handler. Demonstrates keeping logging lines while disabling output.

```ts
import * as api from "replicator@v2";

console.disable(); // disable logging temporarily for this userscript.

api.configureTargetSchema("target_db.target_schema", {
  onRowUpsert: (row, metadata) => {
    // Debug-level logging for detailed troubleshooting
    console.debug("upserting row for table:", metadata.table, "id:", row.id);
    return row;
  },
  onRowDelete: (row, metadata) => row
});
```

--------------------------------

### Download or create systemd service template

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/secure-scale-cluster.md

Download the sample configuration template for the systemd service, or create the file yourself and copy the script into it. Save the file in /etc/systemd/system/.

```shell
curl -o securecockroachdb.service https://raw.githubusercontent.com/cockroachlabs/docs/master/_includes/{{ page.version.version }}/prod-deployment/securecockroachdb.service
```

```shell
{% include {{ page.version.version }}/prod-deployment/securecockroachdb.service %}
```

--------------------------------

### Get node status with cockroach node status

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/orchestration/kubernetes-remove-nodes-manual.md

Use the cockroach node status command to get internal IDs of nodes. This example runs inside a Kubernetes client pod (cockroachdb-client-secure) and uses the root client certificate.

```shell
$ kubectl exec -it cockroachdb-client-secure \
-- ./cockroach node status \
--certs-dir=/cockroach-certs \
--host=cockroachdb-public
```

```text
  id |               address                                     | build  |            started_at            |            updated_at            | is_available | is_live
+----+---------------------------------------------------------------------------------+--------+----------------------------------+----------------------------------+--------------+---------+
   1 | cockroachdb-0.cockroachdb.default.svc.cluster.local:26257 | {{page.release_info.version}} | 2018-11-29 16:04:36.486082+00:00 | 2018-11-29 18:24:24.587454+00:00 | true         | true
   2 | cockroachdb-2.cockroachdb.default.svc.cluster.local:26257 | {{page.release_info.version}} | 2018-11-29 16:55:03.880406+00:00 | 2018-11-29 18:24:23.469302+00:00 | true         | true
   3 | cockroachdb-1.cockroachdb.default.svc.cluster.local:26257 | {{page.release_info.version}} | 2018-11-29 16:04:41.383588+00:00 | 2018-11-29 18:24:25.030175+00:00 | true         | true
   4 | cockroachdb-3.cockroachdb.default.svc.cluster.local:26257 | {{page.release_info.version}} | 2018-11-29 17:31:19.990784+00:00 | 2018-11-29 18:24:26.041686+00:00 | true         | true
(4 rows)
```

--------------------------------

### Starting fetch messages for postgres, mysql, oracle

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/molt/fetch-data-load-output.md

Example `starting fetch` summary log messages for each source database. The `cdc_cursor` value differs per source: for PostgreSQL it is an LSN, for MySQL a binlog coordinate, and for Oracle an SCN range.

```json
{"level":"info","type":"summary","num_tables":3,"cdc_cursor":"0/43A1960","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}

```

```json
{"level":"info","type":"summary","num_tables":3,"cdc_cursor":"4c658ae6-e8ad-11ef-8449-0242ac140006:1-29","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}

```

```json
{"level":"info","type":"summary","num_tables":3,"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}

```

--------------------------------

### View node startup log with grep

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/start-in-docker/mac-linux-steps.md

Displays startup details from the node's log by running grep inside the roach1 container. The command searches for 'node starting' and shows the next 11 lines. The output includes build info, web UI URL, SQL connection string, and cluster ID.

```shell
docker exec -it roach1 grep 'node starting' /cockroach/cockroach-data/logs/cockroach.log -A 11
```

```text
CockroachDB node starting at {{ now | date: "%Y-%m-%d %H:%M:%S.%6 +0000 UTC" }}
build:               CCL {{page.release_info.version}} @ {{page.release_info.build_time}} (go1.19.6)
webui:               http://roach1:8080
sql:                 postgresql://root@roach1:26357?sslmode=disable
client flags:        /cockroach/cockroach <client cmd> --host=roach1:26357
logs:                /cockroach/cockroach-data/logs
temp dir:            /cockroach/cockroach-data/cockroach-temp273641911
external I/O path:   /cockroach/cockroach-data/extern
store[0]:            path=/cockroach/cockroach-data
status:              initialized new cluster
clusterID:           1a705c26-e337-4b09-95a6-6e5a819f9eec
nodeID:              1
```

--------------------------------

### Example PCR stream monitoring response

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/physical-cluster-replication.md

Example response from the GET request to the /v1/physical-replication-streams endpoint. The response includes fields such as id, status, primary_cluster_id, standby_cluster_id, created_at, retained_time, replicated_time, and replication_lag_seconds. The replicated_time and replication_lag_seconds fields are present when the status is REPLICATING.

```json
{
  "id": "c3d35c84-a4ea-41b3-8452-553c5ded3b85",
  "status": "REPLICATING",
  "primary_cluster_id": "3fabc29e-5ced-48d9-b31e-000000000000",
  "standby_cluster_id": "f9b1d580-9be3-47f8-ac28-000000000000",
  "created_at": "2025-05-01T18:57:50.038137Z",
  "retained_time": "2025-05-01T19:02:36.462825Z",
  "replicated_time": "2025-05-01T19:05:25Z",
  "replication_lag_seconds": 9
}
```

--------------------------------

### Start a 9-node multi-region demo cluster with cockroach demo

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/sql/start-a-multi-region-demo-cluster.md

Starts a multi-region demo cluster and shows the expected startup output. The flags create a 9-node cluster with three nodes per region, set node localities, and simulate inter-region latency. The output includes connection URLs for the DB Console and a SQL shell, and notes that the demo database is temporary and in-memory.

```shell
$ cockroach demo --global --nodes 9 --no-example-database --insecure
```

```text
#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB cluster of 9 nodes.
#
# This demo session will attempt to enable enterprise features
# by acquiring a temporary license from Cockroach Labs in the background.
# To disable this behavior, set the environment variable
# COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true.
#
# Reminder: your changes to data stored in the demo session will not be saved!
#
# Connection parameters:
#   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
#   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
#   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257
#
# To display connection parameters for other nodes, use \demo ls.
#
# The user "demo" with password "demo76950" has been created. Use it to access the Web UI!
#
# Server version: CockroachDB CCL v21.1.2 (x86_64-apple-darwin19, built 2021/06/07 18:13:04, go1.15.11) (same version as client)
# Cluster ID: bfd9fc91-69bd-4417-a2f7-66e556bf2cfd
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#
```

--------------------------------

### SQL code block with START/END markers

Source: https://github.com/cockroachdb/docs/blob/main/MarkdownGuide.md

Example of a SQL code block that uses the START and END tags referenced by the remote_include tag.

```SQL
-- START database
CREATE DATABASE movr PRIMARY REGION "gcp-us-east1" REGIONS "gcp-us-east1", "gcp-europe-west1", "gcp-us-west1";
-- END database
```

--------------------------------

### Launch the SQL client with a load balancer

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/secure-test-cluster.md

Connect to the cluster by specifying the load balancer address using the --host flag.

```shell
$ cockroach sql --certs-dir=certs --host=<address of load balancer>
```

--------------------------------

### Start SQL client in bring-your-own-certs pod

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/orchestration/test-cluster-secure.md

Starts the built-in SQL client inside the secure pod created with bring-your-own-certs. The output shows the client and server versions, cluster ID, and the SQL prompt. The pod runs indefinitely; repeat this step to reopen the client or run other cockroach commands.

```shell
$ kubectl exec -it cockroachdb-client-secure \
-- ./cockroach sql \
--certs-dir=/cockroach-certs \
--host=cockroachdb-public

```

```text
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Client version: CockroachDB CCL v19.1.0 (x86_64-unknown-linux-gnu, built 2019/04/29 18:36:40, go1.11.6)
# Server version: CockroachDB CCL v19.1.0 (x86_64-unknown-linux-gnu, built 2019/04/29 18:36:40, go1.11.6)

# Cluster ID: 256a8705-e348-4e3a-ab12-e1aba96857e4
#
# Enter \? for a brief introduction.
#
root@cockroachdb-public:26257/defaultdb>

```

--------------------------------

### Get cockroach process ID with ps

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/cdc/create-sinkless-changefeed.md

Finds the process ID of the running cockroach node. The output shows the process ID 21766 in this example.

```shell
ps -ef | grep cockroach | grep -v grep
```

```output
  501 21766     1   0  6:21PM ttys001    0:00.89 cockroach start-single-node --insecure --listen-addr=localhost
```

--------------------------------

### Create table T and insert values

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/known-limitations/schema-change-ddl-inside-multi-statement-transactions.md

Creates a table T with an integer column and inserts three rows. This is the setup for the example that triggers the schema change error.

```sql
CREATE TABLE T(x INT);
INSERT INTO T(x) VALUES (1), (2), (3);
```

--------------------------------

### Run the Node.js sample app

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/quickstart.md

Runs the sample app with node app.js. The expected output is 'Hello world!'.

```shell
$ node app.js
```

```text
Hello world!
```

--------------------------------

### Create secure client pod with Operator

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/orchestration/test-cluster-secure.md

Creates a secure client pod using the CockroachDB Operator example manifest. Run this before starting the SQL client.

```shell
$ kubectl create \
-f https://raw.githubusercontent.com/cockroachdb/cockroach-operator/v2.18.3/examples/client-secure-operator.yaml

```

--------------------------------

### Create systemd service file manually

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/insecure-scale-cluster.md

Alternative to downloading the sample service file: create the file yourself and copy the script into it. The script is included from the documentation source.

```shell
{% include {{ page.version.version }}/prod-deployment/insecurecockroachdb.service %}
```

--------------------------------

### Get current SCN from Oracle database

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/molt/migration-prepare-database.md

Returns the current System Change Number (SCN) from V$DATABASE. This SCN is used as the starting point for LogMiner in subsequent steps.

```sql
SELECT CURRENT_SCN FROM V$DATABASE;
```

```sql
CURRENT_SCN
-----------
2358840

1 row selected.
```

--------------------------------

### Launch temporary pod and start SQL client (manual)

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/orchestration/test-cluster-insecure.md

Launches a temporary interactive pod and starts the built-in SQL client inside it, connecting to the CockroachDB cluster using the public service name for manual deployments.

```shell
$ kubectl run cockroachdb -it \
--image=cockroachdb/cockroach:{{page.release_info.version}} \
--rm \
--restart=Never \
-- sql \
--insecure \
--host=cockroachdb-public
```

--------------------------------

### Create and use the test database

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/topology-patterns/multiregion-db-setup.md

Creates the 'test' database and sets it as the default database for the session.

```sql
CREATE DATABASE test;
```

```sql
USE test;
```

--------------------------------

### Download sample systemd configuration template

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/insecure-start-nodes.md

Downloads the insecurecockroachdb.service sample configuration file into /etc/systemd/system/.

```shell
curl -o insecurecockroachdb.service https://raw.githubusercontent.com/cockroachlabs/docs/main/src/current/_includes/{{ page.version.version }}/prod-deployment/insecurecockroachdb.service
```

--------------------------------

### Begin transaction with BEGIN

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/sql/set-transaction-as-of-system-time-example.md

Starts a transaction in the cockroach sql shell.

```sql
> BEGIN;

```

--------------------------------

### Verify new CockroachDB pods with kubectl get pods

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/orchestration/kubernetes-scale-cluster-manual.md

Lists all pods to verify that the new CockroachDB nodes have started successfully. The output shows the new pods (cockroachdb-3, cockroachdb-4, cockroachdb-5) running.

```shell
$ kubectl get pods
```

```text
NAME                        READY     STATUS    RESTARTS   AGE
cockroachdb-0               1/1       Running   0          51m
cockroachdb-1               1/1       Running   0          47m
cockroachdb-2               1/1       Running   0          3m
cockroachdb-3               1/1       Running   0          1m
cockroachdb-4               1/1       Running   0          1m
cockroachdb-5               1/1       Running   0          1m
cockroachdb-client-secure   1/1       Running   0          15m
...
```

--------------------------------

### Run Java application with Gradle

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/quickstart.md

Runs the Java sample application using the Gradle wrapper. The expected output shows a successful build and the 'Hello world!' message.

```shell
./gradlew run
```

```text
> Task :app:run
Hello world!

BUILD SUCCESSFUL in 3s
2 actionable tasks: 2 executed
```

--------------------------------

### Get cluster ID for AWS CMEK setup

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/managing-cmek.md

Use the CockroachDB Cloud API to retrieve your cluster details, including the AWS account ID managed by CockroachDB Cloud. The account ID is in the `account_id` field of the response.

```shell
curl --request GET \
     --url https://cockroachlabs.cloud/api/v1/clusters/{YOUR_CLUSTER_ID} \
     --header "Authorization: Bearer {YOUR_API_KEY}"
```

--------------------------------

### Initialize cluster with cockroach init --insecure

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/insecure-initialize-cluster.md

Run this command on your local machine after installing CockroachDB to initialize the cluster. Set the --host flag to the address of any node on the --join list. Each node then prints details to standard output, including the CockroachDB version, DB Console URL, and SQL URL.

```shell
$ cockroach init --insecure --host=<address of any node on --join list>
```

--------------------------------

### Start a CockroachDB node with --locality flag

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/topology-patterns/multi-region-cluster-setup.md

Starts a CockroachDB node with the --locality flag set to region=us-west,zone=west1. This command is used in the multi-region cluster setup to specify the node's region and availability zone. The --join flag lists the internal addresses of the first three nodes, and the --cache and --max-sql-memory flags set resource limits.

```shell
$ cockroach start \
--locality=region=us-west,zone=west1 \
--certs-dir=certs \
--advertise-addr=<node1 internal address> \
--join=<node1 internal address>:26257,<node2 internal address>:26257,<node3 internal address>:26257 \
--cache=.25 \
--max-sql-memory=.25 \
--background
```

--------------------------------

### Initialize database with cockroach sql

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/setup/init-bank-sample.md

Executes the SQL statements in dbinit.sql using the cockroach sql command and the DATABASE_URL environment variable. The expected output shows CREATE TABLE and the execution time.

```shell
cat dbinit.sql | cockroach sql --url $DATABASE_URL
```

```text
CREATE TABLE


Time: 102ms
```

--------------------------------

### Get cluster identity ID for Azure CMEK setup

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/managing-cmek.md

Use the CockroachDB Cloud API to retrieve your cluster's identity ID, stored in the `azure_cluster_identity_client_id` field of the response. This ID is needed for the Azure admin consent URL.

```shell
CLUSTER_ID= #{ your cluster ID }
API_KEY= #{ your API key }
curl --request GET \
  --url https://cockroachlabs.cloud/api/v1/clusters/${CLUSTER_ID} \
  --header "Authorization: Bearer ${API_KEY}"
```

--------------------------------

### Run Replicator with metrics snapshot flags (Oracle)

Source: https://github.com/cockroachdb/docs/blob/main/src/current/molt/replicator-metrics.md

Example replicator oraclelogminer command with metrics snapshotting configured. Requires the --metricsAddr, --metricsSnapshotPeriod, --metricsSnapshotCompression, and --metricsSnapshotRetentionTime flags. On success, the console output indicates the metrics snapshotter has started.

```shell
replicator oraclelogminer \
--targetConn postgres://postgres:postgres@localhost:5432/molt?sslmode=disable \
--stagingConn postgres://root@localhost:26257/_replicator?sslmode=disable \
--scn 26685786 \
--backfillFromSCN 26685444 \
--bindAddr 0.0.0.0:30004 \
--stagingSchema _replicator \
--stagingCreateSchema \
--disableAuthentication \
--tlsSelfSigned \
--stageMode crdb \
--bestEffortWindow 1s \
--flushSize 1000 \
--metricsAddr :30005 \
--metricsSnapshotPeriod 15s \
--metricsSnapshotCompression gzip \
--metricsSnapshotRetentionTime 168h \
-v
```

--------------------------------

### Manual: Start SQL client in pod

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/common/upgrade/major-version-upgrade-kubernetes.md

Start the CockroachDB built-in SQL client inside a pod after it has restarted with the new image.

```shell
$ kubectl exec -it cockroachdb-client-secure \-- ./cockroach sql \
--certs-dir=/cockroach-certs \
--host=cockroachdb-public
```

--------------------------------

### Start cockroach demo with geo-partitioned replicas

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/sql/movr-statements-geo-partitioned-replicas.md

Run this command to open an interactive SQL shell to a temporary 9-node in-memory cluster with the movr database. Use the --geo-partitioned-replicas flag to enable geo-partitioned replicas.

```shell
$ cockroach demo --geo-partitioned-replicas
```

--------------------------------

### Search Initialization and Configuration

Source: https://github.com/cockroachdb/docs/blob/main/src/current/search.html

Initializes the search client, creates a custom hits widget using the InstantSearch connector, and adds widgets for refinement, stats, pagination, and configuration. The search is started and a global handleAskAI function is exposed.

```JavaScript
// Create the custom widget
const customHits = instantsearch.connectors.connectHits(renderHits);

search.addWidgets([
  instantsearch.widgets.refinementList({
    container: document.querySelector("#search-refinements"),
    attribute: 'version',
    operator: 'or',
    showMore: true,
    searchable: true,
    searchablePlaceholder: 'Search docs area'
  }),
  customHits({
    container: document.querySelector("#search-hits")
  }),
  instantsearch.widgets.stats({
    container: "#search-stats",
    templates: {
      text(data) {
        let count = "";
        if (data.hasManyResults) {
          count += `${data.nbHits} results`;
        } else if (data.hasOneResult) {
          count += `1 result`;
        } else {
          count += `no result`;
        }
        return `${count} found for '${queryString}'`;
      }
    }
  }),
  instantsearch.widgets.pagination({
    container: "#search-pages",
    padding: 2,
    showFirst: false,
    showLast: false,
    templates: {
      previous: '<span class="fas fa-chevron-left" />',
      next: '<span class="fas fa-chevron-right" />'
    }
  }),
  instantsearch.widgets.configure({
    hitsPerPage: 10,
    query: queryString
  })
]);

// Starting the search
search.start();

// Make handleAskAI globally available
window.handleAskAI = handleAskAI;
```

--------------------------------

### Prepare Cockroach directory and user (mkdir, useradd, chown) for systemd startup

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/insecure-start-nodes.md

Prepares the CockroachDB directory and dedicated user before starting a node via systemd. Run these commands as root after installing CockroachDB for Linux. The chown command gives the cockroach user ownership of /var/lib/cockroach.

```shell
$ mkdir /var/lib/cockroach
```

```shell
$ useradd cockroach
```

```shell
$ chown cockroach /var/lib/cockroach
```

--------------------------------

### Manual: Check image of each pod after upgrade

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/common/upgrade/major-version-upgrade-kubernetes.md

Confirm all pods have been upgraded by listing their images. The output shows all pods running the new version.

```shell
$ kubectl get pods \
-o jsonpath='{range .items[*]}{.metadata.name}{"\t"}{.spec.containers[0].image}{"\n"}'
```

```text
cockroachdb-0   cockroachdb/cockroach:{{page.release_info.version}}
cockroachdb-1   cockroachdb/cockroach:{{page.release_info.version}}
cockroachdb-2   cockroachdb/cockroach:{{page.release_info.version}}
...
```

--------------------------------

### Configure NTP clock synchronization on CockroachDB nodes

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/synchronize-clocks.md

Steps to configure NTP on each CockroachDB node: disable timesyncd, install and start the ntp daemon, sync with Google's NTP service, and verify. The /etc/ntp.conf lines replace existing server/pool entries to make the change permanent.

```shell
$ sudo timedatectl set-ntp no

```

```shell
$ timedatectl

```

```shell
$ sudo apt-get install ntp

```

```shell
$ sudo service ntp stop

```

```shell
$ sudo ntpd -b time.google.com

```

```plaintext
server time1.google.com iburst
server time2.google.com iburst
server time3.google.com iburst
server time4.google.com iburst

```

```shell
$ sudo service ntp start

```

```shell
$ sudo ntpq -p

```

--------------------------------

### Join crdb_internal.partitions with crdb_internal.tables to include table and database names

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/sql/crdb-internal-partitions-example.md

Use this query to get partition information along with the table and database names by joining crdb_internal.partitions with crdb_internal.tables on table_id. The example filters for the 'users' table and shows the partition name, column names, list value, table name, and database name.

```sql
> SELECT
  partitions.name AS partition_name, column_names, list_value, tables.name AS table_name, database_name
  FROM crdb_internal.partitions JOIN crdb_internal.tables ON partitions.table_id=tables.table_id
  WHERE tables.name='users';
```

```text
  partition_name | column_names |                   list_value                    | table_name | database_name
+----------------+--------------+-------------------------------------------------+------------+---------------+
  us_west        | city         | ('seattle'), ('san francisco'), ('los angeles') | users      | movr
  us_east        | city         | ('new york'), ('boston'), ('washington dc')     | users      | movr
  europe_west    | city         | ('amsterdam'), ('paris'), ('rome')              | users      | movr
(3 rows)
```

--------------------------------

### Start cockroach demo with 9 nodes and demo localities

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/sql/movr-statements-partitioning.md

Opens an interactive SQL shell to a temporary multi-node in-memory cluster with the movr database preloaded. Use the --nodes and --demo-locality flags to simulate a multi-region deployment.

```shell
$ cockroach demo \
--nodes=9 \
--demo-locality=region=us-east1:region=us-east1:region=us-east1:region=us-central1:region=us-central1:region=us-central1:region=us-west1:region=us-west1:region=us-west1
```

--------------------------------

### Install CockroachDB on nodes and client

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/common/manage-certs-vault.md

Installs CockroachDB on each node and the client via gcloud compute ssh. The function checks for an existing installation and downloads the binary from binaries.cockroachdb.com if missing. The output shows the expected 'roach not installed; installing roach' message for each of the four machines.

```shell
function install_roach_on_node {
    gcloud compute ssh $1 --command 'if [[ ! -e cockroach-{{ page.release_info.version }}.linux-amd64 ]];
    then
        echo "roach not installed; installing roach"
        sudo curl https://binaries.cockroachdb.com/cockroach-{{ page.release_info.version }}.linux-amd64.tgz | tar -xz
        sudo cp -i cockroach-{{ page.release_info.version }}.linux-amd64/cockroach /usr/local/bin/
        sudo mkdir -p /usr/local/lib/cockroach
        sudo cp -i cockroach-{{ page.release_info.version }}.linux-amd64/lib/libgeos.so /usr/local/lib/cockroach/
        sudo cp -i cockroach-{{ page.release_info.version }}.linux-amd64/lib/libgeos_c.so /usr/local/lib/cockroach/
    else
        echo "roach already installed"
    fi'
}

install_roach_on_node $node1name
install_roach_on_node $node2name
install_roach_on_node $node3name
install_roach_on_node roach-client
```

```txt
roach not installed; installing roach
roach not installed; installing roach
roach not installed; installing roach
roach not installed; installing roach
```

--------------------------------

### Start a new node manually with cockroach start

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/prod-deployment/secure-scale-cluster.md

Run this command on each new node to join the cluster. Pass the new node's address as --advertise-addr and point --join to the three existing nodes. Include --locality if you set it earlier.

```shell
$ cockroach start \
--certs-dir=certs \
--advertise-addr=<node4 address> \
--join=<node1 address>,<node2 address>,<node3 address> \
--cache=.25 \
--max-sql-memory=.25 \
--background
```

--------------------------------

### Install ccloud via Homebrew on macOS

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/cockroachcloud/ccloud/install-ccloud.md

Installs the ccloud CLI using the Homebrew tap. Requires Homebrew to be installed first.

```shell
brew install cockroachdb/tap/ccloud
```

--------------------------------

### Create StatefulSet with cockroachdb-statefulset.yaml

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/v26.3/orchestration/start-cockroachdb-insecure.md

Creates the StatefulSet and starts the cluster. The output shows the created services, pod disruption budget, and statefulset.

```shell
$ kubectl create -f cockroachdb-statefulset.yaml
```

```text
service/cockroachdb-public created
service/cockroachdb created
poddisruptionbudget.policy/cockroachdb-budget created
statefulset.apps/cockroachdb created
```

--------------------------------

### Get invoice details with ccloud billing invoice get

Source: https://github.com/cockroachdb/docs/blob/main/src/current/cockroachcloud/ccloud-reference.md

Use `ccloud billing invoice get` to retrieve the details of a specific invoice, identified by its invoice ID.

```shell
ccloud billing invoice get d0e1f2a3-b4c5-6789-0123-456789abcdef
```

--------------------------------

### Start LogMiner with start SCN

Source: https://github.com/cockroachdb/docs/blob/main/src/current/_includes/molt/migration-prepare-database.md

Starts LogMiner using the given STARTSCN (from the current SCN query) and uses DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG to load the dictionary from the online catalog. Output is a success message.

```sql
EXEC DBMS_LOGMNR.START_LOGMNR(
  STARTSCN => 2358840,
  OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
);
```

```sql
PL/SQL procedure successfully completed.
```
Tokens:
Raw