CrateDB 6 on Ubuntu 24.04 on Azure User Guide

Overview

CrateDB is a distributed SQL database built on Lucene, with Elasticsearch lineage clustering, real time SQL, and horizontal scaling. It is a natural fit for time series, IoT and analytics workloads where you want familiar SQL over very large, fast moving datasets. The cloudimg image ships CrateDB 6.3.5 on a hardened, fully patched Ubuntu 24.04 LTS base, with the built in Crate Admin UI served through nginx on port 80 and the PostgreSQL wire protocol exposed on port 5432 for any SQL client. CrateDB bundles its own Java runtime, so nothing else needs installing. Authentication is enforced out of the box: a unique admin password is generated on the first boot of every VM, and no unauthenticated request is ever accepted on a network reachable surface. All data lives on a dedicated Azure data disk. Backed by 24/7 cloudimg support.

What is included:

• CrateDB 6.3.5 (Apache 2.0) with its bundled Java runtime, managed by systemd as crate.service
• The built in Crate Admin UI on :80, behind an nginx reverse proxy, password protected
• The PostgreSQL wire protocol on :5432 for psql, JDBC and any PostgreSQL client
• A per VM admin user and password generated on first boot and recorded in a root only file
• Host based authentication enforced: no trust mode on any network reachable surface
• A dedicated Azure data disk at /var/lib/cratedb for all tables and cluster metadata
• An unauthenticated /healthz endpoint for Azure Load Balancer health probes
• A single node cluster you can scale out by adding more nodes
• 24/7 cloudimg support

Prerequisites

An active Azure subscription, an SSH key pair, and a VNet plus subnet in the target region. Standard_D2s_v4 (2 vCPU / 8 GiB RAM) is a sensible starting point; CrateDB is a JVM database, so give it memory and size up for larger datasets or heavier query loads. NSG inbound: allow 22/tcp from your management network, 80/tcp for the Admin UI, and 5432/tcp for SQL clients. CrateDB serves plain HTTP on port 80; for production, terminate TLS in front of it with your own domain and restrict 5432/tcp to trusted networks.

Step 1 - Deploy from the Azure Marketplace

Sign in to the Azure Portal, choose Create a resource, search the Marketplace for CrateDB by cloudimg, and select Create. On Basics pick your subscription, resource group, region and size; under Administrator account choose SSH public key and paste your key; under Inbound port rules allow SSH (22) and HTTP (80). Add an inbound rule for 5432 if you want to reach the PostgreSQL wire protocol from SQL clients. Review the dedicated data disk on the Disks tab, then Review + create then Create.

Step 2 - Deploy from the Azure CLI

az vm create \
  --resource-group <your-rg> \
  --name cratedb \
  --image <marketplace-image-urn> \
  --size Standard_D2s_v4 \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

Then open the Admin UI on port 80 and, if you want remote SQL clients, the PostgreSQL wire port 5432:

az vm open-port --resource-group <your-rg> --name cratedb --port 80  --priority 900
az vm open-port --resource-group <your-rg> --name cratedb --port 5432 --priority 910

Step 3 - Confirm the services are running

SSH in as azureuser and confirm CrateDB and nginx are active. The Admin UI and HTTP endpoint listen on port 4200, the PostgreSQL wire protocol on 5432, and nginx fronts the Admin UI on port 80.

systemctl is-active crate nginx cratedb-firstboot

ss -tlnH | awk '{print $4}' | grep -E ':4200$|:5432$|:80$' | sort -u

Step 4 - Retrieve the per VM admin password

Every VM generates its own CrateDB admin password on first boot and writes it, along with the admin user and URL, to a root only credentials file. Read it with sudo:

sudo cat /root/cratedb-credentials.txt

You sign in as the admin user with the CRATEDB_ADMIN_PASSWORD from this file, both in the Admin UI and from any PostgreSQL client. Authentication is genuinely enforced: an unauthenticated HTTP request or a no password SQL connection is rejected, as the checks above show. On the box itself the built in crate superuser can connect over the loopback PostgreSQL wire without a password as a break glass path.

Step 5 - Sign in to the Crate Admin UI

Browse to http://<vm-public-ip>/ and sign in as admin with the password from Step 4. You land on the cluster overview, which shows the cluster health, replicated and available data, and the total record count.

