How to do it…

Carry out the following steps to configure PgBouncer:

1. Create a pgbouncer.ini file, as follows:

; 
; pgbouncer configuration example 
; 
[databases] 
postgres = port=5432 dbname=postgres 
[pgbouncer] 

listen_addr = 127.0.0.1 
listen_port = 6432 
admin_users = postgres 
;stats_users = monitoring userid 
auth_type = any 
; put these files somewhere sensible: 
auth_file = users.txt 
logfile = pgbouncer.log 
pidfile = pgbouncer.pid 

server_reset_query = DISCARD ALL; 
; default values 
pool_mode = session 
default_pool_size = 20 
log_pooler_errors = 0

2. Create a users.txt file. This must contain the minimum users mentioned in admin_users and stats_users. Its format is very simple: a collection of lines with a username and a password. Consider the following as an example:

"postgres"    ""

3. PgBouncer also supports MD5 authentication. To use that effectively, you need to copy the MD5 encrypted passwords from the database server into the users.txt file.
4. You may wish to create the users.txt file by directly copying the details from the server. This can be done by using the following psql script:

postgres=> \o users.txt
postgres=> \t
postgres=> SELECT '"'||rolname||'" "'||rolpassword||'"'
postgres-> FROM pg_authid;
postgres=> \q

5. Launch pgbouncer:

pgbouncer -d pgbouncer.ini

6. Test the connection; it should respond to reload:

psql -p 6432 -h 127.0.0.1 -U postgres pgbouncer -c "reload"

7. Finally, verify that PgBouncer's max_client_conn parameter does not exceed the max_connections parameter on PostgreSQL.