Databases Azure

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

| Product: PostgreSQL 17 with PgBouncer Connection Pooler on Ubuntu 24.04 on Azure

Overview

This guide covers the deployment and use of PostgreSQL 17 with the PgBouncer connection pooler on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It pairs the proven 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, so 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, routing 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 password less 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 will 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, writes them to the root only file /root/postgresql-pgbouncer-credentials.txt, then starts the pooler. Remote clients are accepted only over TLS with scram-sha-256; on box administration stays password less through the local unix socket.

What is included:

  • 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, 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 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 6432 (the PgBouncer pooler). Open TCP 5432 as well if you also 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, opening SSH and the pooler port to your own network:

az vm create \
  --resource-group my-rg \
  --name pgbouncer-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 pgbouncer-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/postgresql-pgbouncer-credentials.txt

The file is mode 0600, owned by root, and lists the postgres superuser password (used both directly on 5432 and pooled through PgBouncer on 6432) and the pgbadmin pooler admin password, plus ready to paste connection strings. Nothing is baked into the image — every deployed VM has its own passwords and certificate.

Terminal showing the per VM credentials file at /root/postgresql-pgbouncer-credentials.txt with mode 600 owned by root, listing the postgres host, ports 5432 and 6432, the postgres role, the per VM password (masked), database appdb, sslmode require, and the pgbouncer admin user pgbadmin with its own per VM password (masked)

Confirm the services are healthy

Check that both 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;'

You should see both services report active, PostgreSQL listening on 5432 and PgBouncer on 6432, and ssl reported as on.

Terminal showing postgresql.service and pgbouncer.service both active, PostgreSQL listening on port 5432 and PgBouncer listening on port 6432, and SHOW ssl returning on, confirming the database and the pooler are both healthy and TLS is enabled

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 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 by way of PgBouncer, and the pg_stat_ssl row confirms the connection is encrypted (ssl is t) and reports the negotiated TLS version.

Terminal showing a psql client connecting through PgBouncer on port 6432 with sslmode require, returning the PostgreSQL 17.10 server version banner and a pg_stat_ssl row where ssl is t and the TLS version is TLSv1.3, proving the pooled connection reaches PostgreSQL and is encrypted end to end

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 (its per VM password is in the credentials file) 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, and 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.

Terminal showing the PgBouncer admin console reached as user pgbadmin: SHOW POOLS listing an appdb pool for the postgres user with pool_mode transaction, and SHOW CONFIG reporting pool_mode transaction, listen_port 6432, auth_type scram-sha-256, default_pool_size 20 and max_client_conn 200

Connect directly to PostgreSQL

The database also remains reachable directly on 5432 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;"

From a remote client, connect directly over TLS with the per VM password (replace <vm-ip> and <POSTGRES_PASSWORD>):

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, and 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 will 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 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).

  • 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 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 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 at /etc/pgbouncer/userlist.txt. 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 PostgreSQL with PgBouncer image, contact us at support@cloudimg.co.uk.