pgRouting with PostGIS on PostgreSQL 18 on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of pgRouting with PostGIS on PostgreSQL 18 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

pgRouting adds graph algorithms to PostgreSQL. Given a table of edges with costs, pgr_dijkstra returns the cheapest path between two nodes, pgr_aStar does the same with a geometric heuristic, pgr_bellmanFord handles graphs that Dijkstra cannot, and pgr_drivingDistance returns everything reachable within a cost budget. PostGIS is what makes those edges geographic: the network is a table of LINESTRING geometries, the costs are real distances computed from that geometry, and the answer comes back as geometry you can hand straight to a map.

Because it all lives in one database, a route is a SQL query. There is no separate routing engine to run, no tile pipeline to keep in step, and no copy of your network to synchronise.

What this is not

This section is here because the boundary matters more than the feature list.

• No road network data ships with this image. What you get is a small synthetic demonstration graph — a 6x6 lattice of streets and a deliberately separate island — so that every example in this guide runs the moment you boot. It is not a map of anywhere. To route on real streets you load your own network: an OpenStreetMap extract via osm2pgrouting or osm2pgsql, an Ordnance Survey, HERE or TomTom dataset, or your own utility, rail or logistics topology. The licensing and currency of that data are yours, not ours. There is a section below on how to do it.
• This is not a routing service. There is no HTTP API, no /route endpoint, no tile server and no map interface. It is not OSRM, Valhalla, GraphHopper or a hosted directions API. It is a SQL-callable routing library inside a database, and you write the service in front of it. If you want a turn-by-turn HTTP endpoint out of the box, this is the wrong image.
• This is one PostgreSQL instance. No sharding, no clustering and no replication is configured. It suits a network that fits comfortably in one Postgres.

Licensing, stated plainly

This image redistributes three separately-licensed things, and the combination is the normal one:

• PostgreSQL 18 — the PostgreSQL Licence, a permissive BSD/MIT-style licence.
• PostGIS 3.6 — GNU GPL v2 or later.
• pgRouting 4.0.2 — GNU GPL v2 or later.

A GPL extension loaded into a PostgreSQL-licensed server is how PostGIS ships in every Linux distribution and in every cloud's managed PostgreSQL. The PostgreSQL Licence imposes no copyleft on the server, and the GPL applies to the extension code, which this image redistributes unmodified with its licence text on disk. The licence files the build actually read are kept on the image at /stage/scripts/LICENSES/.

Every VM gets its own database. The captured image contains the PostgreSQL binaries and both extensions but no database cluster at all. On first boot each VM runs its own initdb, so no two deployments share a cluster identity, a certificate or a password. There is no default, blank or shared credential at any point, not even briefly.

What is included:

• PostgreSQL 18 (18.6 at build time) from the PostgreSQL Global Development Group repository
• PostGIS 3.6.4, with GEOS, PROJ and GDAL
• pgRouting 4.0.2, installed from the official PGDG package, verified by SHA-256 before installation
• A per-VM first-boot service that creates the cluster, mints a TLS certificate, generates two independent passwords and seeds the demonstration network
• Unattended security upgrades, left enabled

Prerequisites

• An Azure subscription with permission to create virtual machines
• The Azure CLI, or the Azure Portal
• An SSH key pair
• A VM size of Standard_B2s or larger. B2s (2 vCPU, 4 GiB) is ample for the demonstration network and for modest topologies. A country-scale OpenStreetMap extract wants considerably more RAM and disk — size for your data, not for this guide.

Deploy the virtual machine

Run this from your own workstation, not from the VM. Substitute your own resource group, region and admin username.

az group create --name pgrouting-rg --location eastus

az vm create \
  --resource-group pgrouting-rg \
  --name pgrouting-vm \
  --image <the cloudimg pgRouting offer URN from the Marketplace> \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

The database listens on loopback only, so no inbound rule beyond SSH is required, and none should be added unless you deliberately choose to expose the database later.

Connect to the VM:

ssh azureuser@<your vm public ip>

Confirm first boot finished

First boot creates the cluster, the certificate and the credentials. It normally completes within a minute of the VM reaching running.

sudo systemctl is-active pgrouting-firstboot.service
sudo test -f /var/lib/cloudimg/pgrouting-firstboot.done && echo "first boot complete"

Then confirm PostgreSQL is up and both extensions are live:

sudo systemctl is-active postgresql.service
sudo -u postgres psql -d routingdb -c "SELECT extname, extversion FROM pg_extension WHERE extname IN ('postgis','pgrouting') ORDER BY extname;"
sudo -u postgres psql -d routingdb -c "SELECT pgr_version() AS pgrouting, postgis_lib_version() AS postgis;"

