Database Reference
In-Depth Information
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 exist, and must contain at least the minimum
users mentioned in
admin_users
and
stats_users
. For example:
"postgres" ""
pgbouncer also supports md5 authentication. To use that effectively, you need to
copy the encrypted passwords from the database server.
You may wish to create the
users.txt
file by directly copying the details from the
server. That can be done using the following psql script:
postgres=> \o users.txt
postgres=> \t
postgres=> SELECT ‹»›||rolname||›» «›||rolpassword||›»›
postgres-> FROM pg_authid;
postgres=> \q
3. Launch pgbouncer:
pgbouncer -d pgbouncer.ini
4. Test the connection—it should respond
reload
:
psql -p 6543 -U postgres pgbouncer -c "reload"
Also, check that pgbouncer›s
max_client_conn
parameter does not exceed
max_connections
parameter on PostgreSQL.
How it works...
pgbouncer is a great piece of software. It's feature set is very carefully defined to ensure
that it is simple, robust, and very fast. pgbouncer is not multi-threaded, so it runs in a single
process, and thus on a single CPU. It is very efficient, though very large data transfers will tie
up more time and reduce concurrency, so make those data dumps using a direct connection.
pgbouncer doesn't support SSL connections. If it did, then all of the encryption/decryption
would need to take place in the single process, which would make that solution perform
poorly. If you need secure communications, then you should use
stunnel
.
pgbouncer provides connection pooling. If you set:
pool_mode = transaction
then pgbouncer will also provide connection concentration. This allows hundreds or thousands
of incoming connections to be managed while only a few server connections are made.
