PostgreSQL 17 PgBouncer

Option A: AWS Marketplace console

• Open the SecureAnyCloud Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
• When the subscription is active, choose Launch your software.
• Choose One-click launch from AWS Marketplace, or choose Launch from EC2 Console for the full launch wizard. In Version, choose PostgreSQL 17 + PgBouncer on Ubuntu 24.04, then choose your Region.
• Pick an instance type and your key pair. m5.large is recommended, and the software charge is $0.04 per vCPU per hour.
• Set the security group. The listing's default group allows SSH (22) only. Restrict it to your own IP address, and do not open 6432 or 5432 to the internet.
• Choose your VPC and subnet and leave the metadata defaults, because the image already requires IMDSv2. Then choose Launch.

Option B: AWS CLI

Run these commands on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page. Replace the VPC, subnet, key pair and CIDR with your own values:

SG=$(aws ec2 create-security-group --group-name pgbouncer \
  --description "PostgreSQL + PgBouncer" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pgbouncer}]'

You do not need --metadata-options, because the AMI already requires IMDSv2.

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. It creates this instance's TLS key and certificate, turns TLS on and sets the appuser and pgbadmin passwords. It then starts PgBouncer, checks that it can log in through the pooler, and writes the credentials file.

Show the connection details. This command leaves out the two password lines. Read them with sudo cat /root/postgresql-pgbouncer-credentials.txt and keep them in your secrets manager:

sudo grep -vE '^(APP_PASSWORD|PGBOUNCER_ADMIN_PASSWORD)=' /root/postgresql-pgbouncer-credentials.txt

# PostgreSQL 17 + PgBouncer - generated on first boot by postgresql-pgbouncer-firstboot.service
# These passwords are unique to this instance. Store them somewhere safe. APP_USERNAME=appuser APP_DATABASE=appdb PGBOUNCER_HOST=127.0.0.1 PGBOUNCER_PORT=6432 PGBOUNCER_ADMIN_USER=pgbadmin POSTGRES_PORT=5432 PRIVATE_IP=<private-ip> TLS_CERTIFICATE=/etc/postgresql/17/main/sac-server.crt
# Connect THROUGH PgBouncer (transaction pooling, TLS required) on the instance:
# psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require" ...

The commands below read the passwords from that file at run time, so the passwords never appear on screen. In your own sessions, you can instead put them in ~/.pgpass (mode 0600), for example 127.0.0.1:6432:appdb:appuser:<APP_PASSWORD>.

psql --version pgbouncer --version | head -1 systemctl is-active postgresql@17-main.service pgbouncer.service
sudo ss -Hltn

psql (PostgreSQL) 17.11 (Ubuntu 17.11-1.pgdg24.04+2) PgBouncer 1.26.1 active active LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* LISTEN 0 4096 0.0.0.0:22 0.0.0.0:* LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:* LISTEN 0 128 127.0.0.1:6432 0.0.0.0:* LISTEN 0 4096 127.0.0.54:53 0.0.0.0:* LISTEN 0 4096 [::]:22 [::]:*

PgBouncer listens on 127.0.0.1:6432 and PostgreSQL on 127.0.0.1:5432. Port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub.

Connect as appuser through PgBouncer over TLS. Check your user name and connection details:

export PGPASSWORD="$(sudo sed -n 's/^APP_PASSWORD=//p' /root/postgresql-pgbouncer-credentials.txt)" psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require" \
  -c "SELECT current_user, current_database(), current_setting('server_version') AS server_version;" \
  -c '\conninfo'

current_user | current_database | server_version --------------+------------------+------------------------------------ appuser | appdb | 17.11 (Ubuntu 17.11-1.pgdg24.04+2) (1 row) You are connected to database "appdb" as user "appuser" on host "127.0.0.1" at port "6432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)

Create a table, write to it in a transaction, read it back and drop it. Each psql call is a separate client connection, and PgBouncer lends each one a server session for the length of its transaction:

export PGPASSWORD="$(sudo sed -n 's/^APP_PASSWORD=//p' /root/postgresql-pgbouncer-credentials.txt)" P="host=127.0.0.1 port=6432 dbname=appdb user=appuser sslmode=require" psql "$P" -c "CREATE TABLE IF NOT EXISTS orders (id serial PRIMARY KEY, item text NOT NULL, created timestamptz DEFAULT now());" psql "$P" -c "BEGIN; INSERT INTO orders (item) VALUES ('widget'), ('gadget'); COMMIT;" psql "$P" -c "SELECT id, item FROM orders ORDER BY id;" psql "$P" -c "DROP TABLE orders;"

