Streaming & Messaging Azure

PostgreSQL 18 with PGMQ on Ubuntu 24.04 on Azure User Guide

| Product: PostgreSQL 18 with PGMQ on Ubuntu 24.04 on Azure

Overview

This guide covers the deployment and use of PostgreSQL 18 with PGMQ on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. PGMQ is an open source message queue that runs entirely inside PostgreSQL. It gives you the semantics you expect from a queue, visibility timeouts, delivery counting, archiving, partitioned and unlogged queues, but the queue is a set of ordinary Postgres tables and the API is plain SQL. There is no broker to install, no second service to monitor, and no separate cluster to keep alive.

The reason teams reach for it is transactional integrity. Because the queue lives in the same database as your data, enqueuing a job and writing the row that job refers to happen in one transaction. Either both commit or neither does, so you never publish a message about a row that was rolled back, and you never lose a message that a committed write depended on. That single property removes a whole category of dual write bugs that appear when the queue is a separate system.

PostgreSQL 18 and pg_partman come from the official PostgreSQL PGDG repository. PGMQ 1.12.0 is built from the pinned upstream release with PGXS and is pre enabled (CREATE EXTENSION pgmq) in a default database named queuedb, which also ships a small demo_queue holding two messages so you can run pgmq.read the moment the VM boots.

Security by design, no baked credential. The postgres superuser role is password less by construction: a fresh install leaves it with no password, and the captured image ships that way, so there is no default or blank password to discover. On first boot each VM generates a unique password for the postgres role and a unique self signed TLS server certificate, writes them to the root only file /root/pgmq-credentials.txt, then enables TLS. Remote clients are accepted only over TLS (hostssl with scram-sha-256); on box administration stays password less through the local unix socket. Until first boot mints the certificate, the database is not reachable off box at all.

What is included:

  • PostgreSQL 18 from the official PGDG repository, running under systemd as postgresql.service

  • PGMQ 1.12.0 pre enabled in a default database queuedb, with a seeded demo_queue

  • pg_partman 5.5.0, which PGMQ requires for partitioned queues, enabled in a dedicated partman schema

  • A built in self test at /usr/local/sbin/pgmq-selftest.sh that proves the whole queue lifecycle over TLS

  • A per VM postgres password and a per VM TLS certificate generated on first boot, written to a root only credentials file

  • pg_hba.conf configured so remote clients connect only over TLS with the per VM password, while local access stays password less via the unix socket

  • 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 5432 (the PostgreSQL wire protocol) from your client network. In production, restrict 5432 to your application subnet.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development and light workloads. Queue throughput is bound by write throughput, so for busy queues choose a size with faster storage and more RAM, such as Standard_D2s_v5 or larger.

Deploy the virtual machine

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

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

Retrieve your per VM credentials

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

sudo cat /root/pgmq-credentials.txt

The file is mode 0600, owned by root, and contains the per VM password plus a ready to paste TLS connection string. Nothing is baked into the image, so every deployed VM has its own password and its own certificate.

Terminal showing the per VM credentials file at /root/pgmq-credentials.txt with mode 600 owned by root, listing the postgres host, port 5432, role postgres, the per VM password masked, database queuedb and sslmode require, followed by a ready to use TLS psql connection string and three starter pgmq statements

Confirm the service is healthy

Check that PostgreSQL is active and listening, that TLS is on, and that both extensions are enabled in the default database:

sudo systemctl is-active postgresql.service
sudo ss -tlnp | grep 5432
sudo -u postgres psql -tAc 'SHOW ssl;'
sudo -u postgres psql -d queuedb -c "SELECT extname, extversion FROM pg_extension WHERE extname IN ('pgmq','pg_partman') ORDER BY extname;"

You should see the service active, PostgreSQL listening on 5432, ssl reported as on, and both pgmq at 1.12.0 and pg_partman at 5.5.0.

Terminal showing postgresql.service active and postgresql at 18 main running under systemd, PostgreSQL listening on port 5432 on both IPv4 and IPv6, SHOW ssl returning on, and the pg_extension query returning pg_partman 5.5.0 and pgmq 1.12.0 in the queuedb database

Send and read your first message

Everything PGMQ does is a SQL function call in the pgmq schema. Create a queue, send a JSONB message, read it back, then archive it:

sudo -u postgres psql -d queuedb <<'SQL'
SELECT pgmq.create('orders');
SELECT * FROM pgmq.send('orders', '{"order_id": 1001, "action": "ship"}');
SELECT * FROM pgmq.send('orders', '{"order_id": 1002, "action": "refund"}');
SELECT msg_id, read_ct, message FROM pgmq.read('orders', 30, 2);
SELECT pgmq.archive('orders', 1);
SELECT msg_id, message FROM pgmq.a_orders;
SELECT queue_name, queue_length, total_messages FROM pgmq.metrics('orders');
SELECT pgmq.drop_queue('orders');
SQL

