Deploying PgBouncer on Aptible

PgBouncer is a lightweight connection pooler for PostgreSQL which helps reduce resource usage and overhead by managing database connections. This guide provides a overview on how you can get started with PgBouncer on Aptible and Dockerfile Deploy.

Setting Up PgBouncer

To successfully use and configure PgBouncer, you’ll need to have a PostgreSQL database you want to pool connections for. From that database, you’ll need to know the following:

PostgreSQL username
PostgreSQL password
PostgreSQL host
PostgreSQL port
PostgreSQL database name These values can be retrieved from the Database Credentials in the UI, and will be used to set configuration variables later in the guide. For example:

If the Database Credentials were
postgresql://aptible:very_secure_password@db-aptible-docs-example-1000.aptible.in:4000/db

PostgreSQL username      = 'aptible'
PostgreSQL password      = 'very_secure_password'
PostgreSQL host          = 'db-aptible-docs-example-1000.aptible.in'
PostgreSQL port          = 4000
PostgreSQL database name = 'db'

Through the UI or CLI, create the PgBouncer application, and set a few variables:

  aptible apps:create pgbouncer
  aptible config:set --app pgbouncer \
  POSTGRESQL_USERNAME='aptible' \
  POSTGRESQL_PASSWORD=$PASSWORD \
  POSTGRESQL_DATABASE='db' \
  POSTGRESQL_HOST='$DB_HOSTNAME' \
  POSTGRESQL_PORT='$DB_PORT' \
  PGBOUNCER_DATABASE='db' \
  PGBOUNCER_SERVER_TLS_SSLMODE='require' \
  PGBOUNCER_AUTH_USER='aptible' \
  PGBOUNCER_AUTH_QUERY='SELECT uname, phash FROM user_lookup($1)' \
  IDLE_TIMEOUT=2400 \
  PGBOUNCER_CLIENT_TLS_SSLMODE='require' \
  PGBOUNCER_CLIENT_TLS_KEY_FILE='/opt/bitnami/pgbouncer/certs/pgbouncer.key' \
  PGBOUNCER_CLIENT_TLS_CERT_FILE='/opt/bitnami/pgbouncer/certs/pgbouncer.crt' \

Note that you’ll need to fill out a few variables with the Database Credentials you previously gathered. We’re also assuming the certificate and key you’re using to authenticate will be saved as pgbouncer.crt and pgbouncer.key.

Since databases on Aptible require SSL, you’ll also need to provide an authentication certificate and key. These can be self-signed and created using openssl.

Generate a Root Certificate and Key

openssl req -x509 \ 
        -sha256 -days 365 \
        -nodes \
        -newkey rsa:2048 \
        -subj "/CN=app-$APP_ID.on-aptible.com/C=US/L=San Fransisco" \
        -keyout rootCA.key -out rootCA.crt

This creates a rootCA.key and rootCA.crt in your current directory. -subj "/CN=app-$APP_ID.on-aptible.com/C=US/L=San Francisco" is modifiable — notably, the Common Name, /CN, should match the TCP endpoint you’ve created for the pgbouncer App. If you’re using a default endpoint, you can fill in $APP_ID with your Application’s ID.

Using the Root Certificate and key, create the authentication certificate and private key:

openssl genrsa -out pgbouncer.key 2048
openssl req -new -key pgbouncer.key -out pgbouncer.csr
openssl x509 -req \
      -in pgbouncer.csr \
      -CA rootCA.crt -CAkey rootCA.key \
      -CAcreateserial -out pgbouncer.crt \
      -days 365 \
      -sha256

Create the Dockerfile

For a basic implementation, the Dockerfile is quite short:

FROM bitnami/pgbouncer:latest
COPY pgbouncer.key /opt/bitnami/pgbouncer/certs/pgbouncer.key
COPY pgbouncer.crt /opt/bitnami/pgbouncer/certs/pgbouncer.crt

We’re using the PgBouncer image as a base, and then copying a certificate-key pair for TLS authentication to where PgBouncer expects them to be. This means that your git repository needs to contain three files: the Dockerfile, pgbouncer.key, and pgbouncer.crt.

Deploy using Git Push

Now you’re ready to deploy. Since we’re working from a Dockerfile, follow the steps in Deploying with Git to push your repository to your app’s Git Remote to trigger a deploy.

Make an Endpoint for PgBouncer

This is commonly done by creating a TCP endpoint.

aptible endpoints:tcp:create --app pgbouncer CMD --internal

Instead of connecting to your database directly, you should configure your resources to connect to PgBouncer using the TCP endpoint.

Celebrate!

At this point, PgBouncer should be deployed. If you run into any issues, or have any questions, don’t hesitate to reach out to Aptible Support

How to Guides

Troubleshooting

Deploying PgBouncer on Aptible