Dhiraj Kumar Jha Replication of Database

Replication Using CockroachDB

Replication is important in distributed databases. The cockroachDB uses minimum of three node quorum to establish replication in clusters.

Download the cockroachdb from https://binaries.cockroachdb.com/cockroach-v23.2.4.windows-6.2-amd64.zip

Extract it, rename it as “cockroachDB”, and paste it in c:\ drive so that it becomes easy to locate and solve issues if any.


Use the Powershell on Windows to type the following command;

PS C:\Users\LENOVO> cockroach version

Build Tag: v23.2.3

Build Time: 2024/03/14 16:32:15

Distribution: CCL

Platform: windows amd64 (x86_64-w64-mingw32)

Go Version: go1.21.5 X:nocoverageredesign

C Compiler: gcc 6.5.0

Build Commit ID: 03498e5964c0eaa45bb5435b4c600de38d542910

Build Type: release

Enabled Assertions: false

cockroach start-single-node

This page explains the cockroach start-single-node command, which you use to start a single-node cluster with replication disabled. A single-node cluster is appropriate for quick SQL testing or app development.


A single-node cluster is not appropriate for use in production or for performance testing. To run a multi-node cluster with replicated data for availability, consistency and resiliency, including load balancing across multiple nodes, use cockroach start and cockroach init to start a multi-node cluster with a minimum of three nodes instead.

When you run cockroach start-single-node , some helpful details are printed to the standard output:
CockroachDB node starting at
build: CCL v23.2.3 @ 2024-03-20 go1.22.0
webui: http://localhost:8080
sql: postgresql://root@localhost:26257?sslmode=disable
sql (JDBC): jdbc:postgresql://localhost:26257/defaultdb?sslmode=disable&user=root
RPC client flags: cockroach –host=localhost:26257 –insecure
logs: /Users//node1/logs
temp dir: /Users//node1/cockroach-temp242232154
external I/O path: /Users//node1/extern
store[0]: path=/Users//node1
status: initialized new cluster
clusterID: 8a681a16-9623-4fc1-a537-77e9255daafd
nodeID: 1
These details are also written to the INFO log in the /logs directory. You can retrieve them with a command like grep ‘node starting’ node1/logs/cockroach.log -A 11.
build The version of CockroachDB you are running.
webui The URL for accessing the DB Console.
sql The connection URL for your client.
RPC client flags The flags to use when connecting to the node via cockroach client commands.
logs The directory containing debug log data.
temp dir The temporary store directory of the node.
external I/O path The external IO directory with which the local file access paths are prefixed while performing backup and restore operations using local node directories or NFS
attrs If node-level attributes were specified in the –attrs flag, they are listed in this field. These details are potentially useful for configuring replication zones.
locality If values describing the locality of the node were specified in the –locality field, they are listed in this field. These details are potentially useful for configuring replication
store[n] The directory containing store data, where [n] is the index of the store, e.g., store[0] for the first store, store[1] for the second store.
If store-level attributes were specified in the attrs field of the –store flag, they are listed in this field as well. These details are potentially useful for configuring replication zones.
status Whether the node is the first in the cluster (initialized new cluster), joined an existing cluster for the first time (initialized new node, joined pre-existing cluster), or rejoined an existing cluster (restarted pre-existing node).
clusterID The ID of the cluster.

When trying to join a node to an existing cluster, if this ID is different than the ID of the existing cluster, the node has started a new cluster. This may be due to conflicting information in the node’s data directory. For additional guidance, see the troubleshooting docs.
nodeID The ID of the node.


Start a single-node cluster

Create two directories for certificates:

mkdir certs,my-safe-directory

certs You’ll generate your CA certificate and all node and client certificates and keys in this directory.
my-safe-directory You’ll generate your CA key in this directory and then reference the key when generating node and client certificates.

Create the CA (Certificate Authority) certificate and key pair:

cockroach cert create-ca –certs-dir=certs –ca-key=my-safe-directory/ca.key

Create the certificate and key pair for the node:
cockroach cert create-node localhost –certs-dir=certs –ca-key=my-safe-directory/ca.key

Create a client certificate and key pair for the root user:
cockroach cert create-client root –certs-dir=certs –ca-key=my-safe-directory/ca.key

Start the single-node cluster:
cockroach start-single-node –insecure –certs-dir=certs –listen-addr=localhost:26257 –http-addr=localhost:8080

Connect to the single-node cluster:
cockroach sql –insecure –host=localhost:26257

Scale to multiple nodes