Step 6 - Browse tables and schemas

The Tables view in the left navigation lists every schema and table, with health, shard, record count and size for the selected table. CrateDB organises tables into schemas (doc is the default) and stores every table as sharded, replicated Lucene indexes.

Step 7 - Run SQL and check cluster health

The Console gives you a full SQL editor. Type any statement and submit it with the SUBMIT QUERY button (or Shift and Enter); results appear below with timing.

The Cluster view lists every node with its CrateDB version, HTTP endpoint, and live CPU, heap and disk usage, so you can watch node health at a glance.

Step 8 - Connect a PostgreSQL client on port 5432

CrateDB speaks the PostgreSQL wire protocol, so you can use psql, JDBC, or any PostgreSQL driver. From the VM itself, read the admin password and connect over the loopback interface:

PW=$(sudo grep '^CRATEDB_ADMIN_PASSWORD=' /root/cratedb-credentials.txt | cut -d= -f2-); psql "host=127.0.0.1 port=5432 user=admin password=$PW dbname=doc sslmode=disable" -c "SELECT name, version['number'] AS version FROM sys.nodes;"

From a remote machine, point the same client at the VM public IP on port 5432 (open the NSG port first) and enter the admin password when prompted:

psql "host=<vm-public-ip> port=5432 user=admin dbname=doc sslmode=disable"

Create a table, insert a row and read it back to prove the round trip end to end:

PW=$(sudo grep '^CRATEDB_ADMIN_PASSWORD=' /root/cratedb-credentials.txt | cut -d= -f2-); psql "host=127.0.0.1 port=5432 user=admin password=$PW dbname=doc sslmode=disable" -c "CREATE TABLE IF NOT EXISTS doc.demo_guide (id INT PRIMARY KEY, note TEXT)" -c "INSERT INTO doc.demo_guide (id, note) VALUES (1, 'hello from the guide')" -c "REFRESH TABLE doc.demo_guide" -c "SELECT * FROM doc.demo_guide"

Step 9 - Verify the stack

Confirm the CrateDB version, the bundled Java runtime, and the unauthenticated health endpoint:

PW=$(sudo grep '^CRATEDB_ADMIN_PASSWORD=' /root/cratedb-credentials.txt | cut -d= -f2-); psql "host=127.0.0.1 port=5432 user=admin password=$PW dbname=doc sslmode=disable" -tAc "SELECT version['number'] FROM sys.nodes LIMIT 1"

/opt/cratedb/jdk/bin/java -version 2>&1 | head -1

curl -sI http://127.0.0.1/healthz | head -1

Step 10 - Where your data lives

All CrateDB tables and cluster metadata live on a dedicated Azure data disk mounted at /var/lib/cratedb, so your data is kept separate from the operating system disk and rides with the VM. Confirm the mount and the configured data path:

findmnt -no SOURCE,TARGET,FSTYPE,SIZE /var/lib/cratedb

grep -E '^path.data|^path.logs' /opt/cratedb/config/crate.yml

Scaling to a cluster

The appliance ships as a single node cluster, which is ideal for development and small workloads. CrateDB scales horizontally: deploy more VMs from the same image, set a shared cluster.name and list the other nodes under discovery.seed_hosts in each node's /opt/cratedb/config/crate.yml, remove the discovery.type: single-node line, and restart crate.service. New nodes join the cluster and shards rebalance automatically. Increase table replicas so data survives a node loss.

Security notes

• Authentication is enforced on every network reachable surface. The Admin UI (HTTP) and remote PostgreSQL connections both require the per VM admin password; only the built in crate superuser may connect, and only over the loopback PostgreSQL wire, as a local break glass path.
• CrateDB serves plain HTTP on port 80. For anything beyond a trusted network, put it behind your own TLS terminating reverse proxy or Azure Application Gateway with a certificate for your domain, and enable SSL on the PostgreSQL wire.
• The per VM admin password is unique to each VM. Keep /root/cratedb-credentials.txt protected and rotate the password (ALTER USER admin SET (password = '...')) if you share access.
• Restrict inbound 80/tcp and 5432/tcp in your NSG to the networks that need them.

Support

This image is maintained by cloudimg with 24/7 support. If you need help deploying or operating CrateDB on Azure, contact us at cloudimg.co.uk.