XTDB on Ubuntu 24.04 on Azure User Guide

Overview

XTDB is an open source immutable, bitemporal SQL database for application development, time-travel reporting and data compliance. Every row it stores carries two time dimensions automatically: the system time that records when a fact was written, and the valid time that records when the fact is true in the real world. This gives you full history, point-in-time reporting and audit trails using standard SQL:2011 temporal syntax, without maintaining trigger tables or shadow history tables of your own. XTDB speaks the PostgreSQL wire protocol, so you query it with psql, JDBC and the Postgres drivers you already use.

The cloudimg image installs XTDB 2.1.0 as a single-node standalone server with local-disk storage, managed by systemd. It is built as an internal database appliance: the PostgreSQL wire endpoint (127.0.0.1:5432) and the health endpoint (127.0.0.1:8080) both bind to loopback, and the network firewall opens SSH (22) only, so the database is never reachable directly from the internet. You reach it over an SSH tunnel or from within your own virtual network. The data directory is reset before the image is captured, so every VM initialises a fresh node on first boot with no shared data and no node identity, and a small bitemporal demo dataset is seeded so you can run time-travel queries the moment it boots. Backed by 24/7 cloudimg support.

What is included:

• XTDB 2.1.0 (the official xtdb-standalone build), running as the xtdb.service systemd unit under OpenJDK 21
• Single-node standalone configuration with local-disk transaction log and storage under /var/lib/xtdb
• A PostgreSQL wire-compatible endpoint on 127.0.0.1:5432 (bound to loopback only)
• A health/monitoring endpoint on 127.0.0.1:8080 (/healthz/alive, /healthz/started, /healthz/ready)
• Secure by default: the database binds to loopback and the firewall opens SSH only, so nothing is exposed to the network
• A fresh node initialised on the first boot of every VM, with no baked data or node identity
• A seeded bitemporal demo dataset (an employee salary history with several valid-time revisions) so time-travel works immediately
• xtdb.service plus the xtdb-firstboot.service and xtdb-seed.service one-shots, enabled and active
• 24/7 cloudimg support

Prerequisites

An active Azure subscription, an SSH key pair, and a VNet and subnet in the target region. Standard_B2s (2 vCPU / 4 GiB RAM) is a good starting point; scale up for larger workloads. Network security group inbound: allow 22/tcp from your management network only. You do not open the database port: XTDB binds to loopback and you reach it over an SSH tunnel, so 5432 and 8080 are never exposed. If you prefer private networking, peer the VNet and reach the VM's private IP.

Step 1 - Deploy from the Azure Marketplace

Sign in to the Azure Portal, choose Create a resource, search the Marketplace for XTDB by cloudimg, and select Create. On Basics pick your subscription, resource group, region and size (Standard_B2s); under Administrator account choose SSH public key and paste your key; under Inbound port rules allow SSH (22) only. Then Review + create, then Create.

Step 2 - Deploy from the Azure CLI

az vm create \
  --resource-group <your-rg> \
  --name xtdb \
  --image <marketplace-image-urn> \
  --size Standard_B2s \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_ed25519.pub \
  --vnet-name <your-vnet> --subnet <your-subnet> \
  --public-ip-sku Standard

Only SSH (22) needs to be reachable. Do not add an inbound rule for 5432 or 8080.

Step 3 - Connect over SSH and read your connection details

SSH in as azureuser. The first-boot service writes a per-VM connection note to a root-only file, including the exact SSH tunnel command for this VM:

sudo cat /root/xtdb-credentials.txt

Expected output (your public IP will differ):

# XTDB - Per-VM Connection Details
# Generated: ...

XTDB_VERSION=2.1.0
XTDB_HOST=127.0.0.1
XTDB_PORT=5432
XTDB_DATABASE=xtdb
XTDB_USER=xtdb
XTDB_SSH_TUNNEL=ssh -N -L 5432:127.0.0.1:5432 azureuser@<vm-ip>

The database uses a single local user, xtdb, and trusts connections that arrive on the loopback interface - which, because the port is not exposed, means connections that arrive either from on the VM itself or through your SSH tunnel. There is no shared network password baked into the image; the security boundary is the SSH key that gates access to the VM.

Step 4 - Verify the service and health endpoint

XTDB runs as a systemd service. Confirm it is active and that the health endpoint responds on loopback:

sudo systemctl is-active xtdb.service
curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8080/healthz/alive

Expected output:

active
200

You can also confirm the endpoints are bound to loopback only:

ss -ltnH | awk '{print $4}' | grep -E ':(5432|8080)$'

Expected output (the ::ffff:127.0.0.1 form is the loopback address):

[::ffff:127.0.0.1]:8080
[::ffff:127.0.0.1]:5432

Step 5 - Open an SSH tunnel from your workstation

To reach XTDB from a Postgres client on your own machine, forward the loopback port over SSH. Run this on your workstation (leave it running in a terminal):

ssh -N -L 5432:127.0.0.1:5432 azureuser@<vm-ip>

Now localhost:5432 on your workstation is the XTDB endpoint on the VM. Connect with any Postgres client, for example:

psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb"

No password is required over the tunnel: the connection arrives on the VM's loopback interface, which XTDB trusts, and access to the tunnel is gated by your SSH key.

Step 6 - Explore the bitemporal demo data with time travel

