Installing CockroachDB client on Ubuntu 20.04

Here we will install the CockroachDB database & client on Ubuntu 20.04 Operating system
You will need to use sudo if you get any permission errors.

Download CockroachDB libraries
$ curl https://binaries.cockroachdb.com/cockroach-v21.2.10.linux-amd64.tgz | tar -xz && sudo cp -i cockroach-v21.2.10.linux-amd64/cockroach /usr/local/bin/

Note: To get started with CockroachDB, we dont need to install the additional GEOS libraries.
Please follow this documentation for setting up GEOS libraries.


Downloading CA Certificate

-Now once the client is installed, we need to download the CA Certificate so that we can connect remotely to the database.
-The command to download the same is OS dependent and same can be found on the clusters page.
-Click on the Connect button on the cluster webpage and it should open up a popup window.
-Copy the Download CA-Cert command

alter-text

-Execute the command in the terminal
-This will download the certificate the the logged-in users home directory.

$ curl --create-dirs -o $HOME/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/7f5acd64-69eb-4684-bd61-d7cdb946a678/cert
Connecting to Database

-When we create a cluster, by default we will get a Database which is called “defaultdb”
-We can connect to the database using the below Connection string.
-Make sure you pass in the correct path for the “sslrootcert” parameter.

cockroach sql --url 'postgresql://user01:L-fGdxFbgqQ6srSrYIqRxw@free-tier8.aws-ap-southeast-1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=tester-1760'
After logging-in, below messages will be displayed.
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v21.1.19 (x86_64-unknown-linux-gnu, built 2022/05/09 15:01:31, go1.15.14)
# Server version: CockroachDB CCL v21.2.10 (x86_64-unknown-linux-gnu, built 2022/05/02 17:38:58, go1.16.6)
# Cluster ID: 9db6a4da-a8ac-439a-bfd1-3332d059190e
No entry for terminal type "xterm-256color";
using dumb terminal settings.
#
# Enter \? for a brief introduction.
#
user01@free-tier8.aws-ap-southeast-1.cockroachlabs.cloud:26257/defaultdb>
Creating a table & querying it
user01@free-tier8.aws-ap-southeast-1.cockroachlabs.cloud:26257/defaultdb> create table student (id int, name varchar(30)); 
CREATE TABLE

Time: 90ms total (execution 10ms / network 80ms)


user01@free-tier8.aws-ap-southeast-1.cockroachlabs.cloud:26257/defaultdb> insert into student values (1, 'JOHN DOE');         
INSERT 1

Time: 101ms total (execution 3ms / network 98ms)

user01@free-tier8.aws-ap-southeast-1.cockroachlabs.cloud:26257/defaultdb> select * from student;
  id |   name
-----+-----------
   1 | JOHN DOE
(1 row)

Time: 94ms total (execution 2ms / network 92ms)


Thank you for reading this post.
If you would like to know how to connect to CockroachDB server using Python; then be sure to check this post here.

Related Posts

Connecting to CockroachDB cluster from Python

Here we will connect to CockroachDB database using Python. Specifically we will be using psycopg2 library Create a Virtual Environment and install psycopg2 library $ python3 -m venv env $ source env/bin/activate (env) $ pip3 install psycopg2-binary Collecting psycopg2-binary Using cached psycopg2_binary-2.

Read more

Creating a CockroachDB cluster

CockroachDB is a distributed SQL database management system, developed by Cockroach Labs. It is relatively new entrant to the database space & designed for speed, scale, and survival.

Read more

Top Free Jupyter Runtimes to get started with Machine Learning journey

Jupyter notebooks are a great tool for data analysis due to rich interface features it offers. But for training fairly good models require good amount CPU, GPU, RAM and storage.

Read more