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 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
$ ls -a Gemfile Gemfile.lock Procfile config/ config.ru $ heroku buildpacks:add https://github.com/heroku/heroku-buildpack-pgbouncer Buildpack added. Next release on pgbouncer-test-app will use https://github.com/heroku/heroku-buildpack-pgbouncer. Run `git push heroku master` 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 master` to create a new release using these buildpacks. $ 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 ... -----> Multipack app detected -----> Fetching custom git buildpack... done -----> pgbouncer-stunnel app detected Using pgbouncer version: 1.5.4-heroku Using stunnel version: 5.08 Using stack version: cedar-14 -----> 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 -----> Fetching custom git buildpack... done ...
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_MODEDefault is transaction
PGBOUNCER_MAX_CLIENT_CONNDefault is 100
PGBOUNCER_DEFAULT_POOL_SIZEDefault is 1
PGBOUNCER_MIN_POOL_SIZEDefault is 0
PGBOUNCER_RESERVE_POOL_SIZEDefault is 1
PGBOUNCER_RESERVE_POOL_TIMEOUTDefault is 5.0 seconds
PGBOUNCER_SERVER_LIFETIMEDefault is 3600.0 seconds
PGBOUNCER_SERVER_IDLE_TIMEOUTDefault is 600.0 seconds
PGBOUNCER_URLSshould contain all config variables that will be overridden to connect to pgbouncer. For example, set this to
AMAZON_RDS_URLto send RDS connections through pgbouncer. The default is
PGBOUNCER_CONNECTION_RETRYDefault is no
PGBOUNCER_LOG_CONNECTIONSDefault is 1. If your app does not use persistent database connections, this may be noisy and should be set to 0.
PGBOUNCER_LOG_DISCONNECTIONSDefault is 1. If your app does not use persistent database connections, this may be noisy and should be set to 0.
PGBOUNCER_LOG_POOLER_ERRORSDefault is 1
PGBOUNCER_STATS_PERIODDefault is 60
PGBOUNCER_SERVER_RESET_QUERYDefault is empty when pool mode is transaction, and "DISCARD ALL;" when session.
PGBOUNCER_STUNNEL_LOGLEVELDefault is notice (5). Set this var to pass a syslog level name or number value to stunnel. This corresponds to the stunnel global configuration option called "debug".
ENABLE_STUNNEL_AMAZON_RDS_FIXDefault is unset. Set this var if you are connecting to an Amazon RDS instance of postgres. Adds
options = NO_TICKETwhich is documented to make stunnel work correctly after a dyno resumes from sleep. Otherwise, the dyno will lose connectivity to RDS.
For more info, see CONTRIBUTING.md