Recently one of my colleagues proposed we look at using CockroachDB instead of PostgresDB for a project we’re working on for a new customer of Digitalis.
Usually, when someone requests to use Postgres I favour using a patroni-based deployment that our colleague Federico Campoli first introduced to Digitalis.
But here the proposal is quite intriguing because the Postgres requirements for this customer are not complex, a simple multi-region deployment in AWS.
The applications that will be using this database appear to be OK with the unsupported features CoachroachDB has and I thought it could be worth exploring further.
As much as work with databases I don’t consider myself to be a DBA or even good with them. My colleagues at Digitalis and AxonOps are way better than me. But I am a decent DevOps/SRE and I learn by automating things. Therefore, I thought I’d write an Ansible role for CockroachDB to teach me the basics.
Baby steps
The first thing I did was install and start the cluster with a single node. This was very straightforward, I really like that the whole application is a single binary (golang 💌).
/opt/cockroachdb/cockroach start-single-node --insecureWith that, I can simply get a shell using
[root@dev-cockroach-0000 opt]# cockroach-sql --insecure
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v23.1.11 (x86_64-pc-linux-gnu, built 2023/09/27 01:53:43, go1.19.10) (same version as client)
# Cluster ID: 4aea46aa-75a5-4d6e-b54d-e1901bd52d00
#
# Enter \? for a brief introduction.
#
root@localhost:26257/defaultdb>Ok, I thought. But I don’t really want to use plain text, I need SSL. I had a look at the docs and something that surprised me is you cannot specify certificate files but you have to follow some file naming conventions and stick them into a directory for CockroachDB to use:
[root@dev-cockroach-0000 cockroachdb-ssl]# pwd
/opt/cockroachdb-ssl
[root@dev-cockroach-0000 cockroachdb-ssl]# ls -l
total 16
-rw-------. 1 cockroachdb cockroachdb 1150 Oct 26 19:32 ca.crt
-rw-------. 1 cockroachdb cockroachdb 1179 Oct 26 19:32 node.crt
-rw-------. 1 cockroachdb cockroachdb 1674 Oct 26 19:32 node.keyAfter I started the database again, suddenly I could not log in, its shell was asking me for the root password:
Connecting to server "localhost:26257" as user "root".
Enter password:Unexpected… but the clue was in the logs, sslmode is set to full verify. It means I would need to use client certificates also unless there is a configuration to downgrade to prefer.
jdbc:postgresql://dev-exchange-postgres-0000:26257/defaultdb?sslmode=verify-fullAccording to the documentation, I need to create client certificates following the format client-USER.{crt,key}
~$ cockroach cert create-client root \
--certs-dir=/opt/cockroachdb-ssl \
--ca-key /opt/cockroachdb-ssl/ca.key
~$ cockroach-sql --certs-dir=/opt/cockroachdb-ssl
root@localhost:26257/defaultdb>So far so good. I have a working node with SSL and I’m able to login to the CLI.
Web UI
CoachroachDB comes with a built-in UI. When I first started the cluster in insecure mode, I could access it just fine. But now I need to create users to be able to access it.
This appears to be very simple. You just need to log in to the CLI and issue the create command like in the example above.
root@localhost:26257/defaultdb> create user serg with password 'digitalis';
root@localhost:26257/defaultdb> alter user serg with CREATEDB;
root@localhost:26257/defaultdb> alter user serg with CREATEROLE;I added this to my ansible and all is well and good. Security, ✅
Clustering
Now that I have the basics covered, I need a cluster for this to work for me. For testing my ansible role, I’m creating three nodes in AWS and I’m going to attempt to cluster them.
I had some trouble figuring out what SSL certs I needed and the ansible to create them. It was a bit tedious if I’m honest. I think I have grown accustomed to having our own internal CA using Hashicorp Vault and I forgot how to do it 😅
On my first attempt, the clustering failed with
E231026 12:23:17.394472 15 2@rpc/context.go:2404 [rnode=?,raddr=localhost:26257,class=system,rpc] 2 unable to connect (is the peer up and reachable?): initial connection heartbeat failed: problem using security settings: problem with client cert for user root: not found
However, I quickly realised I didn’t allow connectivity between EC2 instances when I created them. After allowing port 26257, voila!
Users management
I’m going to perform another test to check how compatible it is with Postgres by using the community ansible module to create users.
I had to create beforehand the user serg with the createrole permissions and then my playbook worked and it created the joeblocks user without any problems.
root@x.x.x.x:26257/defaultdb> \du
List of roles:
Role name | Attributes | Member of
------------+-------------------------------------------------+------------
root | Superuser, Create role, Create DB | {admin}
admin | Superuser, Create role, Create DB | {}
joeblocks | | {}
serg | Create role, Create DB | {}
node | Superuser, Create role, Create DB, Cannot login | {}
(5 rows)
root@x.x.x.x:26257/defaultdb>PGadmin4 is a widely used web UI for Postgres. I thought it would be a good test to set it up and use it against CockroachDB to see how compatible it is with Postgres.
The compatibility isn’t quite what I anticipated. As you navigate the menus, you encounter a multitude of warnings and errors.
Backups
The backups are surprisingly good and it supports backing up to S3 and other HTTP storage:
BACKUP DATABASE movr INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}&S3_STORAGE_CLASS=INTELLIGENT_TIERING' AS OF SYSTEM TIME '-10s';
It also supports incremental backups and even KMS encryption. This is enough for my needs.
The missing features
Unfortunately, there is one important feature missing for us. In this project, I have a number of developers who are going to get access to the databases for testing and developing purposes.
We don’t provision users to the databases, we use PAM authentication that relies on Azure AAD. As far as I can see, CockroachDB only supports the built-in password and SSL and neither is suitable for me right now.
Conclusion
On the face of it, CockroachDB looks like a good product. It didn’t take me long to get the clustering up and running and the installation and configuration are not difficult.
I do like there is no lengthy config file to edit/template as all the settings are managed inside the cluster.
If you’re looking for a fully compatible Postgres, this may not be your product unless you’re starting from scratch and you can adapt your applications to work with CockroachDB. If you’re looking for a clustered RDBMS database, this may be a good choice though on many occasions Apache Cassandra may be a better option.
The full Ansible role I created for this blog is available on GitHub. This is not suitable for production use, it is just a learning exercise!
https://github.com/digitalis-io/ansible-role-coackroachdb/tree/main
