CockroachDB is a distributed SQL Database that has been gaining popularity in the last years because of its features. It is easy to deploy, with strong consistency, designed for the cloud, it has distributed transactions and easy to scale. Not many SQL Databases have these features today which makes CockroachDB an excellent solution for many workloads. Currently, there are three methods to deploy CDB:
Cockroach Labs provides the managed deployment options for their product, based on different licensing options and the self-hosted solution is licensed by hardware and infrastructure type.
In this article, we will explore the self-hosted solution since it provides complete control of the implementation. We will review and complete all the necessary steps to build a CDB cluster in the Google Cloud Platform with the following requisites:
The process will be divided into three parts:
1 – Creation of the GCP Infrastructure (this one)
2 – Creation and Start of the CDB Cluster
3 – Management and Availability testing
To begin we will need to prepare our GCP project infrastructure. To have all the details of the build I will include all the GCP Cli commands to create the artifacts. There are better ways to automate the creation of this kind of infrastructure, like for example using Terraform.
We will use one private VCP with a subnet for each of the utilized availability zones. Open the GCP console and execute the following statements to create the VCP and the subnets:
gcloud compute networks create private-cdb-cluster-prod --project=cdbblog --description=Multi-region\ VPC\ for\ CDB\ Cluster --subnet-mode=custom --mtu=1460 --bgp-routing-mode=regional gcloud compute networks subnets create private-us-central1-a --project=cdbblog --range=10.14.1.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=us-central1 --enable-private-ip-google-access gcloud compute networks subnets create private-us-central1-b --project=cdbblog --range=10.14.2.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=us-central1 --enable-private-ip-google-access gcloud compute networks subnets create private-us-central1-c --project=cdbblog --range=10.14.3.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=us-central1 --enable-private-ip-google-access gcloud compute networks subnets create private-europe-southwest1-a --project=cdbblog --range=10.20.1.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=europe-southwest1 --enable-private-ip-google-access gcloud compute networks subnets create private-europe-southwest1-b --project=cdbblog --range=10.20.2.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=europe-southwest1 --enable-private-ip-google-access gcloud compute networks subnets create private-europe-southwest1-c --project=cdbblog --range=10.20.3.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=europe-southwest1 --enable-private-ip-google-access gcloud compute networks subnets create private-southamerica-east1-a --project=cdbblog --range=10.50.1.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=southamerica-east1 --enable-private-ip-google-access gcloud compute networks subnets create private-southamerica-east1-b --project=cdbblog --range=10.50.2.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=southamerica-east1 --enable-private-ip-google-access gcloud compute networks subnets create private-southamerica-east1-c --project=cdbblog --range=10.50.3.0/24 --stack-type=IPV4_ONLY --network=private-cdb-cluster-prod --region=southamerica-east1 --enable-private-ip-google-access
Now, lets create a Public VPC for the Bastion Host:
gcloud compute networks create public-cdb-cluster-prod --project=cdbblog --description=Multi-region\ VPC\ for\ CDB\ Cluster\ -\ Public\ VPC --subnet-mode=custom --mtu=1460 --bgp-routing-mode=regional gcloud compute networks subnets create public-us-central1 --project=cdbblog --range=10.30.1.0/24 --stack-type=IPV4_ONLY --network=public-cdb-cluster-prod --region=us-central1 --enable-private-ip-google-access
To be able to communicate between the subnets we need to create a VPC Peering:
gcloud compute networks peerings create cdb-vpc-peering1 --network=public-cdb-cluster-prod --peer-project cdbblog --peer-network private-cdb-cluster-prod --export-subnet-routes-with-public-ip gcloud compute networks peerings create cdb-vpc-peering2 --network=private-cdb-cluster-prod --peer-project cdbblog --peer-network public-cdb-cluster-prod --export-subnet-routes-with-public-ip
And we need to create firewalls to make connections between the subnets:
gcloud compute firewall-rules create cdb-cluster-prod-multiregion-allow-custom --project=cdbblog --network=projects/cdb/global/networks/private-cdb-cluster-prod --description=Allows\ connection\ from\ any\ source\ to\ any\ instance\ on\ the\ network\ using\ custom\ protocols. --direction=INGRESS --priority=65534 --source-ranges=10.30.1.0/24,10.14.1.0/24,10.14.2.0/24,10.14.3.0/24,10.20.1.0/24,10.20.2.0/24,10.20.3.0/24,10.50.1.0/24,10.50.2.0/24,10.50.3.0/24 --action=ALLOW --rules=all gcloud compute firewall-rules create allow-access-from-iap --project=cdbblog --network=projects/cdb/global/networks/private-cdb-cluster-prod --description=Allows\ connection\ from\ IAP. --direction=INGRESS --priority=65534 --source-ranges=35.235.240.0/20 --action=ALLOW --rules=tcp:22 gcloud compute firewall-rules create public-cdb-cluster-prod-allow-custom --project=cdbblog --network=projects/cdb/global/networks/public-cdb-cluster-prod --description=Allows\ connection\ from\ any\ source\ to\ any\ instance\ on\ the\ network\ using\ custom\ protocols. --direction=INGRESS --priority=65534 --source-ranges=10.30.1.0/24,10.14.1.0/24,10.14.2.0/24,10.14.3.0/24,10.20.1.0/24,10.20.2.0/24,10.20.3.0/24,10.50.1.0/24,10.50.2.0/24,10.50.3.0/24 --action=ALLOW --rules=all gcloud compute firewall-rules create public-cdb-cluster-prod-allow-icmp --project=cdbblog --network=projects/cdb/global/networks/public-cdb-cluster-prod --description=Allows\ ICMP\ connections\ from\ any\ source\ to\ any\ instance\ on\ the\ network. --direction=INGRESS --priority=65534 --source-ranges=0.0.0.0/0 --action=ALLOW --rules=icmp gcloud compute firewall-rules create public-cdb-cluster-prod-allow-rdp --project=cdbblog --network=projects/cdb/global/networks/public-cdb-cluster-prod --description=Allows\ RDP\ connections\ from\ any\ source\ to\ any\ instance\ on\ the\ network\ using\ port\ 3389. --direction=INGRESS --priority=65534 --source-ranges=0.0.0.0/0 --action=ALLOW --rules=tcp:3389 gcloud compute firewall-rules create public-cdb-cluster-prod-allow-ssh --project=cdbblog --network=projects/cdb/global/networks/public-cdb-cluster-prod --description=Allows\ TCP\ connections\ from\ any\ source\ to\ any\ instance\ on\ the\ network\ using\ port\ 22. --direction=INGRESS --priority=65534 --source-ranges=0.0.0.0/0 --action=ALLOW --rules=tcp:22
To provide secure Internet access to the private subnets we need to create a Cloud Nat. One for each of the regions with the cloud router also:
gcloud compute routers create cdb-cluster-nat-router-us --project=cdbblog --network=private-cdb-cluster-prod --asn=65001 --region=us-central1 gcloud compute routers create cdb-cluster-nat-router-europe --project=cdbblog --network=private-cdb-cluster-prod --asn=65001 --region=europe-southwest1 gcloud compute routers create cdb-cluster-nat-router-sa --project=cdbblog --network=private-cdb-cluster-prod --asn=65001 --region=southamerica-east1 gcloud compute routers nats create cdb-nat-us --project=cdbblog --router=cdb-cluster-nat-router-us --region=us-central1 --auto-allocate-nat-external-ips --nat-all-subnet-ip-ranges --enable-logging gcloud compute routers nats create cdb-nat-europe --project=cdbblog --router=cdb-cluster-nat-router-europe --region=europe-southwest1 --auto-allocate-nat-external-ips --nat-all-subnet-ip-ranges --enable-logging gcloud compute routers nats create cdb-nat-sa --project=cdbblog --router=cdb-cluster-nat-router-sa --region=southamerica-east1 --auto-allocate-nat-external-ips --nat-all-subnet-ip-ranges --enable-logging
Like we mentioned before, we will use a Bastion Host to connect to each of the cluster nodes. Let’s create one with the following command under the Public VPC:
gcloud compute instances create cdb-bastion --project=cdbblog --zone=us-central1-b --machine-type=n2d-standard-2 --network-interface=network-tier=PREMIUM,subnet=public-us-central1 --maintenance-policy=MIGRATE --provisioning-model=STANDARD --service-account=458159664907-compute@developer.gserviceaccount.com --scopes=https://www.googleapis.com/auth/devstorage.read_only,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/trace.append --tags=public,bastion --create-disk=auto-delete=yes,boot=yes,device-name=cdb-bastion,image=projects/ubuntu-os-cloud/global/images/ubuntu-1804-bionic-v20221005,mode=rw,size=20,type=projects/cdbblog/zones/us-central1-b/diskTypes/pd-balanced --no-shielded-secure-boot --shielded-vtpm --shielded-integrity-monitoring --reservation-affinity=any
And create the first CDB Cluster node. It will have three additional storage disks. One for the CDB binaries, one for the logs and one SSD for the data.
gcloud compute instances create cdb-cluster-node1 --project=cdbblog --zone=us-central1-a --machine-type=n2d-standard-4 --network-interface=subnet=private-us-central1-a,no-address --maintenance-policy=MIGRATE --provisioning-model=STANDARD --service-account=458159664907-compute@developer.gserviceaccount.com --scopes=https://www.googleapis.com/auth/devstorage.read_only,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/trace.append --tags=cdb,private --create-disk=auto-delete=yes,boot=yes,device-name=cdb-cluster-node1,image=projects/ubuntu-os-cloud/global/images/ubuntu-1804-bionic-v20221005,mode=rw,size=20,type=projects/cdbblog/zones/us-central1-a/diskTypes/pd-balanced --create-disk=description=Disk\ for\ Binaries,device-name=cdb,mode=rw,name=cdb,size=50,type=projects/cdbblog/zones/us-central1-a/diskTypes/pd-balanced --create-disk=description=Disk\ for\ Logs,device-name=cdb-logs,mode=rw,name=cdb-logs,size=10,type=projects/cdbblog/zones/us-central1-a/diskTypes/pd-balanced --create-disk=device-name=cdb-data,mode=rw,name=cdb-data,size=600,type=projects/cdbblog/zones/us-central1-a/diskTypes/pd-ssd --no-shielded-secure-boot --shielded-vtpm --shielded-integrity-monitoring --reservation-affinity=any
With the bastion host and the first cluster node created, we can go ahead and configure the first node.
We use gcloud compute ssh to connect to the node from the bastion host:
admin@cdb-bastion:~$ sudo su - root@cdb-bastion:~# gcloud auth login root@cdb-bastion:~# gcloud compute ssh cdb-cluster-node1 --project=cdbblog --zone=us-central1-a
Format and mount the disks:
mkfs.ext4 -m 0 -E lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdb mkfs.ext4 -m 0 -E lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdc mkfs.ext4 -m 0 -E lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdd mkdir /cdb mkdir /cdb_logs mkdir /cdb_data mount -o discard,defaults /dev/sdb /cdb mount -o discard,defaults /dev/sdc /cdb_logs mount -o discard,defaults /dev/sdd /cdb_data root@cdb-cluster-node1:~# df -h | grep cdb /dev/sdb 49G 24K 49G 1% /cdb /dev/sdc 9.8G 24K 9.8G 1% /cdb_logs /dev/sdd 98G 24K 98G 1% /cdb_data
Add the disks details for the /etc/fstab file:
root@cdb-cluster-node1:~# cat /etc/fstab LABEL=cloudimg-rootfs / ext4 defaults 0 1 LABEL=UEFI /boot/efi vfat umask=0077 0 1 UUID=96c77a05-23e0-4fdd-af8e-2e1b83219b6e /cdb ext4 defaults 0 1 UUID=77ab43d0-f299-4308-a6fc-b3e0bcbf2a04 /cdb_logs ext4 defaults 0 1 UUID=effe547d-ed0a-4244-8a57-bc586cc10b54 /cdb_data ext4 defaults 0 1
Install the Cockroach DB Binaries:
root@cdb-cluster-node1:/cdb# curl https://binaries.cockroachdb.com/cockroach-v22.1.8.linux-amd64.tgz | tar -xz && sudo cp -i cockroach-v22.1.8.linux-amd64/cockroach /usr/local/bin/ % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 83.6M 100 83.6M 0 0 31.9M 0 0:00:02 0:00:02 --:--:-- 31.9M root@cdb-cluster-node1:/cdb# root@cdb-cluster-node1:/cdb# cockroach version Build Tag: v22.1.8 Build Time: 2022/09/29 14:21:51 Distribution: CCL Platform: linux amd64 (x86_64-pc-linux-gnu) Go Version: go1.17.11 C Compiler: gcc 6.5.0 Build Commit ID: bdcab67f778617515597f1012f37f14f622b15a0 Build Type: release root@cdb-cluster-node1:/cdb#
Create the certificates to make secure connections:
root@cdb-cluster-node1:/cdb# mkdir certs root@cdb-cluster-node1:/cdb# mkdir my-safe-directory root@cdb-cluster-node1:/cdb# cockroach cert create-ca --certs-dir=certs --ca-key=my-safe-directory/ca.key
Now that we have the first node configured with the CDB installed, we will create a Machine Image. From this image we will create all the other nodes for the cluster. Go back to the GCP console and execute:
gcloud beta compute machine-images create cdb-cluster-node-image --project=cdbblog --source-instance=cdb-cluster-node1 --source-instance-zone=us-central1-a --storage-location=us
We have covered most of the infrastructure creation for the cluster. In the next part we will cover the creation of the rest of nodes, the creation of the Load Balancer and we will start the cluster.
CDB Recommended Production Settings
Ready to optimize your Oracle Database for the future?