Once we have compiled and installed pgbouncer, we can easily fire it up. To do so we have set up two databases on a local instance (p0 and p1). The idea of the setup performed in this example is to use pgbouncer as a proxy.
In order to make pgbouncer work we can write a simple config file, which can be fed to pgbouncer:
[databases] p0 = host=localhost dbname=p0 p1 = host=localhost dbname=p1 [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/log/pgbouncer.pid listen_addr = 127.0.0.1 listen_port = 6432 auth_type = trust auth_file = /etc/pgbouncer/userlist.txt pool_mode = session server_reset_query = DISCARD ALL max_client_conn = 100 default_pool_size = 20
Using the same database name is not required here. You can map any database name to any connect strings. We have just found it useful to use identical names.
Once we have written this config file, we can safely start pgbouncer and see what happens:
hs@iMac:/etc$ pgbouncer bouncer.ini 2013-04-25 17:57:15.992 22550 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 150 2013-04-25 17:57:15.994 22550 LOG listening on 127.0.0.1:6432 2013-04-25 17:57:15.994 22550 LOG listening on unix:/tmp/.s.PGSQL.6432 2013-04-25 17:57:15.995 22550 LOG process up: pgbouncer 1.5.4, libevent 2.0.16-stable (epoll), adns: evdns2
In production, you would configure authentication first but let us do it step-by-step.
The first thing we have to configure when dealing with pgbouncer is the configuration of the database servers we want to connect to. In our example, we simply create links to p0 and p1. We put the connect strings in, which tell pgbouncer where to connect to. As pgbouncer is essentially some sort of proxy, we can also map connections to make things more flexible. In this case mapping means that the database holding the data does not necessarily have the same name as the virtual database exposed by pgbouncer.
The following connect parameters are allowed: dbname, host, port, user, password, client_encoding, datestyle, timezone, pool_size, and connect_query. Everything up to the password is what you would use in any PostgreSQL connect string. The rest is used to adjust pgbouncer to your needs. The most important setting here is the pool size, which defines the maximum number of connections allowed to this very specific pgbouncer virtual database.
Note that the size of the pool is not necessarily related to the number of connections to PostgreSQL. There can be more than just one pending connection to pgbouncer waiting for a connection to PostgreSQL.
The important thing here is that you can use pgbouncer to relay to various different databases on many different hosts—it is not necessary that all databases reside on the same host, so pgbouncer can also help to centralize your network configuration.
Note that we connect to pgbouncer using separate passwords—as all connections are in the pool we don't authenticate against PostgreSQL itself.
Finally we can configure an optional connect_query. Using this setting we can define a query, which has to be executed as soon as the connection has been passed on to the application. What is this good for? Well, you might want to set some variables in your database, clean it or simply change some runtime parameters straight away.
Sometimes you simply don't want to list all database connections. Especially if there are many databases, this can come in handy. The idea is to direct all requests that have not been listed before to the fallback server:
* = host=fallbackserver
Connections to p0 and p1 will be handled as before–everything else will go to the fallback connect string.
In our example, pgbouncer will listen on port 6432. We have set listen_addr to 127.0.0.1 so for now, only local connections are allowed. Basically listen_addr works just like listen_addresses in postgresql.conf. We can define where to listen for IP addresses.
In our setup pgbouncer will produce a fair amount of log entries. To channel this log in to a logfile we have used the
logfile directive in our config. It is highly recommended to write logfiles to make sure that you can track all relevant things going on in your bouncer.
Once we have configured our databases and other basic settings, we can turn our attention to authentication. As you have already seen, this local config points to those databases in your setup. All applications will point to pgbouncer so all authentication-related stuff will actually be handled by the bouncer. How does it work? Well, pgbouncer accepts the same authentication methods supported by PostgreSQL, such as md5 (the auth_file may contain md5-encrypted passwords), crypt (plain text passwords in auth_file), plain (clear text passwords), trust (no authentication) and any (like trust but ignores user names).
The auth_file itself has a very simple format:
"hs" "15359fe57eb03432bf5ab838e5a7c24f" "zb" "15359fe57eb03432bf5ab838e5a7c24f"
The first column holds the username, then comes a tab, and finally there is either a plain text string or an md5-encrypted password.
Once we have written this basic config and started up the system, we can safely connect to one of the databases listed:
hs@iMac:~$ psql -p 6432 p1 -U hs psql (9.2.4) Type "help" for help. p1=#
In our example we are connecting ourselves to the database called p1. We can see that the shell has been opened normally and we can move on and issue the SQL we want just as if we were connected to a normal database.
The logfile will also reflect our efforts to connect to the database and state:
2013-04-25 18:10:34.830 22598 LOG C-0xbca010: p1/hs@unix:6432 login attempt: db=p1 user=hs 2013-04-25 18:10:34.830 22598 LOG S-0xbe79c0: p1/[email protected]:5432 new connection to server
For each connection we get various log entries so that an administrator can easily check what is going on.
If you happen to use Java as frontend there are some points that have to be taken into consideration. Java tends to pass some parameters to the server as part of the connection string. One of those parameters is extra_float_digits. This postgresql.conf parameter governs the floating point behavior of PostgreSQL and is set by Java to make things more deterministic.
The problem is that pgbouncer will only accept the tokens listed in the previous section—otherwise it will error out.
To get around this issue you can add a directive to your bouncer config (pgbouncer section of the file):
ignore_startup_parameters = extra_float_digits
This will ignore the JDBC setting and allow pgbouncer to handle the connection normally. If you want to use Java we suggest putting those parameters into postgresql.conf directly to make sure that no nasty issues will pop up during production.
In the configuration you must have also seen a config variable called pool_mode, which has not been described yet. The reason for that is that the pool mode is so important that we have dedicated an entire section to it.
In general three different pool modes are available:
session is the default mode of pgbouncer. A connection will go back to the pool as soon as the application disconnects from the bouncer. In many cases this is the desired mode because we simply want to save on connection overhead—nothing more.
However, in some cases it might even be useful to return sessions to the pool faster. This is especially important if there are lags between various transactions. In transaction mode, pgbouncer will immediately return a connection to the pool at the end of a transaction (and not when the connection ends). The advantage of this is that we can still enjoy the benefits of transactions but connections are returned much sooner and therefore we can use those open connections more efficiently. For most web applications this can be a big advantage because the lifetime of a session has to be very short.
The third pooling option, statement allows us to return a connection immediately at the end of a statement. This is a highly aggressive setting and has basically been designed to serve high-concurrency setups in which transactions are not relevant at all. To make sure that nothing can go wrong in this setup, long transactions spanning more than one just statement are not allowed.
Most people will stick to the default mode here but you have to keep in mind that other options exist.
One advantage of a clean and fresh connection after PostgreSQL calls fork() is the fact that the connection does not contain any faulty settings, any open cursors, or any other leftovers whatsoever. This makes a fresh connection safe to use and avoids side effects of other connections.
As you have learned in this chapter, pgbouncer will reuse connections to avoid those fork() calls. The question now is, "How can we ensure that some application does not suffer from side effects caused by some other connection?"
The answer to this problem is the server_reset_query: Whenever a connection is returned to the pool, pgbouncer is able to run a query or a set of queries designed to clean up your database connection. This could be basically any query. In practical setups it has proven to be wise to call DISCARD ALL. DISCARD ALL is a PostgreSQL instruction, which has been designed to clean out an existing connection by closing all cursors, resetting parameters, and so on. After DISCARD ALL a connection is as fresh as after a fork() call and can safely be reused by a future request.
Keep in mind that there is no need to run an explicit ROLLBACK before a connection goes back to the pool or after it is fetched from the pool. Rolling back transactions is already done by pgbouncer automatically so you can be perfectly sure that a connection is never inside a transaction.