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