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.
1
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 werepostgresql://aptible:very_secure_password@db-aptible-docs-example-1000.aptible.in:4000/dbPostgreSQL username = 'aptible'PostgreSQL password = 'very_secure_password'PostgreSQL host = 'db-aptible-docs-example-1000.aptible.in'PostgreSQL port = 4000PostgreSQL database name = 'db'
Through the UI or CLI, create the PgBouncer application, and set a few variables:
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.
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:
For a basic implementation, the Dockerfile is quite short:
FROM bitnami/pgbouncer:latestCOPY pgbouncer.key /opt/bitnami/pgbouncer/certs/pgbouncer.keyCOPY 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.
3
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.
