PgCat

Prerequisites

• An active Azure subscription, an SSH public key, and a VNet with a subnet in the target region
• A subscription to this listing on Azure Marketplace
• A network security group (NSG) that allows 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 and open SSH and the pooler port to your own network:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:pgcat-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name pgcat-1 \
  --image sac1647283583153:pgcat-ubuntu-24-04:default:latest \
  --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 first boot, the VM generates its own passwords and TLS certificate. Connect over SSH and read the root-only credentials file:

sudo cat /root/pgcat-credentials.txt

The file is mode 0600 and owned by root. It 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, and every deployed VM has its own passwords and certificate.

Confirm the services are healthy

Check that PgCat and PostgreSQL are both active and listening. The pooler listens on 6432, and the database listens on loopback 5432:

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

Both services report active, PgCat listens on 0.0.0.0:6432, and PostgreSQL listens on 127.0.0.1:5432.

Connect through the pooler

On the VM, you can verify the full pooled path in one step. The command reads the per-VM password from the credentials file and connects 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. The \conninfo output confirms that the client connection to the pooler is an SSL connection on TLSv1.3, encrypted 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 to watch pools and traffic. Its per-VM password is in the credentials file.

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 for 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-in credentials. 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 does not 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 creates 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, which reduces 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, and TLS plus the per-VM password are the second.

To rotate the superuser password later, complete these steps. 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 the 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 is under /etc/pgcat/tls/. The PostgreSQL data directory is 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 SecureAnyCloud image includes 24/7 support. For questions about this PgCat with PostgreSQL image or help with your deployment, contact us at sales@secureanycloud.com.

Deploy on Azure

Launch PgCat PostgreSQL Connection Pooler with PostgreSQL 17 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