CREATE TABLE BEGIN INSERT 0 2 COMMIT id | item ----+-------- 1 | widget 2 | gadget (2 rows) DROP TABLE

Transaction pooling and your application. A client gets a server session for only one transaction, so session state does not carry over between transactions. Use SET LOCAL instead of SET. Avoid session-level advisory locks, LISTEN and WITH HOLD cursors, and keep temporary tables inside one transaction.

Protocol-level prepared statements work. PgBouncer 1.26 tracks them, and max_prepared_statements defaults to 200. If an application needs full session semantics, change pool_mode to session in /etc/pgbouncer/pgbouncer.ini (see Maintenance).

pgbadmin connects to PgBouncer's virtual pgbouncer database, which accepts admin commands such as SHOW DATABASES, SHOW POOLS, SHOW STATS, SHOW CLIENTS and RELOAD:

export PGPASSWORD="$(sudo sed -n 's/^PGBOUNCER_ADMIN_PASSWORD=//p' /root/postgresql-pgbouncer-credentials.txt)" A="host=127.0.0.1 port=6432 dbname=pgbouncer user=pgbadmin sslmode=require" psql "$A" -c "SHOW POOLS;" psql "$A" -tAc "SHOW CONFIG;" | grep -E '^(pool_mode|auth_type|client_tls_sslmode|listen_addr|listen_port|max_client_conn|default_pool_size)\|' | cut -d'|' -f1,2

database | user | cl_active | cl_waiting | cl_active_cancel_req | cl_waiting_cancel_req | sv_active | sv_active_cancel | sv_being_canceled | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode | load_balance_hosts -----------+-----------+-----------+------------+----------------------+-----------------------+-----------+------------------+-------------------+---------+---------+-----------+----------+---------+------------+-------------+-------------------- appdb | appuser | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | transaction | pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement | (2 rows) auth_type|scram-sha-256 client_tls_sslmode|require default_pool_size|20 listen_addr|127.0.0.1 listen_port|6432 max_client_conn|200 pool_mode|transaction

sv_idle is the number of PostgreSQL server sessions PgBouncer holds open for reuse. If cl_waiting stays above zero for long, the pool is too small for your load, so raise default_pool_size.

PgBouncer and PostgreSQL accept connections on localhost only. You can reach the pooler in two ways:

• SSH tunnel (no change to the instance). On your workstation, run ssh -N -L 6432:127.0.0.1:6432 ubuntu@<public-ip>. Then connect your client to 127.0.0.1:6432 as appuser, database appdb, with sslmode=require.
• Open it to your VPC. Set PgBouncer's listener to all interfaces (or to this instance's private IP) and restart it. Then add an inbound rule for 6432 to the instance's security group, from your application subnet or VPC CIDR only (never 0.0.0.0/0). Clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/17/main/sac-server.crt) and the appuser password. PostgreSQL itself stays on localhost behind the pooler.

sudo sed -i 's/^listen_addr = 127.0.0.1$/listen_addr = */' /etc/pgbouncer/pgbouncer.ini
sudo systemctl restart pgbouncer.service

An application connection string for the pooler looks like this:

postgresql://appuser:<APP_PASSWORD>@<private-ip>:6432/appdb?sslmode=require

PgBouncer refuses a client that does not use TLS, and scram-sha-256 refuses a wrong password. The postgres superuser cannot log in through the pooler at all:

for args in "dbname=appdb user=appuser sslmode=disable" "dbname=appdb user=appuser sslmode=require" "dbname=postgres user=postgres sslmode=require"; do OUT="$(PGPASSWORD=<set-at-first-boot> psql "host=127.0.0.1 port=6432 $args" -c "SELECT 1" 2>&1 || true)" echo "${OUT#*failed: }" done

FATAL: SSL required FATAL: SASL authentication failed FATAL: SASL authentication failed

Confirm that every PostgreSQL and PgBouncer unit carries the metadata block, that appuser is not a superuser, and that the postgres superuser has no password:

systemctl show -p IPAddressDeny postgresql@17-main.service pgbouncer.service postgresql-pgbouncer-firstboot.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolname IN ('appuser','postgres') ORDER BY 1;"

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 appuser|f|t postgres|t|f