Scaling a cluster started with cockroach start-single-node involves restarting the first node with the cockroach start command instead, and then adding new nodes with that command as well, all using a –join flag that forms them into a single multi-node cluster.

Since replication is disabled in clusters started with start-single-node, you also need to enable replication to get CockroachDB’s availability and consistency guarantees.

Stop the single-node cluster:

Get the process ID of the node using: ps command

Gracefully shut down the node, specifying its process ID:
kill 19584
OR, simply press “CTRL+C” two times. The node will shutdown but not gracefully. It is not recommended style to shutdown

To execute cockroach in a single node instead of cluster
cockroach start-single-node –certs-dir=certs –listen-addr=localhost:26257 –http-addr=localhost:8080

In new power shell, execute cockroach init command to perform a one-time initialization of the cluster, sending the request to any node on the –join list
cockroach init –insecure –host=localhost:26257

These commands are the same as before but with unique –store, –listen-addr, and –http-addr flags, since this all nodes are running on the same machine. Also, since all nodes use the same hostname (localhost), you can use the first node’s certificate.

Note that this is different than running a production cluster, where you would need to generate a certificate and key for each node, issued to all common names and IP addresses you might use to refer to the node as well as to any load balancer instances.

Open the built-in SQL shell:

cockroach sql --certs-dir=certs --host=localhost:26257

STEP #1 open up powershell and type this command

cockroach start --insecure --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259,localhost:26260 

Step #2 : in a new Powershell

cockroach start --insecure --store=node2 --listen-addr=localhost:26258 --http-addr=localhost:8081 --join=localhost:26257,localhost:26258,localhost:26259, localhost:26260 

Step #3 : in new powershell

cockroach start --insecure --store=node3 --listen-addr=localhost:26259 --http-addr=localhost:8082 --join=localhost:26257,localhost:26258,localhost:26259,localhost:26260 

Step #4 in new power shell, execute cockroach init command to perform a one-time initialization of the cluster, sending the request to any node on the –join list:

cockroach init --insecure --host=localhost:26257


Now that your cluster is live, you can use any node as a SQL gateway. To test this out, let’s use CockroachDB’s built-in SQL client. In a new terminal, run the cockroach sql command and connect to node1:

cockroach sql --insecure --host=localhost:26257

To exit the SQL shell at any time, you can use the \q command: \q

Run some basic CockroachDB SQL statements:

root@localhost:26257/defaultdb> CREATE DATABASE kalp;

root@localhost:26257/defaultdb> USE kalp;

root@localhost:26257/kalp> CREATE TABLE kalp.students (id INT PRIMARY KEY, marks DECIMAL);

root@localhost:26257/kalp> SHOW TABLES;

Inserting new record in the table.
root@localhost:26257/kalp> INSERT INTO kalp.students VALUES (1, 55.45);

To view the records
root@localhost:26257/kalp> SELECT * FROM kalp.students;

In a new terminal window, open a new SQL shell and connect to node2:

PS C:\Windows\system32> cockroach sql –insecure –host=localhost:26258

To check the databases in another node.

root@localhost:26258/defaultdb> SHOW DATABASES;

root@localhost:26258/defaultdb> use oic;

root@localhost:26258/kalp> select * from students;


Load the initial dataset:

cockroach workload init movr ‘postgresql://root@localhost:26257?sslmode=disable’

Run the workload for 5 minutes:

cockroach workload run movr --duration=5m 'postgresql://root@localhost:26257?sslmode=disable'

Access the DB Console

The CockroachDB DB Console gives you insight into the overall health of your cluster as well as the performance of the client workload.

Go to http://localhost:8080.

On the Cluster Overview, notice that three nodes are live, with an identical replica count on each node: This demonstrates CockroachDB’s automated replication of data via the Raft consensus protocol.

FOR YOUR PRACTICE : SIMULATE NODE MAINTENANCE- see how when one node is shutdown, it automatically gets replicated once it is up again.


He specializes on Database Technologies, from Analysis and Design of Enterprise Level Database Architecture to it's implementation. He is responsible for implementation of Oracle 19C or 21C On premises and Cloud, Backup and Recovery, Performance Tuning and Query Optimization. He also has hands-on experience as a PL/SQL Developer on enterprise level application. Apart from Oracle, he conducts lectures, seminars and workshops on MySQLi Replication, High Availability, Persona, Postgres and MongoDB. His current research is based on Scalability using CockroachDB. As a technology enthusiast, he primarily develops solutions as a back-end developer using PHP/Laravel framework. He is more inclined towards FOSS and an admirer of Linux System Administration and secretly practices DevOps as a remote Engineer.

Leave a Reply