SPQR 3.0 Stateless Postgres Query Router on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of SPQR 3.0 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. SPQR (Stateless Postgres Query Router, from the pg-sharding project) speaks the PostgreSQL wire protocol and transparently routes and pools client queries across a set of PostgreSQL shards by a sharding key. Applications keep talking to a single endpoint — the router on port 6432 — while SPQR decides which shard each row belongs to, letting a database grow horizontally.

This is a single node appliance: it ships one PostgreSQL 17 backend hosting two shard databases (shard1, shard2), fronted by spqr-router running with its embedded coordinator (with_coordinator: true) and an in memory metadata store (memqdb, persisted to disk). There is no external etcd cluster and no separate coordinator process, so the whole stack fits a small VM. The router is built from source at the SPQR 3.0.1 tag; PostgreSQL 17 is installed from the official PostgreSQL PGDG repository.

Security by design — no baked credential. There are three built in accounts and none has a password baked into the image. On first boot each VM generates a unique password for the SPQR router user, a unique password for the shard/backend role (shardusr), a unique password for the PostgreSQL superuser, and a unique self signed TLS server certificate, writes them to the root only file /root/spqr-credentials.txt, then starts the router and sets up the sharding topology. The spqr-router service will not start until first boot has written the per VM configuration. Remote clients are accepted only over TLS with scram-sha-256.

What is included:

• SPQR 3.0.1 built from source, running under systemd as spqr-router.service, listening on port 6432 for client traffic

• PostgreSQL 17 from the official PGDG repository, running as postgresql.service, hosting two shard databases shard1 and shard2

• A distribution ds1 with two key ranges — keys 1–999 route to shard1, keys 1000+ route to shard2 — and the demo relation orders attached with distribution key id, set up automatically on first boot

• Per VM passwords for the router user, the shardusr shard role and the postgres superuser, plus a per VM TLS certificate, all generated on first boot and written to a root only credentials file

• The SPQR admin console reachable on box on port 7432 for defining your own distributions, key ranges and sharded relations

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

Prerequisites

• An Azure subscription with permission to deploy virtual machines.

• The psql PostgreSQL client on your workstation (from postgresql-client), or you can run every command over SSH on the VM itself.

• Inbound access from your client IP to TCP 6432 (the SPQR router) and 5432 (direct PostgreSQL) on the VM's network security group. The SPQR admin console on 7432 is deliberately not exposed — reach it on box over SSH.

Deploy the virtual machine

