,

pgBouncer – Installation and Configuration in Ubuntu

PgBouncer is a great lightweight connection pool for Postgres

Installation


sudo apt-get install -y pgbouncer

Configuration

Example pgbouncer.ini configuration file


postgres = host=127.0.0.1 port=5432 dbname=postgres
postgres = host=127.0.0.1 dbname=postgres
postgres = host=127.0.0.1 port=5432 user=jon password=doe client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

logfile = pgbouncer.log
pidfile = pgbouncer.pid
listen_addr = *
listen_port = 6000
unix_socket_dir = /tmp
auth_type = trust
auth_file = /usr/local/postgres/userlist.txt
admin_users = postgres
stats_users = stats, root
pool_mode = transaction
server_reset_query =
server_check_query = select 1
server_check_delay = 10
max_client_conn = 100
default_pool_size = 500
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
server_idle_timeout = 60
client_idle_timeout = 60
client_login_timeout = 60

Define user authentication file: /usr/local/postgres/userlist.txt

> cat userlist.txt

"postgres" "postgres"

Set user permissions
> chown postgres:postgres userlist.txt

Here’s a working pgbouncer.ini configuration file


[databases]
template1 = host=127.0.0.1 port=5432 dbname=mydatabase

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = md5
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
ignore_startup_parameters = application_name

users.txt


"postgres" "postgres"

Start pgBouncer

pgbouncer -d -v pgbouncer.ini
#pgbouncer -v pgbouncer.ini

Login


psql -p 6543 -U postgres template1

Toubleshooting

1. psql: ERROR: Unknown startup
Ensure that the “ignore_startup_parameters = application_name” entry is set in pgbouncer.ini

2. ERROR broken auth file
Ensure the username and password defined in users.txt are in quotes. E.g. “postgres” instead of postgres.

References

1. http://pgbouncer.projects.postgresql.org/doc/usage.html
2. http://wiki.postgresql.org/wiki/PgBouncer
3. http://www.techienuggets.com/Comments?tx=106898
4. http://blog.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/month=20100601