Retrieve your per VM credentials

First boot generated two independent passwords for this VM — one for the postgres superuser and one for the routingapp application role — and wrote them to a root-only file.

sudo ls -l /root/pgrouting-credentials.txt
sudo sed -E 's/^([a-z]+\.password)=.*/\1=<redacted for this guide>/' /root/pgrouting-credentials.txt | head -20

This VM also has a cluster identity and a TLS certificate that no other VM shares:

sudo -u postgres psql -d routingdb -c "SELECT system_identifier FROM pg_control_system();"
sudo openssl x509 -in /etc/ssl/certs/ssl-cert-snakeoil.pem -noout -fingerprint -sha256

Connect to the database

On the VM itself, the simplest route in is local peer authentication as the postgres operating-system user. No password is involved because no password crosses a socket:

sudo -u postgres psql -d routingdb -c "SELECT current_user, current_database();"

To connect as the application role over TCP, use the per-VM password. Every TCP connection — loopback included — is required to be TLS, so sslmode=require is not optional:

sudo psql "host=127.0.0.1 port=5432 dbname=routingdb user=routingapp password=<ROUTINGAPP_PASSWORD> sslmode=require" -c "SELECT current_user, ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

ssl comes back t. A connection with sslmode=disable is refused outright — there is no plaintext path to this database, on loopback or off it.

The demonstration network

The seeded network is deliberately small and deliberately honest about being synthetic: a 6x6 lattice of 36 junctions joined by 60 streets, plus a separate three-junction island that nothing connects to the lattice. The island exists so you can see what "no route" looks like as a correct answer.

Every street is a real LINESTRING in EPSG:4326, and every cost is that street's true geodesic length in metres:

sudo -u postgres psql -d routingdb -c "SELECT (SELECT count(*) FROM routing_edges) AS streets, (SELECT count(*) FROM routing_vertices) AS junctions, (SELECT count(*) FROM routing_edges WHERE source IS NULL OR target IS NULL) AS untopologised;"
sudo -u postgres psql -d routingdb -c "SELECT id, name, round(cost::numeric,1) AS metres, ST_AsText(geom) AS geometry FROM routing_edges ORDER BY id LIMIT 3;"

The topology came from the geometry. pgRouting 4.0 removed the old pgr_createTopology; the supported way is pgr_extractVertices, which reads each line's endpoints and returns a vertex table carrying geometry. The source and target columns are then filled by joining on ST_StartPoint and ST_EndPoint. That is the whole trick, and it is why routing and mapping stay in step: the graph is the geometry.

Your first route

pgr_dijkstra takes a SQL string describing the edges, a start node and an end node. agg_cost is the running total — in this network, metres.

sudo -u postgres psql -d routingdb -c "SELECT seq, node, edge, round(cost::numeric,1) AS metres, round(agg_cost::numeric,1) AS running_total FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', (SELECT id FROM routing_vertices ORDER BY ST_Distance(geom, ST_SetSRID(ST_MakePoint(-0.20,51.45),4326)) LIMIT 1), (SELECT id FROM routing_vertices ORDER BY ST_Distance(geom, ST_SetSRID(ST_MakePoint(-0.15,51.50),4326)) LIMIT 1)) ORDER BY seq;"

Note how the start and end nodes are found: by geometry, with ST_Distance against a coordinate. In a real deployment that is exactly how you snap a customer's latitude and longitude onto your network.

Check the answer is actually right

A routing function that always returns something is not the same as a routing function that returns the right thing. Three checks are worth knowing, because they are the ones you should run against your network too.

Independent algorithms must agree. Dijkstra, A* and Bellman-Ford are different algorithms with different implementations. On the same graph they must find the same optimum:

sudo -u postgres psql -d routingdb -c "SELECT 'pgr_dijkstra' AS algorithm, round(max(agg_cost)::numeric,3) AS metres FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, 39) UNION ALL SELECT 'pgr_aStar', round(max(agg_cost)::numeric,3) FROM pgr_aStar('SELECT id, source, target, cost, reverse_cost, ST_X(ST_StartPoint(geom)) AS x1, ST_Y(ST_StartPoint(geom)) AS y1, ST_X(ST_EndPoint(geom)) AS x2, ST_Y(ST_EndPoint(geom)) AS y2 FROM routing_edges', 4, 39) UNION ALL SELECT 'pgr_bellmanFord', round(max(agg_cost)::numeric,3) FROM pgr_bellmanFord('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, 39);"

Note that pgr_aStar needs x1, y1, x2, y2 on every edge — derived here straight from the geometry with ST_X and ST_StartPoint.

