Odyssey PostgreSQL Connection Pooler on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of Odyssey, the PostgreSQL connection pooler, on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. Odyssey is a scalable, multithreaded PostgreSQL connection pooler and request router (BSD 3 Clause, from Yandex), built from source in this image and bundled with PostgreSQL 17 so the appliance works on its own.

Clients connect through Odyssey on port 6432 in transaction pooling mode. Odyssey multiplexes many short lived client connections onto a small pool of backend PostgreSQL sessions, so a connection heavy application stays responsive without exhausting the database with one backend process per client. Because Odyssey is multithreaded, it scales that work across CPU cores. You can also point Odyssey at your own external PostgreSQL instead of the bundled one.

The bundled PostgreSQL is loopback only: it listens on 127.0.0.1:5432 and is never reachable on the routable address. Odyssey on 6432 is the single public entry point, protected by three layers: the Azure Network Security Group, TLS, and a per VM scram-sha-256 password.

Security by design — no baked credential. There are two built in accounts and neither has a password baked into the image. appuser is the pooled application role that owns the default database appdb; one per VM secret authenticates both the client leg (into Odyssey) and the backend leg (Odyssey into PostgreSQL). The postgres superuser is for local administration over the loopback. Both are password less by construction in the captured image, and Odyssey will not start until first boot has written the per VM secret into its configuration. On first boot each VM generates a unique password for each account and a unique self signed TLS server certificate, writes the secrets to the root only file /root/odyssey-credentials.txt, fills the pooler configuration, and starts Odyssey.

What is included:

• Odyssey 1.5.1 built from source (BSD 3 Clause), running under systemd as odyssey.service, listening on 6432 in transaction pooling mode with TLS and scram-sha-256

• PostgreSQL 17 from the official PGDG repository, running as postgresql.service, loopback only on 127.0.0.1:5432

• A default database appdb owned by the pooled role appuser

• Per VM passwords for appuser and the postgres superuser, plus a per VM TLS certificate, generated on first boot and written to a root only credentials file

• Odyssey configured so remote clients connect only over TLS with a per VM password, while local PostgreSQL administration stays password less via the unix socket

• A pooling self test (/usr/local/sbin/odyssey-selftest.sh) you can run to prove real multiplexing on your own VM

• 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 6432 (the Odyssey pooler). In production, restrict 6432 to your application subnet. The bundled PostgreSQL on 5432 is loopback only and never needs an NSG rule.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development and light workloads. For higher connection counts and throughput, choose a larger size such as Standard_E2s_v5 or above, and raise Odyssey's workers to match the core count.

Deploy the virtual machine

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

az vm create \
  --resource-group my-rg \
  --name odyssey-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 odyssey-1 --port 6432 --priority 900

Retrieve your per VM credentials

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

sudo cat /root/odyssey-credentials.txt

The file is mode 0600, owned by root, and lists the appuser pooled password (used through Odyssey on 6432) and the postgres superuser password (for local administration on 5432), plus ready to paste connection strings. Nothing is baked into the image — every deployed VM has its own passwords and certificate.

Confirm the services are healthy

Check that both PostgreSQL and Odyssey are active and listening, and confirm PostgreSQL is bound only to the loopback address:

sudo systemctl is-active postgresql.service odyssey.service
sudo ss -tln | grep -E ':5432|:6432'

You should see both services report active, Odyssey listening on 6432 on all interfaces, and PostgreSQL listening on 5432 only on 127.0.0.1 — it is never exposed on the routable address.

Connect through the pooler

On the VM you can verify the full pooled path in one step, reading the per VM password straight from the credentials file and connecting through Odyssey on 6432:

PGPASSWORD="$(sudo sed -n 's/^odyssey.password=//p' /root/odyssey-credentials.txt)" \
  psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require" \
  -c "SELECT version();" \
  -c "SELECT current_user, current_database();"

The query runs against PostgreSQL 17 by way of Odyssey, returning as appuser on appdb. The connection uses sslmode=require, so it succeeds only because Odyssey presents its per VM TLS certificate on 6432; a plaintext client (sslmode=disable) is refused by the pooler. Odyssey terminates the client TLS and forwards the query to PostgreSQL over the loopback.

