DEV Community: Fabio Ghirardello

CockroachDB Graceful Shutdowns

Fabio Ghirardello — Thu, 09 Oct 2025 13:30:58 +0000

This is a brief extension to the Node Shutdown section discussed in blog Repaving CockroachDB cluster node VMs the easy way. In this blog, we go through some hands-on experiments which will help you observe the client, load-balancer and CockroachDB nodes behavior.

Create a local 9 node cluster

Start a 3x3 node cluster, locally.
Run each of these commands in its own terminal window.

# NY region
cockroach start --insecure --store=node1 --listen-addr=localhost:26291 --http-addr=localhost:8091 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=NY,dc=a
cockroach start --insecure --store=node2 --listen-addr=localhost:26292 --http-addr=localhost:8092 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=NY,dc=b
cockroach start --insecure --store=node3 --listen-addr=localhost:26293 --http-addr=localhost:8093 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=NY,dc=c

# TX region
cockroach start --insecure --store=node4 --listen-addr=localhost:26281 --http-addr=localhost:8081 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=TX,dc=a
cockroach start --insecure --store=node5 --listen-addr=localhost:26282 --http-addr=localhost:8082 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=TX,dc=b
cockroach start --insecure --store=node6 --listen-addr=localhost:26283 --http-addr=localhost:8083 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=TX,dc=c

# CA region
cockroach start --insecure --store=node7 --listen-addr=localhost:26271 --http-addr=localhost:8071 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=CA,dc=a
cockroach start --insecure --store=node8 --listen-addr=localhost:26272 --http-addr=localhost:8072 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=CA,dc=b
cockroach start --insecure --store=node9 --listen-addr=localhost:26273 --http-addr=localhost:8073 --join=localhost:26291,localhost:26281,localhost:26271 --locality=region=CA,dc=c

Init the cluster, then start a SQL prompt

cockroach init --insecure --port 26291

cockroach sql --insecure --port 26291

Open the DB Console at http://localhost:8091/#/reports/localities to review the topology.

We configure cluster settings so that the server pauses for 45s before initialing the drain, and 70s for the connection pool to close off all existing connections.

SET CLUSTER SETTING server.shutdown.initial_wait = '45s';
SET CLUSTER SETTING server.shutdown.connections.timeout = '70s';

HAProxy configuration

Check the HAProxy .cfg files.

# haproxy-ny.cfg

global
  maxconn 4096

defaults
    mode                tcp
    retries             3
    timeout connect     10s
    timeout client      10m
    timeout server      10m
    option              clitcpka

listen psql
    bind :26290
    mode tcp
    balance roundrobin
    option httpchk GET /health?ready=1
    default-server inter 10s fall 3 rise 2
    server cockroach1 127.0.0.1:26291 check port 8091
    server cockroach2 127.0.0.1:26292 check port 8092
    server cockroach3 127.0.0.1:26293 check port 8093

    # Secondary (TX LB)
    server tx_lb 127.0.0.1:26280 check port 8080 backup

    # Tertiary (CA LB)
    server ca_lb 127.0.0.1:26270 check port 8070 backup

listen http
    bind :8090
    mode tcp
    balance roundrobin
    server cockroach1 127.0.0.1:8091
    server cockroach2 127.0.0.1:8092
    server cockroach3 127.0.0.1:8093

# haproxy-tx.cfg

global
  maxconn 4096

defaults
    mode                tcp
    retries             3
    timeout connect     10s
    timeout client      10m
    timeout server      10m
    option              clitcpka

listen psql
    bind :26280
    mode tcp
    balance roundrobin
    option httpchk GET /health?ready=1
    default-server inter 10s fall 3 rise 2
    server cockroach1 127.0.0.1:26281 check port 8081
    server cockroach2 127.0.0.1:26282 check port 8082
    server cockroach3 127.0.0.1:26283 check port 8083

    # Secondary (NY LB)
    server ny_lb 127.0.0.1:26290 check port 8090 backup

    # Tertiary (CA LB)
    server ca_lb 127.0.0.1:26270 check port 8070 backup

listen http
    bind :8080
    mode tcp
    balance roundrobin
    server cockroach1 127.0.0.1:8081
    server cockroach2 127.0.0.1:8082
    server cockroach3 127.0.0.1:8083

# haproxy-ca.cfg
global
  maxconn 4096

defaults
    mode                tcp
    retries             3
    timeout connect     10s
    timeout client      10m
    timeout server      10m
    option              clitcpka

listen psql
    bind :26270
    mode tcp
    balance roundrobin
    option httpchk GET /health?ready=1
    default-server inter 10s fall 3 rise 2
    server cockroach1 127.0.0.1:26271 check port 8071
    server cockroach2 127.0.0.1:26272 check port 8072
    server cockroach3 127.0.0.1:26273 check port 8073

    # Secondary (TX LB)
    server tx_lb 127.0.0.1:26280 check port 8080 backup

    # Tertiary (NY LB)
    server ny_lb 127.0.0.1:26290 check port 8090 backup

listen http
    bind :8070
    mode tcp
    balance roundrobin
    server cockroach1 127.0.0.1:8071
    server cockroach2 127.0.0.1:8072
    server cockroach3 127.0.0.1:8073

Notice that we have lines:

option httpchk GET /health?ready=1
default-server inter 10s fall 3 rise 2

server tx_lb 127.0.0.1:26280 check port 8080 backup

HAProxy will

probe every 10s and declare a server down after 3 failed attempts.
probe server endpoint /health?ready=1 for server health.
if all servers in the pool are down, we have backup servers available.

Start the Load Balancers, running each command in its own terminal window.

# NY LB
haproxy -f haproxy-ny.cfg

# TX LB
haproxy -f haproxy-tx.cfg

# CA LB
haproxy -f haproxy-ca.cfg

Running the Client App

The simple python app test.py creates a connection pool, then queries and prints the elapsed time, the node location and the connection object details.

#!/usr/bin/env python3
import time

from psycopg_pool import ConnectionPool

# note we specify the NY, TX, CA LBs in the connection string.
# by default, the first host is chosen unless the host is down

DB_DSN = "postgres://root@localhost:26290,localhost:26280,localhost:26270/defaultdb?sslmode=disable"


# Create a pool with exactly 5 connections
# the `check` params checks the validity of the connection before handing it out of the pool.
# if the conneciton is unhealthy, a new one will be recreated.

pool = ConnectionPool(
    conninfo=DB_DSN,
    min_size=5,
    max_size=5,
    open=True,
    max_lifetime=60,
    check=ConnectionPool.check_connection,
    kwargs={"autocommit": True},
)

start_time = time.time()


while True:
    for x in range(3):
        with pool.connection() as conn:
            with conn.cursor() as cur:
                rs = cur.execute("show locality", ()).fetchone()

                print(int(time.time() - start_time), rs, conn)

    print()
    time.sleep(5)

This is sufficient to test connections health, and it allows to observe what connection we are using and to what server node is connected.

Notice in the test.py file, in the pool init command, we set a max_lifetime=60 seconds.
Also, we have set that connections should be validated before handed out using check=ConnectionPool.check_connection.

Also, this app will only connect to the NY LB, however, in the connection string I have also configured the TX and CA loadbalancers as failback options.

DB_DSN = "postgres://root@localhost:26290,localhost:26280,localhost:26270/defaultdb?sslmode=disable"

Start the app.

# you first need to install the dependencies
pip install psycopg[pool] psycopg-binary

# now you can run the app
python test.py

Let the app run continuosly.

Notice that we are connected to the NY LB, identifiable by port 26290, and are correctly reaching a cockroachdb NY based node, region=NY,dc=a.
Finally, keep an eye on the 0x1041... memory address, which identifies the connection object.
You will see there are 5 distinct objects, but after 60s, these will be replaced by a new set of connections.