The reported cost must match the edges returned. A route that reports a plausible number while returning the wrong path fails this and nothing else:

sudo -u postgres psql -d routingdb -c "SELECT round(max(d.agg_cost)::numeric,3) AS reported_cost, round(sum(e.cost)::numeric,3) AS sum_of_returned_edges FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, 39) d JOIN routing_edges e ON e.id = d.edge WHERE d.edge <> -1;"

The route should come back as geometry. Join the result to the edge table and merge:

sudo -u postgres psql -d routingdb -c "WITH r AS (SELECT ST_LineMerge(ST_Union(e.geom)) AS g FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, 39) d JOIN routing_edges e ON e.id = d.edge WHERE d.edge <> -1) SELECT ST_GeometryType(g) AS type, ST_SRID(g) AS srid, ST_NPoints(g) AS points, round(ST_Length(g::geography)::numeric,1) AS metres FROM r;"

A single ST_LineString in EPSG:4326. Swap ST_GeometryType(g) for ST_AsGeoJSON(g) and you have something a web map can draw directly.

When there is no route

This is the case worth rehearsing, because getting it wrong in production is expensive. A destination in a disconnected part of the graph must return no rows — not an error, and not an invented path.

First confirm the graph really does have two separate parts:

sudo -u postgres psql -d routingdb -c "SELECT component, count(*) AS junctions FROM pgr_connectedComponents('SELECT id, source, target, cost, reverse_cost FROM routing_edges') GROUP BY component ORDER BY junctions DESC;"

Then route into the island and compare against a reachable destination:

sudo -u postgres psql -d routingdb -c "SELECT (SELECT count(*) FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, (SELECT id FROM routing_vertices ORDER BY ST_Distance(geom, ST_SetSRID(ST_MakePoint(-0.50,51.80),4326)) LIMIT 1))) AS rows_to_unreachable, (SELECT count(*) FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, 39)) AS rows_to_reachable;"

Zero rows for the unreachable junction; eleven for the reachable one. Your application must treat an empty result as "no route exists", not as a failure, and pgr_connectedComponents is the tool for finding out in advance whether your imported network is one graph or several. A freshly imported OpenStreetMap extract is very often several, which is a data problem rather than a routing problem.

Cost is what is optimised, not connectivity

The cost column is the whole model. Change it and the answer changes. Here a single street is made prohibitively expensive inside a transaction that is rolled back, so nothing is altered permanently:

sudo -u postgres psql -d routingdb -X <<'SQL'
BEGIN;
SELECT round(max(agg_cost)::numeric,1) AS normal_route_metres
  FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, 34);
UPDATE routing_edges SET cost = 1e6, reverse_cost = 1e6 WHERE name = 'ew-c2-r0';
SELECT round(max(agg_cost)::numeric,1) AS with_one_street_closed
  FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 4, 34);
ROLLBACK;
SQL

The route gets longer and goes around. That is the mechanism behind road closures, turn restrictions, vehicle weight limits and time-of-day penalties: none of them are special pgRouting features, they are all just the cost column. Setting reverse_cost to -1 makes an edge one-way.

Catchment areas

pgr_drivingDistance answers "what can I reach within this budget" — service areas, delivery zones, isochrones. The budget is in the same units as cost, so metres here:

sudo -u postgres psql -d routingdb -c "SELECT 1500 AS budget_metres, count(*) AS junctions_reachable FROM pgr_drivingDistance('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 21, 1500) UNION ALL SELECT 800, count(*) FROM pgr_drivingDistance('SELECT id, source, target, cost, reverse_cost FROM routing_edges', 21, 800);"

Tighten the budget and the catchment shrinks. Join the result back to routing_vertices and you have a set of points you can hull into a polygon with ST_ConvexHull or ST_ConcaveHull for display.

Load your own network

This is the step that turns the image into a working system. The demonstration table is a placeholder; replace it with real data.

From OpenStreetMap. The conventional tool is osm2pgrouting, which reads an .osm or .pbf extract and writes a pgRouting-shaped ways table directly. Install it on the VM and point it at an extract from a provider such as Geofabrik:

sudo apt-get install -y osm2pgrouting
osm2pgrouting --f your-extract.osm --dbname routingdb --username postgres \
              --conf /usr/share/osm2pgrouting/mapconfig.xml --clean

For large extracts, osm2pgsql plus your own edge-table construction gives more control over which ways become routable and what their costs are. Check the licence of any dataset you import — OpenStreetMap data is ODbL, which carries share-alike obligations on derived databases.

From your own data. pgRouting has no opinion about where edges come from. Any table with an integer id, source, target, cost and reverse_cost works. If you have geometry and no topology yet, derive it the same way this image does:

SELECT * INTO my_vertices
FROM pgr_extractVertices('SELECT id, geom FROM my_edges ORDER BY id');

UPDATE my_edges e SET source = v.id
  FROM my_vertices v WHERE ST_StartPoint(e.geom) = v.geom;
UPDATE my_edges e SET target = v.id
  FROM my_vertices v WHERE ST_EndPoint(e.geom) = v.geom;

Then index source, target and geom, run ANALYZE, and check pgr_connectedComponents before trusting any route.

Security posture

The image is configured to be safe on first boot without any hardening step from you.

• PostgreSQL listens on loopback only. listen_addresses is localhost. The only port reachable from the network is SSH on 22. You can confirm this yourself:

ss -lnt
sudo -u postgres psql -d routingdb -c "SHOW listen_addresses;"

• Every TCP path requires TLS and a password. pg_hba.conf carries hostssl ... scram-sha-256 rules and no plaintext host rules at all, so even a loopback TCP connection must be encrypted and authenticated. Local socket connections use peer authentication.
• Per-VM credentials, generated on first boot. Two independent 32-character passwords, set over standard input so they never appear in a command line or in the journal. The file is 0600 root:root.
• No cluster is captured in the image. There is therefore no window in which a default, blank or shared credential exists.
• Root SSH login is disabled and password authentication is off:

sudo sshd -T | grep -E '^(permitrootlogin|passwordauthentication|clientaliveinterval) '

• Automatic security updates are enabled:

dpkg-query -W -f='${Status}\n' unattended-upgrades
cat /etc/apt/apt.conf.d/20auto-upgrades

• The Dirty Frag kernel mitigation is in place (CVE-2026-43284 / CVE-2026-43500):

cat /etc/modprobe.d/dirtyfrag.conf
modprobe -n -v esp4

esp4 resolves to install /bin/false, so the module cannot be loaded. The insmod line that appears first is a dependency, not esp4 itself.

Optionally expose the database to your application subnet

If your application runs on another VM, you can open the database deliberately. This is a two-step change, and it should be scoped to your own subnet rather than to the internet.

First, on the VM, change listen_addresses and restart PostgreSQL:

sudo sed -i "s/^listen_addresses.*/listen_addresses = '*'/" /etc/postgresql/18/main/postgresql.conf
sudo systemctl restart postgresql

Then open TCP 5432 to your own subnet CIDR in the VM's network security group:

az network nsg rule create --resource-group pgrouting-rg --nsg-name pgrouting-vmNSG \
  --name allow-postgres-from-app-subnet --priority 1010 \
  --source-address-prefixes 10.0.1.0/24 --destination-port-ranges 5432 \
  --access Allow --protocol Tcp

pg_hba.conf already requires TLS and scram-sha-256 on every TCP path, so the per-VM password is enforced from the first packet. Tighten the 0.0.0.0/0 entry in pg_hba.conf to your own CIDR as well if you want defence in depth. After the change, confirm what you have actually exposed:

sudo -u postgres psql -d routingdb -c "SHOW listen_addresses;"

Rotating the credentials

Rotate either role whenever you like, then update the credentials file to match so the reference stays accurate:

sudo -u postgres psql -c "ALTER ROLE routingapp PASSWORD 'your-new-password'"
sudo -u postgres psql -c "ALTER ROLE postgres WITH PASSWORD 'your-new-password'"
sudo nano /root/pgrouting-credentials.txt

Maintenance

• Security updates arrive through unattended-upgrades, which is installed and enabled. PostgreSQL, PostGIS and pgRouting all come from the PostgreSQL Global Development Group repository, which is the only third-party source enabled on the image.
• Version currency. This image ships pgRouting 4.0.2, which at build time was newer than the version in the PGDG stable suite; it was installed from the official PGDG package for that version, verified by SHA-256. When PGDG promotes that version or later to its stable suite, unattended-upgrades picks it up like any other package. No prerelease repository is enabled on the image.
• Backups are ordinary PostgreSQL backups. pg_dump for logical dumps, or pg_basebackup plus WAL archiving for point-in-time recovery. Your routing tables are just tables.
• Performance. Routing over a large network is dominated by how many edges the query hands to pgRouting. Filter the edge SQL by a bounding box around the origin and destination rather than passing the whole country, and keep source, target and geom indexed.

Support

cloudimg provides 24/7 support for this image. Raise an issue through the Azure Marketplace listing or contact cloudimg support directly.

For pgRouting itself, the reference documentation at docs.pgrouting.org covers every function, and the PostGIS documentation at postgis.net covers the spatial side.