pgmq.send returns the msg_id of each message. pgmq.read('orders', 30, 2) takes up to two messages and hides them from every other consumer for 30 seconds, the visibility timeout: this is what stops two workers processing the same job. Each returned row carries read_ct, the number of delivery attempts, which is how you spot a poison message that keeps failing. pgmq.archive moves a message out of the queue table pgmq.q_orders and into the archive table pgmq.a_orders, so you keep an auditable history instead of deleting it. Use pgmq.delete instead if you do not want to retain it.

Terminal showing a full PGMQ round trip in the queuedb database: creating a queue named orders, sending two JSONB messages that return msg_id 1 and 2, reading both back with read_ct 1 and their payloads intact, archiving message 1 which returns t, the archived message appearing in the pgmq.a_orders table, and metrics reporting queue_length 1 with total_messages 2

If a worker crashes before archiving a message, the visibility timeout simply expires and the message becomes visible again for another worker to pick up. Nothing is lost.

Verify the appliance with the built in self test

Every image ships a self test that connects over TLS with this VM's own credentials and exercises the complete lifecycle, then proves a wrong password is rejected:

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

It exits 0 only if every step held. Run it after deployment, after a restore, or any time you want a fast end to end proof that the appliance is healthy.

Terminal showing the pgmq selftest script output: connected over TLS with the per VM credential, pgmq extension 1.12.0 live in database queuedb, a message sent and read back with payload intact, the visibility timeout honoured, the message archived and the queue drained to zero, the throwaway queue dropped, and a deliberately wrong password rejected, followed by a pg_stat_ssl query returning ssl t and TLSv1.3

Connect from a remote client

On the VM itself you can connect as the postgres superuser with no password, through the local unix socket:

sudo -u postgres psql -d queuedb -c "SELECT version();"

From a remote client, connect over TLS with the per VM password from the credentials file (replace <vm-ip> with your VM's address and <POSTGRES_PASSWORD> with the value from the file):

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<vm-ip> port=5432 dbname=queuedb user=postgres sslmode=require" -c "SELECT ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

The pg_stat_ssl view confirms the connection is encrypted (ssl is t) and reports the negotiated TLS version. Any PostgreSQL driver works unchanged, because PGMQ is only SQL: your application calls pgmq.send and pgmq.read the same way it runs any other query, inside your existing transactions.

Queue types and maintenance

pgmq.create makes a standard durable queue. Two other shapes are available:

sudo -u postgres psql -d queuedb <<'SQL'
SELECT pgmq.create_unlogged('fast_queue');
SELECT pgmq.create_partitioned('events_queue');
SELECT queue_name, is_partitioned, is_unlogged FROM pgmq.meta ORDER BY queue_name;
SELECT pgmq.drop_queue('fast_queue');
SELECT pgmq.drop_queue('events_queue');
SQL

Unlogged queues skip the write ahead log for much higher throughput, at the cost of losing their contents if the server crashes: use them for work that is cheap to regenerate. Partitioned queues spread a queue across time or id ranges via pg_partman, which makes retention and the pruning of old messages far cheaper on very high volume queues. pg_partman is installed and enabled in this image for exactly this reason, so pgmq.create_partitioned works out of the box.

pgmq.meta lists every queue on the appliance and which shape it is. For partitioned queues, drive partition maintenance on your own schedule by calling partman.run_maintenance_proc(); the pg_partman background worker is deliberately not preloaded so you keep control of when maintenance runs.

Security posture

  • No baked credential. The postgres role ships password less; the per VM password is generated only on first boot and stored in /root/pgmq-credentials.txt (mode 0600, root only). No other login role exists in the image.

  • TLS for every remote connection. pg_hba.conf permits remote clients only via hostssl ... scram-sha-256. 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).

  • No build toolchain ships. PGMQ is compiled at build time and the PGXS toolchain is then removed, so the image carries no development packages it does not need.

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

To rotate the password later, run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'" and update the credentials file to match.

Operations

Manage the service through systemd and inspect logs with journalctl:

sudo systemctl status postgresql.service --no-pager
sudo journalctl -u postgresql@18-main.service --no-pager | tail -20

Take a logical backup of a database with pg_dump, for example sudo -u postgres pg_dump queuedb > queuedb.sql. Because queues are ordinary tables, a normal Postgres backup captures your queue state and its archive along with the rest of your data, and any existing PostgreSQL monitoring or replication you already run covers the queue too. The data directory lives under /var/lib/postgresql/18/main.

Support

Every cloudimg image includes 24/7 support. If you have any questions about this PostgreSQL with PGMQ image, contact us at support@cloudimg.co.uk.