Deployment guide

PostgreSQL PgBouncer

Overview

This guide shows how to deploy and use PostgreSQL 17 with the PgBouncer connection pooler on Ubuntu 24.04 on Azure, using SecureAnyCloud Azure Marketplace images. It pairs the PostgreSQL 17 relational database with PgBouncer, the lightweight connection pooler maintained by the PostgreSQL community.

Clients connect through PgBouncer on port 6432 in transaction pooling mode. PgBouncer multiplexes many short-lived client connections onto a small pool of backend PostgreSQL sessions. A connection-heavy application stays responsive without exhausting the database with one backend process per client. PostgreSQL itself remains directly reachable on 5432 for administration and migrations.

Both PostgreSQL 17 and PgBouncer are installed from the official PostgreSQL PGDG repository. The pooler is configured in transaction mode with scram-sha-256 authentication and TLS on the client-facing port. It routes to the local PostgreSQL backend on 127.0.0.1:5432.

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 passwordless by construction: a fresh install leaves it with no password, and the captured image ships that way. PgBouncer ships with no user list at all, and the pooler service does not start until first boot has written the per-VM credentials.

On first boot, each VM generates a unique password for the postgres superuser, a unique password for the PgBouncer admin console user (pgbadmin), and a unique self-signed TLS server certificate. It writes the passwords to the root-only file /root/postgresql-pgbouncer-credentials.txt, then starts the pooler. The VM accepts remote clients only over TLS with scram-sha-256. On-box administration stays passwordless through the local unix socket.

What you get:

PostgreSQL 17 from the official PGDG repository, running under systemd as postgresql.service
PgBouncer in transaction pooling mode on port 6432, running as pgbouncer.service and pooling to the local PostgreSQL backend
A default database appdb, reachable both through the pooler and directly
Per-VM passwords for the postgres superuser and the pgbadmin pooler admin, plus a per-VM TLS certificate, all generated on first boot and written to a root-only credentials file
pg_hba.conf and PgBouncer configured so remote clients connect only over TLS with a per-VM password, while local access stays passwordless through the unix socket
Unattended security upgrades left enabled, so the appliance keeps receiving patches

Prerequisites

An active Azure subscription, an SSH public key, and a VNet and subnet in the target region
A subscription to this listing on Azure Marketplace
A Network Security Group that allows TCP 22 (administration) and TCP 6432 (the PgBouncer pooler). Also open TCP 5432 if you want direct PostgreSQL access. In production, restrict both database ports to your application subnet.

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:postgresql-pgbouncer-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name pgbouncer-1 \
  --image sac1647283583153:postgresql-pgbouncer-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 pgbouncer-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/postgresql-pgbouncer-credentials.txt

The file is mode 0600 and owned by root. It lists the postgres superuser password, which works both directly on 5432 and pooled through PgBouncer on 6432. It also lists the pgbadmin pooler admin password and 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 PostgreSQL and PgBouncer are active and listening, and that PostgreSQL TLS is on:

sudo systemctl is-active postgresql.service pgbouncer.service
sudo ss -tln | grep -E ':5432|:6432'
sudo -u postgres psql -tAc 'SHOW ssl;'

Both services report active, PostgreSQL listens on 5432 and PgBouncer on 6432, and ssl is reported as on.

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

PGPASSWORD="$(sudo sed -n 's/^postgres.password=//p' /root/postgresql-pgbouncer-credentials.txt)" \
  psql "host=127.0.0.1 port=6432 dbname=appdb user=postgres sslmode=require" \
  -c "SELECT version();" \
  -c "SELECT ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

The query runs against PostgreSQL 17 through PgBouncer. The pg_stat_ssl row confirms that the connection is encrypted (ssl is t) and reports the negotiated TLS version.

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

PgBouncer exposes a virtual admin database. Connect to it as the pgbadmin user, whose per-VM password is in the credentials file. Use it to watch pools, clients and servers, and to confirm the pool mode:

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

SHOW POOLS lists one pool per database and user, with live client and server counts. The pool_mode column shows transaction.

SHOW CONFIG reports the full pooler configuration, including pool_mode = transaction, listen_port = 6432 and auth_type = scram-sha-256.

Connect directly to PostgreSQL

The database also remains reachable directly on 5432 for administration and migrations. On the VM, connect as the postgres superuser through the local unix socket, with no password:

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

From a remote client, connect directly over TLS with the per-VM password. Replace <vm-ip> and <POSTGRES_PASSWORD> with your values:

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

Use the pooler on 6432 for your application's connection-heavy traffic. Use the direct port 5432 for admin sessions, migrations and tools that hold long-lived connections.

Security posture

No baked credential. Both the postgres role and the pgbadmin pooler admin ship without a password. The per-VM passwords are generated only on first boot and stored in /root/postgresql-pgbouncer-credentials.txt (mode 0600, root only). PgBouncer does not start until first boot has written its user list.

TLS for every remote connection. The pooler requires TLS on 6432, and pg_hba.conf permits remote PostgreSQL clients only through hostssl ... scram-sha-256. Each VM creates its own self-signed certificate on first boot, so no certificate is shared between deployments.

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

Network. Expose only TCP 22, TCP 6432 and, if needed, TCP 5432 in your NSG, and restrict the database ports to your application subnet. The NSG is the first layer. TLS and the per-VM passwords are the second.

To rotate the superuser password later, run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'". Update the postgres entry in /etc/pgbouncer/userlist.txt to match, reload the pooler with sudo systemctl reload pgbouncer, and update the credentials file.

Operations

Manage both services through systemd:

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

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

PgBouncer's configuration lives at /etc/pgbouncer/pgbouncer.ini, and its user list lives at /etc/pgbouncer/userlist.txt. 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. If you have any questions about this PostgreSQL with PgBouncer image, contact us at sales@secureanycloud.com.

Deploy on Azure

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

Deploy on Azure