• Recent blogs

    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.

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

    Deploy CockroachDB on Standalone AWS EC2

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


    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
    * - 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.

    Create a bank database:

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

    Time: 9.80718ms



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

    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


    root@localhost:26257/defaultdb> SHOW DATABASES;
    (4 rows)

    Time: 1.058354ms

    Use \q to exit the SQL shell.
    root@localhost:26257/defaultdb> \q


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



    1 comment: