Getting started¶

This page takes you from the AWS Marketplace listing to a pooled query against your own database: subscribe, launch, connect, run the walkthrough, then point PgBouncer at your PostgreSQL.

You need: an AWS account with permission to subscribe to AWS Marketplace products, launch EC2 instances and use AWS Systems Manager, plus a PostgreSQL or Amazon RDS database that accepts TLS connections from the instance.

Open the PgBouncer listing in AWS Marketplace, choose View purchase options, review the terms and subscribe. When the subscription is confirmed, choose Launch your software.

2. Launch the instance¶

On the EC2 launch page:

Keep the instance type at t3.medium.
Under Configure storage, use an encrypted gp3 root volume of at least 16 GiB.
Under Network settings, allow no inbound traffic. If you also want SSH, allow port 22 from your administrative address range only. Launch it in a subnet that can reach your database.
Under Advanced details, choose an IAM instance profile that includes the AWS managed policy AmazonSSMManagedInstanceCore, so you can connect with Session Manager.
Choose Launch instance.

3. Connect¶

In the EC2 console, select the instance, choose Connect, then Session Manager, then Connect. Switch to the ubuntu user:

sudo -iu ubuntu

4. Wait for first boot¶

On first boot the instance creates its TLS certificates, passwords, user list and /etc/pgbouncer/pgbouncer.ini, then starts PgBouncer:

cloud-init status --wait
systemctl is-active sushi-pgbouncer-firstboot pgbouncer

5. Run the walkthrough¶

The walkthrough starts a throwaway PostgreSQL on 127.0.0.1:15432, runs queries through PgBouncer, and removes the throwaway database when it ends:

bash /opt/sushi/docs/demo.sh

6. Point PgBouncer at your database¶

Edit the [databases] section of /etc/pgbouncer/pgbouncer.ini so the example databases point at your server, then reload. The full steps are in Point PgBouncer at your database.

Your database must accept the same user name and password that PgBouncer uses for the client. Out of the box that is the app user with the password in /var/lib/pgbouncer/credentials/app.password. Either create that role on your database with that password, or change the user list to match a role you already have, see Users and passwords.

7. Run a pooled query¶

From a root shell, read the app password privately and connect through PgBouncer with full certificate verification:

sudo bash -euo pipefail <<'PGBOUNCER_QUERY'
PGPASSWORD=$(tr -d '\n' < /var/lib/pgbouncer/credentials/app.password)
export PGPASSWORD
psql "host=127.0.0.1 port=6432 dbname=app user=app sslmode=verify-full sslrootcert=/usr/local/share/sushi/pgbouncer/ca.pem" \
  -c 'SELECT 1'
unset PGPASSWORD
PGBOUNCER_QUERY

A result of 1 means the query went through the pool to your database.

Connect your application¶

Setting	Value
Host and port	`127.0.0.1`, `6432`
Database	`app` (transaction pooling) or `session` (session pooling)
User	`app`
Password	the contents of `/var/lib/pgbouncer/credentials/app.password`
TLS	required; use `sslmode=verify-full` with `sslrootcert=/usr/local/share/sushi/pgbouncer/ca.pem`

The password file is readable by root and the pgbouncer group only. Keep it out of tickets, chat and screen recordings.

Transaction or session pooling

The app database uses transaction pooling, which serves the most clients with the fewest server connections but does not keep session state such as SET, LISTEN, SQL PREPARE or WITH HOLD cursors between transactions. If your driver or ORM needs those, use the session database. Test your application against both before production.