Pgpool-II

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 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, 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:pgpool-ii-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name pgpool-1 \
  --image sac1647283583153:pgpool-ii-ubuntu-24-04:default:latest \
  --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 generates every credential. This takes roughly fifteen seconds longer than a bare OS image.

Retrieve your per-VM credentials

Connect over SSH 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 (the secrets are 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 always show different values.

Confirm the services are healthy

Check the services, the versions and the listening ports:

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 only data path into this VM.

Connect through the pooler

Your application connects to Pgpool-II, not to the database. To connect from the VM itself, run:

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.

The following commands assert the refusal, so they succeed 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 that 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. The pooler served more concurrent clients than the database would accept on its own, and the database never saw more connections 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. If you send the same 100 clients straight at PostgreSQL on 5432, most are refused with too many clients already. That contrast is the value Pgpool-II adds.

Inspect the pool

Pgpool-II answers its own SHOW commands on the pooler port. Use 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. Use 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 administration on the VM needs no password, because 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. Two settings matter most:

• num_init_children (default 32) sets how many clients Pgpool-II serves simultaneously. Clients beyond this number wait for a free child instead of being refused.
• max_pool (default 1) sets 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-sac.conf to match, then 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. Enable it deliberately. Cached results are served without consulting the backend, so use it only for read patterns that tolerate that.

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. The Pgpool-II documentation in /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. This covers 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. If you intend to use sslmode=verify-full, replace the self-signed certificate with one your clients trust.

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. Connect 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

unattended-upgrades applies security updates automatically. After a kernel update, reboot at a convenient time. Both services start automatically, and first boot does not run again.

Support

SecureAnyCloud supports this image 24/7. Contact sales@secureanycloud.com with the VM size, the region, and the output of sudo /usr/local/sbin/pgpool-ii-selftest.sh. The output summarizes the pooler's health without revealing any secret.

Deploy on Azure

Launch Pgpool-II with PostgreSQL 18 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