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.
Q: Why should I use transaction pooling?
A: You have many workers per dyno that hold open idle Postgres connections and and you want to reduce the number of unused connections. This is a slightly more complete answer from stackoverflow
Q: Why shouldn't I use transaction pooling?
A: If you need to use named prepared statements, advisory locks, listen/notify, or other features that operate on a session level. Please refer to PGBouncer's feature matrix for all transaction pooling caveats.
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 diable 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
$ ls -a .buildpacks Gemfile Gemfile.lock Procfile config/ config.ru $ heroku config:add BUILDPACK_URL=https://github.com/ddollar/heroku-buildpack-multi.git $ cat .buildpacks https://github.com/gregburek/heroku-buildpack-pgbouncer.git#v0.3.3 https://github.com/heroku/heroku-buildpack-ruby.git $ cat Procfile web: bin/start-pgbouncer-stunnel bundle exec unicorn -p $PORT -c ./config/unicorn.rb -E $RACK_ENV worker: bundle exec rake worker $ git push heroku master ... -----> Fetching custom git buildpack... done -----> Multipack app detected =====> Downloading Buildpack: https://github.com/gregburek/heroku-buildpack-pgbouncer.git =====> Detected Framework: pgbouncer-stunnel Using pgbouncer version: 1.5.4 Using stunnel version: 5.02 Using stack version: cedar -----> Fetching and vendoring pgbouncer into slug -----> Fetching and vendoring stunnel into slug -----> Moving the configuration generation script into app/bin -----> Moving the start-pgbouncer-stunnel script into app/bin -----> pgbouncer/stunnel done =====> Downloading Buildpack: https://github.com/heroku/heroku-buildpack-ruby.git =====> Detected Framework: Ruby/Rack -----> Using Ruby version: ruby-1.9.3 -----> Installing dependencies using Bundler version 1.3.2 ...
The buildpack will install and configure pgbouncer and stunnel to connect to
DATABASE_URL over a SSL connection. Prepend
to any process in the Procfile to run pgbouncer and stunnel alongside that process.
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:email@example.com:5482/db6h3bkfuk5430 DATABASE_URL=postgres://uf2782hv7b3uqe:firstname.lastname@example.org:5622/deamhhcj6q0d31 ~ $ bin/start-pgbouncer-stunnel env # filtered for brevity HEROKU_POSTGRESQL_ROSE_URL=postgres://u9dih9htu2t3ll:email@example.com:6000/db2 DATABASE_URL=postgres://uf2782hv7b3uqe:firstname.lastname@example.org:6000/db1
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.
PGBOUNCER_POOL_MODE Default is transaction
PGBOUNCER_MAX_CLIENT_CONN Default is 100
PGBOUNCER_DEFAULT_POOL_SIZE Default is 1
PGBOUNCER_RESERVE_POOL_SIZE Default is 1
PGBOUNCER_RESERVE_POOL_TIMEOUT Default is 5.0 seconds
PGBOUNCER_URLS Default is DATABASE_URL
PGBOUNCER_CONNECTION_RETRY Default is no
PGBOUNCER_LOG_CONNECTIONS Default is yes
PGBOUNCER_LOG_DISCONNECTIONS Default is yes
PGBOUNCER_LOG_POOLER_ERRORS Default is yes
PGBOUNCER_STATS_PERIOD Default is 60
PGBOUNCER_SERVER_RESET_QUERY Default is empty when pool mode is transaction, and "DISCARD ALL;" when session.
ENABLE_STUNNEL_AMAZON_RDS_FIX Default is 0. Set to 1 if you are connecting to an Amazon RDS instance of postgres.
Ensures that stunnel will work correctly after a dyno resumes from sleep. Otherwise, the dyno will lose connectivity to RDS.
For more info, see CONTRIBUTING.md
Copy the snippet above into CLI.