Pgpool-II with PostgreSQL 18 on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of Pgpool-II with PostgreSQL 18 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

This is a pooled PostgreSQL database, not a standalone proxy. Two processes run on one VM:

• PostgreSQL 18 is the backend, bound to loopback only. It holds your data.
• Pgpool-II 4.7.2 is the front door, listening on port 9999 over TLS. Your application connects here.

Connection pooling is the reason to run it. PostgreSQL gives every client its own backend process, so a connection heavy application, a serverless function that reconnects constantly, or a framework with a per worker connection pool can exhaust max_connections long before it exhausts CPU or memory. Pgpool-II keeps a small set of backend connections open and reuses them across client sessions, so the database sees a stable, bounded number of connections no matter how many clients arrive.

On this image the effect is measurable rather than theoretical. The backend is configured with max_connections = 50, and Pgpool-II holds at most num_init_children x max_pool = 32 backend connections. 100 concurrent clients through the pooler are all served while the database holds only 33 connections. The same 100 clients sent straight at the database mostly get refused with too many clients already. The built in self test on every VM reproduces that result on demand.

Both Pgpool-II and PostgreSQL 18 are installed from the official PostgreSQL PGDG repository (apt.postgresql.org, GPG verified).

What is turned on, and what is not. This appliance is a single node, and the guide says so plainly rather than implying capabilities it does not have:

If you later add remote PostgreSQL backends, load balancing and streaming replication mode become meaningful. That path is documented at the end of this guide.

Security by design, with nothing baked into the image. The captured image contains no PostgreSQL cluster at all — no data directory, no cluster identifier, no role password hashes, and no pooler key material. On first boot each VM runs its own initdb and then generates:

• a unique password for the postgres superuser
• a unique password for the appuser application role
• the Pgpool-II pool_passwd entries, AES encrypted under a key unique to that VM
• a unique PCP (Pgpool-II control protocol) password
• a unique self signed TLS server certificate

They are written to the root only file /root/pgpool-ii-credentials.txt. Every secret is handed to the tool that consumes it on standard input, never as a command line argument, so no password is written into the system journal or the process table. The pooler service is gated so that it cannot start until those secrets exist.

What is included:

• PostgreSQL 18 from the official PGDG repository, on loopback 127.0.0.1:5432

• Pgpool-II 4.7.2 on 0.0.0.0:9999, TLS required, scram-sha-256 authentication

• A default database appdb owned by an application role appuser

• Per VM passwords, pooler key material and TLS certificate generated on first boot into a root only credentials file

• pool_hba.conf with no trust rule anywhere: remote clients are accepted only over TLS with a per VM password

• A built in self test, /usr/local/sbin/pgpool-ii-selftest.sh, that proves pooling works and that no common password opens the database

• Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

• Active Azure subscription, an SSH public key, and a VNet plus subnet in the target region

• Subscription to this listing on Azure Marketplace

• A Network Security Group allowing TCP 22 (administration) and TCP 9999 (the Pgpool-II pooler). In production, restrict 9999 to your application subnet. PostgreSQL on 5432 and the PCP control port on 9898 are bound to loopback and are deliberately not reachable from outside the VM.

Recommended virtual machine size: Standard_B2ms (2 vCPU, 8 GB RAM) for development and light workloads. For higher connection counts and throughput, choose a larger size such as Standard_E2s_v5 or above, and raise num_init_children to match.

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 pgpool-1 \
  --image <this-marketplace-image> \
  --size Standard_B2ms \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name pgpool-1 --port 9999 --priority 900

First boot creates the database cluster and mints every credential, which takes roughly fifteen seconds longer than a bare OS image.

Retrieve your per VM credentials

SSH in as azureuser and read the root only credentials file:

sudo ls -l /root/pgpool-ii-credentials.txt
sudo cat /root/pgpool-ii-credentials.txt

Expected output, with the secrets themselves masked here:

-rw------- 1 root root 1564 Sep 20 01:13 /root/pgpool-ii-credentials.txt

# --- connect THROUGH the pooler (this is the product's front door) -----------
pgpool.host=10.0.0.12
pgpool.port=9999
pgpool.database=appdb
pgpool.sslmode=require

# --- application role (use this from your application) -----------------------
app.role=appuser
app.password=****

# --- PostgreSQL superuser (administration) -----------------------------------
postgres.role=postgres
postgres.password=****

# --- PCP: the Pgpool-II control protocol, LOOPBACK ONLY on this VM -----------
pcp.user=pcpadmin
pcp.password=****
pcp.port=9898

# --- this VM's own PostgreSQL cluster identity -------------------------------
cluster.system_identifier=7687419290575647825

The cluster.system_identifier is this VM's own PostgreSQL cluster identity, created by its own initdb at first boot. Two VMs launched from this image will always show different values.

Confirm the services are healthy