5 ('region=NY,dc=a',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104161090>
6 ('region=NY,dc=b',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104147310>
6 ('region=NY,dc=c',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104146e10>

11 ('region=NY,dc=a',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104161090>
11 ('region=NY,dc=a',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x101d55210>
11 ('region=NY,dc=b',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104147310>

16 ('region=NY,dc=c',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104146810>
16 ('region=NY,dc=c',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104146e10>
16 ('region=NY,dc=a',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x104161090>

If below tests succeed, you will NOT see any error messages, and instead all events have been handled gracefully.

Test 1 - node shutdown

We test a node going down for maintenance.

Pick any of the terminals where you started a NY node, and Ctrl+C.
The node will start a graceful shutdown.
The node sets its healthcheck endpoint to HTTP503, signaling to the LB that the server is unhealthy.
initial_wait timeout will start.
The server is however still working. Both existing and new connections succeed.

After about 30s, the LB has declared this server as down and will be removed from the LB pool.

[WARNING]  (42103) : Server psql/cockroach1 is DOWN, 
reason: Layer7 wrong status, code: 503, 
info: "Service Unavailable", check duration: 0ms. 
2 active and 2 backup servers left. 
0 sessions active, 0 requeued, 0 remaining in queue.

Therefore, new connection requests will be routed elsewhere.
After 45s, the initial_wait is done and the node starts to drain.
connections_timeout of 75s kicks in. Existing connections are allowed to continue working, waiting for the connection pool to retire them.
After about 60s, the connection pool will have retired all existing connections and recreated them elsewhere.
connections_timeout expires, and any remaining connection will be severed.
Eventually, node shutdown completes, gracefully.
The app won't have noticed any disruption.
Bring the node back up.

Test 2 - datacenter shutdown

In this test, all nodes in a given datacenter go down. The NY load balancer should route all traffic to TX LB.

Note from the HAProxy config file, I have added backup servers in case the 3 primary servers fail, and note that the 2 backup servers are the LB of the other regions.

Start a graceful shutdown for all 3 NY servers, using Ctrl+C.
initial_wait timeout kicks in, and the healthcheck endpoint returns HTTP503.

After about 30s, the NY HAProxy will mark all NY nodes as unavailable, and route new connections to the TX LB.

[WARNING]  (90981) : Server psql/cockroach1 is DOWN, 
reason: Layer7 wrong status, code: 503, 
info: "Service Unavailable", check duration: 0ms. 
2 active and 2 backup servers left. 1 sessions active, 0 requeued, 0 remaining in queue.

[WARNING]  (90981) : Server psql/cockroach2 is DOWN, 
reason: Layer7 wrong status, code: 503, 
info: "Service Unavailable", check duration: 0ms. 
1 active and 2 backup servers left. 2 sessions active, 0 requeued, 0 remaining in queue.

[WARNING]  (90981) : Server psql/cockroach3 is DOWN, 
reason: Layer7 wrong status, code: 503, 
info: "Service Unavailable", check duration: 0ms. 
0 active and 2 backup servers left. 
Running on backup. 2 sessions active, 0 requeued, 0 remaining in queue.

initial_wait is completed, and the connections_timeout kicks in. New connections will not be accepted.
After about 60s, all connections have been closed by the conn pool.

Everything proceeds like before. Despite all nodes going down, the app still found a way to the CockroachDB cluster, in this case, to TX, see below the transition from NY to TX, still via the NY LB (port 26290)

125 ('region=NY,dc=b',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x10b940490>
125 ('region=NY,dc=c',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x10b925590>
125 ('region=TX,dc=b',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x10b91f110>

130 ('region=NY,dc=b',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x10b91e390>
130 ('region=TX,dc=c',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x10b91ce90>
130 ('region=TX,dc=b',) <psycopg.Connection [IDLE] (host=localhost port=26290 user=root database=defaultdb) at 0x10b9405d0>

Bring all 3 nodes back up. Soon, the LB will start rerouting new connections to the NY nodes again.

Test 3 - LB goes down

In this test, we simulate the NY LB crashing.

When the NY LB goes down, all its connections will be broken, so they need to be recreated.
I have instantiated the connection pool with the check parameter so that every connection is health-checked before it is given to the caller.

The pool will verify the connection, identify that it is broken, delete it and create a new one.
As however the NY LB is down, the pool will use the 2nd host listed in the connection string, which is the TX LB.

Stop the NY LB by using Ctrl+C.
Check the app output. Notice that without throwing any error, connections now are established against port 26280, which is TX.
Bring the NY LB back up.
After all connections have been recycled, after 60s, you will see that new connections are routed back to 26270, NY.

Conclusion

By careful configuration, you can handle all services disruption gracefully; even if you don't use HAProxy as your LB solution, you will find in your LB very similar configuration options.

It is important to understand that all 3 services need to be configured, not just CockroachDB.

The example tests in this blog should help you validate your own setup, even if you don't use psycopg_pool as your connection pool. For Java apps, HikariCP can do exactly the same things.

You can now stop all processes and delete the node* directory in your filesystem.

CockroachDB SSO integration using Keycloak

Fabio Ghirardello — Fri, 06 Jun 2025 15:54:39 +0000

In this blog post, I will cover the steps required to enable SSO in a CockroachDB cluster, specifically, when running locally on MacOS, but the steps are similar for any environment.

Installation

We need to install the following software:

CockroachDB
PostgreSQL Server
OpenJDK
Keycloak

Let's get started!

PostgreSQL Server

Install PostgreSQL Server for MacOS: https://postgresapp.com/

Upon installation you will see the elephant in your toolbar close to the clock

Make sure you create a username and password with admin privileges, and start the server.

CockroachDB

Make sure CRDB is running locally and listens on port 8080.
Instructions on where to download the binary is on the Release page.

This is the command I use

/usr/local/bin/cockroach start-single-node --certs-dir /Users/fabio/certs --store /Users/fabio/cockroach-data

I use my fancy CRDB Launcher for MacOS. It's not strictly required, but I loved how the PostgresApp menu bar looks and wanted the same for CockroachDB.

OpenJDK 21+

Keycloak requires Java 21+, so I installed openJDK from brew.

brew install openjdk

After installation, I was still seeing Java 14 when issuing a java -version.

Check for solutions in the keg instructions:

$ brew info openjdk
==> openjdk: stable 23.0.2 (bottled) [keg-only]
Development kit for the Java programming language
https://openjdk.java.net/
Installed
/usr/local/Cellar/openjdk/23.0.2 (602 files, 337.0MB)
  Poured from bottle using the formulae.brew.sh API on 2025-06-05 at 13:28:19
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/o/openjdk.rb
License: GPL-2.0-only WITH Classpath-exception-2.0
==> Dependencies
Build: autoconf ✘, pkgconf ✘
Required: freetype ✔, giflib ✔, harfbuzz ✔, jpeg-turbo ✔, libpng ✔, little-cms2 ✔
==> Requirements
Build: Xcode (on macOS) ✔
Required: macOS >= 10.15 (or Linux) ✔
==> Caveats
For the system Java wrappers to find this JDK, symlink it with
  sudo ln -sfn /usr/local/opt/openjdk/libexec/openjdk.jdk /Library/Java/JavaVirtualMachines/openjdk.jdk

[...]

I thus run as suggested

sudo ln -sfn /usr/local/opt/openjdk/libexec/openjdk.jdk /Library/Java/JavaVirtualMachines/openjdk.jdk

and I now get

$ java -version
openjdk version "23.0.2" 2025-01-21

Keycloak

Installation and basic config

Download the Keycloak binaries for the OpenJDK platform, see link in the OpenJDK Getting Started page.

Unzip it, cd into the dir and issue the start command.
We just want to confirm there are no problem getting it up and running.

Note, I use port 8081 as the default, 8080, is reserved for my locally running CockroachDB cluster.

$ bin/kc.sh start-dev --http-port 8081
Running the server in development mode. DO NOT use this configuration in production.
2025-06-05 13:45:53,073 INFO  [org.Keycloak.quarkus.runtime.storage.infinispan.CacheManagerFactory] (main) Starting Infinispan embedded cache manager
2025-06-05 13:45:53,144 INFO  [org.Keycloak.quarkus.runtime.storage.infinispan.CacheManagerFactory] (main) JGroups JDBC_PING discovery enabled.
2025-06-05 13:45:53,270 INFO  [org.infinispan.CONTAINER] (main) Virtual threads support enabled
2025-06-05 13:45:53,463 INFO  [org.infinispan.CONTAINER] (main) ISPN000556: Starting user marshaller 'org.infinispan.commons.marshall.ImmutableProtoStreamMarshaller'
2025-06-05 13:45:53,832 INFO  [org.Keycloak.connections.infinispan.DefaultInfinispanConnectionProviderFactory] (main) Node name: node_904367, Site name: null
2025-06-05 13:45:54,791 INFO  [io.quarkus] (main) Keycloak 26.2.5 on JVM (powered by Quarkus 3.20.1) started in 5.618s. Listening on: http://0.0.0.0:8081
2025-06-05 13:45:54,791 INFO  [io.quarkus] (main) Profile dev activated. 
2025-06-05 13:45:54,791 INFO  [io.quarkus] (main) Installed features: [agroal, cdi, hibernate-orm, jdbc-h2, Keycloak, narayana-jta, opentelemetry, reactive-routes, rest, rest-jackson, smallrye-context-propagation, vertx]

Go to http://localhost:8081 to make sure you see the website.

You'll be prompted to create a temporary admin user, then to login.
Upon login, you should see something like the below

Now you can Ctrl+C to stop the server.

While you can keep using the builtin database, I prefer using Postgres in case data gets lost.
Related docs are here.

Start Keycloak pointing at your local PostgreSQL Server.

bin/kc.sh start-dev --http-port 8081 --db-url-host=localhost --db-password=postgres --db-username=fabio --db postgres

The server should be up and running, and if you inspect PostgreSQL server, you'll see a new database keycloak was created with a bunch of tables.

fabio: ~ $ psql -d keycloak
Timing is on.
psql (17.4, server 16.2 (Postgres.app))
Type "help" for help.

keycloak=# \d
                   List of relations
 Schema |             Name              | Type  | Owner 
--------+-------------------------------+-------+-------
 public | admin_event_entity            | table | fabio
 public | associated_policy             | table | fabio
 public | authentication_execution      | table | fabio
 public | authentication_flow           | table | fabio
 public | authenticator_config          | table | fabio
 ...

Configuration

Everything is ready, we can start configuring Keycloak and CRDB for SSO integration.

Create admin user in Keycloak

Now you can login into Keycloak with the temp user. Since you will be prompted to delete it after the first login, call it tempadmin and password tempadmin.

Upon login, go to Users, click Add User button and create a username called, simply, admin and optionally set the "Email Verified" toggle to On.
Click Create, then click on the newly created user.

Go to the Credential tab and create a password, simply admin to make it easy to remember.
Then go to tab Role mapping to add the admin role to the user admin. So click on Assign Role, make sure you choose the Filter by realm roles and there you'll see the role_admin.

Now you can logout, log back in as the admin user, go to the Users section and delete the tempadmin user.

Create regular users and group

You can now go ahead and create a regular user, set a password, then create a group, and assign the user to the group.

Make sure the user you create has a corresponding username in CRDB.

For example, I created my Keycloak user fabio with password fabio, and there's also a fabio username in my CRDB cluster.
The keycloack and crdb password for the user don't have to match.

> show users;                                                                                                                                                                                                                  
    username   | options | member_of
---------------+---------+------------
  admin        |         | {}
  cockroach    |         | {admin}
  fabio        |         | {admin}
  root         |         | {admin}
(4 rows)

Time: 7ms total (execution 7ms / network 0ms)

Create a Realm `main`

Setup a new realm and client as instructed in the Getting Started doc.

I called my realm main.
When you set your current realm to main, go to Realm Settings page, and scroll to the bottom to see the Endpoints.

The well-known page for a realm called main is http://localhost:8081/realms/main/.well-known/openid-configuration.

If you open the endpoint, you'll see something like this: we need these details to configure the SSO integration.

{
    "issuer": "http://localhost:8081/realms/main",
    "authorization_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/auth",
    "token_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/token",
    "introspection_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/token/introspect",
    "userinfo_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/userinfo",
    "end_session_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/logout",
    "frontchannel_logout_session_supported": true,
    "frontchannel_logout_supported": true,
    "jwks_uri": "http://localhost:8081/realms/main/protocol/openid-connect/certs",
    "check_session_iframe": "http://localhost:8081/realms/main/protocol/openid-connect/login-status-iframe.html",
    "grant_types_supported": [
        "authorization_code",
        "client_credentials",
        "implicit",
        "password",
...

Try clicking on the link in jwks_uri: you'll see the keys that will help CRDB validate the authenticity of the JWT.

Create a Client `BankApp`

Go to the Clients page and create a new client called BankApp.

Set client_authentication to on, make sure Standard flow and Direct Access Grant is checked.

Set the valid redirect URI to CockroachDB's callback url: https://localhost:8080/oidc/v1/callback.

Set the web origin to CockroachDB's webserver: https://localhost:8080.

Create the client, then go to the Credentials tab and jot down the client secret.

In my case, the client secret is 401O9E8awNtCL934rPx3pbWgIoJxv1Bb.

Save the configuration, then verify we can get a JWT from Keycloak manually from the terminal client, simulating any webservice such as CockroachDB.

Open a Terminal, and run the below. I hope you have jq installed else try installing it with brew.

Notice that we're hitting the token_endpoint taken from the well-known configuration for this realm.
We are also passing the client_id and client_secret, plus the username and password for the user that wishes to authenticate, in this case, user fabio.

$ curl -X POST "http://localhost:8081/realms/main/protocol/openid-connect/token" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=password" \
  -d "client_id=BankApp" \
  -d "client_secret=401O9E8awNtCL934rPx3pbWgIoJxv1Bb" \
  -d "username=fabio" \
  -d "password=fabio" \
  -d "scope=openid" | jq

The output is truncated here as the tokens are very large

{
  "access_token": "eyJhb...",
  "expires_in": 300,
  "refresh_expires_in": 1800,
  "refresh_token": "ey...",
  "token_type": "Bearer",
  "id_token": "ey...",
  "not-before-policy": 0,
  "session_state": "ba6328d5-39d6-42df-9ceb-225319245541",
  "scope": "openid email profile"
}

Grab the id_token and paste it into a JWT debugger such as https://token.dev/, it will decode it.
Please note the token is not encrypted, it's just base64 encoded.

The website will show these interesting details about who logged in.
Notice that preferred_username is what Keycloak uses to share the username field.

  {
  "exp": 1749153006,
  "iat": 1749152706,
  "jti": "dda84f1c-3f07-4e59-8553-5d1672f8a66d",
  "iss": "http://localhost:8081/realms/main",
  "aud": "BankApp",
  "sub": "18596f04-b197-4752-8e6c-790222e235c7",
  "typ": "ID",
  "azp": "BankApp",
  "sid": "5f20475c-4930-407e-b5c1-37d736eba6e8",
  "at_hash": "epLu6r16ndQosFol9K6sOw",
  "acr": "1",
  "email_verified": true,
  "name": "Fabio Ghirardello",
  "preferred_username": "fabio",
  "given_name": "Fabio",
  "family_name": "Ghirardello",
  "email": "fabio@cockroachlabs.com"
}

At the bottom of the page you'll also see the private and public keys.
These are used by CockroachDB to validate the authenticity of the JWT, that is, it will provide proof that the JWT comes indeed from the Keycloak IdP,
and it can do so because we will share the keys that were used to sign the JWT (remember, those are the keys in jwks_uri in the well-known).

Well, we get the token just fine, so Keycloak is ready!

CockroachDB configuration

Relevant docs are:

Thus I setup these cluster settings.
Notice that the values are taken mostly from the well-known page.

--
-- SQL SSO
--

-- trivial
SET CLUSTER SETTING server.jwt_authentication.enabled = true;

-- taken from field "issuer" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.issuers.configuration = 'http://localhost:8081/realms/main';

-- taken by navigating to the URL in field "jwks_uri" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.jwks = '{"keys":[{"kid":"h....."}]}';

-- this is the client_id
SET CLUSTER SETTING server.jwt_authentication.audience = '["BankApp"]';

SET cluster setting server.oidc_authentication.client_secret = '401O9E8awNtCL934rPx3pbWgIoJxv1Bb';

-- this is the field we grab from the token to use as the db's username
SET CLUSTER SETTING server.jwt_authentication.claim = 'preferred_username';

-- reference only: this strips the @bank.com in, say, fabio@bank.com 
-- SET CLUSTER SETTING server.identity_map.configuration = 'http://localhost:8081/realms/main /^(\S+)(?:@) \1';

-- takes everything passed in the `claim`, that is, `preferred_username`.
SET CLUSTER SETTING server.identity_map.configuration = 'http://localhost:8081/realms/main /^(\S+) \1';

--
-- DBConsole SSO
--
SET CLUSTER SETTING server.oidc_authentication.enabled = true   ; 

SET CLUSTER SETTING server.oidc_authentication.client_id = 'BankApp';

SET CLUSTER SETTING server.oidc_authentication.client_secret = '401O9E8awNtCL934rPx3pbWgIoJxv1Bb';;

SET CLUSTER SETTING server.oidc_authentication.claim_json_key = 'preferred_username';

SET CLUSTER SETTING server.oidc_authentication.provider_url = 'http://localhost:8081/realms/main';

SET CLUSTER SETTING server.oidc_authentication.redirect_url = 'https://localhost:8080/oidc/v1/callback';

SET CLUSTER SETTING server.oidc_authentication.scopes = 'openid';

SET CLUSTER SETTING server.oidc_authentication.button_text = 'Keycloak SSO';

SET CLUSTER SETTING server.oidc_authentication.generate_cluster_sso_token.enabled = true;

Once these are set, you should see these 2 buttons appear in the Login page

DB Console SSO login

Clicking on Keycloak SSO will direct you to the Keycloack SSO website.
Login as fabio/fabio as that's the Keycloak username.

Once you hit enter, Keycloak will authenticate you and, if successful, redirect you to the DBConsole.
Behind the scene, a JWT is sent by Keycloak to CRDB that confirms your authentication.
CockroachDB verifies and validates the JWT, and grants you access.

SQL SSO login

Either using the curl command above or by clicking on the Generate JWT auth token button in the DBConsole, get hold of your id_token.

Then use it as a password in your connection DBURL.
Make sure you add options=--crdb:jwt_auth_enabled=true to the DBURL string to inform CRDB that what you're passing is not a password, but a JWT.

$ export TOKEN=ey...
$ cockroach sql --url "postgres://fabio:$TOKEN@localhost:26257/defaultdb?sslmode=require&options=--crdb:jwt_auth_enabled=true"
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v25.1.0 (x86_64-apple-darwin19, built 2025/02/14 18:09:08, go1.22.8 X:nocoverageredesign)
# Server version: CockroachDB CCL v25.1.7 (x86_64-apple-darwin19, built 2025/05/26 13:41:39, go1.22.8 X:nocoverageredesign)
# Cluster ID: e360faa9-2ba3-4e92-bd51-fc7e88cf24a8
# Organization: Workshop
#
# Enter \? for a brief introduction.
#
fabio@localhost:26257/defaultdb>

This concludes the tutorial, I hope you had fun setting up your local IdP!

For any question, reach out to the CockroachDB Community Slack channel.

Kafka 2 crdb

Fabio Ghirardello — Wed, 09 Apr 2025 21:36:54 +0000

From CockroachDB to AWS SNS via AWS API Gateway

Fabio Ghirardello — Wed, 09 Oct 2024 16:11:55 +0000

This is a brief write up on how to send rows inserted into a CockroachDB table as messages to a SNS topic.

CockroachDB Change Data Capture (CDC) features several sinks, see docs for a full list here, however AWS SNS is not yet one of these.

We can workaround this limitation by leveraging the webhook sink, which publishes to a HTTP endpoint, and API Gateway, which provides such HTTP endpoint and is tightly integrated internally with other AWS services, such as SNS.

At high level, the pipeline works as follows:

A Changefeed is created on the CockroachDB table.
The Changefeed watches for INSERTs (any write operation, to be precise) on the table and emits messages to the API Gateway endpoint.
API Gateway receives the message and publishes the payload to the SNS topic via an internal AWS integration.
Subscribers of the SNS topic get a notification.

We will create this pipeline backwards, starting from creating the SNS topic back to configuring the Changefeed.

Create SNS topic

This is the easiest step of them all, thanks to the AWS Console for making it so easy.

Open the AWS Console and head to Simple Notification Service.
You'll be welcomed to a page where you are prompted to create a new topic.
Create a topic named "events". Keep all the default settings; for this test we won't bother with certs and keys, we want to make sure the pipeline works first.
Create a subscriber. In my example, I choose "Protocol" to be "Email", and entered my email address as the "Endpoint".
Finally, very important, test it out directly from the Console by using the Publish message button.
Check your inbox, you should have received a request to verify your email address, and the message you published.

Jot down your topic ARN, in my case is arn:aws:sns:ca-central-1:3xxx8:events

Good, you have created the topic and validated it's working.

Create AWS Role

A prerequisite for the API Gateway is to have a AWS Role with privileges to Publish to the SNS topic we just created.

Go to AWS IAM.
Go to Roles and "Create role". Select AWS Service as 'Trusted Entity Type' and "API Gateway" as the 'Use case'.
Continue to Step 3. Name it "events_role" and create it.
Your role is now created, but it doesn't have the permission it needs. Find the role in the list of roles and select it.
In the Permission section, Add Permission and choose "AmazonSNSFullAccess", for simplicity, then save.

Jot down the ARN of the role, in my case it's arn:aws:iam::3xxx8:role/events_role

Create API Gateway endpoint

From the AWS Console, head to API Gateway.
Click the Create API button and select a 'REST API'.
Name it "events", and set 'API Endpoint type' to "Regional".
Within the "events" API selected, click the Create Resource button and choose / as Resource Path and events as Resource Name. No need to enable CORS or the Proxy Resource.
Once in the Resource Detail page for the /events/ path, choose Create Method
Choose:
Method type = POST
Integration type = AWS service
AWS Region = ca-central-1, which is the region I've used to deploy the SNS topic
AWS Service = Simple Notification Service
AWS Subdomain = keep it blank
HTTP method = POST
Action Type = Use path override
Path override = arn:aws:apigateway:ca-central-1:sns:path// <-- substitute with your region
Execution role = the ARN role you jotted down previously

Leave the rest the default then "Create method"

You should now have something that looks like this:

Now, we need to create the tidbit that takes the incoming HTTP payload and inserts it as to the SNS topic.

Click the "Integration Request" tab, then Edit
Scroll to the "URL request headers parameters" section and add

Name = Content-Type
Mapped from = 'application/x-www-form-urlencoded'

Go to "Mapping templates" and set

Content type = application/json
Template body =

#set($topic="arn:aws:sns:ca-central-1:3xxx8:events")
#set($msg=$input.path('$.payload'))
Action=Publish&TopicArn=$util.urlEncode($topic)&Message=$util.urlEncode($msg)

Substitute my value with your SNS topic ARN

Save, then you should see the below

Finally, click the Test tab, and in the Request Body enter

{"payload":"test_event_api"}

You should get a confirmation that the message was published, and soon you should receive the same SNS email in your inbox.

Wow, it's working, getting there!

Now we need to publish this API, and create a usage plan.

Deploy API

On the main page for this API, click "Deploy API"
At the prompt, choose New stage and call it DEV.
Click 'Deploy'

You'll be redirected to the Stages section.
Jot down the Invoke URL, in my case, it's https://72eebc6w0k.execute-api.ca-central-1.amazonaws.com/DEV

Create usage plan

On the main page for this API, click 'Usage Plans'
Create a new usage plan. I added these sample values for this functional test

Name = events_plan
Rate = 10
Burst = 10
Requests = 100 per day
Click 'Create usage plan'
Re-select the newly create events_plan usage plan, and add a Stage
At the prompt, choose

API = events
Stage = DEV
Save your changes.

Test locally

Now that the API is live and that we have a billing plan attached, we are ready to test if that can be invoked by an external client.

On your laptop, thus locally, invoke it using curl

$ curl -X POST https://72eebc6w0k.execute-api.ca-central-1.amazonaws.com/DEV/events --json '{"payload":"hello_from_curl"}' | jq
{
  "PublishResponse": {
    "PublishResult": {
      "MessageId": "7a3e9555-6031-5d2b-b72a-cad5c8ddf3b4",
      "SequenceNumber": null
    },
    "ResponseMetadata": {
      "RequestId": "a63ca446-743e-520b-b689-fbc6dcd2bc94"
    }
  }
}

And sure enough I got my email with the same text.

We are now ready to have CockroachDB send data to our endpoint.

Create test table and configure the changefeed

Create a simple events table such as

CREATE TABLE events (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    payload JSONB NULL,
    ts TIMESTAMPTZ NULL DEFAULT now():::TIMESTAMPTZ,
    CONSTRAINT pk PRIMARY KEY (id ASC)
);

Create the changefeed on the newly created events table.
You will need to update the endpoint hostname with yours.

-- enable rangefeed
SET CLUSTER SETTING kv.rangefeed.enabled = true;

CREATE CHANGEFEED FOR TABLE events INTO 'webhook-https://72eebc6w0k.execute-api.ca-central-1.amazonaws.com/DEV/events?insecure_tls_skip_verify=true'
WITH updated, resolved='20s', diff, schema_change_policy=backfill, initial_scan=no, 
    webhook_sink_config = '{ "Flush": {"Messages": 100, "Frequency": "5s"}, "Retry": { "Max": 4, "Backoff": "10s"} }'
;
--         job_id
-- -----------------------
--   1010627173653250049

Check that the changefeed is running and posting a resolved timestamp

select running_status from [show changefeed jobs where job_id 1010627173653250049;
--                running_status
-- --------------------------------------------
--   running: resolved=1728489668.321914000,0

Good, it's running so we're finally ready to test by inserting a row into the table

insert into events (payload) values ('{"my_key" : "my_value"}');

Check your inbox:

Generate multiple, large, sorted CSV files with pseudo-random data

Fabio Ghirardello — Fri, 16 Feb 2024 16:00:59 +0000

For this exercise, I was tasked to create a 500 million rows dataset for an IMPORT INTO speed test.
Details about the IMPORT INTO command can be found here.
The requirement was to create 100 sorted CSV files, each containing 5 million rows, and save them to an S3 bucket.
The data should also be sorted across all files.

In this blog, I show how to use pgworkload to create such pseudo-random dataset.

Setup pgworkload and aws-cli

Provision a large machine with plenty of RAM.
The AWS instance type r7i.8xlarge, which sports 32 vCPUs and 256GB RAM, is a good candidate.
Make sure to attach an IAM Role with permissions to write to your S3 bucket.

Once ready, ssh into the box and install the tools

sudo apt update
sudo apt install -y python3-pip unzip

pip install -U pip
pip install pgworkload

Logout and log back in so that pgworkload is in the PATH...

Now install AWS CLI, here the official docs.

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install

Confirm it's working

$ aws --version
aws-cli/2.15.17 Python/3.11.6 Linux/5.15.0-1052-aws exe/x86_64.ubuntu.20 prompt/off

# confirm you're authenticated..
$ aws s3 ls workshop-ca
                          PRE fab/

# put some files in the bucket
$ aws s3 cp s.sql 's3://workshop-ca/fab/'
upload: ./s.sql to s3://workshop-ca/fab/s.sql                  

# check the value is there
$ aws s3 ls workshop-ca/fab/
                           PRE 2024/
                           PRE metadata/
2024-02-02 19:03:10       3585 s.sql

Good, we're all set!

Generate data

The data that needs to be created must match the schema of the table into which it will be imported.

The DDL of the schema is in file s.sql.

-- file s.sql
CREATE TABLE accounts (
    acc_no STRING(20) NOT NULL,
    pos_dt DATE NOT NULL,
    ent_id DECIMAL(25) NOT NULL,
    col_00 STRING(35) NOT NULL,
    col_01 STRING(16) NULL,
    col_02 STRING(9) NOT NULL,
    col_03 STRING(30) NOT NULL,
    col_04 DECIMAL(22,6) NOT NULL DEFAULT 0:::DECIMAL,
    col_05 DECIMAL(22,6) NOT NULL DEFAULT 0:::DECIMAL,
    col_06 STRING(3) NOT NULL,
    col_07 DECIMAL(22,8) NULL,
    col_08 DECIMAL(20,3) NULL,
    col_09 DECIMAL(20,4) NULL,
    col_10 DECIMAL(20,3) NULL,
    col_11 DECIMAL(20,5) NULL,
    col_12 DECIMAL(20,5) NULL DEFAULT 0:::DECIMAL,
    col_13 DECIMAL(20,5) NULL DEFAULT 0:::DECIMAL,
    col_14 DECIMAL(20,5) NULL,
    col_15 DECIMAL(20,3) NULL,
    col_16 DECIMAL(20,3) NULL DEFAULT 0:::DECIMAL,
    col_17 DECIMAL(21,8) NULL,
    col_18 DECIMAL(21,8) NULL DEFAULT 0:::DECIMAL,
    col_19 DECIMAL(21,2) NULL DEFAULT 0:::DECIMAL,
    col_20 DECIMAL(21,2) NULL,
    col_21 DECIMAL(21,2) NULL,
    col_22 DECIMAL(21,2) NULL DEFAULT 0:::DECIMAL,
    col_23 STRING(1) NOT NULL,
    col_24 STRING(3) NULL,
    col_25 DECIMAL(22,9) NULL,
    col_26 DECIMAL(21,8) NULL,
    col_27 DECIMAL(21,8) NULL DEFAULT 0:::DECIMAL,
    col_28 DECIMAL(22,9) NULL,
    col_29 DECIMAL(20,5) NULL,
    col_30 DECIMAL(20,5) NULL,
    col_31 DECIMAL(20,5) NULL,
    col_32 DECIMAL(20,5) NULL,
    col_33 DECIMAL(20,5) NULL,
    col_34 DECIMAL(20,5) NULL,
    col_35 DECIMAL(20,2) NULL,
    col_36 DECIMAL(20,2) NULL,
    col_37 DECIMAL(20,5) NULL,
    col_38 DATE NULL,
    col_39 DATE NULL,
    col_40 DATE NULL,
    col_41 STRING(25) NULL,
    col_42 DECIMAL(21,6) NULL,
    col_43 DECIMAL(21,6) NULL,
    col_44 DECIMAL(21,6) NULL,
    CONSTRAINT pk PRIMARY KEY (acc_no ASC, pos_dt ASC, ent_id ASC)
);

Given the schema, pgworkload can generate an intermediate representation of what needs to be generated - a definition file - in YAML syntax.

pgworkload util yaml -i s.sql

The result is a file called, by default, s.yaml, below a snippet of the first few lines

# file s.yaml
accounts:
- count: 1000
  sort-by: []
  columns:
    acc_no:
      type: string
      args:
        min: 10
        max: 30
        seed: 0.7225861820526325
        null_pct: 0.0
        array: 0
    pos_dt:
      type: date
      args:
        start: '2022-01-01'
        end: '2022-12-31'
        format: '%Y-%m-%d'
        seed: 0.24769809060740589
        null_pct: 0.0
        array: 0
    ent_id:
      type: float
      args:
        max: 10000
        round: 2
        seed: 0.028215986930010706
        null_pct: 0.0
        array: 0
    col_00:
      type: string
      args:
        min: 10
        max: 30
        seed: 0.8785098436269427
        null_pct: 0.0
        array: 0
    col_01:
      type: string
      args:
        min: 10
        max: 30
        seed: 0.8561702097239098
        null_pct: 0.0
        array: 0
[...]

This is just a template, we need to configure it as per our needs:

generate 500,000,000 rows
ensure dataset is sorted as per Primary Key
the 2nd column must always be the same date.

Here is therefore the updated head of the file

accounts:
- count: 500000000
  sort-by: ["acc_no", "pos_dt", "ent_id"]
  columns:
    acc_no:
      type: string
      args:
        min: 10
        max: 30
        seed: 0.7225861820526325
        null_pct: 0.0
        array: 0
    pos_dt:
      type: costant
      args:
        value: "2024-02-01"

At this point, we're ready to generate the data.

# check the options available for the `util csv` command
$ pgworkload util csv --help


╭─ Options ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│ *  --input         -i      FILE       Filepath to the YAML data generation file. [default: None] [required]                                  │
│    --output        -o      DIRECTORY  Output directory for the CSV files. Defaults to <input-basename>.                                      │
│    --procs         -x      INTEGER    Number of processes to spawn. Defaults to <system-cpu-count>.                                          │
│    --csv-max-rows          INTEGER    Max count of rows per resulting CSV file. [default: 100000]                                            │
│    --hostname      -n      INTEGER    The hostname of the http server that serves the CSV files.                                             │
│    --port          -p      INTEGER    The port of the http server that servers the CSV files. [default: 3000]                                │
│    --table-name    -t      TEXT       The table name used in the import statement. [default: table_name]                                     │
│    --compression   -c      TEXT       The compression format. [default: None]                                                                │
│    --delimiter     -d      TEXT       The delimeter char to use for the CSV files. Defaults to "tab".                                        │
│    --help                             Show this message and exit.                                                                            │
╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯

Here, you are interested in setting the --csv-max-rows and -x parameters.

To make sure we end up with 100 files, we will use 10 processes (-x) with each process generating files of size 5,000,000 until the total is 500,000,000.

pgworkload util csv -i s.yaml -d "," --csv-max-rows 5000000 -x 10

This will take time, and memory: monitor memory utilization using top or free.
Once completed, you have 100 CSV files in a s directory.

$ ls -A1 s/
accounts.0_0_0.csv
accounts.0_0_1.csv
accounts.0_0_2.csv
accounts.0_0_3.csv
accounts.0_0_4.csv
accounts.0_0_5.csv
accounts.0_0_6.csv
accounts.0_0_7.csv
accounts.0_0_8.csv
accounts.0_0_9.csv
accounts.0_1_0.csv
accounts.0_1_1.csv
[...]
accounts.0_9_8.csv
accounts.0_9_9.csv

Inspecting one file, we see it's sorted

$ head s/accounts.0_0_0.csv
abcdgzrjyphtemsbcjvt,2024-02-01,4314.45,jkiuotkttqwjnjnbxzbxhgsyke,uxejzkiirmitunpzybjnakoic,ovvqhgmsbwoajhwmiyhnugj,...
aefkzjdckjylb,2024-02-01,9375.53,bswvhyjkodukhwpcxf,uevjmwqhdfaobtlf,oahiaiztayyzftmfkyuez,qtxhjuwpfalfzaeoiiahuoxamns,...
aerkycbddriqtygvilb,2024-02-01,6150.55,mprfweeqoe,nvddlibqqzncrwdnffm,phcnnzvxrauxllj,vjnabkrzgiimmt,...
agjiqkisyshjeorqna,2024-02-01,9901.21,fipnlqezgzzdfreg,yokerzbkxcrzfdeckjkk,guaeeecdqgbbwtnzleopfznzcuv,lqglvuyetypvnovdbflbnodozfebz,...
atpkoobmhvhhxuqxceurv,2024-02-01,2455.17,cwmkzijlrqhtdcx,jtbelvfoajfdagwigpevnmameq,uedouumekwxagdgwbtivewaq,uytgiqpewfexlqkmbelpik,...

Now you have the data, sorted by file, but not across all files.
That is, each file is sorted as per PK, but across all the files the data is not yet sorted.
Currently, pgworkload doesn't have the capability to do so, so we have to develop our own sorted-merge script.

Note You can't possibly be thinking to load everything into memory, sort, and save.
That's too big to fit into a single machine, no matter how big it is.
Instead, you must read in chunks and write in chunks, so that it can scale.
Below script will work no matter how many files you have or how large they are.

Here's my quick and dirty Python script.

UPDATE: as of version 0.1.8, the sort merge functionality has been added to pgworkload, check with pgworkload util merge --help.

# file: sort_merge.py
from io import TextIOWrapper
import sys

# input CSV files - it assumes files are already sorted
CSVs = sys.argv[1:]

CSV_MAX_ROWS = 5000000
COUNTER = 0
C = 0

source: dict[int, list] = {}
file_handlers: dict[int, TextIOWrapper] = {}


def initial_fill(csv: str, idx: int):
    """
    opens the CSV file, saves the file handler,
    read few lines into source list for the index.
    """
    f = open(csv, "r")
    file_handlers[idx] = f
    while len(source[idx]) < 5:
        line = f.readline()
        if line != "":
            source[idx].append(line)
        else:
            # reached end of file
            print(f"initial_fill: CSV file '{csv}' at source index {idx} reached EOF.")
            f.close()
            break


def replenish_source_list(idx: int):
    """
    Refills the source list with a new value from the source file
    """
    try:
        f = file_handlers.get(idx, None)
        if not f:
            return
        line = f.readline()
        if line != "":
            source[idx].append(line)
        else:
            # reached end of file
            print(f"index {idx} reached EOF.")
            f.close()
            del file_handlers[idx]
    except Exception as e:
        print("Excepton in replenish_queue: ", e)


def write_to_csv(v):
    global C
    global output
    global COUNTER
    if C >= CSV_MAX_ROWS:
        output.close()
        COUNTER += 1
        C = 0
        output = open(f"out_{str.zfill(str(COUNTER), 3)}.csv", "+w")

    output.write(v)
    C += 1    

# init the source dict by opening each CSV file
# and only reading few lines.
for idx, csv in enumerate(CSVs):
    source[idx] = []

    initial_fill(csv, idx)

# the source dict now has a key for every file and a list of the first values read

l = []
# pop the first value in each source to a list `l`
# `l` will have the first values of all source CSV files
for k, v in source.items():
    try:
        l.append((v.pop(0), k))
    except IndexError as e:
        pass

first_k = None
first_v = None
output = open(f"out_{str.zfill(str(COUNTER), 3)}.csv", "+w")

# sort list `l`
# pop the first value (the smallest) in `first_v`
# make a note of the source of that value in `first_k`
# replenish the corrisponding source
while True:
    if first_k is not None:
        try:
            replenish_source_list(first_k)
            l.append((source[first_k].pop(0), first_k))

        except IndexError as e:
            # the source list is empty
            print(f"source list {first_k} is now empty")
            first_k = None

    if l:
        l.sort(key=lambda x: x[0])
        try:
            first_v, first_k = l.pop(0)
            write_to_csv(first_v)
        except IndexError as e:
            print("Exception in main: ", e)
            output.close()
    else:
        break


output.close()

print("\ndone!")

Run it

$ python3 sort_merge.py s/*
index 58 reached EOF.
index 82 reached EOF.
[...]
source list 26 is now empty
source list 69 is now empty

done!

Inspect the new files, also 100 in total

$ ls -A1 out_*
out_000.csv
out_001.csv
out_002.csv
[...]
out_098.csv
out_099.csv

You should now see that the data is now sorted also across files, too.

$ head out_000.csv 
aabrwawoedcqnosvgzcvf,2024-02-01,5285.54,...
aasobyznvehzvrppwijpbbxfrjzdj,2024-02-01,7942.57,..
abcppzyblqnksovdnf,2024-02-01,7577.34,...
abjfxjatqangpalindkcdzsmzbasfx,2024-02-01,9831.37,...
aepkvifbrl,2024-02-01,1239.02,...

$ head out_099.csv 
zxwcmhfnwuqarb,2024-02-01,8477.3,...
zyjucqqytplxf,2024-02-01,5049.06,...
zyvwzspgaxzcymlvo,2024-02-01,5590.13,...
zzmrrnytooz,2024-02-01,7936.68,...
zzqpnbksbdheo,2024-02-01,7950.73,...

You can now upload those files to your S3 bucket using the aws cli

aws s3 cp s/ 's3://workshop-ca/sorted_across_files/' --recursive

At this point, you can safely terminate the AWS instance.

CockroachDB SSO login to the SQL prompt via JWT

Fabio Ghirardello — Wed, 30 Aug 2023 17:38:43 +0000

In this post, I walk through how to configure CockroachDB to allow users to login to the SQL prompt using their SSO login.

Instructions on how to configure SSO access for the DBConsole are available here.

The users will request a JSON Web Token (JWT) from their identity provider (IdP) and use that token as the temporary password.

There are many IdPs, and in this example I am using Okta. The workflow is similar for all IdPs, so while the details for how to configure the IdP varies from IdP to IdP, from the point of view of CockroachDB this is entirely transparent.

Prerequisite: IdP (Okta) setup

To generate a JWT, we first need to setup an IdP. Okta offers a developer account, so I signed up and quickly created an App integration.

Here is a quick walk-through:

Create a few users

Navigate to Directory > People and click Add Person.

Here's my list: I set password Mazinga123 for all these users.
Later, we'll use the password to request a JWT.

Create a Group

Go to Directory > Groups, click Add Group and call it "Bankers".

Then, select that group and assign users to it:

Create an App Integration

Navigate to Applications > Applications and click Create App Integration.

Select the "OIDC" as the protocol, and "Native application" as the type.

I call the integration "BankApp". Ensure "Resource Owner Password" is selected.

Under "Assignments", add the "Bankers" group we previously created.

Click Save.
Now, create a Client Secret.

Make sure you jot down your credentials. In my case:

client_id=0oab1arbbieTSsFVJ5d7
client_secret=WbAWHW9UeURzXez99PMGWvuVp4KiGDXTNQrDajvszN2iQ3YyMjGyx0sWts1sM36J

Configure Token fields

Next, we need to edit the Okta JWT by adding the user.login field.
This is the "Username" field in the Person profile.

Go to Security > API, select the "default" Authorization Server, and select the "Claims" tab.
From here, add a claim:

You can actually preview what the token will look like.
Check the bottom right hand corner, in the Payload section, the "login" field is now present.

Finally, jot down the Issuer URI, we will need this URL later to find the .well-known details. Ours is https://dev-85651931.okta.com/oauth2/default

We are now ready to configure CockroachDB against our Okta BankApp integration.

CockroachDB setup

Start a CockroachDB demo cluster, as it automatically loads a temporary license.

$ cockroach demo --no-example-database
#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB cluster of 1 node.
#
# This demo session will send telemetry to Cockroach Labs in the background.
# To disable this behavior, set the environment variable
# COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true.
#
# Reminder: your changes to data stored in the demo session will not be saved!
#
# If you wish to access this demo cluster using another tool, you will need
# the following details:
#
#   - Connection parameters:
#      (webui)    http://127.0.0.1:8080/demologin?password=demo9642&username=demo
#      (cli)      cockroach sql --certs-dir=/Users/fabio/.cockroach-demo -u demo -d defaultdb
#      (sql)      postgresql://demo:demo9642@127.0.0.1:26257/defaultdb?sslmode=require&sslrootcert=%2FUsers%2Ffabio%2F.cockroach-demo%2Fca.crt
#   
#   - Username: "demo", password: "demo9642"
#   - Directory with certificate files (for certain SQL drivers/tools): /Users/fabio/.cockroach-demo
#
# You can enter \info to print these details again.
#
# Server version: CockroachDB CCL v23.1.4 (x86_64-apple-darwin19, built 2023/06/16 20:54:39, go1.19.4) (same version as client)
# Cluster ID: f72ea38c-c5b3-437e-bd03-2391e6b5d150
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#
demo@127.0.0.1:26257/defaultdb>

The relevant documentation to configure CockroachDB with the IdP for SSO login is here.

First, open the .well-known page for our IdP.
In our case, this is URL https://dev-85651931.okta.com/oauth2/default/.well-known/openid-configuration.

Here, we can take the server.jwt_authentication.issuers setting, and by navigating to the URL indicated by the jwkt_uri field, also the server.jwt_authentication.jwks.

-- trivial
SET CLUSTER SETTING server.jwt_authentication.enabled = true;

-- taken from field "issuer" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.issuers = 'https://dev-85651931.okta.com/oauth2/default';

-- taken by navigating to the URL in field "jwks_uri" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.jwks = '{"keys":[{"kty":"RSA","alg":"RS256","kid":"6jnGo_xZFr13SVCKgH5Tl8RN9cXqybYBQEo2Vf7Wagw","use":"sig","e":"AQAB","n":"mF3tIoT8h_2lpvYSCE_YopPW9Yp9fx4ddDNYFhFhmcqeMKMLl_JIXjnfs2EMB9zlwBm0hHkphGGWPfsy8wLZob2bVwVj8-3yPK3qRIt7ouW8OhNGn8tmQHB_fSDugPp-A_MuedNAkgeFB4zEcEJbVHjykC6cEYbyyPmhD0VJf0q3ifkg2Fxm8075QcV0iIcl46RHxTToTDeW44gPyMqzLrVq2UxOw-yn_I-o_M065hiONMthdiIR6KvjhO-fqtBbD37BH6XehSe2rtAxUpuLvGnoa25Gl7GkhD7_f6qIa3tmoYMPVvbxQSU8Ly2_AGJ7BCqgOMMyE0knsGLUi-dlBw"}]}';

-- this is the client_id
SET CLUSTER SETTING server.jwt_authentication.audience = '["0oab1arbbieTSsFVJ5d7"]';

-- this is the name of the custom field we added to our token
SET CLUSTER SETTING server.jwt_authentication.claim = 'login';

-- this strips the @bank.com
SET CLUSTER SETTING server.identity_map.configuration = 'https://dev-85651931.okta.com/oauth2/default /^(\S+)(?:@) \1';

Finally, let's create some users, with the name matching the name part of the email address we saved in Okta.

-- 'mrossi@bank.com' is mapped to user 'mrossi', that is, 
-- we strip the '@bank.com' part
CREATE USER mrossi;
CREATE USER akumar;
CREATE USER yfofana;

Demo

We are now ready to request Okta for a JWT.

We use curl, following the example in the Okta doc.

curl -s -X POST \
  -H "Content-type:application/x-www-form-urlencoded" \
  -d "client_id=0oab1arbbieTSsFVJ5d7&client_secret=WbAWHW9UeURzXez99PMGWvuVp4KiGDXTNQrDajvszN2iQ3YyMjGyx0sWts1sM36J&grant_type=password&username=mrossi@bank.com&password=Mazinga123&scope=openid" \
  "https://dev-85651931.okta.com/oauth2/default/v1/token" | jq

Here's the formatted response. We are only interested in the id_token, so copy that string.

{
  "token_type": "Bearer",
  "expires_in": 3600,
  "access_token": "eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJ2ZXIiOjEsImp0aSI6IkFULlMtT0wzdmo2cl9Kem82RFMyOGcyNDRhUnVOSWFKUDBqRXVHVUlLZkFvajQiLCJpc3MiOiJodHRwczovL2Rldi04NTY1MTkzMS5va3RhLmNvbS9vYXV0aDIvZGVmYXVsdCIsImF1ZCI6ImFwaTovL2RlZmF1bHQiLCJpYXQiOjE2OTM0MTI2MTAsImV4cCI6MTY5MzQxNjIxMCwiY2lkIjoiMG9hYjFhcmJiaWVUU3NGVko1ZDciLCJ1aWQiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInNjcCI6WyJvcGVuaWQiXSwiYXV0aF90aW1lIjoxNjkzNDEyNjEwLCJzdWIiOiJtcm9zc2lAYmFuay5jb20ifQ.LqiPT1lwzTVWJ1yCFUfL7toFANAIcx4v9c6fYl5HTApbq3wmrpRCJQ9Jy-UgH-SHp4DxYV-tt-i1I-l3409_nVPTK751CxqNCySBsUqXiUHvsV7ZfNxkzgw_0BxCatirk32T08oJc0wuARyP9a1Pif_BwzawHP46vdhrikGwRXrrdtjV9yDLvzGwiHlS8IyTz0lRcCwVR0tq02EfEpBxFg992HJl-VZ4NMzFPj-D6LgAdo4vpJnY-fgHP-IZo26ijfSD9mvyB2V5lu4GAmcD9bV7LfSH_CilPrGFh481y2-YrBknMxUT_4tmN3LY6TZeBo3nDt4gqRjxPxhdDgcTHg",
  "scope": "openid",
  "id_token": "eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJzdWIiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInZlciI6MSwiaXNzIjoiaHR0cHM6Ly9kZXYtODU2NTE5MzEub2t0YS5jb20vb2F1dGgyL2RlZmF1bHQiLCJhdWQiOiIwb2FiMWFyYmJpZVRTc0ZWSjVkNyIsImlhdCI6MTY5MzQxMjYxMCwiZXhwIjoxNjkzNDE2MjEwLCJqdGkiOiJJRC41M3FaQ3pRVzZNa3dLZXh0d2FFcG1HWDFyX1k1bGFpNEp3TUJlWGl1c2J3IiwiYW1yIjpbInB3ZCJdLCJpZHAiOiIwMG9iMHUzOTI0QXJ1Q3RLZjVkNyIsImF1dGhfdGltZSI6MTY5MzQxMjYxMCwiYXRfaGFzaCI6IllqTV8xUGFxRHpGU3V6bkJPQ3Y4QVEiLCJsb2dpbiI6Im1yb3NzaUBiYW5rLmNvbSJ9.abhdpxyTW7WnQApWBJmiuZo_ziz7UEoKkHnf4nEaHsnDPzen32KwLWV4fNM4lvGl7YSND6lFErcZ_xLhBuqHutiH888UEyLzbuOSSFch0ie63VS0ElKlF6M1sXq_U8vRKikWpxC0dF3z1VTnKbRcfxN7QB6sxYn7iAUWt2xbEkaRTFO5lL4t5lFx8xUUBqtFFgy6im4w4CUyEvOww0DGwTC9gVifPiTa-fxGfN-OLFDyUZPM8F2JMmaYhpVWy-dplQjrmOv96zJ07GbsutPP5BrSb2JRCrEBZY0nTK8LklcVV4uZUbZJtTRDpHgNlmgzqzwlRWrt4dDvybnRZRq3Xg"
}

For the sake of learning, we can use a tool such as token.dev to decode the JWT.
Note our "login" field in the Payload.

Armed with our JWT, we can now proceed to login.

$ cockroach sql --url "postgresql://mrossi:eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJzdWIiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInZlciI6MSwiaXNzIjoiaHR0cHM6Ly9kZXYtODU2NTE5MzEub2t0YS5jb20vb2F1dGgyL2RlZmF1bHQiLCJhdWQiOiIwb2FiMWFyYmJpZVRTc0ZWSjVkNyIsImlhdCI6MTY5MzQxMjYxMCwiZXhwIjoxNjkzNDE2MjEwLCJqdGkiOiJJRC41M3FaQ3pRVzZNa3dLZXh0d2FFcG1HWDFyX1k1bGFpNEp3TUJlWGl1c2J3IiwiYW1yIjpbInB3ZCJdLCJpZHAiOiIwMG9iMHUzOTI0QXJ1Q3RLZjVkNyIsImF1dGhfdGltZSI6MTY5MzQxMjYxMCwiYXRfaGFzaCI6IllqTV8xUGFxRHpGU3V6bkJPQ3Y4QVEiLCJsb2dpbiI6Im1yb3NzaUBiYW5rLmNvbSJ9.abhdpxyTW7WnQApWBJmiuZo_ziz7UEoKkHnf4nEaHsnDPzen32KwLWV4fNM4lvGl7YSND6lFErcZ_xLhBuqHutiH888UEyLzbuOSSFch0ie63VS0ElKlF6M1sXq_U8vRKikWpxC0dF3z1VTnKbRcfxN7QB6sxYn7iAUWt2xbEkaRTFO5lL4t5lFx8xUUBqtFFgy6im4w4CUyEvOww0DGwTC9gVifPiTa-fxGfN-OLFDyUZPM8F2JMmaYhpVWy-dplQjrmOv96zJ07GbsutPP5BrSb2JRCrEBZY0nTK8LklcVV4uZUbZJtTRDpHgNlmgzqzwlRWrt4dDvybnRZRq3Xg@localhost:26257/defaultdb?options=--crdb:jwt_auth_enabled=true&sslmode=require" 
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v23.1.4 (x86_64-apple-darwin19, built 2023/06/16 20:54:39, go1.19.4) (same version as client)
# Cluster ID: f72ea38c-c5b3-437e-bd03-2391e6b5d150
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#
mrossi@localhost:26257/defaultdb>

Success! Let's try to use that token with another user, akumar:

$ cockroach sql --url "postgresql://akumar:eyJraWQiOiI2am5Hb194WkZyMTNTVkNLZ0g1VGw4Uk45Y1hxeWJZQlFFbzJWZjdXYWd3IiwiYWxnIjoiUlMyNTYifQ.eyJzdWIiOiIwMHViMWRuNXFjN0pBaFpHOTVkNyIsInZlciI6MSwiaXNzIjoiaHR0cHM6Ly9kZXYtODU2NTE5MzEub2t0YS5jb20v
b2F1dGgyL2RlZmF1bHQiLCJhdWQiOiIwb2FiMWFyYmJpZVRTc0ZWSjVkNyIsImlhdCI6MTY5MzQxNjM4NCwiZXhwIjoxNjkzNDE5OTg0LCJqdGkiOiJJRC5feXhFOEd4MG9oZVZvQ1RYRXkwZFNwc0JrWlFZeHFoam9sY0JZZVBLTXFJIiwiYW1yIjpbInB3ZCJdLCJpZHAiOiIwMG9iMHUzOTI0QXJ1Q3RLZjVkNyIsImF1dGhfdGltZSI6M
TY5MzQxNjM4NCwiYXRfaGFzaCI6IlljNF9SUF9UWUxNM3B1RjdjSjlCYmciLCJsb2dpbiI6Im1yb3NzaUBiYW5rLmNvbSJ9.GrviZKNF4HezfhnmpvpZFZgKrYmivMknsZGFjD1eXSx2IoiBaG-t-q8CB9oVL48e3ZQXeXqXIl9PvL84a4ed8VCR1wJWH53xKTsAMGmx3dAJpxdO7PhNK1P0Eg2eY4p76mJ2qHbUGb201As_oC-OQAGkuGEZq
lBHV634Mv3zcdKjFFuUYaFkadMXqvIZomSoWUL5jDM8fqKtOUNoONHilthnOgZ3RxgfzGyeijdFS1_ODscbjbIzamKw6C7u698KG7eohVJMP2dIJr4jo33jpcwztRRQfU5ElcuINqLBw8Gv-_A1MlM4WrPHzsYN_OWgaRM_wd8UzOiyuMLFKirHFA@localhost:26257/defaultdb?options=--crdb:jwt_auth_enabled=true&sslm
ode=require" 
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: JWT authentication: invalid principal
SQLSTATE: 28000
DETAIL: token issued for [mrossi@bank.com] and login was for akumar
Failed running "sql"

Failed as expected!

Your IdP administrator will be responsible for instructing on how to request a JWT, and what claim to use, so do not worry if all this is overwhelming.

From the CockroachDB side, what matters is that our SQL username matches with what the IdP has stored for a particular user: in this example, we used the email address, but it can be any unique ID that your company has adopted.

References

Kafka 2 CockroachDB via JDBC Sink Connector Blueprint

Fabio Ghirardello — Tue, 14 Mar 2023 18:08:13 +0000

This is a short write up on the exercise of inserting batches of Kafka Records into CockroachDB using Confluent's JDBC Sink Connector, a 'no-code' solution for data ingestion.
An example on how to setup the pipeline locally using Docker is documented in this blog post.

The GitHub repository referenced in this write up is fabiog1901/kafka2cockroachdb.

The pipeline is very simple:

A python script generates data that gets ingested into a Kafka Topic within the Kafka Broker.
The Topic is partitioned.
Exactly 1 Kafka Connect task is started for each partition.
The Task reads from the topic partition and inserts into CockroachDB by making a conneciton through the Load Balancer.

Test Infrastructure and Components Setup

Infrastructure was deployed using Ansible on Google Cloud VMs:

Single node Confluent Platform (Kafka broker and Kafka Connect) on n2-standard-16 instance type.
3 nodes CockroachDB cluster using the n2d-standard-8|16|32 instance types. Each VM was provisioned with 1 x 2.5TB Persistent SSD (pd-ssd) volume.
Single node Load Balancer instance running HAProxy.

The main Kafka backend was installed using the Ansible Playbooks for Confluent Platform.

The CockroachDB cluster and the HAProxy load balancer instance were installed using the fabiog1901.cockroachdb Ansible Collection.

The test was run executing convenience Python script play.py.
The script coordinates the execution of 4 Ansible Playbooks:

kafka.yaml - Provision and prepare the Kafka cluster.
cockroachdb.yaml - Provision and prepare the CockroachDB cluster.
kafka-producer.yaml - Prepare Kafka broker and start the Kafka producer.
kafka-consumer.yaml - Run the Kafka consumer i.e. Kafka Connect.

Kafka Producer

To load data into the Kafka Topic we used a simple generator written in Python, libs/gen.py.
The generator leverages the confluent-kafka package for publishing Avro records of about 60 fields.
The generator is started and let run for 20 minutes before any consumer process is started, so that the Topic is always well filled with records.

Kafka Consumer

Kafka Connect was configured with the JDBC Sink Connector, however, a custom kafka-connect-jdbc-10.6.1.jar file was used: the only change made to the original version was to set autocommit=true for the SQL transactions, here.
This change is important as it allows statements to be executed implicitly, saving therefore a roundtrip for the commit message.
The Jar file can be found in the libs directory.

Similarly, a custom PostgreSQL JDBC Driver was used, allowing for batch statements to be larger than 128 records, see here.
The result is we can now test with multi-value INSERT statements that have more than 128 values.
The custom driver Jar file is also in the libs directory.

CockroachDB Cluster

The 3 nodes CockroachDB cluster runs version 22.2.5|6.
The database was seeded with approximately 0.5TB of data.
The data was generated externally and imported from Google Cloud Storage directly into the database.
CockroachDB stored the data with a Replication Factor of 3, the default.
This implies that every single node has a full copy of the entire dataset.
See custom settings and DDL statements executed in file libs/s.sql.

Test Description

We tested with 3 instance types, multiple Kafka topic partitions and batch sizes.

Script play.py was used to run the tests.
In short, for each instance type, we cycled through all partitions, and for each partition, we cycled through all batch sizes.

On each partition cycle, the JDBC Sink Connector was created with tasks.max set to the same number as the partition count.
Here, a task is a process that creates a database connection, consumes records from the assigned topic partition, prepares the INSERT statement and finally sends it to CockroachDB for execution.

On each batch size cycle, the JDBC Sink Connector was created with batch.size and consumer.override.max.poll.records set to the current batch_size value.

Results of transaction latency, throughput (TPS) and CPU util are shown below for each of the test cases.
per_stmt_latency_ms is a computed value, derived by dividing txn_latency_ms by batch_size.

Using n2d-standard-8

total_vcpus	k_partitions	batch_size	tps	cpu_util_pct	txn_latency_ms	per_stmt_latency_ms
24	18	1	3160	40	3.0	3.00
24	18	8	9984	70	10.7	1.34
24	18	16	12064	70	19.3	1.21
24	18	32	14457	75	32.7	1.02
24	18	64	15920	75	59.9	0.94
24	18	128	17820	80	105.3	0.82
24	36	1	5839	60	3.3	3.30
24	36	8	10653	80	22.4	2.80
24	36	16	11854	80	41.7	2.61
24	36	32	13923	80	71.4	2.23
24	36	64	15765	85	126.9	1.98
24	36	128	17684	85	219.0	1.71

Overview Dashboard -> SQL Statements

Hardware Dashboard -> CPU Utilization

SQL Activity --> Transaction page - data for the 18 partitions test

Using n2d-standard-16

total_vcpus	k_partitions	batch_size	tps	cpu_util_pct	txn_latency_ms	per_stmt_latency_ms
48	18	1	2955	20	3.3	3.30
48	18	16	12104	65	19.0	1.19
48	18	32	13824	65	35.0	1.09
48	18	64	16187	70	61.0	0.95
48	18	128	18558	75	105.0	0.82
48	36	1	5846	35	3.3	3.30
48	36	16	14061	70	35.0	2.19
48	36	32	16187	75	63.0	1.97
48	36	64	18700	75	109.0	1.70
48	36	128	21231	80	188.0	1.47
48	54	1	8070	50	3.8	3.80
48	54	16	14788	75	52.0	3.25
48	54	32	16641	75	94.0	2.94
48	54	64	20007	80	154.0	2.41
48	54	128	20485	80	298.0	2.33
48	72	1	10237	60	4.1	4.10
48	72	16	15456	75	67.0	4.19
48	72	32	18817	80	111.0	3.47
48	72	64	19569	80	212.0	3.31
48	72	128	18393	80	441.0	3.45
48	90	1	11153	65	5.0	5.00
48	90	16	15526	75	85.0	5.31
48	90	32	18632	75	141.0	4.41
48	90	64	18488	80	277.0	4.33
48	90	128	18043	80	569.0	4.45

Overview Dashboard -> SQL Statements

Hardware Dashboard -> CPU Utilization

Hardware Dashboard -> Disk Write MiB/s

Replication Dashboard -> Leaseholders per Node

Using n2d-standard-32

total_vcpus	k_partitions	batch_size	tps	cpu_util_pct	txn_latency_ms	per_stmt_latency_ms
96	36	1	8237	20	3.0	3.00
96	36	32	35012	65	27.5	0.86
96	36	64	39455	65	48.7	0.76
96	36	128	42938	70	88.7	0.69
96	36	256	46214	75	153.3	0.60
96	54	1	11559	35	3.2	3.20
96	54	32	34039	70	44.4	1.39
96	54	64	37177	70	93.5	1.46
96	54	128	36003	75	160.8	1.26
96	54	256	37501	80	292.6	1.14
96	72	1	14253	40	3.4	3.40
96	72	32	32578	70	63.3	1.98
96	72	64	32340	75	129.2	2.02
96	72	128	31045	80	260.0	2.03
96	72	256	30034	80	489.4	1.91
96	90	1	16325	50	3.8	3.80
96	90	32	30576	70	86.9	2.72
96	90	64	30277	75	169.2	2.64
96	90	128	29890	80	330.0	2.58
96	90	256	29235	80	668.2	2.61

Hardware Dashboard -> CPU Utilization - Sometimes load is slightly uneven, even if the workload is perfectly distributed

Considerations

It is generally recommended to keep the cluster CPU Utilization at around 50% as to have headroom for sudden spikes, node failures, and background database operations such as backups, CDC feeds, import/export jobs, etc.
Write throughput varies greatly depending on the hardware utilized. See public clouds hardware recommendation for CockroachDB in the Cloud Report.
Transaction latency varies in multi-region clusters, as you can expect transactions have to ensure at least 1 out of region replica has to be kept in sync.
Other factors impacting latency include, but are not limited to: read/write ratio, count of secondary indexes, database topology, client location, record size.

In this project, I have tweaked both the driver and the kafka-connect-jdbc connector. For my next tests, I like to:

Explore best ways to optimize the Kafka Connector, possibly working along with the Confluent engineering team.
Replace the standard JDBC PostgreSQL Driver with the cockroachdb-jdbc driver, kindly developed and maintained by Kai Niemi.

References

Repaving CockroachDB cluster node VMs the easy way

Fabio Ghirardello — Mon, 30 Jan 2023 21:27:26 +0000

In this brief post, I will demonstrate how you can repave/re-image your CockroachDB cluster VMs while still being online and without much data movement and replication. The process is simple and can/should be fully automated.

For each node in the cluster:

Halt the VM - systemd will gracefully stop the CockroachDB node.
Detach all data disks.
Upgrade/replace/patch the VM image with the new image (or use a new VM pre-configured with CockroachDB).
Attach disks to re-imaged/repaved VM (or the new VM).
Start VM and CockroachDB process.
Wait few minutes for cluster to adjust.

Prepare for Node Shutdown

Before attempting any repave or restart procedure, make sure you have configured your cluster, load balancer and application connection pool software for a graceful shutdown of the CockroachDB node.

This involves following closely the draining procedure described in the official docs for the Node Shutdown.

The documentation page describes in great details the sequence of events that occur whens a node is sent the SIGTERM signal aka the "graceful shutdown" signal, which is what happens when systemd tries to stop all the running services as a result of a system halt request, initiated by a sysadmin during a patch, repave or any maintenance task. I wrote another blog with a hands-on example, see CockroachDB Graceful Shutdwons.

Draining is the CockroachDB term for bringing a node to a state that the process can be terminated without impact to clients or other nodes. There are 5 phases – the sequence is quite complex and it’s important that we understand how to mitigate risks to the app at every phase.

The 1st phase starts with the node setting its health-check status to HTTP503 (Service Unavailable). This is to inform the load balancer that the node is no longer healthy and thus any new connection request should be routed elsewhere. The problem is, the LB will take some time to declare the server as unhealthy: usually, the LB will check the health-check endpoint every few seconds - the interval - and declare the server as unhealthy only after a few failed probes - the threshold - . For example, if interval=5s and threshold=3, the load balancer can take up to 5*4=20s to set the server as unavailable.

By setting cluster setting server.shutdown.initial_wait to a value larger than the LB timeout, we are ensured that phase 2 will start only after the LB has declared the server dead.
For example, we could set the initial_wait to 21s. With this value, we know for sure that the LB will declare the node unavailable way before the node starts the 2nd phase of the draining activity. This avoids the unpleasant situation that the LB routes a new connection request to the node only to be denied, bubbling up the error to the application.

But what happens to already established connections? We have to wait for those to close by themselves, usually this happens because the connection pool, such as HikariCP, has a setting maxLifetime that dictates how long a connection can live. In CockroachDB, you configure cluster setting server.shutdown.connections.timeout to a value few seconds larger than your CP’s "max-lifetime" property. This means that the CockroachDB node will wait those many seconds before forcefully close any open connection session. But by that time, your Connection Pool will have retired/closed all existing connections anyway. For example, HikariCP's maxLifetime's default is 30minutes. That's a lot of time to wait for, so a more sensible value is, say, 5 minutes. Accordingly, set the server.shutdown.connections.timeout to 360s. Only after 360s have passed will CockroachDB start the 3rd draining phase.

And what if there’s a connection on which there’s a long running transaction going on, or the node itself is involved in a transaction started on another node and that transaction is still executing? Again, CockroachDB will pause its draining activity and will wait for all transactions to complete, or when the timeout set by cluster setting server.shutdown.transactions.timeout has been reached - naturally, you want to set a timeout to avoid runaway transactions, such as full scans of very large tables. Only after all transactions have completed, or the transactions have been interrupted for reaching the transaction timeout, will the 5th and final phase begin.

By coordinating the node shutdown procedure with the LB and the CP, you are ensured that your app will not run into any service disruption. It is also very important that you configure your systemd service property TimeoutStopSec accordingly - you don't want systemd to send a SIGKILL before the draining process is complete and, as we saw, it can take several minutes.

For example, if the total of the 3 above mentioned cluster settings is 7 minutes, and you have noticed the node usually takes 3 minutes to shed all its LeaseHolders to other nodes, set TimeoutStopSec=720 to give good 12 minutes for a graceful shutdown. Monitor the shutdown procedure to ensure the node shutdown always completes gracefully.

Demo

I created a basic 3 nodes cluster on AWS with 1 attached EBS volume each. I started some sample load, and let it run for about 10 minutes.

Here is the view of one of these nodes, see the mounted disk at /dev/sdf? That's an EBS volume.

I then reached out to the AWS Console and stopped a node, n3. CockroachDB complains about a suspect/dead node, and shows that some ranges are under-replicated.

Once the VM was stopped, I detached the EBS volume (see green banner)...

...and attached it to a new and up and running VM.

I then ssh'ed into the new VM, and started the CockroachDB process. If you are repaving into a new VM, make sure the TLS certificates for inter-node communication are correctly installed.
Immediately, the node connects to the cluster and complains about under-replicated ranges are gone.

That's it! Confirm under-replicated ranges are gone, then wash, rinse and repeat for every other node!

While, as you can see from the charts, the whole process took about 5 minutes, this can be greatly simplified and improved upon by using a dev-ops automation tool such as Ansible.

Cleaning up the old nodes

Once repaved, we can remove the notion of the old, repaved node n3 from the cluster using the decommission command.

root@ip-10-10-11-95:/home/ubuntu# cockroach node status --certs-dir /var/lib/cockroach/certs/ 
  id |       address       |     sql_address     |  build  |              started_at              |              updated_at              |        locality         | is_available | is_live
-----+---------------------+---------------------+---------+--------------------------------------+--------------------------------------+-------------------------+--------------+----------
   1 | 18.224.59.170:26257 | 18.224.59.170:26257 | v22.2.3 | 2023-01-30 20:03:59.66018 +0000 UTC  | 2023-01-30 20:26:07.183524 +0000 UTC | region=us-east-2,zone=a | true         | true
   2 | 18.119.133.34:26257 | 18.119.133.34:26257 | v22.2.3 | 2023-01-30 20:04:00.13035 +0000 UTC  | 2023-01-30 20:26:07.642953 +0000 UTC | region=us-east-2,zone=a | true         | true
   3 | 18.117.158.82:26257 | 18.117.158.82:26257 | v22.2.3 | 2023-01-30 20:04:00.944523 +0000 UTC | 2023-01-30 20:13:52.332716 +0000 UTC | region=us-east-2,zone=a | false        | false
   4 | 3.141.0.11:26257    | 3.141.0.11:26257    | v22.2.3 | 2023-01-30 20:21:37.594691 +0000 UTC | 2023-01-30 20:26:07.609098 +0000 UTC | region=us-east-2,zone=a | true         | true
(4 rows)
root@ip-10-10-11-95:/home/ubuntu# 
root@ip-10-10-11-95:/home/ubuntu# 
root@ip-10-10-11-95:/home/ubuntu# cockroach node decommission 3 --certs-dir /var/lib/cockroach/certs/ 

  id | is_live | replicas | is_decommissioning |   membership    | is_draining
-----+---------+----------+--------------------+-----------------+--------------
   3 |  false  |        0 |        true        | decommissioning |    true
(1 row)

No more data reported on target nodes. Please verify cluster health before removing the nodes.

The cluster is now updated and as good as new.

References

Ingesting data from Kafka to CockroachDB via Kafka Connect

Fabio Ghirardello — Wed, 21 Dec 2022 19:22:27 +0000

In this brief write-up, I demonstrate how to build a working pipeline to ingest Kafka records into CockroachDB via Kafka Connect's JDBC Sink Connector, locally, using Docker.

This serves as functional testing, that is, for understanding the concepts and all moving parts before moving on to performance testing, on real Production grade clusters.
Stay tuned for a follow-up blog where we will use this content to build and run a performance test.
UPDATE: performance test is here!

Setup

Save below as file kafka2crdb.yaml.

# kafka2crdb.yaml
---
version: '2'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.3.0
    hostname: zookeeper
    container_name: zookeeper
    ports:
      - 2181:2181
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000

  broker:
    image: confluentinc/cp-server:7.3.0
    hostname: broker
    container_name: broker
    depends_on:
      - zookeeper
    ports:
      - 9092:9092
      - 9101:9101
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: 'zookeeper:2181'
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://broker:29092,PLAINTEXT_HOST://localhost:9092
      KAFKA_METRIC_REPORTERS: io.confluent.metrics.reporter.ConfluentMetricsReporter
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
      KAFKA_CONFLUENT_LICENSE_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_CONFLUENT_BALANCER_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_TRANSACTION_STATE_LOG_MIN_ISR: 1
      KAFKA_TRANSACTION_STATE_LOG_REPLICATION_FACTOR: 1
      KAFKA_JMX_PORT: 9101
      KAFKA_JMX_HOSTNAME: localhost
      KAFKA_CONFLUENT_SCHEMA_REGISTRY_URL: http://schema-registry:8081
      CONFLUENT_METRICS_REPORTER_BOOTSTRAP_SERVERS: broker:29092
      CONFLUENT_METRICS_REPORTER_TOPIC_REPLICAS: 1
      CONFLUENT_METRICS_ENABLE: 'true'
      CONFLUENT_SUPPORT_CUSTOMER_ID: 'anonymous'

  schema-registry:
    image: confluentinc/cp-schema-registry:7.3.0
    hostname: schema-registry
    container_name: schema-registry
    depends_on:
      - broker
    ports:
      - 8081:8081
    environment:
      SCHEMA_REGISTRY_HOST_NAME: schema-registry
      SCHEMA_REGISTRY_KAFKASTORE_BOOTSTRAP_SERVERS: 'broker:29092'
      SCHEMA_REGISTRY_LISTENERS: http://0.0.0.0:8081

  kafka-connect:
    image: cnfldemos/cp-server-connect-datagen:0.6.0-7.3.0
    hostname: connect
    container_name: connect
    depends_on:
      - broker
      - schema-registry
    ports:
      - 8083:8083
    environment:
      CONNECT_BOOTSTRAP_SERVERS: 'broker:29092'
      CONNECT_REST_ADVERTISED_HOST_NAME: connect
      CONNECT_GROUP_ID: compose-connect-group
      CONNECT_CONFIG_STORAGE_TOPIC: docker-connect-configs
      CONNECT_CONFIG_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_OFFSET_FLUSH_INTERVAL_MS: 10000
      CONNECT_OFFSET_STORAGE_TOPIC: docker-connect-offsets
      CONNECT_OFFSET_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_STATUS_STORAGE_TOPIC: docker-connect-status
      CONNECT_STATUS_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_KEY_CONVERTER: org.apache.kafka.connect.storage.StringConverter
      CONNECT_VALUE_CONVERTER: io.confluent.connect.avro.AvroConverter
      CONNECT_VALUE_CONVERTER_SCHEMA_REGISTRY_URL: http://schema-registry:8081
      CLASSPATH: /usr/share/java/monitoring-interceptors/monitoring-interceptors-7.3.0.jar
      CONNECT_PRODUCER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringProducerInterceptor"
      CONNECT_CONSUMER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringConsumerInterceptor"
      CONNECT_PLUGIN_PATH: "/usr/share/java,/usr/share/confluent-hub-components"
      CONNECT_LOG4J_LOGGERS: org.apache.zookeeper=ERROR,org.I0Itec.zkclient=ERROR,org.reflections=ERROR
    command: 
      - bash 
      - -c 
      - |
        # Installing connector plugin
        confluent-hub install --no-prompt confluentinc/kafka-connect-jdbc:latest
        # Downloading newer Postgres JDBC driver
        cd /usr/share/confluent-hub-components/confluentinc-kafka-connect-jdbc/lib
        rm -rf postgresql*
        wget https://jdbc.postgresql.org/download/postgresql-42.5.0.jar 
        # Launching Kafka Connect worker
        /etc/confluent/docker/run &
        sleep infinity

  control-center:
    image: confluentinc/cp-enterprise-control-center:7.3.0
    hostname: control-center
    container_name: control-center
    depends_on:
      - broker
      - schema-registry
      - kafka-connect
    ports:
      - 9021:9021
    environment:
      CONTROL_CENTER_BOOTSTRAP_SERVERS: 'broker:29092'
      CONTROL_CENTER_CONNECT_CONNECT-DEFAULT_CLUSTER: 'connect:8083'
      CONTROL_CENTER_KSQL_KSQLDB1_URL: "http://ksqldb-server:8088"
      CONTROL_CENTER_KSQL_KSQLDB1_ADVERTISED_URL: "http://localhost:8088"
      CONTROL_CENTER_SCHEMA_REGISTRY_URL: "http://schema-registry:8081"
      CONTROL_CENTER_REPLICATION_FACTOR: 1
      CONTROL_CENTER_INTERNAL_TOPICS_PARTITIONS: 1
      CONTROL_CENTER_MONITORING_INTERCEPTOR_TOPIC_PARTITIONS: 1
      CONFLUENT_METRICS_TOPIC_REPLICATION: 1
      PORT: 9021

  cockroach:
    image: cockroachdb/cockroach:latest
    container_name: cockroach-1
    command: start-single-node --insecure
    ports:
      - 26257:26257
      - 8080:8080

Notice in the kafka-connect definition how we're installing the latest version of the JDBC Sink Connector and update the JDBC Postgresql driver.

Bring the Docker Compose up.

Note:
You might have to tweak your Docker env to allow for more resources.
I use Docker Desktop for macOS configured with 12 CPUs and 22GB Memory.

docker-compose -f kafka2crdb.yaml up -d

Make sure everything is up and running

$ docker-compose -f kafka2crdb.yaml ps
NAME                COMMAND                  SERVICE             STATUS              PORTS
broker              "/etc/confluent/dock…"   broker              running             0.0.0.0:9092->9092/tcp, 0.0.0.0:9101->9101/tcp
cockroach-1         "/cockroach/cockroac…"   cockroach           running             0.0.0.0:8080->8080/tcp, 0.0.0.0:26257->26257/tcp
connect             "bash -c '# Installi…"   kafka-connect       running             0.0.0.0:8083->8083/tcp, 9092/tcp
control-center      "/etc/confluent/dock…"   control-center      running             0.0.0.0:9021->9021/tcp
schema-registry     "/etc/confluent/dock…"   schema-registry     running             0.0.0.0:8081->8081/tcp
zookeeper           "/etc/confluent/dock…"   zookeeper           running             2888/tcp, 0.0.0.0:2181->2181/tcp, 3888/tcp

Open the Control Center at http://localhost:9021 and wait until the broker shows up as healthy.

Open another tab for the CockroachDB Console at http://localhost:8080.

Demo

We create the below pipeline:

datagen(source) --> Kafka Topic --> Kafka JDBC Sink Connector --> CockroachDB(target)

You can do most of below tasks from within the Control Center.

Create Kafka topic

Connect to the broker container, and create a topic transactions with 4 partitions

docker exec -it broker /bin/bash

Once in the broker shell

# $ create topic with 4 partitions
kafka-topics --bootstrap-server broker:9092 --create --topic transactions --partitions 4 

# describe it
$ kafka-topics --bootstrap-server broker:9092 --topic transactions --describe
Topic: transactions     TopicId: F6NF00Z8Ry2xIWnbYVF9vg PartitionCount: 4       ReplicationFactor: 1    Configs: 
        Topic: transactions     Partition: 0    Leader: 1       Replicas: 1     Isr: 1  Offline: 
        Topic: transactions     Partition: 1    Leader: 1       Replicas: 1     Isr: 1  Offline: 
        Topic: transactions     Partition: 2    Leader: 1       Replicas: 1     Isr: 1  Offline: 
        Topic: transactions     Partition: 3    Leader: 1       Replicas: 1     Isr: 1  Offline:

With the topic partitioned into 4, we can have an equal amount of consumer processes, or tasks, consuming from the topic and ingesting into CockroachDB, in parallel.

All set, now we are ready to ingest data into this topic.

Configure the Source Connector

Our source will be a built-in data generator.

Open a new terminal, and create the Datagen Connector.

# the datagen-connect container listens on port 8083
curl -s -X POST http://localhost:8083/connectors/ \
    -H "Content-Type: application/json" -d '{
        "name": "datagen-transactions",
        "config": {
            "connector.class": "io.confluent.kafka.connect.datagen.DatagenConnector",
            "key.converter": "org.apache.kafka.connect.storage.StringConverter",
            "kafka.topic": "transactions",
            "tasks.max": "64",
            "max.interval": "100",
            "quickstart": "transactions"
        }
    }' | jq '.'

Confirm in the Control Center that messages are getting generated by visiting the Topics section.

Check also on the Messages and Schema tabs to see what the records look like

Configure the Sink Connector

We're ready to setup the JDBC Sink Connector to ingest data into CockroachDB.

Note how we set batch.size the same as max.poll.records to make sure 1 transaction includes only 128 records.

Because we've set reWriteBatchedInserts=true, the JDBC Postgres driver will conflate the 128 individual INSERT statements into a single, multi-record INSERT statement.

Note however that this single statement transaction is still an explicit transaction.

Note:
I've re-built the connector set with autocommit=true to leverage implicit transactions.
I have therefore replaced the original file kafka-connect-jdbc-10.6.1.jar in /usr/share/confluent-hub-components/confluentinc-kafka-connect-jdbc/lib/ with my custom build JAR file.
Below screenshots show the results from using this custom build.

We also set tasks.max=4 to have 4 consumer processes reading from the 4 topic partitions and ingesting data into CockroachDB in parallel. Each task creates its own database connection and reads from a specific topic partition.

# register the connector 
curl -s -X POST http://localhost:8083/connectors/ \
     -H "Content-Type: application/json" -d '{
        "name": "sink-crdb",
            "config": {
            "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
            "connection.url": "jdbc:postgresql://cockroach-1:26257/defaultdb?reWriteBatchedInserts=true&ApplicationName=txns",
            "topics": "transactions",
            "tasks.max": "4",
            "key.converter": "org.apache.kafka.connect.storage.StringConverter",
            "value.converter": "io.confluent.connect.avro.AvroConverter",
            "value.converter.schema.registry.url": "http://schema-registry:8081",
            "connection.user": "root",
            "connection.password": "",
            "auto.create": true,
            "auto.evolve": true,
            "insert.mode": "insert",
            "pk.mode": "none",
            "pk.fields": "none",
            "batch.size": 128,
            "consumer.override.max.poll.records": 128
            }
    }' | jq '.'

You should see now 4 active SQL connections established to CockroachDB, and activity on the SQL Statement chart

Here, in CockroachDB Console in the SQL Activity > Transaction page, we confirm each transaction writes 128 rows.

Drilling down into the transaction, we can see of how many statements it is composed. In this case, as expected, it is just 1 INSERT statement

Further drilling down into the Statement itself, we can see this is a multi-record INSERT statements with 128 records, executed as an implicit transaction.

Query data in CockroachDB

Open a SQL prompt

docker exec -it cockroach-1 cockroach sql --insecure

-- the table was automatically created by the Kafka JDBC Sink Connector 
-- this behavior is of course configurable
> SHOW TABLES;
  schema_name |  table_name  | type  | owner | estimated_row_count | locality
--------------+--------------+-------+-------+---------------------+-----------
  public      | transactions | table | root  |                   0 | NULL

-- check the schema of the created table 
-- note how CockroachDB created its own Primary Key
-- this is also configurable
> SHOW CREATE transactions;
   table_name  |                      create_statement
---------------+--------------------------------------------------------------
  transactions | CREATE TABLE public.transactions (
               |     transaction_id INT8 NOT NULL,
               |     card_id INT8 NOT NULL,
               |     user_id STRING NOT NULL,
               |     purchase_id INT8 NOT NULL,
               |     store_id INT8 NOT NULL,
               |     rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
               |     CONSTRAINT transactions_pkey PRIMARY KEY (rowid ASC)
               | )


-- inspect few rows
> SELECT * FROM transactions LIMIT 5;
  transaction_id | card_id | user_id | purchase_id | store_id
-----------------+---------+---------+-------------+-----------
               1 |       7 | User_6  |           0 |        1
               1 |      19 | User_7  |           0 |        6
               1 |      18 | User_5  |           0 |        6
               1 |       6 | User_   |           0 |        3
               1 |      13 | User_   |           0 |        3

-- avoid contention by using AS OF SYSTEM TIME when running large aggregate queries
> SELECT count(*) FROM transactions AS OF SYSTEM TIME '-5s';
  count
----------
  594633

-- wait few seconds, then query again to see count increasing...
> SELECT count(*) FROM transactions AS OF SYSTEM TIME '-5s';
  count
----------
  594992

You can view more metrics and statement statistics on the DB Console, at http://localhost:8080.

Clean up

Bring down and remove all containers with

docker-compose -f kafka2crdb.yaml down

Reference

Display CockroachDB metrics in Splunk Dashboards

Fabio Ghirardello — Fri, 02 Dec 2022 17:58:03 +0000

CockroachDB ships with a very convenient built-in web monitoring interface, the DB Console. In the DB Console you can visualize all important health and ops metrics by using the pre-configured dashboards. Currently, the DB Console sports 12 dashboards, covering anything from Hardware and Storage metrics to SQL and Distribution. For many customers, this is a great monitoring solution.

Larger enterprises however usually have a separate team that is responsible to monitor pretty much every component of an application, including databases, so they have a centralized solution from which they can more holistically assess the health of an application. For these cases, the same metrics that power the CockroachDB Console dashboards can be forwarded to the enterprise monitoring solution.

Recently, I worked on such an integration with Splunk. The Splunk dashboard files that emulate the DB Console are now available in our repo for everyone's benefit.

In this blog, I demonstrate how I used the OpenTelemetry Collector to send the metrics from CockroachDB to a Splunk instance in a way that can be done on one's laptop using docker.

Setup

The architecture is simple: CockroachDB --> OTEL Collector --> Splunk.
CockroachDB generates detailed time series metrics for each node in the cluster. The collector will pull these metrics from each node endpoint and push them to the Splunk HEC endpoint.
Once in Splunk, it's just a matter to make sense of the metrics by building the right charts and group them into dashboards.

CockroachDB Cluster

As customary, we use a Load Balancer to interact with the CockroachDB cluster.
Create the haproxy.cfg file and save it on the current directory.

# file: haproxy.cfg
global
  maxconn 4096

defaults
    mode                tcp
    timeout connect     10s
    timeout client      10m
    timeout server      10m
    option              clitcpka

listen psql
    bind :26257
    mode tcp
    balance roundrobin
    option httpchk GET /health?ready=1
    server cockroach1 cockroach1:26257 check port 8080
    server cockroach2 cockroach2:26257 check port 8080
    server cockroach3 cockroach3:26257 check port 8080
    server cockroach4 cockroach4:26257 check port 8080

listen http
    bind :8080
    mode tcp
    balance roundrobin
    option httpchk GET /health?ready=1
    server cockroach1 cockroach1:8080 check port 8080
    server cockroach2 cockroach2:8080 check port 8080
    server cockroach3 cockroach3:8080 check port 8080
    server cockroach4 cockroach4:8080 check port 8080

Create the docker network and containers

# create the network bridge
docker network create --driver=bridge --subnet=172.28.0.0/16 --ip-range=172.28.0.0/24 --gateway=172.28.0.1 demo-net

# CockroachDB cluster
docker run -d --name=cockroach1 --hostname=cockroach1 --net demo-net cockroachdb/cockroach:latest start --insecure --join=cockroach1,cockroach2,cockroach3
docker run -d --name=cockroach2 --hostname=cockroach2 --net demo-net cockroachdb/cockroach:latest start --insecure --join=cockroach1,cockroach2,cockroach3
docker run -d --name=cockroach3 --hostname=cockroach3 --net demo-net cockroachdb/cockroach:latest start --insecure --join=cockroach1,cockroach2,cockroach3
docker run -d --name=cockroach4 --hostname=cockroach4 --net demo-net cockroachdb/cockroach:latest start --insecure --join=cockroach1,cockroach2,cockroach3

# initialize the cluster
docker exec -it cockroach1 ./cockroach init --insecure

# HAProxy load balancer
docker run -d --name haproxy --net demo-net -p 26257:26257 -p 8080:8080 -v `pwd`/haproxy.cfg:/etc/haproxy.cfg:ro haproxy:latest -f /etc/haproxy.cfg

At this point you should be able to open the CockroachDB Console at http://localhost:8080.

Start a workload against the cluster to generate some metrics

# init
docker run --rm --name workload --net demo-net cockroachdb/cockroach:latest workload init tpcc 'postgres://root@haproxy:26257?sslmode=disable' --warehouses 10
# run the workload - you might want to use a separate terminal
docker run --rm --name workload --net demo-net cockroachdb/cockroach:latest workload run tpcc 'postgres://root@haproxy:26257?sslmode=disable' --warehouses 10 --tolerate-errors

With 4 nodes, you can simulate a node failure (just stop the container) and view the range activity (replication, lease-transfers, etc).
You can optionally setup CDC to a Kafka container, configure Row Level TTL, add more nodes, etc.

Splunk

Start a Splunk container.

docker run -d --name splunk --net demo-net -p 8088:8088 -p 8000:8000 -e "SPLUNK_START_ARGS=--accept-license" -e "SPLUNK_PASSWORD=cockroach" splunk/splunk

Create a data input and token for HEC.
In Splunk, click Settings > Data Inputs.
Under Local Inputs, click HTTP Event Collector:
1. Click Global Settings.
2. For All Tokens, click Enabled if this button is not already selected.
3. Uncheck the SSL checkbox.
4. Click Save.
Configure an HEC token for sending data by clicking New Token.
On the Select Source page, for Name, enter a token name, for example "Metrics token".
Leave the other options blank or unselected.
Click Next.
On the Input Settings page, for Source type, click New.
In Source Type, set value to "otel".
For Source Type Category, select Metrics.
Next to Default Index, click Create a new index. In the New Index dialog box:
1. Set Index Name to "metrics_idx".
2. For Index Data Type, click Metrics.
3. Click Save.
Select the newly created "metrics_idx".
Click Review, and then click Submit.
Copy the Token Value that is displayed. This HEC token is required for sending data.

OpenTelemetry Collector

There are 2 collector types: the core and the contrib. I have used the contrib as it features the splunk_hec exporter.

The collectors come already pre-compiled and are available for download in the releases repo. Docker containers are also available.

Create file config.yaml and save it in the current directory.
Ensure to replace the Splunk Token with the one you created in the previous step.

# file: config.yaml
---
receivers:
  prometheus:
    config:
      scrape_configs:
        - job_name: 'cockroachdb'
          metrics_path: '/_status/vars'
          scrape_interval: 10s
          scheme: 'http'
          tls_config:
            insecure_skip_verify: true
          static_configs:
          - targets: 
              - cockroach1:8080
              - cockroach2:8080
              - cockroach3:8080
              - cockroach4:8080
            labels:
              cluster_id: 'cockroachdb'

exporters:
  splunk_hec:
    source: otel
    sourcetype: otel
    index: metrics_idx
    max_connections: 20
    disable_compression: false
    timeout: 10s
    tls:
      insecure_skip_verify: true
    token: TOKEN
    endpoint: "http://splunk:8088/services/collector"


service:
  pipelines:
    metrics:
      receivers: 
        - prometheus
      exporters: 
        - splunk_hec

Start the Collector

docker run -d --name otel --net demo-net -v `pwd`/config.yaml:/etc/config.yaml ghcr.io/open-telemetry/opentelemetry-collector-releases/opentelemetry-collector-contrib --config=/etc/config.yaml

CockroachDB metrics should be now pulled by the Prometheus Receiver and forwarded to Splunk via the Splunk HEC Exporter.

Demo

After few minutes, you can do a quick test and run the below queries in Splunk to make sure data is received correctly.
In Splunk, click on Apps, then Search & Reporting.
Enter below commands:

# check what metrics we're receiving
| mcatalog values(metric_name) WHERE index="metrics_idx"

# preview the data in its raw format
| mpreview index="metrics_idx"

# execute the query to show the SQL Statements
| mstats 
rate_sum(sql_select_count) as select, 
rate_sum(sql_insert_count) as insert, 
rate_sum(sql_update_count) as update, 
rate_sum(sql_delete_count) as delete
where index="metrics_idx" span=10s

If Splunk shows data, the pipeline is working correctly and you can load the dashboards.

Click on Dashboards, then on Create New Dashboard.
In the pop-up window:
1. In Dashboard Title, set "CockroachDB Overview".
2. Select Classic Dashboard.
3. Click Create.
The Dashboard is now in edit mode. Click on Source.
Replace the current content with the XML in the overview.xml file in the repo.
Click Save.
Repeat for every Dashboard file in the repo directory.

Here are a few screenshots:

CockroachDB Hardware

CockroachDB SQL

Of course, you can create your very own dashboards, too:

The same Collector can be used for integration with many other sources and targets. It also features processors, so you can pre-process the data (say, filtering) before pushing it to your target solution.

References

Build a CockroachDB Control Plane using Ansible Tower

Fabio Ghirardello — Wed, 16 Nov 2022 17:25:07 +0000

CockroachDB can be easily deployed on the public cloud via Cockroach Cloud, Cockroach Labs DBaaS offering. With a few clicks, your Dedicated or Serverless cluster is ready in minutes if not seconds. Connect, and profit.

Some customers however face restrictions with regards to public cloud usage, preferring their own private cloud instead. Installing and deploying a CockroachDB cluster is very easy, but it's very hard to beat the convenience of a Control Plane if your goal is wide company adoption and streamlined maintenance (i.e. software upgrades).

In this blog, I use Ansible Tower as the cornerstone system to create a simple Control Plane. Tower's enterprise grade features allow you to create users and permissions, manage credentials and environments for executing basically any script or script workflow. Also, it has a little handy feature to create basic GUIs, the Survey, which I leverage to take user inputs.

The idea is that any user in the organization can login into Tower, create a cluster specifying its characteristics (size, number of nodes, regions, etc..) and receive the database connection string. Tower will then use an existing inventory of servers (or create new VMs), install and deploy CockroachDB, and return the connection details. You can of course add pre- and post-installation scripts at your will.

Setup

Ansible Tower, now evolved to a new product called Red Hat Ansible Automation Platform, is a licensed product. So I installed AWX instead, the upstream project that is free and open source.

AWX requires a Kubernetes environment, and on my laptop I created a single node K8s cluster using Minikube.

With AWX up and running, it is time to configure it and integrate it with other components.

Execution environment

First, we need an execution environment, that is, the k8s Pod image that Tower will use to run our "CockroachDB cluster create" script. Ansible provides a base image called ansible/awx-ee which I have extended using below Dockerfile and published as fabiog1901/awx-ee on Dockerhub.

FROM quay.io/ansible/awx-ee:latest

USER root

RUN pip install --upgrade pip

RUN ansible-galaxy collection install ansible.posix

RUN ansible-galaxy collection install community.general

RUN pip install boto boto3 botocore 

RUN pip install google-api-core google-auth google-cloud-compute googleapis-common-protos 

RUN pip install azure-common azure-core azure-identity azure-mgmt-compute azure-mgmt-core azure-mgmt-network azure-mgmt-resource

RUN pip install cockroachdb-cloud-client

USER 1000

Nothing crazy, just adding a few basic Ansible Collections and making sure the cloud provider python SDKs are installed. I mentioned before that the idea was to create a CockroachDB cluster for the private cloud, but as I don't have one, I use the public cloud instead. Assume that for the private cloud we will also install the private cloud python SDKs, for example, the OpenStack SDK or the VMWare vSphere SDK; the idea is to create a docker image that has all the libraries required by the script.

Here I added that docker image as the Default execution env in Tower

Repository Project

What has to be executed is provided to Tower via a Project. Tower integrates with GitHub so we can use a repository as our Tower Project. The sample repo I created contains a playbook to create a cluster and another playbook to destroy a cluster. The playbooks require an Ansible Collection, cockroachdb-collection, which has roles and modules to create VMs and deploy CockroachDB. As we have put the details of the collection in the collections/requirements.yml file, the collection is fetched automatically at runtime by Tower.

Template

A Tower Template is what describes what needs to be run. I created a "CockroachDB Create" template that collects the cluster information via a Survey, and runs the create.yaml file in my Project.

Please note that for the sake of brevity, there are many other details that I'm glossing over, like Credentials (AWS keys, SSH key, etc..), local variables, user creation, etc..

Here the details of the Survey:

Run

It's time to run our Template! I have created a user fabio that has only permission to run the 'CockroachDB Create' job. Now I am logged in with such user and click on the rocket icon to launch the job.
Please note that Tower powers an API server, so you are free to create your own Tower client and create better GUIs - you're not limited to Survey and Template via the standard Tower interface you've seen in these screenshot. Build your own web-client, and let the client call the Templates!

Details on how to connect to the cluster are printed out, but of course you can have better notification mechanisms, for example, you could have Tower send an email or a Slack message.

Creating the cluster on VMs took about 2 minutes, check the elapsed time on the top right.
I can now access my cluster, and profit.

Extend Tower to create clusters on CockroachCloud

You have by now realized that you can pretty much run anything via Tower: it's not a solution solely for creating CockroachDB clusters on private/public cloud infrastructure.

To highlight this, I've created a new Template that uses a new Ansible playbook, cc_create.yaml, that allows me to create a CockroachCloud cluster (a CockroachDB cluster on Cockroach Labs DBaaS service).
How does that work? CockroachCloud can be managed entirely via APIs, see the OpenAPI spec. From the spec JSON file I have generated a Python SDK, cockroachdb-cloud-client.
The SDK has allowed me to create Ansible Modules (still a work-in-progress project) which I am keeping in the same cockroachdb-collection. You can view the module docs here.

The new Template has a new Survey GUI, making it very easy to create a CockroachCloud cluster directly from Tower, where you can audit and track who can do what, and setup things like billing, reporting, etc.

Below is the flow for this new template, and once the Job has run, you'll see the familiar message with details of the newly created cluster

And just to confirm, the cluster is also visible in the CockroachCloud console

Closing Thoughts

This was a small, simple example on how we can use a system such as Tower to quickly build a Control Plane. While there are many aspects that we haven't covered, I hope I've shown what Tower can bring in terms of enterprise features and easiness of adding new workflows and scripts to automate or facilitate common tasks.

The choice is not limited to Tower, however. There is an interesting project that I'm following called Ansible Semaphore. It's by far not as mature as Tower, but worth keeping an eye on it.

How-To: CockroachDB AuthN with Kerberos

Fabio Ghirardello — Fri, 04 Feb 2022 13:58:29 +0000

CockroachDB supports user AuthN using the following methods:

password
cert (TLS client cert)
cert-password (either cert or password)
trusted (always authenticate)
reject (always reject)
gss (Use GSSAPI e.g. Kerberos)

By default every user can authenticate using either the cert or password methods.

In this brief blog, we outline the high level steps required to configure Kerberos AuthN in place of cert/password AuthN.

1 - Create Kerberos SPN and Keytab

In most organizations, you might have to find and ask another department to create the SPN and its keytab on your behalf.

Below the command to create the SPN, assuming we've access to the KDC server.
The keytab default location is /etc/krb5.keytab.

In our case, we add a Principal for the LB address only: we are permitting connection to the cluster only via the LB, that is, you can't point to a CockroachDB node directly.

This is to avoid endusers hardcoding a connection to a single node.
With this implementation, we ensure endusers go to the load-balancer for connection, which is the intended behavior.

Please note: user root bypasses Kerberos AuthN so it can connect from any node as long as the root key+crt are available.

# create principal named 'cockroach'
kadmin.local addprinc -pw <password> cockroach/<load-balancer-IP>@<YOUR.REALM>
kadmin.local addprinc -pw <password> cockroach/<load-balancer-hostname>@<YOUR.REALM>

# create the principal keytab at /etc/krb5.keytab
kadmin.local ktadd cockroach/<load-balancer-IP>@<YOUR.REALM>
kadmin.local ktadd cockroach/<load-balancer-hostname>@<YOUR.REALM>

Each CockroachDB User must have an equally named Principal.

# Principal and DB username is 'fabio'
kadmin.local addprinc -pw <password> fabio@<YOUR.REALM>

2 - Configure keytab on CockroachDB nodes

Download keytab file etc/krb5.keytab locally, then upload it to every CockroachDB cluster node.

The keytab file should be located inside the CockroachDB config directory, by default is /var/lib/cockroach.

It is customary to rename the file to cockroach.keytab.

Also, we assume a Linux user cockroach was created for running CockroachDB.

# assume krb5.keytab is available in the current directory
sudo mv krb5.keytab /var/lib/cockroach/cockroach.keytab

# ensure permissions and ownership are set correctly
sudo chmod 644 /var/lib/cockroach/cockroach.keytab
sudo chown cockroach:cockroach /var/lib/cockroach/cockroach.keytab

CockroachDB will look for this file by querying the environment variable KRB5_KTNAME.

You make this env var available to CockroachDB via the Environment parameter in systemd unit file.

Here's an example systemd unit file, notice the Environment parameter.

[Unit]
Description=Cockroach Database cluster node
Requires=network.target
[Service]
Type=notify
WorkingDirectory=/var/lib/cockroach

Environment="KRB5_KTNAME=/var/lib/cockroach/cockroach.keytab"

ExecStart=/usr/local/bin/cockroach start \
    --certs-dir=/var/lib/cockroach/certs \
    --store=/mnt/cockroach \
    --listen-addr=0.0.0.0:26257 \
    --advertise-addr=host1:26257 \
    --cache=.25 \
    --max-sql-memory=.25 \
    --http-addr=0.0.0.0:8080 \
    --join=host1,host2,host3 \
    --locality=region=us-east4,zone=a
TimeoutStopSec=300
LimitNOFILE=65000
Restart=always
RestartSec=10
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=cockroach
User=cockroach
[Install]
WantedBy=default.target

Reload systemd service files, and restart each CockroachDB node

# repeat for each CockroachDB node
systemctl daemon-reload
systemctl restart cockroachdb

3 - Configure CockroachDB for Kerberos AuthN

As an admin user, login into CockroachDB and enter these commands at the SQL prompt.

Please note, you need an enterprise license for this feature.

-- enable Kerberos AuthN for all users from all hosts
SET cluster setting server.host_based_authentication.configuration = 'host all all all gss include_realm=0';

-- if not present already, create user 'fabio'
-- the password is only used for logging into the DB Console.
-- The password doesn't have to match the 
-- password you gave for the Kerberos Principal, they
-- are not related.
CREATE USER fabio WITH password 'cockroach';

-- apply required grants to the user
GRANT admin TO fabio;

More info on the cluster setting value is available with great details here.

4 - Test

Login into the app server or any other server setup so that you can kinit (or equivalent in your favorite programming language).

# get the kerberos ticket
kinit fabio

You can use klist to verify the ticket is valid.
Now you're ready to connect to the database.

cockroach sql --url "postgresql://fabio@<load-balancer-hostname>:26257/defaultdb?sslmode=verify-full&sslrootcert=ca.crt&krbsrvname=cockroach"

krbsrvname=cockroach this is where you tell CockroachDB what is the SPN user you created in step 1.

To confirm, destroy the ticket and re-attempt connection.
It should fail AuthN of the user.

# destroy all kerberos tickets
kdestroy

# there's no ticket, AuthN will fail
cockroach sql --url "postgresql://fabio@<load-balancer-hostname>:26257/defaultdb?sslmode=verify-full&sslrootcert=ca.crt&krbsrvname=cockroach"

References

Helpful blogs:

DEV Community: Fabio Ghirardello

CockroachDB Graceful Shutdowns

Create a local 9 node cluster

HAProxy configuration

Running the Client App

Test 1 - node shutdown

Test 2 - datacenter shutdown

Test 3 - LB goes down

Conclusion

CockroachDB SSO integration using Keycloak

Installation

PostgreSQL Server

CockroachDB

OpenJDK 21+

Keycloak

Installation and basic config

Configuration

Create admin user in Keycloak

Create regular users and group

Create a Realm main

Create a Client BankApp

CockroachDB configuration

DB Console SSO login

SQL SSO login

Kafka 2 crdb

From CockroachDB to AWS SNS via AWS API Gateway

Create SNS topic

Create AWS Role

Create API Gateway endpoint

Deploy API

Create usage plan

Test locally

Create test table and configure the changefeed

Generate multiple, large, sorted CSV files with pseudo-random data

Setup pgworkload and aws-cli

Generate data

CockroachDB SSO login to the SQL prompt via JWT

Prerequisite: IdP (Okta) setup

Create a few users

Create a Group

Create an App Integration

Configure Token fields

CockroachDB setup

Demo

References

Kafka 2 CockroachDB via JDBC Sink Connector Blueprint

Test Infrastructure and Components Setup

Kafka Producer

Kafka Consumer

CockroachDB Cluster

Test Description

Using n2d-standard-8

Using n2d-standard-16

Using n2d-standard-32

Considerations

Next

References

Repaving CockroachDB cluster node VMs the easy way

Prepare for Node Shutdown

Demo

Cleaning up the old nodes

References

Ingesting data from Kafka to CockroachDB via Kafka Connect

Setup

Demo

Create Kafka topic

Configure the Source Connector

Configure the Sink Connector

Query data in CockroachDB

Clean up

Reference

Display CockroachDB metrics in Splunk Dashboards

Setup

CockroachDB Cluster

Splunk

OpenTelemetry Collector

Demo

References

Build a CockroachDB Control Plane using Ansible Tower

Setup

Create a Realm `main`

Create a Client `BankApp`