PgCat PostgreSQL Connection Pooler with PostgreSQL 17 on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of PgCat with PostgreSQL 17 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It pairs PgCat, the open source PostgreSQL connection pooler and proxy written in Rust, with the proven PostgreSQL 17 relational database on a single machine. Clients connect through PgCat on port 6432 in transaction pooling mode; PgCat 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.

PgCat is the single public entry point: the bundled PostgreSQL 17 backend is bound to the loopback interface (127.0.0.1:5432) and is reached only through the pooler. PgCat is built from the pinned upstream v1.2.0 source and runs under systemd; clients authenticate to it over TLS, and PgCat in turn authenticates to the local PostgreSQL backend with scram-sha-256. PgCat also exposes an administrative console (SHOW POOLS, SHOW STATS) for live pool and traffic statistics.

Security by design — no baked credential. There are two built in accounts and neither has a password baked into the image. The postgres superuser role is password less by construction: a fresh install leaves it with no password, and the captured image ships that way. PgCat ships with no configuration file at all, so no pooler or admin credential exists in the image, and the pooler service will not start until first boot has written the per VM configuration. On first boot each VM generates a unique password for the postgres superuser, a unique password for the PgCat admin console user (pgcat_admin), and a unique self signed TLS server certificate, writes them to the root only file /root/pgcat-credentials.txt, then starts the pooler.

What is included:

• PgCat v1.2.0 in transaction pooling mode on port 6432, running under systemd as pgcat.service, pooling to the local PostgreSQL backend

• PostgreSQL 17 from the official PGDG repository, running under systemd as postgresql.service, bound to loopback behind the pooler

• A default database appdb, reachable through the pooler

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

• A pooler service gated on first boot so it never starts with a default or absent credential

• 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 PgCat pooler). In production, restrict the pooler port to your application subnet. The bundled PostgreSQL backend is loopback only and is never exposed directly.

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.

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 pgcat-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 pgcat-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/pgcat-credentials.txt

The file is mode 0600, owned by root, and lists the postgres superuser password (used to connect through PgCat on 6432) and the pgcat_admin pooler admin password, 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 PgCat and PostgreSQL are active and listening — the pooler on 6432 and the database on loopback 5432:

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

You should see both services report active, PgCat listening on 0.0.0.0:6432 and PostgreSQL listening on 127.0.0.1:5432.

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 PgCat on 6432:

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

The SELECT version() query runs against PostgreSQL 17 by way of PgCat, and \conninfo confirms the client connection to the pooler is an SSL connection on TLSv1.3, encrypted end to end from the client to PgCat.

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 <POSTGRES_PASSWORD> with the value from the file):

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<vm-ip> port=6432 dbname=appdb user=postgres sslmode=require" -c "SELECT now();"

Inspect the pooler admin console

PgCat exposes a virtual admin database called pgcat. Connect to it as the pgcat_admin user (its per VM password is in the credentials file) to watch pools and traffic:

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

SHOW POOLS lists one row per pooled database and user with live client and server counts, and the pool_mode column shows transaction. SHOW STATS reports per pool traffic counters — transactions, queries, bytes received and sent, and timing — so you can watch the pooler's throughput in real time.

Administer the database on the box

The bundled PostgreSQL backend is loopback only and is not exposed to the network — the pooler on 6432 is the single entry point for applications. For administration and migrations on the VM, connect as the postgres superuser with no password through the local unix socket:

sudo -u postgres psql -d appdb -c "SELECT current_database(), current_user;"

Use the pooler on 6432 for your application's connection heavy traffic; use the on box unix socket for admin sessions, migrations and maintenance tools.

Security posture

• No baked credential. Both the postgres role and the pgcat_admin pooler admin ship without a password; the per VM passwords are generated only on first boot and stored in /root/pgcat-credentials.txt (mode 0600, root only). PgCat ships with no configuration file, so it will not even start until first boot has written the per VM configuration.

• TLS on the pooler. PgCat presents a per VM self signed certificate on 6432, so clients can connect with sslmode=require and encrypt the connection; each VM mints its own certificate on first boot, so no certificate is shared between deployments.

• The database is loopback only. PostgreSQL is bound to 127.0.0.1 and is reached only through PgCat, reducing the exposed surface to the single pooler port.

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

To rotate the superuser password later, run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'", update the password for the postgres user in /etc/pgcat/pgcat.toml to match, reload the pooler with sudo systemctl restart pgcat, and update the credentials file.

Operations

Manage both services through systemd:

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

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

PgCat's configuration lives at /etc/pgcat/pgcat.toml and its per VM TLS certificate under /etc/pgcat/tls/. 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 PgCat with PostgreSQL image, contact us at support@cloudimg.co.uk.