systemctl is-active postgresql@18-main.service pgpool2.service pgpool-ii-firstboot.service
pgpool --version
psql --version
ss -tlnH | awk '{print $4}' | sort -u
sudo -u postgres psql -tAc 'SHOW max_connections;'

All three units report active. The listener list is the important part: only 22 and 9999 are bound to a routable address. PostgreSQL on 5432 and PCP on 9898 appear only on 127.0.0.1, so the pooler is the sole data path into this VM.

Connect through the pooler

Your application connects to Pgpool-II, not to the database. From the VM itself:

APP_PW=$(sudo sed -n 's/^app\.password=//p' /root/pgpool-ii-credentials.txt)
PGPASSWORD="$APP_PW" psql "host=127.0.0.1 port=9999 dbname=appdb user=appuser sslmode=require" \
  -x -c "SELECT current_user, current_database(), substring(version() from 'PostgreSQL [0-9.]+') AS backend;"

From your application or your laptop, use the VM's address and the password from the credentials file:

PGPASSWORD='<APP_PASSWORD>' psql "host=<vm-ip> port=9999 dbname=appdb user=appuser sslmode=require"

A standard PostgreSQL connection string works unchanged — only the port differs. Any PostgreSQL driver, ORM or connection pool library can talk to Pgpool-II because it speaks the PostgreSQL wire protocol.

TLS is mandatory. A connection that asks for sslmode=disable matches no rule in pool_hba.conf and is refused:

This block asserts the refusal, so it succeeds only if the non TLS connection is actually rejected:

APP_PW=$(sudo sed -n 's/^app\.password=//p' /root/pgpool-ii-credentials.txt)
OUT=$(PGPASSWORD="$APP_PW" psql "host=127.0.0.1 port=9999 dbname=appdb user=appuser sslmode=disable connect_timeout=10" \
  -qtAc "SELECT 'CONNECTED';" 2>&1 || true)
if printf '%s' "$OUT" | grep -q CONNECTED; then
  echo "UNEXPECTED: a non-TLS connection was accepted"
  exit 1
fi
printf '%s' "$OUT" | grep -q pool_hba || { echo "UNEXPECTED: refused, but not by pool_hba"; exit 1; }
echo "Refused as expected. Pgpool-II reported:"
printf '%s\n' "$OUT" | grep -o 'no pool_hba.conf entry.*'

Expected output:

Refused as expected. Pgpool-II reported:
no pool_hba.conf entry for host "127.0.0.1", user "appuser", database "appdb", SSL off

Prove the pooling actually works

The image ships a self test that demonstrates the pooler doing its job and confirms no common password opens the database:

sudo /usr/local/sbin/pgpool-ii-selftest.sh

Expected output:

  application role authenticates through Pgpool-II on :9999 over TLS
  SHOW POOL_NODES reports backend 0 status=up
  80/80 concurrent clients served while the backend held only 33 connections (bound 32, max_connections 50)
  blank, postgres, password, pgpool and admin are all refused on both built-in roles
  a non-TLS connection is refused by pool_hba
  this VM's PostgreSQL cluster system identifier: 7687419290575647825
PGPOOL_SELFTEST_OK

The third line is the one that matters. More clients were served concurrently than the database would accept on its own, and the database never saw more than the pooler's structural limit.

You can watch the same effect directly. Open many clients through the pooler and sample the backend's connection count while they are in flight:

APP_PW=$(sudo sed -n 's/^app\.password=//p' /root/pgpool-ii-credentials.txt)
for i in $(seq 1 100); do
  ( PGPASSWORD="$APP_PW" psql "host=127.0.0.1 port=9999 dbname=appdb user=appuser sslmode=require connect_timeout=45" \
      -qtAc "SELECT pg_sleep(3); SELECT 'CLIENT_OK';" >/tmp/pool-client-$i.out 2>&1 </dev/null ) &
done
sleep 4
sudo -u postgres psql -tAc "SELECT count(*) FROM pg_stat_activity WHERE backend_type='client backend';"
wait
echo "served: $(grep -l CLIENT_OK /tmp/pool-client-*.out | wc -l) / 100"
rm -f /tmp/pool-client-*.out

All 100 clients are served, while the backend count printed mid flight stays around 33. Send the same 100 clients straight at PostgreSQL on 5432 and most are refused with too many clients already — that contrast is precisely the value Pgpool-II adds.

Inspect the pool

Pgpool-II answers its own SHOW commands on the pooler port, using the postgres superuser credential:

PG_PW=$(sudo sed -n 's/^postgres\.password=//p' /root/pgpool-ii-credentials.txt)
PGPASSWORD="$PG_PW" psql "host=127.0.0.1 port=9999 dbname=appdb user=postgres sslmode=require" \
  -c "SHOW POOL_NODES;"
PGPASSWORD="$PG_PW" psql "host=127.0.0.1 port=9999 dbname=appdb user=postgres sslmode=require" \
  -qtAc "SHOW POOL_VERSION;"