The image seeds an employees table with valid-time salary history, so you can see time travel working immediately. The commands below run against the loopback endpoint - on the VM directly over SSH, or from your workstation through the tunnel from Step 5.

Ask what Alice's salary was as of two different points in valid time:

psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb" -c "SELECT name, salary FROM employees FOR VALID_TIME AS OF DATE '2023-07-01' WHERE _id = 1;"
psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb" -c "SELECT name, salary FROM employees FOR VALID_TIME AS OF DATE '2024-07-01' WHERE _id = 1;"

Expected output - the same row returns a different salary depending on the point in time you ask about:

   name   | salary
----------+--------
 Alice Ng |  85000
(1 row)

   name   | salary
----------+--------
 Alice Ng |  95000
(1 row)

Now see the full valid-time history for that employee in one query. FOR ALL VALID_TIME returns every version of the row, each with the period it was valid for:

psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb" -c "SELECT _valid_from, _valid_to, salary FROM employees FOR ALL VALID_TIME WHERE _id = 1 ORDER BY _valid_from;"

Expected output - three periods, the last one open-ended (valid until the end of time):

        _valid_from        |         _valid_to         | salary
---------------------------+---------------------------+--------
 2023-01-01 00:00:00+00:00 | 2024-01-01 00:00:00+00:00 |  85000
 2024-01-01 00:00:00+00:00 | 2025-01-01 00:00:00+00:00 |  95000
 2025-01-01 00:00:00+00:00 |                           | 110000
(3 rows)

A plain query with no temporal clause returns the current view - the latest valid version of every row:

psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb" -c "SELECT _id, name, department, salary FROM employees ORDER BY _id;"

Expected output:

 _id |   name    | department  | salary
-----+-----------+-------------+--------
   1 | Alice Ng  | Engineering | 110000
   2 | Ben Cole  | Sales       |  78000
   3 | Cara Diaz | Engineering | 120000
(3 rows)

Step 7 - Insert your own bitemporal data

Inserting a row with a new _id creates a record valid from now to the end of time. Inserting the same _id again with a _valid_from records a new valid-time period, building history exactly like the seeded data. Try it:

psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb" -c "INSERT INTO employees (_id, name, department, salary, _valid_from) VALUES (100, 'Dana Ito', 'Support', 60000, DATE '2024-01-01');"
psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb" -c "INSERT INTO employees (_id, name, department, salary, _valid_from) VALUES (100, 'Dana Ito', 'Support', 66000, DATE '2025-01-01');"
psql "host=127.0.0.1 port=5432 dbname=xtdb user=xtdb" -c "SELECT _valid_from, salary FROM employees FOR ALL VALID_TIME WHERE _id = 100 ORDER BY _valid_from;"

Expected output - your two valid-time periods:

        _valid_from        | salary
---------------------------+--------
 2024-01-01 00:00:00+00:00 |  60000
 2025-01-01 00:00:00+00:00 |  66000
(2 rows)

Because XTDB is immutable, the earlier version is never overwritten - it is preserved and remains queryable as of any past date. You also have the system-time dimension: FOR ALL SYSTEM_TIME shows the history of when facts were recorded, independently of when they are true, which is what makes audit and "as of when we knew it" reporting possible.

Step 8 - Connect a client

Any PostgreSQL driver connects through the tunnel. For example, a JDBC URL:

jdbc:postgresql://127.0.0.1:5432/xtdb?user=xtdb

or a Python client with psycopg:

psycopg.connect("host=127.0.0.1 port=5432 dbname=xtdb user=xtdb")

Use standard SQL, adding XTDB's temporal clauses (FOR VALID_TIME AS OF, FOR ALL VALID_TIME, FOR ALL SYSTEM_TIME) where you want time travel. The full SQL reference is in the XTDB documentation.

Security model

This image ships as an internal database appliance and is secure by default:

• Loopback binding. Both the PostgreSQL wire endpoint (5432) and the health endpoint (8080) bind to 127.0.0.1 only. The database is never listening on a public or private network address.
• Firewall opens SSH only. The recommended NSG opens 22/tcp only. You reach the database over an SSH tunnel, so access is gated by your SSH key.
• No baked credential or node identity. The data directory is reset before the image is captured. Every VM initialises a fresh node on first boot, so no two machines share data or identity, and no shared password ships in the image.

If you need to expose XTDB beyond an SSH tunnel, do so deliberately behind your own network controls (private endpoint, VNet peering, or a proxy with authentication) rather than by opening the port to the internet.

Maintenance

Manage the service with systemd:

sudo systemctl status xtdb.service --no-pager
sudo systemctl restart xtdb.service
sudo journalctl -u xtdb.service --no-pager -n 50

The data lives on the OS disk under /var/lib/xtdb (a log/ transaction log and a buffers/ storage directory). It persists across reboots and service restarts, as you proved in Step 6 after any restart. Back it up by snapshotting the disk or by exporting query results.

The image is fully patched at build time and keeps unattended security upgrades enabled, so the operating system continues to receive security updates. XTDB itself is pinned to the version shipped in this image; to move to a newer XTDB release, deploy a newer cloudimg image.

Support

Every cloudimg deployment includes 24/7 support. If you have questions about this image or need help with your deployment, contact the cloudimg support team.