pgbouncer #9

  • By charmers
  • Latest version (#9)
  • precise
  • Stable
  • Edge

Description

PgBouncer is a lightweight connection pooler for PostgreSQL.


Overview

PgBouncer is a lightweight connection pooler for PostgreSQL.

http://wiki.postgresql.org/wiki/PgBouncer

Usage

Installation

To deploy you'll need at a minimum: a cloud environment, a working Juju
installation, and a successful bootstrap. Please refer to the
Juju Getting Started
documentation before continuing.

It is also recommended that you read the documentation for the
postgresql charm so you understand how to set up postgresql in
a master-slave relationship.

Example

First, if you haven't already done so, bootstrap your environment:

juju bootstrap

Now deploy a standalone PostgreSQL instance:

juju deploy postgresql

Let's add another unit:

juju add-unit postgresql

As per the documentation in the postgresql charm, you now have a master
and a hot standby set up with replication.

Now that you have a functional PostgreSQL setup, deploy the
PgBouncer charm from the Juju charm store:

juju deploy pgbouncer

Create the relations between pgbouncer and postgresql:

juju add-relation pgbouncer:backend-db-admin postgresql:db-admin

Now you have set up pgbouncer in front of your PostgreSQL units.

In a real world scenario, you might have a (web) application that
sends write queries to a master (directly) and read-only queries
to a cluster of slaves, with load balancing using pgbouncer.

Configuration

juju set pgbouncer max_client_conn=50

db-proxy relationship

The charm joining the db-proxy relationship can specify a database that
will be created in addition to the default one based on the service name
and can specify a comma seperated list of roles that will be granted to
the user (these roles will be created if they do not already exist)

Monitoring

This charm provides relations that support monitoring via Nagios using
nrpe_external_master as a subordinate charm.

Configuration

auth_type
(string) DEPRECATED AND IGNORED. How to authenticate users.
md5
server_connect_timeout
(int) If connection and login wont finish in this amount of time, the connection will be closed. [seconds]
15
default_pool_size
(int) How many server connections to allow per user/database pair. Can be overridden in the per-database configuration.
20
reserve_pool_size
(int) How many additional connections to allow to a pool. 0 disables.
server_idle_timeout
(int) If a server connection has been idle more than this many seconds it will be dropped. If 0 then timeout is disabled. [seconds]
600
wait_warn
(int) The parameters to pass to the nrpe plugin check_pgbouncer_pool_waittime for warning level.
5
ignore_startup_parameters
(string) By default, PgBouncer allows only parameters it can keep track of in startup packets - client_encoding, datestyle, timezone and standard_conforming_strings. All other parameters will raise an error. To allow other parameters, they can be specified here, so that pgbouncer knows that they are handled by admin and it can ignore them.
application_name
nagios_service_type
(string) What service this component forms part of. Used by nrpe.
generic
logfile
(string) DEPRECATED AND IGNORED. Specifies log file. Log file is kept open so after rotation kill -HUP or on console RELOAD; should be done. Note: On Windows machines, the service must be stopped and started.
/var/log/postgresql/pgbouncer.log
listen_port
(int) Which port to listen on. Applies to both TCP and Unix sockets.
6432
listen_addr
(string) Specifies list of addresses, where to listen for TCP connections. You may also use * meaning "listen on all addresses". When not set, only Unix socket connections are allowed. Addresses can be specified numerically (IPv4/IPv6) or by name.
*
conn_crit
(int) The parameters to pass to the nrpe plugin check_pgbouncer_connection_count for critical level.
90
pidfile
(string) DEPRECATED AND IGNORED. Specifies the pid file. Without a pidfile, daemonization is not allowed.
/var/run/postgresql/pgbouncer.pid
wait_crit
(int) The parameters to pass to the nrpe plugin check_pgbouncer_pool_waittime for critical level.
20
server_check_delay
(int) How long to keep released connections available for immediate re-use, without running sanity-check queries on it. If 0 then the query is ran always.
30
client_login_timeout
(int) If a client connects but does not manage to login in this amount of time, it will be disconnected. Mainly needed to avoid dead connections stalling SUSPEND and thus online restart. [seconds]
60
package_status
(string) The status of service-affecting packages will be set to this value in the dpkg database. Useful valid values are "install" and "hold".
install
max_client_conn
(int) Maximum number of client connections allowed. When increased then the file descriptor limits should also be increased. Note that actual number of file descriptors used is more than max_client_conn.
100
auth_file
(string) DEPRECATED AND IGNORED. The name of the file to load user names and passwords from. The file format is the same as the PostgreSQL pg_auth/pg_pwd file, so this setting can be pointed directly to one of those backend files.
/etc/pgbouncer/userlist.txt
max_open_files
(int) DEPRECATED AND IGNORED. Set maximum open files
65536
pool_mode
(string) Specifies when a server connection can be reused by other clients.
transaction
admin_users
(string) DEPRECATED AND IGNORED. Comma-separated list of database users that are allowed to connect and run all commands on console. Ignored when auth_mode=any, in which case any username is allowed in as admin.
postgres
server_lifetime
(int) The pooler will try to close server connections that have been connected longer than this. Setting it to 0 means the connection is to be used only once, then closed. [seconds]
3600
nagios_context
(string) Used by the nrpe-external-master subordinate charm. A string that will be prepended to instance name to set the host name in nagios. So for instance the hostname would be something like: juju-pgbouncer-0 If you're running multiple environments with the same services in them this allows you to differentiate between them.
juju
conn_warn
(int) The parameters to pass to the nrpe plugin check_pgbouncer_connection_count for warn level.
75
server_login_retry
(int) If login failed, because of failure from connect() or authentication that pooler waits this much before retrying to connect. [seconds]
15