SHOW POOL_NODES reports the backend's status and pg_status as up. Other useful commands are SHOW POOL_POOLS, SHOW POOL_PROCESSES and SHOW POOL_BACKEND_STATS.

The PCP control protocol is available on loopback only, using the pcpadmin credential from the credentials file:

PCP_PW=$(sudo sed -n 's/^pcp\.password=//p' /root/pgpool-ii-credentials.txt)
PCP_U=$(sudo sed -n 's/^pcp\.user=//p' /root/pgpool-ii-credentials.txt)
umask 077; printf '127.0.0.1:9898:%s:%s\n' "$PCP_U" "$PCP_PW" > /tmp/pcppass
PCPPASSFILE=/tmp/pcppass pcp_node_count -h 127.0.0.1 -p 9898 -U "$PCP_U" -w
PCPPASSFILE=/tmp/pcppass pcp_node_info  -h 127.0.0.1 -p 9898 -U "$PCP_U" -w -n 0
rm -f /tmp/pcppass

Administer the database

Local, on box administration needs no password at all — the unix socket uses peer authentication:

sudo -u postgres psql -d appdb -c "\dt"
sudo -u postgres psql -tAc "SELECT system_identifier FROM pg_control_system();"

Create your application's schema through the pooler exactly as you would against any PostgreSQL server. The appuser role owns appdb, so it can create tables in the public schema without further grants.

Tuning the pool

The pooler configuration lives at /etc/pgpool2/pgpool.conf. The two settings that matter most:

• num_init_children (default 32) — how many clients Pgpool-II serves simultaneously. Clients beyond this number wait for a free child rather than being refused.
• max_pool (default 1) — how many backend connections each child caches.

Backend connections are bounded by num_init_children x max_pool, so that product must stay below the backend's max_connections, which is 50 on this image. If you raise num_init_children, raise max_connections in /etc/postgresql/18/main/conf.d/10-cloudimg.conf to match and restart both services.

The in memory query cache is off by default. To evaluate it, set memory_cache_enabled = on in pgpool.conf and restart the pooler. Be deliberate: cached results are served without consulting the backend, so only enable it for read patterns that tolerate it.

Adding remote backends later

Load balancing and streaming replication become meaningful once there is more than one backend. To add a remote PostgreSQL server, append a second backend stanza to /etc/pgpool2/pgpool.conf, switch the clustering mode, and enable load balancing:

backend_clustering_mode = 'streaming_replication'
load_balance_mode = on

backend_hostname1 = '10.0.0.20'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/18/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'standby1'

The remote server must already be a streaming replica of this VM's database, must accept connections from this VM, and must know the same role passwords. Pgpool-II's own documentation on /usr/share/doc/pgpool2/ covers replication setup in full.

Security posture

• No credential is baked into the image. The image ships with no database cluster at all, so there is no password hash, no data and no cluster identity to inherit. Every VM creates its own at first boot.

• Every secret is unique per VM — the postgres and appuser passwords, the pool_passwd AES key material, the PCP password, the TLS certificate and key, the SSH host keys and the machine ID.

• Secrets never reach the journal or the process table. Passwords are passed to PostgreSQL, pg_enc and pg_md5 on standard input, never as command line arguments.

• No trust rule exists in pool_hba.conf or in the backend's pg_hba.conf. Remote clients must present TLS and a scram-sha-256 password; a non TLS connection matches no rule and is refused.

• Only two ports are reachable from outside the VM: 22 and 9999. PostgreSQL and the PCP control port are bound to loopback.

• Common passwords do not work. Blank, postgres, password, pgpool and admin are all refused on both built in roles. The shipped self test re-proves this on demand.

• The build account does not exist in the shipped image, and SSH is configured with PermitRootLogin no and PasswordAuthentication no.

• In production, restrict 9999 in your Network Security Group to your application subnet, and replace the self signed certificate with one your clients trust if you intend to use sslmode=verify-full.

Operations

Service control:

sudo systemctl status pgpool2.service --no-pager
sudo systemctl status postgresql@18-main.service --no-pager

Logs:

sudo journalctl -u pgpool2.service -n 50 --no-pager

Back up the database with the standard PostgreSQL tools, connecting on the loopback backend port so the dump does not occupy a pooler child:

sudo -u postgres pg_dump -Fc -d appdb -f /var/lib/postgresql/appdb.dump
ls -lh /var/lib/postgresql/appdb.dump

Security updates are applied automatically by unattended-upgrades. After a kernel update, reboot at a convenient time; both services start automatically and first boot does not run again.

Support

cloudimg images are supported 24/7. Contact support@cloudimg.co.uk with the VM size, region, and the output of sudo /usr/local/sbin/pgpool-ii-selftest.sh, which summarises the pooler's health without revealing any secret.