How to Install CockroachDB in Standalone AWS EC2 and its basic operations

How to Install CockroachDB in Standalone AWS EC2 and its basic operations

Cockroach Labs is a computer software company that develops commercial database management systems. It is best known for CockroachDB, which has been compared to Google Spanner. CockroachDB is a project that is designed to store copies of data in multiple locations in order to deliver speedy access. CockroachDB provides cloud-native, distributed SQL for cloud applications. CockroachDB powers companies like Comcast, Lush, and WeWork.

Deploy CockroachDB on Standalone AWS EC2

We will show you how to manually deploy an insecure Standalone CockroachDB.

Requirements

1. You must have SSH access to each machine. This is necessary for distributing and starting CockroachDB binaries.

2. Your network configuration must allow TCP communication on the following ports:

26257 for intra-cluster and client-cluster communication
8080 to expose your Admin UI

Step 1. Create instances

Open the Amazon EC2 console and launch an instance for each node you plan to have in your cluster. In our case it is a standalone installation, so we'll launch a single  instance.

Step 2. Start node

You can start the node manually or automate the process using systemd.

1. SSH to the machine where you want the node to run. Ensure you are logged in as the root user.

2. Download the CockroachDB archive for Linux, and extract the binary:

wget -qO- https://binaries.cockroachdb.com/cockroach-v19.2.4.linux-amd64.tgz | tar  xvz

3. Copy the binary into the PATH:

cp -i cockroach-v19.2.4.linux-amd64/cockroach /usr/local/bin/

4. Run the cockroach start command:

cockroach start --insecure --advertise-addr=<node1 address> --join=<node1 address>,<node2 address>,<node3 address> --cache=.25 --max-sql-memory=.25 --background

[root@ip-10-0-1-63 cockroach]# cockroach start --insecure --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8088     --join=localhost:26257 --background
*
* WARNING: RUNNING IN INSECURE MODE!
*
* - Your cluster is open for any client that can access localhost.
* - Any user, even root, can log in without providing a password.
* - Any user, connecting as root, can read or write any data in your cluster.
* - There is no network encryption nor authentication, and thus no confidentiality.
*
* Check out how to secure your cluster: https://www.cockroachlabs.com/docs/v19.2/secure-a-cluster.html
*
*
* INFO: initial startup completed.
* Node will now attempt to join a running cluster, or wait for `cockroach init`.
* Client connections will be accepted after this completes successfully.
* Check the log file(s) for progress.
*

Step 3. Initialize the cluster

Run the cockroach init command, with the --host flag set to the address of any node:

cockroach init --insecure --host=<address of any node>

[root@ip-10-0-1-63 cockroach]# cockroach init --insecure --host=localhost:26257
Cluster successfully initialized
[root@ip-10-0-1-63 cockroach]# grep 'node starting' node1/logs/cockroach.log -A 11
CockroachDB node starting at 2020-02-04 04:04:35.416005065 +0000 UTC (took 103.7s)
build:               CCL v19.2.2 @ 2019/12/11 01:33:43 (go1.12.12)
webui:               http://localhost:8088
sql:                 postgresql://root@localhost:26257?sslmode=disable
RPC client flags:    cockroach <client cmd> --host=localhost:26257 --insecure
logs:                /cockroach/node1/logs
temp dir:            /cockroach/node1/cockroach-temp026129314
external I/O path:   /cockroach/node1/extern
store[0]:            path=/cockroach/node1
status:              initialized new cluster
clusterID:           7df0b3a0-e0d2-4dcf-8188-b3e51fbb8d25
nodeID:              1
[root@ip-10-0-1-63 cockroach]#

Each node then prints helpful details to the standard output, such as the CockroachDB version, the URL for the admin UI, and the SQL URL for clients.

Step 4. Test the cluster

CockroachDB replicates and distributes data behind-the-scenes and uses a Gossip protocol to enable each node to locate data across the cluster. Once a cluster is live, any node can be used as a SQL gateway. In our case its a singlr node setup, Hence we'll directly login to that host and test the cockroachdb.

When using a load balancer, you should issue commands directly to the load balancer, which then routes traffic to the nodes.

Use the built-in SQL client locally as follows:

On your local machine, launch the built-in SQL client, with the --host flag set to the address of the load balancer. In our case we'll connect to "Localhost"


cockroach sql --insecure --host=<address of lb or host>

[root@ip-10-0-1-63 cockroach]# cockroach sql --insecure --host=localhost:26257
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v19.2.2 (x86_64-unknown-linux-gnu, built 2019/12/11 01:33:43, go1.12.12) (same version as clie    nt)
# Cluster ID: 7df0b3a0-e0d2-4dcf-8188-b3e51fbb8d25
#
# Enter \? for a brief introduction.
#
root@localhost:26257/defaultdb>

Create a bank database:

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

Time: 9.80718ms

root@localhost:26257/defaultdb>

root@localhost:26257/defaultdb>

CREATE A TABLE :

root@localhost:26257/defaultdb> CREATE TABLE bank.accounts (id INT PRIMARY KEY, balance DECIMAL);
CREATE TABLE

Time: 9.130328ms

Insert a Record and retrieve it:

root@localhost:26257/defaultdb> INSERT INTO bank.accounts VALUES (1, 1000.50);
INSERT 1

Time: 7.330928ms

root@localhost:26257/defaultdb> SELECT * FROM bank.accounts;
  id | balance
+----+---------+
   1 | 1000.50
(1 row)

Time: 737.333µs

SHOW DATABASES :

root@localhost:26257/defaultdb> SHOW DATABASES;
  database_name
+---------------+
  bank
  defaultdb
  postgres
  system
(4 rows)

Time: 1.058354ms

root@localhost:26257/defaultdb>

Use \q to exit the SQL shell.

root@localhost:26257/defaultdb> \q

WebUI:

A graphical interface can be accessed for cockroach DB at the webui URL. In our case the URL is " http://localhost:8088"

Overview:

Metrics: