This book is the official documentation of Pgpool-II. It has been written by the Pgpool-II developers and other volunteers in parallel to the development of the Pgpool-II software. It describes all the functionality that the current version of Pgpool-II officially supports. To make the large amount of information about Pgpool-II manageable, this book has been organized in several parts. Each part is targeted at a different class of users, or at users in different stages of their Pgpool-II experience: is an informal introduction for new users. describes the installation and administration of the server. Everyone who runs a Pgpool-II server, be it for private use or for others, should read this part. explains several configuration examples so that users can choose the starting point of their actual systems. contains reference information about SQL commands, client and server programs. This part supports the other parts with structured information sorted by command or program. is an appendix information such as release notes. Pgpool-II manages a pool of PostgreSQL servers to achieve some features that are not available with single PostgreSQL installation. The features include: High Availability Pgpool-II provides a high availability (HA) feature by using multiple PostgreSQL servers so that it automatically removes broken server from the server pool to continue the database task. This is called automatic failover. Also Pgpool-II offers an HA feature for Pgpool-II itself, called Watchdog (see for more details). Moreover Pgpool-II hires sophisticated quorum algorithm to avoid false positive errors and split brain problem to make the whole HA system highly reliable. See for more details. Load balancing Pgpool-II distributes read queries over multiple PostgreSQL servers to gain higher performance. This feature is called load balancing. Write queries are sent to either the primary server (in streaming replication mode) or all servers (in native replication mode and snapshot isolation mode). See for more details about these mode. In any case, Pgpool-II automatically distinguish the read queries from the write queries. Besides these essential features, Pgpool-II also provides useful features such as: Connection Pooling Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput. Online Recovery Pgpool-II can perform online recovery of database node by executing one command. When the online recovery is used with the automated fail over, a detached node by fail over is possible to attach as standby node automatically. It is possible to synchronize and attach new PostgreSQL server too. Limiting Exceeding Connections There is a limit on the maximum number of concurrent connections with PostgreSQL, and new connections are rejected when this number is reached. Raising this maximum number of connections, however, increases resource consumption and has a negative impact on overall system performance. Pgpool-II also has a limit on the maximum number of connections, but extra connections will be queued instead of returning an error immediately. However, you can configure to return an error when the connection limit is exceeded (4.1 or later). Watchdog Watchdog can coordinate multiple Pgpool-II, create a robust cluster system and avoid the single point of failure or split brain. To avoid the split brain, you need at least 3 Pgpool-II nodes. Watchdog can perform lifecheck against other pgpool-II nodes, to detect a fault of Pgpool-II. If active Pgpool-II goes down, standby Pgpool-II can be promoted to active, and take over Virtual IP. In Memory Query Cache In memory query cache allows to save a pair of SELECT statement and its result. If an identical SELECTs comes in, Pgpool-II returns the value from cache. Since no SQL parsing nor access to PostgreSQL are involved, using in memory cache is extremely fast. On the other hand, it might be slower than the normal path in some cases, because it adds some overhead of storing cache data. Pgpool-II speaks PostgreSQL's backend and frontend protocol, and relays messages between a backend and a frontend. Therefore, a database application (frontend) thinks that Pgpool-II is the actual PostgreSQL server, and the server (backend) sees Pgpool-II as one of its clients. Because Pgpool-II is transparent to both the server and the client, an existing database application can be used with Pgpool-II almost without a change to its source code. Pgpool-II works on Linux, FreeBSD, and most of the UNIX-like architectures. Windows is not supported. Supported PostgreSQL server's versions are 7.4 and higher (some of the features may not work with older versions of PostgreSQL). You must also make sure that all of your PostgreSQL servers are using the same major version. In addition to this, we do not recommend mixing different PostgreSQL installation with different build options: including supporting SSL or not, to use --disable-integer-datetimes or not, different block size. These might affect part of functionality of Pgpool-II. The difference of PostgreSQL minor versions is not usually a problem. However we do not test every occurrence of minor versions and we recommend to use exact same minor version of PostgreSQL. There are some restrictions to using SQL via Pgpool-II. See Restrictions for more details. &history; ¬ation; &info; &restrictions; &problems;