> ## Documentation Index
> Fetch the complete documentation index at: https://www.aptible.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Deploying PgBouncer on Aptible

> How to deploy 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 [deploying via Git](https://www.aptible.com/docs/core-concepts/apps/deploying-apps/image/deploying-with-git/overview.md#deploying-with-git).

**1. Setting Up PgBouncer**

> [accordion] Gather Database Variables
>
> 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](https://www.aptible.com/docs/core-concepts/managed-databases/connecting-databases/database-credentials.md#overview) 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'
> ```

> [accordion] Create your PgBouncer Application
>
> 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`.

> [accordion] Generate a Certificate and Key for SSL Authentication
>
> 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`.
>
> 1. 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.
>
> 2. 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
> ```

**2. 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`.

**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](https://www.aptible.com/docs/core-concepts/apps/deploying-apps/image/deploying-with-git/overview.md) to push your repository to your app's Git Remote to trigger a deploy.

**4. 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.

**5. 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](https://app.aptible.com/support)