SPQR

Prerequisites

• An Azure subscription with permission to deploy virtual machines
• The psql PostgreSQL client on your workstation (from postgresql-client), or 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 the box over SSH.

Deploy the virtual machine

Deploy the SecureAnyCloud 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, connect over SSH 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/sac/data/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

The file lists the router connection details (host, port 6432, user router, the per-VM router password and 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). spqr-router listens on 6432 (clients) and 7432 (admin console, on the 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 is routed to shard2. 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 the box) shows the shards, the distribution and the key ranges that 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, work in the admin console. Create a distribution over the sharding key type, attach your relation, and define key ranges. Then create the table through the router.

This example shards 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 run CREATE TABLE users (...) through the router on 6432. 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 the box, administration stays passwordless 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 passwordless 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, so reach it on the 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, run 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 that route to them in the admin console. SPQR's spqr-mover and balancer tooling can rebalance existing key ranges.

Support

SecureAnyCloud images include 24/7 support. If you have any questions about this image, contact us at sales@secureanycloud.com.

Deploy on Azure

Launch SPQR 3.0 Stateless Postgres Query Router 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