Home

Awesome

Heroku buildpack: pgbouncer

This is a Heroku buildpack that allows one to run pgbouncer in a dyno alongside application code. It is meant to be used in conjunction with other buildpacks.

The primary use of this buildpack is to allow for transaction pooling of PostgreSQL database connections among multiple workers in a dyno. For example, 10 unicorn workers would be able to share a single database connection, avoiding connection limits and Out Of Memory errors on the Postgres server.

FAQ

Disable Prepared Statements

With Rails 4.1, you can disable prepared statements by appending ?prepared_statements=false to the database's URI. Set the PGBOUNCER_PREPARED_STATEMENTS config var to false for the buildpack to do that for you.

Rails versions 4.0.0 - 4.0.3, reportedly can't disable prepared statements at all. Make sure your framework is up to date before troubleshooting prepared statements failures.

Rails 3.2 - 4.0 also requires an initializer to properly cast the prepared_statements configuration string as a boolean. This initializer is adapted from this commit. In file config/initializers/database_connection.rb insert the following:

require "active_record/connection_adapters/postgresql_adapter"

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  alias initialize_without_config_boolean_coercion initialize
  def initialize(connection, logger, connection_parameters, config)
    if config[:prepared_statements] == 'false'
      config = config.merge(prepared_statements: false)
    end
    initialize_without_config_boolean_coercion(connection, logger, connection_parameters, config)
  end
end

Usage

Example usage:

$ ls -a
Gemfile  Gemfile.lock  Procfile  config/  config.ru

$ heroku buildpacks:add heroku/pgbouncer
Buildpack added. Next release on pgbouncer-test-app will use heroku/pgbouncer.
Run `git push heroku main` to create a new release using this buildpack.

$ heroku buildpacks:add heroku/ruby
Buildpack added. Next release on pgbouncer-test-app will use:
  1. https://github.com/heroku/heroku-buildpack-pgbouncer
  2. https://github.com/heroku/heroku-buildpack-ruby
Run `git push heroku main` to create a new release using these buildpacks.

$ cat Procfile
web:    bin/start-pgbouncer bundle exec unicorn -p $PORT -c ./config/unicorn.rb -E $RACK_ENV
worker: bundle exec rake worker

$ git push heroku main
...
-----> Multipack app detected
-----> Fetching custom git buildpack... done
-----> pgbouncer app detected
       Using pgbouncer version: 1.23.1-heroku
-----> Fetching and vendoring pgbouncer into slug
-----> Moving the configuration generation script into app/bin
-----> Moving the start-pgbouncer script into app/bin
-----> pgbouncer done
-----> Fetching custom git buildpack... done
...

The buildpack will install and configure pgbouncer to connect to DATABASE_URL over a TLS connection, where available. Prepend bin/start-pgbouncer to any process in the Procfile to run pgbouncer alongside that process.

PgBouncer Version

Multiple Databases

It is possible to connect to multiple databases through pgbouncer by setting PGBOUNCER_URLS to a list of config vars. Example:

$ heroku config:add PGBOUNCER_URLS="DATABASE_URL HEROKU_POSTGRESQL_ROSE_URL"
$ heroku run bash

~ $ env | grep 'HEROKU_POSTGRESQL_ROSE_URL\|DATABASE_URL'
HEROKU_POSTGRESQL_ROSE_URL=postgres://u9dih9htu2t3ll:password@ec2-107-20-228-134.compute-1.amazonaws.com:5482/db6h3bkfuk5430
DATABASE_URL=postgres://uf2782hv7b3uqe:password@ec2-50-19-210-113.compute-1.amazonaws.com:5622/deamhhcj6q0d31

~ $ bin/start-pgbouncer env # filtered for brevity
HEROKU_POSTGRESQL_ROSE_URL=postgres://u9dih9htu2t3ll:password@127.0.0.1:6000/db2
DATABASE_URL=postgres://uf2782hv7b3uqe:password@127.0.0.1:6000/db1

⚠️ A referenced configuration variable in PGBOUNCER_URLS must not be empty, and must be a valid PostgreSQL connection string.

Follower Replica Databases

As of v0.3.2 of this buildpack, it is possible to use pgbouncer to connect to multiple databases that share a database name, such as a leader and follower. To use, add the follower's config var to PGBOUNCER_URLS as detailed in the Multiple Databases section.

If you are using Octopus Replication to send reads to a replica, make sure to include the color url of your leader in the SLAVE_DISABLED_FOLLOWERS blacklist. Otherwise, Octopus will attempt to use your leader as a read-only replica, potentially doubling your connection count.

Tweak settings

Some settings are configurable through app config vars at runtime. Refer to the appropriate documentation for pgbouncer configurations to see what settings are right for you.

For more info, see CONTRIBUTING.md

Using the edge version of the buildpack

The heroku/pgbouncer buildpack points to the latest stable version of the buildpack published in the Buildpack Registry. To use the latest version of the buildpack (the code in this repository, run the following command:

$ heroku buildpacks:add https://github.com/heroku/heroku-buildpack-pgbouncer

Notes

Currently, the connection string parsing requires the connection string to be in a specific format:

postgres://<user>:<pass>@<host>:<port>/<database>

This corresponds to the regular expression ^postgres(?:ql)?:\/\/([^:]*):([^@]*)@(.*?):(.*?)\/(.*?)$. All components must be present in order for the buildpack to correctly parse the connection string.