CockroachDB Cluster

Experimental CockroachDB cluster setup in docker.

This is suitable for:

• Local testing and learning
• Bare-metal (offline) deployment

Features

• Automatically create the cluster.
  • https://www.cockroachlabs.com/docs/stable/deploy-cockroachdb-on-premises
  • Create certs from short-lived container, self-signed certificate for local testing
  • Does not create certs unnecessarily
  • Init cluster from short-lived container, safe to init even if cluster already exists
• Load balancing across cluster
  • for http console
  • for cockroachdb sql client
• Set up automation for using the cluster with multiple projects (assume one project uses one database)
  • Script for creating a database and its user
• Set up postgres for feature comparison with cockroachdb
• Example nodejs code to use this cluster
  • pg and pg-migrate ?
  • knex.js?
  • https://sqorn.org/benchmarks.html ?
• Example python code to use this cluster
  • ./python-psycopg3
    • using psycopg3 directly
    • using yoyo migrations through terminal
      • docker exec into the container to run the yoyo cli
  • [ ] sqlalchemy core and alembic? (alembic is too coupled with sqlalchemy for my taste)
  • ./python-sql-alchemy-core
    • yoyo migrations called from python code
    • sqlalchemy-core for data manipulation

Prerequisites

Tested on the following:

• Linux (EndeavourOS)
• docker >= 24.0.5
• docker compose >= 2.20.3

Quickstart

1. Clone this repository
   • cd cockroach-db-cluster: Enter the project root directory.
   • Unless otherwise stated, all commands in documentation are meant to be run from this directory.
2. cp dbcluster/.env.example dbcluster/.env
   • Edit variables as necessary. Default values in .env.example should be sufficient to simply run the system.
   • .env file should contain deployment-specific values and should not be committed to the repository.
3. docker compose -f dbcluster/docker-compose.yml up: Sets up a 3-instance cluster
   • Create the required certs
   • Create a docker volume for each instance
   • Start the cluster instances
   • Init the cluster (after some healthcheck delays)
   • Set a password for the root user
   • Create HAProxy container with bind-mounted configs
4. Using the current configs, cockroachdb console is served by all 3 instances through https. Browser will complain about security but that's fine for a dev/test cluster.
   • https://localhost:8081
   • https://localhost:8082
   • https://localhost:8083
5. HAProxy has been set up to serve out the web console as well as sql clients. Only the web console port has been forwarded to the host network.
   • https://localhost:8080

Create Database and User

1. Edit ./dbcluster/scripts/create-db-and-user.sh, to set the DB_NAME, DB_USER, and DB_PSWD. These must agree with whatever is set in the client apps.
2. If necessary, chmod +x ./dbcluster/scripts/create-db-and-user.sh.
3. Run ./dbcluster/scripts/create-db-and-user.sh.
4. Some other program should be able to use this database, through this user, either using a cockroachdb client or a postgres client.

Inspect Data using CLI

1. Docker exec into cockroachdb through the loadbalancer (as the root user):

   docker compose -f ./dbcluster/docker-compose.yml \
       run --no-deps --entrypoint "cockroach sql" \
       cluster-init \
       --host=cockroach-db.local \
       --user=root \
       --certs-dir=/certs
   

2. Commands are similar to psql cli AND mysql cli:
   • List databases: \l or SHOW DATABASES;
   • Connect to a database: \c system or USE DATABASE system
   • List tables: \dt or SHOW TABLES;
   • All of the example client apps will use the database test_app (as mentioned in Create Database and User above).

Run test applications (WIP)

Example code is provided for testing with the cluster, compared with postgres.

Postgres DB

For feature/behaviour comparison, the client applications may connect to either postgres or cockroachdb.

1. docker compose -f ./dbcluster/docker-compose-postgres.yml up

Python psycopg3 client

Normal python code:

1. Edit ./python-psycopg3/docker-compose.yml, uncomment environment variables to choose either cockroachdb or postgres.
2. docker compose -f ./python-psycopg3/docker-compose.yml up --build
3. see some logs...
4. docker compose -f ./python-psycopg3/docker-compose.yml down

Using yoyo migrations:

1. Set up an interactive terminal to call yoyo commands:

   docker compose -f python-psycopg3/docker-compose.yml run python-psycopg3-client bash
   

2. Yoyo is installed in poetry environment, so call it through poetry:

   poetry run yoyo list
   

3. Perform all migrations, with verbose output:

   poetry run yoyo apply -vvv --batch
   

4. Rollback all migrations (requires interactive prompting):

   poetry run yoyo rollback -vvv --all
   

5. Also possible to perform migrations using code

Python sqlalchemy client

1. Reset all yoyo tables in the database (if yoyo cli was run in the psycopg3 client test).
   • docker exec into one of the cockroachdb instances (see above)
   • \c test_app: Connect to the test_app database
   • \dt: List tables
   • DROP TABLE IF EXISTS _yoyo_log, _yoyo_migration, _yoyo_version, yoyo_lock
2. docker compose -f ./python-sqlalchemy-core/docker-compose.yml up --build
3. (WIP) see some logs...
4. docker compose -f ./python-sqlalchemy-core/docker-compose.yml down

More Clients WIP

Stop the Cluster

1. docker compose -f ./dbcluster/docker-compose.yml down: Stop the cluster
   • Does not destroy the cluster or its data.
   • Running docker compose -f ./dbcluster/docker-compose.yml up again will revive the cluster.
2. docker compose -f ./dbcluster/docker-compose-postgres.yml down: Stop postgres

Clean Up the Entire Cluster

This removes the entire cluster and its data.

1. Stop the cluster (see above).
2. sudo ./dbcluster/scripts/destroy-cluster.sh: Remove the certs and docker volumes that were created.