pgRouting PostgreSQL 18 PostGIS

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 needs considerably more RAM and disk, so size the VM for your data, not for this guide.

Deploy the virtual machine

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

az group create --name pgrouting-rg --location eastus
# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:pgrouting-ubuntu-24-04:default:latest
az vm create \
  --resource-group pgrouting-rg \
  --name pgrouting-vm \
  --image sac1647283583153:pgrouting-ubuntu-24-04:default:latest sac 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. Add none 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. Check that the service is active and the completion marker exists:

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

Then confirm that 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. It wrote both to a root-only file. Check the file and view it with the passwords redacted:

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. Confirm both:

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 way in is local peer authentication as the postgres operating-system user. No password is involved, because none 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, must use TLS, so sslmode=require is mandatory:

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();"

The ssl column 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 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 lets you 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. Count the streets and junctions and inspect three of them:

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 comes 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 technique, 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, which in this network is in 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;"

The query finds the start and end nodes by geometry, using ST_Distance against a coordinate. In a real deployment, this is 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 does not necessarily return the right thing. Run these three checks here, and run them 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);"

pgr_aStar needs x1, y1, x2, y2 on every edge. The query derives them straight from the geometry with ST_X and ST_StartPoint.

The reported cost must match the edges returned. Only this check catches a route that reports a plausible number while returning the wrong path:

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 the lines:

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;"

The result is a single ST_LineString in EPSG:4326. Swap ST_GeometryType(g) for ST_AsGeoJSON(g) and a web map can draw it directly.

When there is no route

Rehearse this case, because getting it wrong in production is expensive. A destination in a disconnected part of the graph must return no rows. It must not return an error or an invented path.

First confirm that the graph has 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;"

The query returns zero rows for the unreachable junction and eleven for the reachable one. Your application must treat an empty result as "no route exists", not as a failure.

Use pgr_connectedComponents to find out in advance whether your imported network is one graph or several. A freshly imported OpenStreetMap extract is 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. This example makes a single street 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 the closed street. The same mechanism handles road closures, turn restrictions, vehicle weight limits and time-of-day penalties. None of them is a special pgRouting feature. Each is 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?", which covers service areas, delivery zones and 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);"

A tighter budget shrinks the catchment. Join the result back to routing_vertices to get a set of points. You can hull them into a polygon for display with ST_ConvexHull or ST_ConcaveHull.

Load your own network

This step turns the image into a working system. The demonstration network exists only so the examples run, so replace it with real data.

From OpenStreetMap. The conventional tool is osm2pgrouting. It 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 does not care where edges come from. Any table with an integer id, source, target, cost and reverse_cost works. If you have geometry but 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 and run ANALYZE. Check pgr_connectedComponents before you trust 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, 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 are set over standard input, so they never appear in a command line or in the journal. The credentials 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. Scope it to your own subnet, not 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. For defence in depth, also tighten the 0.0.0.0/0 entry in pg_hba.conf to your own CIDR. After the change, confirm what you have exposed:

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

Rotating the credentials

You can rotate either role's password at any time. Update the credentials file afterwards so that it 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, 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 and verified by SHA-256. When PGDG promotes that version or a later one 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 instead of passing the whole country, and keep source, target and geom indexed.

Support

SecureAnyCloud provides 24/7 support for this image. Raise an issue through the Azure Marketplace listing or contact SecureAnyCloud 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.

Deploy on Azure

Launch pgRouting with PostGIS on PostgreSQL 18 on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com