Deploy the cloudimg SPQR image from the Azure Marketplace onto a Standard_B2s (or larger) VM. Open ports 22, 5432 and 6432 to your client IP. Once the VM is running, SSH in as azureuser (replace the address with your VM's public IP):

ssh azureuser@YOUR_VM_PUBLIC_IP

First boot takes a few seconds to generate the per VM secrets, mint the TLS certificate, start the router and set up the sharding topology. If you connect immediately, wait for /var/lib/cloudimg/spqr-firstboot.done to appear.

Retrieve your per VM credentials

Every VM writes its unique secrets to a root only credentials file. Read it with sudo:

sudo cat /root/spqr-credentials.txt

You will see the router connection details (host, port 6432, user router, the per VM router password, database sharded), the PostgreSQL superuser password, and the shardusr shard role password. Each value is unique to this VM.

Confirm the services are healthy

Check that PostgreSQL and the SPQR router are both active and listening:

systemctl is-active postgresql@17-main postgresql@17-shard2 spqr-router
sudo ss -tlnp | grep -E ':(5432|5433|6432|7432)'

All three services report active: the two PostgreSQL shard clusters listen on 5432 (shard1) and 5433 (shard2), and spqr-router listens on 6432 (clients) and 7432 (admin console, on box only).

Route a query through SPQR

Connect through the router on port 6432 with the per VM router password and TLS, then insert two rows whose keys route to different shards. On the VM, load the router password from the credentials file into a shell variable (from your own workstation, use the router.password value and your VM's public IP as the host):

ROUTER_PW=$(sudo sed -n 's/^router.password=//p' /root/spqr-credentials.txt)
PGPASSWORD="$ROUTER_PW" psql "host=127.0.0.1 port=6432 dbname=sharded user=router sslmode=require" -c "CREATE TABLE IF NOT EXISTS orders (id int PRIMARY KEY, note text);"
PGPASSWORD="$ROUTER_PW" psql "host=127.0.0.1 port=6432 dbname=sharded user=router sslmode=require" -c "INSERT INTO orders (id, note) VALUES (1, 'small-key'), (1500, 'large-key');"
PGPASSWORD="$ROUTER_PW" psql "host=127.0.0.1 port=6432 dbname=sharded user=router sslmode=require" -c "SELECT * FROM orders WHERE id = 1;"

The CREATE TABLE propagates to both shards; the row with id = 1 is routed to shard1 and the row with id = 1500 to shard2, and the SELECT reads the row back through the router.

Inspect the sharding topology in the admin console

The SPQR admin console on port 7432 (reachable on box) shows the shards, the distribution and the key ranges the router uses to place each row. Connect to it with the router identity over TLS, using the router.password from your credentials file:

ROUTER_PW=$(sudo sed -n 's/^router.password=//p' /root/spqr-credentials.txt)
PGPASSWORD="$ROUTER_PW" psql "host=127.0.0.1 port=7432 dbname=sharded user=router sslmode=require" -c "SHOW shards;"
PGPASSWORD="$ROUTER_PW" psql "host=127.0.0.1 port=7432 dbname=sharded user=router sslmode=require" -c "SHOW key_ranges;"

SHOW shards lists shard1 and shard2; SHOW key_ranges shows krid1 routing keys from 1 to shard1 and krid2 routing keys from 1000 to shard2 for distribution ds1.

Define your own sharded tables

To shard your own data, create a distribution over the sharding key type, attach your relation, and define key ranges — all in the admin console — then create the table through the router. For example, to shard a users table by an integer user_id:

ROUTER_PW=$(sudo sed -n 's/^router.password=//p' /root/spqr-credentials.txt)
PGPASSWORD="$ROUTER_PW" psql "host=127.0.0.1 port=7432 dbname=sharded user=router sslmode=require" <<'SQL'
CREATE DISTRIBUTION users_ds COLUMN TYPES integer;
ALTER DISTRIBUTION users_ds ATTACH RELATION users DISTRIBUTION KEY user_id;
CREATE KEY RANGE users_kr2 FROM 1000000 ROUTE TO shard2 FOR DISTRIBUTION users_ds;
CREATE KEY RANGE users_kr1 FROM 1 ROUTE TO shard1 FOR DISTRIBUTION users_ds;
SQL

Then CREATE TABLE users (...) through the router on 6432 and SPQR propagates the DDL to both shards and routes rows by user_id. (Create key ranges highest-bound-first, as above, so SPQR does not reject an overlapping range.) See the SPQR documentation for hash sharding, reference tables and adding worker shards.

Connect directly to PostgreSQL

The PostgreSQL 17 backend remains directly reachable on 5432 over TLS for administration and migrations, using the postgres superuser password from the credentials file:

POSTGRES_PW=$(sudo sed -n 's/^postgres.password=//p' /root/spqr-credentials.txt)
PGPASSWORD="$POSTGRES_PW" psql "host=127.0.0.1 port=5432 dbname=postgres user=postgres sslmode=require" -c "SELECT version();"

On box, administration stays password less through the local unix socket: sudo -u postgres psql.

Security posture

• No baked credential. The router user, the shardusr shard role and the postgres superuser all receive unique per VM passwords on first boot. The image ships with the roles password less and the router configuration absent until first boot writes it.

• TLS everywhere client facing. First boot mints a per VM self signed certificate; the router's client port 6432 and PostgreSQL's 5432 both require TLS (sslmode=require). Replace the self signed certificate with your own CA issued certificate for production.

• Layered access control. The Azure network security group is the first layer on 5432/6432; scram-sha-256 with the per VM password is the second. The admin console on 7432 is not exposed to the network — reach it on box over SSH.

• Least privilege. spqr-router runs as a dedicated unprivileged spqr system user; the shard role owns only its two shard databases.

• Patching. Unattended security upgrades remain enabled so the appliance keeps receiving OS updates.

Operations

• Service management: sudo systemctl status spqr-router and sudo systemctl status postgresql. Router logs are at /var/log/spqr/spqr-router.log.

• Router configuration: /etc/spqr/router.yaml (rendered on first boot with the per VM secrets). After editing, sudo systemctl restart spqr-router.

• Sharding metadata: persisted to /var/lib/spqr/memqdb.json and restored on restart. Manage it live through the admin console on 7432.

• Scaling out: add real PostgreSQL shard hosts to shards: in /etc/spqr/router.yaml, then define key ranges routing to them in the admin console. SPQR's spqr-mover / balancer tooling can rebalance existing key ranges.

Support

cloudimg images include 24/7 support. If you have any questions about this image, contact us at support@cloudimg.co.uk.