From a remote client, connect to the pooler over TLS with the per VM password from the credentials file (replace <vm-ip> with your VM's address and <ODYSSEY_PASSWORD> with the value from the file). Set the password as an environment variable so it does not appear in your shell history or process list:

export PGPASSWORD=<ODYSSEY_PASSWORD>
psql "host=<vm-ip> port=6432 dbname=appdb user=appuser sslmode=require" -c "SELECT now();"
unset PGPASSWORD

Prove real connection pooling

The reason to run a pooler is multiplexing: many client connections sharing a small number of backend database connections. This image ships a self test that proves it on your own VM. It opens more concurrent client connections through Odyssey than the backend pool size (pool_size is 10), each holding a transaction, then counts the backend PostgreSQL connections Odyssey actually opened:

sudo /usr/local/sbin/odyssey-selftest.sh

It reports that 24 concurrent clients were multiplexed onto no more than 10 backend server connections, reusing a small set of backend process IDs, and that wrong and blank passwords are rejected through the pooler while PostgreSQL stays loopback only. You can see the same effect live: open several clients that each hold a transaction, then count the backend connections directly on the box:

# In one shell, open 24 pooled clients that each hold a 15s transaction:
PW="$(sudo sed -n 's/^odyssey.password=//p' /root/odyssey-credentials.txt)"
for i in $(seq 1 24); do
  PGPASSWORD="$PW" psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require" \
    -qtAX -c 'BEGIN' -c 'SELECT pg_sleep(15)' -c 'COMMIT' >/dev/null 2>&1 &
done
# Then count the backend connections Odyssey opened to PostgreSQL:
sudo -u postgres psql -tAc \
  "SELECT count(*) FROM pg_stat_activity WHERE datname='appdb' AND usename='appuser';"

The backend count stays at or below 10 even with 24 clients connected — that gap is the pooling. Raise or lower pool_size in the appdb route of /etc/odyssey/odyssey.conf to tune it, then sudo systemctl restart odyssey.

Inspect the pooler with the Odyssey admin console

Odyssey exposes a built in admin console as a virtual console database. Connect to it as appuser (the per VM password is in the credentials file) to read live pooler statistics:

PGPASSWORD="$(sudo sed -n 's/^odyssey.password=//p' /root/odyssey-credentials.txt)" \
  psql "host=127.0.0.1 port=6432 dbname=console user=appuser sslmode=require" \
  -c "SHOW STATS;"

SHOW STATS reports per database transaction, query and wait counters for the pooler. Odyssey also writes periodic statistics to its journal, which you can follow with sudo journalctl -u odyssey.service -f.

Point Odyssey at your own PostgreSQL (optional)

To use Odyssey in front of an existing external PostgreSQL instead of the bundled one, edit the postgres_server storage block in /etc/odyssey/odyssey.conf, set host and port to your database, set storage_user, storage_db and storage_password on the appdb route to your database's credentials, then restart the pooler:

sudo systemctl restart odyssey.service

You can then stop and disable the bundled database with sudo systemctl disable --now postgresql.service if you no longer need it.

Security posture

• No baked credential. Both appuser and the postgres superuser ship without a password; the per VM passwords are generated only on first boot and stored in /root/odyssey-credentials.txt (mode 0600, root only). Odyssey will not start until first boot has filled its configuration, and the pooler configuration file is mode 0640 and never world readable.

• PostgreSQL is loopback only. The database listens on 127.0.0.1:5432 and refuses connections on the routable address, so the only way in is through Odyssey.

• TLS for every remote connection. Odyssey requires TLS on 6432. Each VM mints its own self signed certificate on first boot, so no certificate is shared between deployments.

• Local access stays password less through the unix socket for on box administration (sudo -u postgres psql).

• Network. Expose only TCP 22 and TCP 6432 in your NSG, and restrict 6432 to your application subnet. The NSG is the first layer, TLS and the per VM password the second.

To rotate the appuser password later, run sudo -u postgres psql and ALTER ROLE appuser PASSWORD '<new>';, update both the password and storage_password values on the appdb route in /etc/odyssey/odyssey.conf to match, restart the pooler with sudo systemctl restart odyssey, and update the credentials file.

Operations

Manage both services through systemd:

sudo systemctl is-active postgresql.service odyssey.service
systemctl show postgresql.service odyssey.service -p Id,ActiveState,SubState,MainPID

Both services should report active / running. Inspect logs at any time with sudo journalctl -u odyssey.service --no-pager | tail -20 or sudo journalctl -u postgresql@17-main.service --no-pager | tail -20.

Odyssey's configuration lives at /etc/odyssey/odyssey.conf. The PostgreSQL data directory lives under /var/lib/postgresql/17/main. Take a logical backup of a database with pg_dump, for example sudo -u postgres pg_dump appdb > appdb.sql.

Support

Every cloudimg image includes 24/7 support. If you have any questions about this Odyssey connection pooler image, contact us at support@cloudimg.co.uk.