PostgreSQL 17 with PostGIS and MobilityDB on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of PostgreSQL 17 with PostGIS and MobilityDB on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It pairs the PostgreSQL 17 relational database with PostGIS and MobilityDB (mobilitydb), an extension that adds temporal and spatiotemporal types for moving objects right inside the database engine.

The idea is simple and powerful: instead of storing a position as a single point, you store a whole trajectory — how an object moved through space over time — in one temporal geometry point (tgeompoint) column. You then query that trajectory directly with SQL: where a vehicle was at a given moment, the path it followed over an interval, how far it travelled, its speed, and how close any two objects ever came. MobilityDB adds these temporal types and hundreds of functions and operators on top of PostGIS geometry, and they are indexable, so spatial-window, time-slice and nearest-approach queries stay fast as the trajectory history grows.

What you get on this image:

• Trajectories as first-class data — a tgeompoint column holds an object's full movement over time, so one row describes an entire trip rather than a single fix.
• Position at a time — valueAtTimestamp(trip, ...) returns exactly where an object was at any instant; atTime(trip, ...) returns the movement over an interval.
• Spatiotemporal analytics in SQL — trajectory length(), speed(), spatial-window filtering with atGeometry(), and nearest-approach distance with the |=| operator, all queried like ordinary columns.

PostgreSQL 17, PostGIS 3.6 and MobilityDB 1.3.0 are all installed from the official PostgreSQL PGDG repository. The mobilitydb extension is already created (it pulls in PostGIS automatically), and the image ships a small demo trips table of vehicle trajectories with a spatiotemporal index already built, so you can run a real moving-object query within a minute of first boot.

Security by design — no baked credential. Every role in the shipped image is password-less by construction, and TLS is off, so the database is not reachable off-box at all in the image itself. On first boot each VM mints a unique password for the postgres superuser and for the least-privilege application role fleet_app, generates a unique self-signed TLS server certificate, writes everything to the root-only file /root/mobilitydb-credentials.txt, then enables TLS. Two VMs launched from this image never share a secret. A credential guard runs on every boot and refuses to leave the database serving if a published or example credential is ever actually in effect.

What is included:

• PostgreSQL 17 from the official PGDG repository, running under systemd as postgresql.service

• PostGIS 3.6 and MobilityDB 1.3.0 from the PGDG repository, with mobilitydb already created in a demo database (PostGIS is loaded via shared_preload_libraries, configured for you)

• A demo database mobility with a trips table of three vehicle trajectories stored as tgeompoint, and a USING gist spatiotemporal index already built

• A least-privilege application role fleet_app with DML on the demo schema, for your application to run spatiotemporal queries

• Per-VM passwords for both roles and a per-VM TLS certificate generated on first boot, written to a root-only credentials file

• A boot-time credential guard that fails closed if a published or example credential is in effect

• Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

• Active Azure subscription, an SSH public key, and a VNet + subnet in the target region

• Subscription to this listing on Azure Marketplace

• A Network Security Group allowing TCP 22 (administration) and TCP 5432 (the PostgreSQL wire protocol) from your client network. In production, restrict 5432 to your application subnet.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development, evaluation and light workloads. For larger trajectory histories and higher query rates, choose a memory-rich size such as Standard_E2s_v5 or larger.

Deploy the virtual machine

Create the VM from the image, opening only SSH and the PostgreSQL port to your own network:

az vm create \
  --resource-group my-rg \
  --name mobilitydb-1 \
  --image <this-marketplace-image> \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name mobilitydb-1 --port 5432 --priority 900

Retrieve your per VM credentials

On the first boot the VM mints its own passwords and TLS certificate. SSH in and read the root-only credentials file:

sudo cat /root/mobilitydb-credentials.txt

You will see the postgres superuser password, the fleet_app application role password, and ready-to-paste connection strings. The file is mode 0600, owned by root, and the values in it exist only on this VM.

The postgres.host line is filled in from the VM's own network metadata. Azure's instance metadata service returns an empty value for Standard SKU public IPs, so the image falls back to the load-balancer metadata and then to the VM's private address. When connecting from outside Azure, use the VM's public IP address from the portal or az vm list-ip-addresses.

Confirm the service is healthy

Check that PostgreSQL and the credential guard are in good order, and that both extensions are loaded:

systemctl is-active postgresql.service mobilitydb-credguard.service
sudo -u postgres psql -tAc "SELECT version();"
sudo -u postgres psql -d mobility -tAc "SELECT extname || ' ' || extversion FROM pg_extension WHERE extname IN ('mobilitydb','postgis') ORDER BY extname;"
sudo -u postgres psql -d mobility -tAc "SELECT a.amname FROM pg_class c JOIN pg_am a ON a.oid = c.relam WHERE c.relname = 'trips_trip_gist';"

The last query must return gist — that is the proof the demo trajectory index really uses the GiST access method, so spatiotemporal predicates against it are index-assisted rather than sequential scans.

Query trajectories: position, length and time slices

This is the heart of the product. The mobility database ships a trips table where each row is a vehicle and its whole path over the interval 08:00–08:20, stored in a single tgeompoint (temporal geometry point) column. Because the movement is in the database as data, you can query it directly.

Ask where a vehicle was at a specific instant — valueAtTimestamp() interpolates the position along the trajectory:

sudo -u postgres psql -d mobility -c "SELECT vehicle, ST_AsText(valueAtTimestamp(trip, timestamptz '2000-01-01 08:10')) AS position_at_0810 FROM trips WHERE vehicle = 'bus-A';"

Measure how far each vehicle travelled — length() returns the spatial length of the whole trajectory:

sudo -u postgres psql -d mobility -c "SELECT vehicle, round(length(trip)::numeric, 3) AS trajectory_length FROM trips ORDER BY vehicle;"

Restrict a trajectory to a time window and get the path it took — atTime() slices the movement, and trajectory() projects it to a geometry:

sudo -u postgres psql -d mobility -c "SELECT ST_AsText(trajectory(atTime(trip, tstzspan '[2000-01-01 08:05, 2000-01-01 08:15]'))) AS path_0805_to_0815 FROM trips WHERE vehicle = 'bus-A';"

Spatial windows and nearest approach, over TLS

MobilityDB combines the time dimension with PostGIS space. Connect as the least-privilege application role over TLS and ask which vehicles ever pass through a map window, and how close any two vehicles ever came.

atGeometry() restricts a trajectory to the part that falls inside a geometry, so a IS NOT NULL test tells you which trajectories ever entered a spatial window:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "SELECT vehicle FROM trips WHERE atGeometry(trip, ST_MakeEnvelope(3,-1,5,1)) IS NOT NULL ORDER BY vehicle;"

The |=| operator returns the nearest approach distance — the closest two moving objects ever came to each other, taking time into account. A distance of 0 means their paths crossed the same point at the same instant:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "SELECT a.vehicle AS v1, b.vehicle AS v2, round((a.trip |=| b.trip)::numeric, 3) AS nearest_approach FROM trips a JOIN trips b ON a.id < b.id ORDER BY nearest_approach;"

You can confirm the connection is over TLS directly:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -tAc "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

Store your own trajectories

Adding your own moving objects is ordinary SQL. Create a table with a tgeompoint column and a GiST index, then insert trajectories in MobilityDB's temporal literal form [Point(x y)@timestamp, ...]. Create the table and index as the postgres owner (schema DDL is an owner operation); the least-privilege fleet_app role then reads and writes the rows:

sudo -u postgres psql -d mobility -c "CREATE TABLE IF NOT EXISTS my_trips (id int PRIMARY KEY, name text, trip tgeompoint);"
sudo -u postgres psql -d mobility -c "CREATE INDEX IF NOT EXISTS my_trips_gist ON my_trips USING gist (trip);"
sudo -u postgres psql -d mobility -c "GRANT SELECT, INSERT, UPDATE, DELETE ON my_trips TO fleet_app;"
PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "INSERT INTO my_trips (id, name, trip) VALUES (1, 'van-1', tgeompoint '[Point(0 0)@2000-01-01 10:00, Point(5 5)@2000-01-01 10:30]') ON CONFLICT (id) DO NOTHING;"

Now query your own trajectory — for example its length and where it was halfway through:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "SELECT name, round(length(trip)::numeric, 3) AS len, ST_AsText(valueAtTimestamp(trip, timestamptz '2000-01-01 10:15')) AS at_1015 FROM my_trips WHERE id = 1;"

MobilityDB provides temporal types for floats, integers, text and geographies too (tfloat, tint, tgeogpoint, …), plus set-of-time (tstzspan, tstzspanset) types for reasoning about when things happened; see the upstream reference for the full type and function set.

Security posture

• No baked credential. Every role ships with no password at all. Both role passwords are minted on first boot from a cryptographic random source and are unique per VM.

• No shared certificate. TLS is off in the image and the certificate is generated per VM on first boot, so no two VMs share a server key.

• TLS enforced for remote clients. pg_hba.conf accepts remote connections only through hostssl with scram-sha-256. There is no trust rule anywhere. On-box administration stays password-less through the local unix socket.

• A credential guard that fails closed. On every boot, mobilitydb-credguard.service proves the recorded per-VM credentials genuinely authenticate, then attempts authentication with a list of published and example values. If any of them succeeds, the guard stops PostgreSQL rather than serve a database with a known credential.

• Least privilege for applications. The fleet_app role has DML on the demo schema but is not a superuser. Point your application at it rather than at postgres.

Rotate a password at any time. Generate a fresh random secret and record it in the credentials file in the same step, so the credential guard continues to agree with reality:

NEWPW="$(openssl rand -base64 40 | tr -dc 'A-Za-z0-9' | cut -c1-28)"
sudo -u postgres psql -c "ALTER ROLE fleet_app PASSWORD '${NEWPW}';"
sudo sed -i "s|^fleet_app.password=.*|fleet_app.password=${NEWPW}|" /root/mobilitydb-credentials.txt
echo "fleet_app password rotated and recorded in the credentials file"

Always update /root/mobilitydb-credentials.txt when you rotate. The credential guard verifies on every boot that the recorded password genuinely authenticates, and stops PostgreSQL if it does not — that check is what makes a stale or mismatched credential impossible to ignore. Never set a password to a value published in documentation: the guard actively tries a list of such values and refuses to serve if one of them works.

Operations

Service control and logs:

systemctl status postgresql.service --no-pager
sudo journalctl -u mobilitydb-firstboot.service --no-pager | tail -20

Back up the demo database:

sudo -u postgres pg_dump -d mobility -f /tmp/mobility-backup.sql && ls -la /tmp/mobility-backup.sql

Third-party licence attribution for MobilityDB, PostGIS and PostgreSQL is shipped inside the image:

cat /usr/share/doc/mobilitydb-cloudimg/ATTRIBUTION.txt

Support

cloudimg images include 24/7 support. Raise an issue through the Azure Marketplace listing or contact cloudimg support with the VM name, region, and the output of systemctl status postgresql.service.

MobilityDB is developed at the Université libre de Bruxelles and distributed under The PostgreSQL License; PostGIS is GPL-2.0+ and PostgreSQL is under The PostgreSQL License. Upstream documentation lives at mobilitydb.com.