How to install PGbouncer in Ubuntu

how to install pgbouncer in linux and how does pgbouncer work and how to use pgbouncer and how to restart pgbouncer
Dr.MohitBansal Profile Pic
Dr.MohitBansal,Canada,Teacher
Published Date:26-10-2017
Your Website URL(Optional)
Comment
Working with PgBouncer When you are working with a large-scale installation, it is sometimes quite likely that you have to deal with many concurrent open connections. Nobody will put up 10 servers to serve just two concurrent users; in many cases, this simply makes no sense. A large installation will usually have to deal with hundreds, or even thousands, of concurrent connections. Introducing a connection pooler, such as PgBouncer, will help improve performance of your systems. PgBouncer is really the classic workhorse when it comes to connection pooling. It is rock solid and it has already been around for many years. Usually creating thousands and thousands of connections can be quite an overhead, because every time a connection to PostgreSQL is created, afork() call is required. If a connection is only used for a short period of time, this can be expensive to do. This is exactly when PgBouncer should be used. Basically, PgBouncer is not a replication-related tool. However, we have decided to include it in this book because it is often used in combination with replication to make it work more efficiently. In this chapter, we will take a deep look at PgBouncer and see how it can be installed and used to speed up our installation. This chapter is not meant to be a comprehensive guide to PgBouncer, and it can in no way replace the official documentation (https://pgbouncer.github.io/overview.html). The following topics will be covered in this chapter: • The purpose of PgBouncer • Fundamental concepts of connection pooling • Installing PgBouncer • Configuring and administering PgBouncer • Performance tuning • Making PgBouncer work with Java 151 Working with PgBouncer Understanding the fundamental PgBouncer concepts As stated before, the basic idea of PgBouncer is to save connection-related costs. When a user creates a new database connection, it usually means burning a couple of hundred kilobytes of memory. This consists of approximately 20 KB of shared memory and the amount of memory used by the process serving the connection itself. While the memory consumption itself might not be a problem, the actual creation process of the connection can be comparatively time consuming. What does "time consuming" mean? Well, if you create a connection and use it, you might not even notice the time PostgreSQL needs to fork a connection. But let's take into account what a typical website does. It opens a connection, r fi es a handful of simple statements, and disconnects. Even though creating a connection can be barely noticed, it is still a fair amount of work compared to all the rest. How long can looking up a handful of phone numbers or some other trivial information take, after all? So, the less the work a single connection has to do in its life cycle, the more important the time taken to actually create the connection becomes. PgBouncer solves this problem by placing itself between the actual database server and the heavily used application. To the application, PgBouncer looks just like a PostgreSQL server. Internally, PgBouncer will simply keep an array of open connections and pool them. Whenever a connection is requested by the application, PgBouncer will take the request and assign a pooled connection. In short, it is some sort of proxy. The main advantage here is that PgBouncer can quickly provide a connection to the application, because the real database connection already exists behind the scenes. In addition to this, a very low memory footprint can be observed. Users have reported a footprint that is as small as 2 KB per connection. This makes PgBouncer ideal for very large connection pools. Installing PgBouncer Before we dig into the details, we will see how PgBouncer can be installed. Just as with PostgreSQL, you can take two routes. You can either install binary packages or simply compile from source. In our case, we will show you how a source installation can be performed: 1. The first thing you have to do is download PgBouncer from the ofc fi ial website athttp://pgfoundry.org/projects/pgbouncer. 152 Chapter 8 2. Once you have downloaded the.tar archive, you can safely unpack it using the following command: tar xvfz pgbouncer-1.5.4.tar.gz 3. Once you are done with extracting the package, you can enter the newly created directory and runconfigure. Expect this to fail due to missing packages. In many cases, you have to installlibevent (the development package) first before you can successfully run configure. On Debian (or Debian-based distributions), the easiest way to install thelibevent development package is to runapt-get install libevent-dev. 4. Once you have successfully executedconfigure, you can move forward and runmake. This will compile the source and turn it into binaries. Once this is done, you can finally switch to root and runmake install to deploy those binaries. Keep in mind that the build process needs PostgreSQL server development packages, as access topg_config (a tool used to congu fi re external software) is needed. You have now successfully installed PgBouncer. Configuring your first PgBouncer setup 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 andp1). The idea behind the setup performed in this example is to use PgBouncer as a proxy. Writing a simple config file and starting PgBouncer up To make PgBouncer work, we can write a simple cong fi fi le, and this file 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 153 Working with PgBouncer 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: hsiMac:/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's do it step by step. Dispatching requests The first thing we have to configure when dealing with PgBouncer is the database servers we want to connect to. In our example, we simply create links top0 and p1. We put the connect strings in, and they 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 connection parameters are allowed:dbname,host,port,user, password,client_encoding,datestyle,timezone,pool_size, andconnect_ query. Everything up to the password is what you would use in any PostgreSQL connect string. The rest is used to adjust PgBouncer for 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. 154 Chapter 8 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 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 parameter. Using this setting, we can define a query that 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 straightaway. 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 top0 andp1 will be handled as before. Everything else will go to the fallback connect string. More basic settings In our example, PgBouncer will listen on port6432. We have setlisten_addr to 127.0.0.1, so for now, only local connections are allowed. Basically,listen_addr works just likelisten_addresses inpostgresql.conf. We can define where to listen for IP addresses. In most cases, you might want to use forlisten_addr, because you might want to take all bound network addresses into consideration. The idea is to exclude networks and make sure that PgBouncer does not listen to all of them. In our setup, PgBouncer will produce a fair amount of log entries. To channel this log into a log file, we have used the logfile directive in our config. It is highly recommended to write log files to make sure that you can track all the relevant data going on in your bouncer. 155 Working with PgBouncer Handling pool sizes So far, only two parameters for controlling the pool size have been used. However, there are more settings that can be used to control the behavior of PgBouncer. The following list of parameters is essential. max_client_conn Themax_client_conn setting has already been described briefly. The theoretical maximum used is as follows: max_client_conn + (max_pool_size total_databases total_users) If a database user is specified in the connect string (all users connect under the same username), the theoretical maximum is this: max_client_conn + (max_pool_size total_databases) The theoretical maximum should be never reached, unless somebody deliberately tries to come up with a special workload to reach the upper border. default_pool_size Thedefault_pool_size setting specifies the default number of connections allowed for each user/database pair. The default setting can be overwritten for a certain database. min_pool_size Themin_pool_size setting will make sure that new connections to the databases are added as soon as the number of spare connections drops below the threshold due to inactivity. If the load spikes rapidly,min_pool_size is vital to keep the performance up. reserve_pool_size Thereserve_pool_size setting specifies the number of additional connections that can be allowed to a pool. pool_size Thepool_size parameter sets the maximum pool size for this database. If the variable is not set, thedefault_pool_size parameter is used. 156 Chapter 8 Authentication Once we have configured our databases and other basic settings, we can turn our attention to authentication. As you have already seen, this local cong p fi oints to the databases in your setup. All applications will point to PgBouncer, so all authentication-related proceeding will actually be handled by the bouncer. How does it work? Well, PgBouncer accepts the same authentication methods supported by PostgreSQL, such as MD5 (theauth_file may contain passwords encrypted with MD5),crypt (plain-text passwords inauth_file),plain (clear-text passwords), trust (no authentication), andany (which is like trust, but ignores usernames). Theauth_file 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 a password encrypted with MD5. If you want to encrypt as a password, PostgreSQL offers the following command: test= SELECT md5('abc'); md5 900150983cd24fb0d6963f7d28e17f72 (1 row) Connecting to PgBouncer Once we have written this basic config and started up the system, we can safely connect to one of the databases listed: hsiMac: psql -p 6432 p1 -U hs psql (9.4.4) Type "help" for help. p1= In our example, we are connecting to the database calledp1. 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. 157 Working with PgBouncer The log file will also ree fl ct our efforts to connect to the database and state: 2013-04-25 18:10:34.830 22598 LOG C-0xbca010: p1/hsunix:6432 login attempt: db=p1 user=hs 2013-04-25 18:10:34.830 22598 LOG S-0xbe79c0: p1/hs127.0.0.1:5432 new connection to server For each connection, we get various log entries so that an administrator can easily check what is going on. Java issues If you happen to use Java as the 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 isextra_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 (the 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 recommend putting those parameters into postgresql.conf directly to make sure that no nasty issues will pop up during production. Pool modes In the configuration, you might have also seen a config variable called pool_mode, which has not been described yet. The reason is that pool modes are so important that we have dedicated an entire section to them. In general, three different pool modes are available: • session • transaction • statement 158 Chapter 8 Thesession mode 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—and nothing more. Nowadays, many programming languages' frameworks come with some sort of connection management of their own. So, it is actually quite rare to find a modern application that just opens a connection, executes some small transactions, and disconnects. However, in some cases, it might be useful to return sessions to the pool faster. This is especially important if there are lags between various transactions. In the 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 efc fi iently. For most web applications, this can be a big advantage because the lifetime of a session has to be very short. In thetransaction mode, some commands, such asSET,RESET,LOAD, and so on, are not fully supported due to side effects. 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 statement are not allowed. Most people will stick to the default mode here, but you have to keep in mind that other options exist. Cleanup issues One advantage of a clean and fresh connection after PostgreSQL callsfork() is the fact that the connection does not contain any faulty settings, open cursors, or 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?" 159 Working with PgBouncer The answer to this problem isserver_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 been proven to be wise to callDISCARD ALL. TheDISCARD ALL command is a PostgreSQL instruction that has been designed to clean out an existing connection by closing all cursors, resetting parameters, and so on. AfterDISCARD ALL, a connection is as fresh as after afork() call and can be reused safely by a future request. Keep in mind that there is no need to run an explicitROLLBACK command 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. Improving performance Performance is one of the key factors when considering PgBouncer in the first place. To make sure that performance stays high, some issues have to be taken seriously. First of all, it is recommended to make sure that all nodes participating in your setup are fairly close to each other. This greatly helps reduce network round trip times, and thus boosts performance. There is no point in reducing the overhead of callingfork() and paying for this gain with network time. Just as in most scenarios, reducing network time and latency is definitely a huge benefit. Basically, PgBouncer can be placed on a dedicated PgBouncer server, on the database node directly, or on the web server. In general, it is recommended to avoid putting database infrastructure onto the web server. If you have a larger setup, a dedicated server might be a good option. Mixing up things too much can lead to nasty side effects. If one component of the system starts to go crazy, it might have horrible side effects and harm others. Therefore, isolating components in containers or having things on different servers might be a good idea in many cases. One additional issue, which is often forgotten, is related to pooling itself. As we have already stated, the idea of PgBouncer is to speed up the process of getting a database connection. But what if the pool is short of connections? If there are no spare database connections idling around, what will happen? Well, you will spend a lot of time making those connections by forking them in the backend. To fix this problem, it is recommended that you setmin_pool_size to a reasonable value. This is especially important if many connections are created at the same time (if a web server is restarted, for example). Always make sure that your pool is reasonably sized to sustain high performance (in terms of creating new connections). 160 Chapter 8 The perfect value ofmin_pool_size will depend on the type of application you are running. However, we have good experience with values substantially higher than the default. To figure out how to set this value, check out the PgBouncer system statistics and see the maximum number of connections used, the number of connections in use at the moment, and so on. Based on this statistical information, a wise choice can be made. A simple benchmark In this chapter, we have already outlined that it is very beneficial to use PgBouncer if many short-lived connections have to be created by an application. To prove our point, we have compiled an extreme example. The goal is to run a test doing as little as possible. We want to measure merely how much time we spend in opening a connection. To do so, we have set up a virtual machine with just one CPU. The test itself will be performed usingpgbench (a contribution module widely used to benchmark PostgreSQL). We can easily create ourselves a nice and shiny test database: pgbench -i p1 Then we have to write ourselves a nice sample SQL command that should be executed repeatedly: SELECT 1; Now we can run an extreme test against our standard PostgreSQL installation: hsVM:test pgbench -t 1000 -c 20 -S p1 -C -f select.sql starting vacuum...end. transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 20 number of threads: 1 number of transactions per client: 1000 number of transactions actually processed: 20000/20000 tps = 67.540663 (including connections establishing) tps = 15423.090062 (excluding connections establishing) 161 Working with PgBouncer We want to run 20 concurrent connections. They all execute 1,000 single transactions. The–C option indicates that after every single transaction, the benchmark will close the open connection and create a new one. This is a typical case on a web server without pooling—each page might be a separate connection. Now keep in mind that this test has been designed to look ugly. We may observe that keeping the connection alive will make sure that we can execute roughly 15,000 transactions per second on our single VM CPU. If we have to fork a connection each time, we will drop to just 67 transactions per second, as we have stated before. This kind of overhead is worth thinking about. Let's now repeat the test and connect to PostgreSQL through PgBouncer: hsVM:test pgbench -t 1000 -c 20 -S p1 -C -f select.sql -p 6432 starting vacuum...end. transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 20 number of threads: 1 number of transactions per client: 1000 number of transactions actually processed: 20000/20000 tps = 1013.264853 (including connections establishing) tps = 2765.711593 (excluding connections establishing) As you can see, our throughput has risen to 1,013 transactions per second. This is 15 times more than before—indeed a nice gain. However, we also have to see whether our performance level has dropped since we did not close the connection to PgBouncer. Remember that the bouncer, the benchmark tool, and PostgreSQL are all running on the same single CPU. This does have an impact here (context switches are not too cheap in a virtualized environment). Keep in mind that this is an extreme example. If you repeat the same test with longer transactions, you will see that the gap logically becomes much smaller. Our example has been designed to demonstrate our point. Maintaining PgBouncer In addition to what we have already described in this chapter, PgBouncer has a nice interactive administration interface capable of performing basic administration and monitoring tasks. 162 Chapter 8 How does it work? PgBouncer provides you with a fake database calledpgbouncer. It cannot be used for queries, as it only provides a simple syntax for handling basic administrative tasks. If you are using PgBouncer, please don't use a normal database called pgbouncer. It will just amount to confusion and yield zero benefits. Configuring the admin interface To configure this interface, we have to adapt our config file. In our example, we will simply add one line to the config (in the PgBouncer section of the file): admin_users = zb We want Zoltan, whose username iszb, to be in charge of the admin database, so we simply add him here. If we want many users to get access to the system, we can list them one after another (separated by commas). After restarting PgBouncer, we can try connecting to the system: psql -p 6432 -U zb pgbouncer psql (9.4.4, server 1.5.4/bouncer) WARNING: psql version 9.4, server version 1.5. Some psql features might not work. Type "help" for help. Don't worry about the warning message; it is just telling us that we have connected to something that does not look like a native PostgreSQL 9.4 database instance. Using the management database Once we have connected to this virtual management database, we can check which commands are available there. To do so, we can runSHOW HELP: pgbouncer= SHOW HELP; NOTICE: Console usage DETAIL: SHOW HELPCONFIGDATABASESPOOLSCLIENTSSERVERSVERSION SHOW STATSFDSSOCKETSACTIVE_SOCKETSLISTSMEM SHOW DNS_HOSTSDNS_ZONES SET key = arg 163 Working with PgBouncer RELOAD PAUSE db RESUME db KILL db SUSPEND SHUTDOWN SHOW As we have mentioned, the system will only accept administrative commands; normalSELECT statements are not possible in this virtual database: pgbouncer= SELECT 1+1; ERROR: invalid command 'SELECT 1+1;', use SHOW HELP; Extracting runtime information One important thing you can do with the management interface is figure out which databases have been configured for the system. To do that, you can call the SHOW DATABASES command: pgbouncer= \x Expanded display is on. pgbouncer= SHOW DATABASES; - RECORD 1 + name p0 host localhost port 5432 database p0 force_user pool_size 20 reserve_pool 0 - RECORD 2 + name p1 host localhost port 5432 database p1 force_user pool_size 20 164 Chapter 8 reserve_pool 0 - RECORD 3 + name pgbouncer host port 6432 database pgbouncer force_user pgbouncer pool_size 2 reserve_pool 0 As you can see, we have two productive databases and the virtualpgbouncer database. What is important here to see is that the listing contains the pool size as well as the size of the reserved pool. It is a good check to see what is going on in your bouncer setup. Once you have checked the list of databases on your system, you can turn your attention to the active clients in your system. To extract the list of active clients, PgBouncer offers theSHOW CLIENTS instruction: pgbouncer= \x Expanded display is on. pgbouncer= SHOW CLIENTS; - RECORD 1 + type C user zb database pgbouncer state active addr unix port 6432 local_addr unix local_port 6432 connect_time 2013-04-29 11:08:54 request_time 2013-04-29 11:10:39 ptr 0x19e3000 link At the moment, we have exactly one user connection to thepgbouncer database. We can see nicely where the connection comes from and when it has been created.SHOW CLIENTS is especially important if there are hundreds, or even thousands, of servers on the system. 165 Working with PgBouncer Sometimes, it can be useful to extract aggregated information from the system. The SHOW STATS parameter will provide you with statistics about what is going on in your system. It shows how many requests have been performed, and how many queries have been performed on an average: pgbouncer= SHOW STATS; - RECORD 1 + database pgbouncer total_requests 3 total_received 0 total_sent 0 total_query_time 0 avg_req 0 avg_recv 0 avg_sent 0 avg_query 0 Finally, we can take a look at the memory consumption we are facing. PgBouncer will return this information ifSHOW MEM is executed: pgbouncer= SHOW MEM; name size used free memtotal ++++ user_cache 184 4 85 16376 db_cache 160 3 99 16320 pool_cache 408 1 49 20400 server_cache 360 0 0 0 client_cache 360 1 49 18000 iobuf_cache 2064 1 49 103200 (6 rows) As you can see, PgBouncer is really lightweight and does not consume much memory as other connection pools do. It is important to see that all information is returned by PgBouncer as a table. This makes it really easy to process this data and use it in some kind of application. 166 Chapter 8 Suspending and resuming operations One of the core reasons for using the interactive virtual database is to be able to suspend and resume normal operations. It is also possible to reload the config on the fly, as shown in the following example: pgbouncer= RELOAD; RELOAD TheRELOAD command will reread the config, so there will be no need to restart the entire PgBouncer tool for most small changes. This is especially useful if there is just a new user. An additional feature of PgBouncer is the ability to stop operations for a while. But why would anybody want to stop queries for some time? Well, let's assume you want to perform a small change somewhere in your infrastructure. Just interrupt operations briefly without actually throwing errors. Of course, you have to be a little careful to make sure that your frontend infrastructure can handle such an interruption nicely. From the database side, however, it can come in handy. To temporarily stop queries, we can callSUSPEND: pgbouncer= SUSPEND; SUSPEND Once you are done with your changes, you can resume normal operations easily: pgbouncer= RESUME; RESUME Once this has been called, you can continue sending queries to the server. Finally, you can even stop PgBouncer entirely from the interactive shell, but it is highly recommended that you be careful when doing this: pgbouncer= SHUTDOWN; The connection to the server was lost. Attempting reset: Failed. The system will shut down instantly. 167 Working with PgBouncer Summary In this chapter, you learned how to use PgBouncer for highly scalable web applications to reduce the overhead of permanent creation of connections. We saw how to cong fi ure the system and how we can utilize a virtual management database. In the next chapter, you will be introduced to pgpool, a tool used to perform replication and connection pooling. Just like PgBouncer, pgpool is open source and can be used along with PostgreSQL to improve your cluster setups. 168 Working with pgpool In the previous chapter, we looked at PgBouncer and learned how to use it to optimize replicated setups as much as possible. In this chapter, we will take a look at a tool called pgpool. The idea behind pgpool is to bundle connection pooling with some additional functionality to improve replication, load balancing, and so on. It has steadily been developed over the years and can be downloaded freely from the www.pgpool.net website. Installing pgpool Just as we have seen for PostgreSQL, PgBouncer and most other tools covered in this book, we can either install pgpool from source or just use a binary. Again, we will describe how the code can be compiled from source. To install pgpool, we have to download it first from the following path: http://www.pgpool.net/download.php?f=pgpool-II-3.4.2.tar.gz. Once this has been done, we can extract the tarball: tar xvfz pgpool-II-3.4.2.tar.gz The installation procedure is just like we have seen already. The first thing we have to call isconfigure along with some parameters. In our case, the main parameter iswith-pgsql, which tells the build process where to find our PostgreSQL installation. ./configure with-pgsql=/usr/local/pgsql/ Of course, there are some additional settings: • with-openssl builds pgpool with OpenSSL support thus allowing encrypted connections 169 Working with pgpool • enable-table-lock andenable-sequence-lock are needed for compatibility with very old versions of pgpool • with-memached enabledmemcache support to make sure the query cache has enough memory to do its job efficiently Now, we can compile and install the software easily as follows: make make install Installing additional modules What you have just seen is a basic pgpool installation. However, to make things work really nicely, it can be beneficial to install additional modules such aspgpool- regclass andinsert_lock. Installingpgpool-regclass is important to handle DDL replication. Theinsert_lock parameter is important to handle distributed writes. It is highly recommended to install this module because otherwise handling DDLs won't work. Up to now, we have not seen a practical setup where using this module did not make sense. Let us installpgpool-regclass r fi st as follows: cd sql/pgpool-regclass/ make make install To enable the module, we have to deploy thepgpool-regclass.sql file. The module must be present in all databases we are going to use. The easiest way to achieve that is to simply load the SQL file into template1. Whenever a new database is created,template1 will be cloned; so all new databases will automatically have this module. It is also possible to use the following command: CREATE EXTENSION pgpool_regclass; The same applies toinsert_lock.sql, which can be found in thesql directory of the pgpool source code. The easiest solution is to load this intotemplate1 directly as follows: psql -f insert_lock.sql template1 Finally, there is thepgpool_recovery module. It is needed if you are planning to use online recovery in pgpool. Here is how installation works: cd pgpool-II-x.x.x/sql/pgpool-recovery 170