<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html">
<link rel="stylesheet" href="./pgpool.css" type="text/css">
<title>pgpool-II user manual</title>
</head>
<!-- hhmts start -->
Last modified: Tue Feb 2 09:30:57 JST 2010
<!-- hhmts end -->
<body bgcolor="#ffffff">
<a name="top"></a>
<table border="0" cellpadding="2" cellspacing="1">
<tr>
<td colspan="2" valign="top"><div class="header_text">Welcome to pgpool -II page</div></td>
</tr>
<tr>
<td valign="top" style="border-right:1px dotted #cccccc;">
<br />
<div id="navcontainer">
<ul id="navlist">
<li id="active"><a href="#Whatis" id="current">What is pgpool</a></li>
<li><a href="#platform">Platforms</a></li>
<li><a href="#install">pgpool-II Installation</a></li>
<li><a href="#config">Configuring pgpool-II</a></li>
<li><a href="#start">Starting/Stopping pgpool-II</a></li>
<li><a href="#reload">Reloading pgpool-II configuration files</a></li>
<li><a href="#online-recovery">Online recovery</a></li>
<li><a href="#restriction">Restrictions</a></li>
<li><a href="#reference">References</a></li>
<li><a href="#internal">internal</a></li>
</ul>
</div>
<br />
<div class="header_small" align="center">
[<a href="pgpool-ja.html">Japanese page</a>] </div> </td>
<td valign="top" style="border-left:1px dotted #cccccc;">
<h1>What is pgpool-II?<a name="whatis"></a></h1>
<p> pgpool-II is a middleware that works between PostgreSQL servers
and a PostgreSQL database client. It provides the following features.</p>
<p>
<ul>
<li>Connection Pooling</li>
<p>pgpool-II saves connections to the PostgreSQL servers, and reuse
them whenever a new connection with the same properties
(i.e. username, database, protocol version) comes in. It reduces
connection overhead, and improves system's overall throughput.</p>
<li>Replication</li>
<p>pgpool-II can manage multiple PostgreSQL servers. Using the
replication function enables creating a realtime backup on 2 or
more physical disks, so that the service can continue without stopping
servers in case of a disk failure.</p>
<li>Load Balance</li>
<p>If a database is replicated, executing a SELECT query on any
server will return the same result. pgpool-II takes an advantage of
the replication feature to reduce the load on each PostgreSQL server
by distributing SELECT queries among multiple servers, improving
system's overall throughput. At best, performance improves
proportionally to the number of PostgreSQL servers. Load balance works
best in a situation where there are a lot of users executing many
queries at the same time.</p>
<li>Limiting Exceeding Connections</li>
<p>There is a limit on the maximum number of concurrent
connections with PostgreSQL, and connections are rejected after this
many connections. Setting the maximum number of connections, however,
increases resource consumption and affect 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.</p>
<li>Parallel Query</li>
<p>Using the parallel query function, data can be divided
among the multiple servers, so that a query can be executed on all the
servers concurrently to reduce the overall execution time. Parallel query
works the best when searching large-scale data.</p>
</ul>
</p>
<p>pgpool-II talks PostgreSQL's backend and frontend protocol, and
relays a connection between them. 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 sources.</p>
<h1>Supported Platforms<a name="platform"></a></h1>
<p>pgpool-II works on Linux, Solaris, FreeBSD, and most of the
UNIX-like architectures. Windows is not supported. Supported
PostgreSQL server's versions are 6.4 and higher. To use the parallel
query, however, 7.4 and higher must be used.</p>
<h1>pgpool-II Installation<a name="install"></a></h1>
<p>
pgpool-II can be downloaded from <a href="http://pgfoundry.org/projects/pgpool/">pgpool Development page</a>.
Also packages are provided for various platforms including CentOS, RedHat Enterprise Linux, Fedora and Debian.
</p>
<p>Installing pgpool-II from source code requires gcc 2.9 or higher, and GNU
make. Also, pgpool-II links libpq library, so the libpq library and development
headers must be installed on a machine used to build pgpool-II. Additionally
the OpenSSL library and development headers must be present in order to
enable OpenSSL support in pgpool-II</p>
<dl>
<dt>configure</dt>
<dd>
<p>
After extracting the source tar ball, execute the configure script.
<pre>
./configure
</pre>
There are options that you can set if you want values other
than the default.
<ul>
<li><code>--prefix=path</code><br/>
pgpool-II binaries and docs will be installed to this
directory. Default value is <code>/usr/local</code></li>
<li><code>--with-pgsql=path</code><br/>
Top directory where PostgreSQL's client libraries are
installed. Default value is obtained by <code>pg_config</code></li>
<li><code>--with-openssl</code><br/>
pgpool-II binaries will be built with OpenSSL support. The default
is that OpenSSL support is disabled.</li>
</ul>
</p>
</dd>
<dt>make</dt>
<dd>
<p>
<pre>
make
make install
</pre>
will install pgpool-II. (If you use FreeBSD, replace make with gmake)
</p>
</dd>
</dl>
<h1>Configuring pgpool-II<a name="config"></a></h1>
<p>Configuration files for pgpool-II are
<code>/usr/local/etc/pgpool.conf</code> and
<code>/usr/local/etc/pcp.conf</code> by default. There are several
operation modes in pgpool-II. Each mode has associated functions which
can be enabled or disabled, and specific configuration parameters to
control their behaviors.</p>
<table border>
<tr>
<th>Function/Mode</th>
<th>raw Mode</th>
<th>Connection Pool Mode</th>
<th>Replication Mode</th>
<th>Master/Slave Mode</th>
<th>Parallel Query Mode</th>
</tr>
<tr>
<td>Connection Pool</td>
<td align="center">X</td>
<td align="center">O</td>
<td align="center">O</td>
<td align="center">O</td>
<td align="center">0</td>
</tr>
<tr>
<td>Replication</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">O</td>
<td align="center">X</td>
<td align="center">(*)</td>
</tr>
<tr>
<td>Load Balance</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">O</td>
<td align="center">O</td>
<td align="center">(*)</td>
</tr>
<tr>
<td>Failover</td>
<td align="center">O</td>
<td align="center">O</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
</tr>
<tr>
<td>Parallel Query</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">O</td>
</tr>
<tr>
<td>Required # of Servers</td>
<td align="center">1 or higher</td>
<td align="center">1 or higher</td>
<td align="center">2 or higher</td>
<td align="center">2 or higher</td>
<td align="center">2 or higher</td>
</tr>
<tr>
<td>System DB required?</td>
<td align="center">no</td>
<td align="center">no</td>
<td align="center">no</td>
<td align="center">no</td>
<td align="center">yes</td>
</tr>
</table>
(*)Parallel Query Mode requires the replication or the load-balancing turned on, however the function of the replication and the load-balancing cannot be used for the table preserved by dividing data in Parallel Query Mode.
<h2>Configuring <code>pcp.conf</code></h2>
<p>pgpool-II provides the control interface where an administrator
can collect pgpool-II status, and terminate pgpool-II processes via
network. The <code>pcp.conf</code> is the user/password file for
authentication with the interface. All modes require the
<code>pcp.conf</code> file to be set. After installing pgpool-II,
<code>$prefix/etc/pcp.conf.sample</code> is created. Change the name
of the file to <code>pcp.conf</code> and add your username and the
password.
</p>
<pre>
cp $prefix/etc/pcp.conf.sample $prefix/etc/pcp.conf
</pre>
<p>
An empty line or a line starting with "<code>#</code>" is treated as a
comment and will be ignored. The username and the password must be
written on one line using the following format:
</p>
<pre>
username:[password encrypted in md5]
</pre>
<p>
<code>[password encrypted in md5]</code> can be produced with the
<code>$prefix/bin/pg_md5</code> command.
</p>
<pre>
pg_md5 -p
password: <your password>
</pre>
or
<pre>
./pg_md5 foo
acbd18db4cc2f85cedef654fccc4a4d8
</pre>
<p>
The <code>pcp.conf</code> file must be readable by the user who
executes pgpool-II.</p>
<h2>Configuring <code>pgpool.conf</code></h2>
<p>As described already, each operation mode has specific
configuration parameters in <code>pgpool.conf</code>. After instaling
pgpool-II, <code>$prefix/etc/pgpool.conf.sample</code> is
created. Change the name of the file to <code>pgpool.conf</code> and
edit the contents.
<pre>
cp $prefix/etc/pgpool.conf.sample $prefix/etc/pgpool.conf
</pre>
<p>
An empty line or a line starting with "#" is treated as a
comment and will be ignored.</p>
</p>
<h3>raw Mode</h3>
<p>In the raw mode, clients simply connect to the PostgreSQL servers
via pgpool-II. This mode is useful for simply limiting excess
connections to the servers, or enabling failover with multiple
servers.</p>
<dl>
<dt>listen_addresses</dt>
<dd>
<p>Specifies the address in hostname or IP address, which will
be accepted by pgpool-II via TCP/IP network. <code>'*'</code> accepts
all incoming connections. <code>''</code> disables TCP/IP
connections. Default is <code>'localhost'</code>. Connections via UNIX
domain socket are always accepted. This parameter can only be set at server start.</p>
</dd>
<dt>port</dt>
<dd>
<p>The port number where pgpool-II accepts connections. Default
is 9999. This parameter can only be set at server start.</p>
</dd>
<dt>socket_dir</dt>
<dd>
<p>The directory path of the UNIX domain socket accepting
connections for pgpool-II. Default is <code>'/tmp'</code>. Be
aware that the socket might be deleted by cron. We recommend to
set this value to <code>'/var/run'</code> or such directory.
This parameter can only be set at server start.</p>
</dd>
<dt>pcp_port</dt>
<dd>
<p>The port number where PCP process accepts
connections. Default is 9898. This parameter can only be set at
server start.</p>
</dd>
<dt>pcp_socket_dir</dt>
<dd>
<p>The directory path of the UNIX domain socket accepting
connections for PCP process. Default is <code>'/tmp'</code>. Be
aware that the socket might be deleted by cron. We recommend to
set this value to <code>'/var/run'</code> or such
directory. This parameter can only be set at server start.</p>
</dd>
<dt>backend_socket_dir</dt>
<dd>
<p>The directory path of the PostgreSQL server's UNIX domain
socket, which is used by pgpool-II to communicate with the
server. Default is <code>'/tmp'</code>. This parameter can only
be set at server start.<p>
</dd>
<dt>pcp_timeout</dt>
<dd>
<p>PCP connection timeout value in seconds. If a client does not
respond within the set seconds, PCP closes the connection with
the client. Default is 10 seconds. 0 means no timeout. This
parameter can be changed on service.</p>
</dd>
<dt>num_init_children</dt>
<dd>
<p>The number of preforked pgpool-II server processes. Default
is 32. Please note that cancelling a query creates another
connection to the backend; thus, a query cannot be cancelled if
the connections are full. If you want to ensure that queries can
be cancelled, set this value to twice the expected
connections. This parameter can only be set at server start.</p>
</dd>
<dt>child_life_time</dt>
<dd>
<p>A pgpool-II child process' life time in seconds. When a child
is idle for so many seconds, it is terminated and the new child
is created. This parameter is a measure to prevent memory
leaks and other unexpected errors. Default value is 300 (5
minutes). 0 disables this function. Note that processes that have
not accepted any connections are not applicable for this.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>child_max_connections</dt>
<dd>
<p>A pgpool-II child process will be terminated after so many
connections from clients. This parameter is useful on the server
if it is too busy that child_life_time and connection_life_time
are not effective.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>client_idle_limit
<dd>
<p>Disconnect the connection to a client being idle for
client_idle_limit seconds since the last query has completed.
This is usefull for preventing for pgpool childs from being
ocuppied by a lazy client or TCP/IP connection between client and
pgpool is accidentally down. The default value for
client_idle_limit is 0, which means the functionality is turned
off. You need to reload pgpool.conf This parameter is ignored in
the second stage of on line recovery. if you change
client_idle_limit.</p>
<dt>authentication_timeout
<dd>
<p>Specify the timeout for pgpool authentication. 0 disables the time
out, which is the default. You need to restart pgpool-II if you
change authentication_timeout.</p>
<dt>logdir</dt>
<dd>
<p>The directory path of the logs. pgpool_status is written under this directory.
</p>
</dd>
<dt>pid_file_name</dt>
<dd>
<p>Full path to a file which contains pgpool's process id.
Default is "/var/run/pgpool/pgpool.pid".
You need to restart pgpool-II if you change the value.
</p>
</dd>
<dt>print_timestamp</dt>
<dd>
<p>Add timestamps to the logs when set to true. Default is
true.
This parameter can be changed on service.
You need to reload pgpool.conf if you change print_timestamp.
</p>
</dd>
<dt>connection_cache</dt>
<dd>
<p>Caches connections to backend when set to true. Default is
true.</p>
</dd>
<dt>health_check_timeout</dt>
<dd>
<p>pgpool-II periodically tries to connect to the backends to
detect any errors on the servers or networks. This error check
procedure is called "health check". If an error is detected,
pgpool-II tries to perform failover or degeneration.
This parameter is to prevent the health check to wait for a long
time in a case like network cable has been disconnected. The
timeout value is in seconds. Default value is 20. 0 disables
timeout (waits until TCP/IP timeout).
The health check requires one (1) extra connection to each
backend, so <code>max_connections</code> in the
<code>postgresql.conf</code> needs to be incremented as
needed.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>health_check_period</dt>
<dd>
<p>This parameter specifies the interval between the health
checks in seconds. Default is 0, which means health check is
disabled.
You need to reload pgpool.conf if you change health_check_period.
</p>
</dd>
<dt>health_check_user</dt>
<dd>
<p>The user name to perform health check. This user must exist
in all the PostgreSQL backends.
You need to reload pgpool.conf if you change health_check_user.
</p>
</dd>
<dt>failover_command
<dd>
<p>
This parameter specifies a command when a node is detached.
pgpool-II replaces special characters to backend information.
<center>
<table border>
<tr><td>Special character</td><td>Description</td></tr>
<tr><td>%d</td><td>Backend ID of a detached node.</td></tr>
<tr><td>%h</td><td>Hostname of a detached node.</td></tr>
<tr><td>%p</td><td>Port number of a detached node.</td></tr>
<tr><td>%D</td><td>Database cluster directory of a detached node.
</td></tr>
<tr><td>%M</td><td>Old master node ID.</td></tr>
<tr><td>%m</td><td>New master node ID.</td></tr>
<tr><td>%%</td><td>'%' character</td></tr>
</table>
</center>
You need to reload pgpool.conf if you change failover_command.
</p>
<p>
When a failover is performed, pgpool kills all its child processes, which
will in turn terminate all active sessions to pgpool. Then pgpool invoke
failover_command and wait for its completion.
After this, pgpool starts new child processes and becomes ready to wait
for connections from clients.
</p>
<dt>failback_command
<dd>
<p>
This parameter specifies a command when a node is attached.
pgpool-II replaces special characters to backend information.
<center>
<table border>
<tr><td>Special character</td><td>Description</td></tr>
<tr><td>%d</td><td>Backend ID of an attached node.</td></tr>
<tr><td>%h</td><td>Hostname of an attached node.</td></tr>
<tr><td>%p</td><td>Port number of an attached node.</td></tr>
<tr><td>%D</td><td>Database cluster path of an attached node.
</td></tr>
<tr><td>%M</td><td>Old master node</td></tr>
<tr><td>%m</td><td>New master node</td></tr>
<tr><td>%%</td><td>'%' character</td></tr>
</table>
</center>
You need to reload pgpool.conf if you change failback_command.
</p>
<dt>fail_over_on_backend_error</dt>
<dd>
<p>
If true, trigger fail over when writing to the backend communication
socket fails. This is the same behavior of pgpool-II 2.2.x or
earlier. If set to false, pgpool will report an error and disconnect
the session.
Please note that, however, pgpool will do the fail over when connecting to backend fails or pgpool detects the administrative shutdown of postmaster.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>ignore_leading_white_space</dt>
<dd>
<p>pgpool-II ignores white spaces at the beginning of SQL
queries while in the load balance mode. It is useful for using
APIs like DBI/DBD:Pg which adds white spaces against the user's
will.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>log_statement</dt>
<dd>
<p>Produces SQL log messages when true. This is similar to the
log_statement parameter in PostgreSQL. It produces logs even if the
debug option was not passed to pgpool-II at startup.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>log_per_node_statement</dt>
<dd>
<p>Similar to log_statement, except that it prints logs for each DB node separately.
It would be usefull if you want to make sure that replication is working or not, for example.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>log_hostname</dt>
<dd>
<p>
If true, ps command status will show the client's hostname instead
of an IP address. Also, if log_connections is enabled, hostname will
be logged. You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>log_connections</dt>
<dd>
<p>
If true, all incoming connections will be printed to the log.
You need to reload pgpool.conf if you change the value.
</p>
</dd>
<dt>enable_pool_hba</dt>
<dd>
<p>
If true, use pool_hba.conf for client authentication. See <a href="#hba">
Setting up pool_hba.conf for client authentication</a>.
You need to restart pgpool-II if you change the value.
</p>
</dd>
<dt>backend_hostname</dt>
<dd>
<p>Specifies the host name of the PostgreSQL backend. The empty
string (<code>''</code>) means pgpool-II uses UNIX domain
socket.
Multiple backends can be specified by adding a number at the end
of the parameter name (e.g.<code>backend_hostname0</code>). This
number is referred to as "DB node ID", and it starts from 0. The
backend which was given the DB node ID of 0 will be called
"Master DB". When multiple backends are defined, the service can
be continued even if the Master DB is down (not true in some
modes). In this case, the youngest DB node ID alive will be the
new Master DB.</p>
<p>If you plan to use only one PostgreSQL server, specify it by
<code>backend_hostname0</code>.</p>
<p>
This parameter can be added by reloading a configuration
file. However, this cannot be updated so you must restart
pgpool-II.
</p>
</dd>
<dt>backend_port</dt>
<dd>
<p>Specifies the port number of the backends. Multiple backends
can be specified by adding a number at the end of the parameter
name (e.g. <code>backend_port0</code>). If you plan to use only
one PostgreSQL server, specify it by
<code>backend_port0</code>.</p>
<p>
This parameter can be added by reloading a configuration
file. However, this cannot be updated so you must restart
pgpool-II.
</p>
</dd>
<dt>backend_weight</dt>
<dd>
<p>Specifies the load balance ratio for the backends. Multiple
backends can be specified by adding a number at the end of the
parameter name (e.g. <code>backend_weight0</code>). If you plan
to use only one PostgreSQL server, specify it by
<code>backend_weight0</code>. In the raw mode, set to 1.</p>
<p>
This parameter can be added by reloading a configuration
file. However, this cannot be updated so you must restart
pgpool-II.
In pgpool-II 2.2.6/2.3 or later, you can change the value by re-loading the configuration file.
This will take effect on next newly connected client sesson.
This is usefull if you want to prevent any query sent to slaves to perform some administrative work in master/slave mode.
</p>
</dd>
<dt>backend_data_directory</dt>
<dd>
<p>Specifies the database cluster directory of the
backends. Multiple backends can be specified by adding a number
at the end of the parameter name
(e.g. <code>backend_data_directory0</code>).
If you plan not to use online recovery, you do not need to
specify this parameter.
</p>
<p>
This parameter can be added by reloading a configuration
file. However, this cannot be updated so you must restart
pgpool-II.
</p>
</dd>
<dt>ssl</dt>
<dd>
<p>
If true, enable SSL support for both the frontend and backend
connections. Note that <code>ssl_key</code> and <code>ssl_cert</code>
must also be set in order for SSL to work in the frontend connections.
</p>
<p>
The default is that SSL is off. Note that OpenSSL support must also
have been configured at compilation time, as mentioned in the
<a href="#install">installation</a> section.
</p>
<p>
The pgpool-II daemon must be restarted when updating SSL related settings.
</p>
</dd>
<dt>ssl_key</dt>
<dd>
<p>
The path to the private key file to use for incoming frontend connections.
</p>
<p>
There is no default value for this option, and if left unset SSL will
be disabled for incoming frontend connections.
</p>
</dd>
<dt>ssl_cert</dt>
<dd>
<p>
The path to the public x509 certificate file to use for incoming
frontend connections.
</p>
<p>
There is no default value for this option, and if left unset SSL will
be disabled for incoming frontend connections.
</p>
</dd>
</dl>
</dl>
<h4>Generating SSL certificates</h4>
Certificate handling is outside the scope of this document. The
<a href="http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html">
Secure TCP/IP Connections with SSL</a> page at postgresql.org has pointers
with sample commands for how to generate self-signed certificates.
<h4><p>Failover in the raw Mode</p></h4>
<p>Failover can be performed in the raw mode if multiple servers are
defined. pgpool-II usually accesses the backend specified by
<code>backend_hostname0</code> during the normal operation. If the
backend_hostname0 fails for some reason, pgpool-II tries to access the
backend specified by backend_hostname1. If that fails, pgpool-II tries
the backend_hostname2, 3 and so on.</p>
<h3>Connection Pool Mode</h3>
<p>In the connection pool mode, all functions in raw mode and the
connection pool function can be used. To enable this mode, set
configuration parameters in the raw mode and below.</p>
<dl>
<dt>max_pool</dt>
<dd>
<p>The maximum number of cached connections in pgpool-II
children processes. pgpool-II reuses the cached connection if an
incoming connection is connecting to the same database by the
same username. If not, pgpool-II creates a new connection to the
backend. If the number of cached connections exceeds max_pool,
the oldest connection will be discarded, and uses that slot for
the new connection.
Default value is 4. Please be aware that the number of
connections from pgpool-II processes to the backend will be
<code>num_init_children</code> * <code>max_pool</code>.
This parameter can only be set at server start. </p>
</dd>
<dt>connection_life_time</dt>
<dd>
<p>Cached connections expiration time in seconds. The expired
cached connection will be disconnected. Default is 0, which
means the cached connections will not be disconnected.</p>
</dd>
<dt>reset_query_list</dt>
<dd>
<p>Specifies the SQL commands sent to the backend when exitting
a session to reset the connection. Multiple commands can be
specified by delimitting each by ";". Default is
the following, but can be changed to suit your system.
<pre>
reset_query_list = 'ABORT; DISCARD ALL'
</pre>
<p>
Commands differ in each PostgreSQL versions. Here are recommended settings.
</p>
<p>
<table border>
<tr><th>PostgreSQL version</th><th>reset_query_list value</th></tr>
<tr><td>7.1 or before</td><td>ABORT</td></tr>
<tr><td>7.2 to 8.2</td><td>ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT</td></tr>
<tr><td>8.3 or later</td><td>ABORT; DISCARD ALL</td></tr>
</table>
</p>
<ul>
<li>"ABORT" is not issued when not in a transaction block for 7.4 or later.
</ul>
<p>
You need to re-read pgpool.conf upon modification of this direrctive.
</p>
</dd>
</dl>
<h4><p>Failover in the Connection Pool Mode</p></h4>
<p>Failover in the connection pool mode is the same as in the raw mode.</p>
<h3>Replication Mode</h3>
<p>This mode enables data replication between the backends. The
configuration parameters below must be set in addtion to everything above.</p>
<dl>
<dt>replication_mode
<dd>
<p>Setting true enables replication mode. Default
is false.</p>
</dd>
<dt>load_balance_mode</dt>
<dd>
<p>When set to true, SELECT queries will be
distributed to each backend for load balance. Default is
false.</p>
</dd>
<dt>replication_stop_on_mismatch</dt>
<dd>
<p>When set to true, pgpool-II degenerates the
backends and keeps the service only with the Master DB if data
mismatch occurs. If false, pgpool-II just
terminates the query. Default is false.</p>
</dd>
<a name="replicate_select">
<dt>replicate_select</dt>
<dd>
<p>When set to true, pgpool-II replicate SELECTs. If false,
pgpool-II only sends them to Master DB. Default is false.</p>
</dd>
<p>
replicate_select, load_balance_mode, if the SELECT query is inside an
explicit transaction block will affect to how replication works. Details
are shown below.
</p>
<p>
<table border>
<tr>
<td>SELECT is inside a transaction block</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>Y</td>
<td>N</td>
</tr>
<tr>
<td>replicate_select is true</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>load_balance_mode is true</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>Y</td>
<td>Y</td>
</tr>
<tr>
<td>results(R:replication, M: send only to master，L: load balance</td>
<td>R</td>
<td>R</td>
<td>M</td>
<td>M</td>
<td>L</td>
<td>R</td>
<td>M</td>
<td>L</td>
</tr>
</table>
</p>
<dt>insert_lock</dt>
<dd>
<p>Replicating a table with SERIAL data type, the SERIAL column
value may differ between the backends. This problem is avoidable by
locking the table explicitly (although, transactions' parallelism will
be lost). To achieve this, however, the following change must be made:
<pre>
INSERT INTO ...
</pre>
to
<pre>
BEGIN;
LOCK TABLE ...
INSERT INTO ...
COMMIT;
</pre>
Setting <code>insert_lock</code> to true, pgpool-II
automatically adds the above queries each time INSERT is
executed (if already in transaction, simply adds LOCK TABLE
....).
As of pgpool-II 2.2, it automatically detects if the table has a
SERIAL columns or not, so only tables having SERIAL columns are locked.
Also you might want to make a fine control:
</p>
<ol>
<li>set <code>insert_lock</code> to true, and add <code>/*NO
INSERT LOCK*/</code> at the beginning of an INSERT
statement which you do not want to acquire the table
lock.</li>
<li>set <code>insert_lock</code> to false, and add
<code>/*INSERT LOCK*/</code> at the beginning of an INSERT
statement which you want to acquire the table lock.</li>
</ol>
<p>
Default value is false. If <code>insert_lock</code> is enabled,
the regression test for PostgreSQL 8.0 will fail in transactions,
privileges, rules and alter_table. The reasons for this is that
pgpool-II tries to LOCK the VIEW for the rule test, and others will
produce the following error message.
<pre>
! ERROR: current transaction is aborted, commands ignored until
end of transaction block
</pre>
For example, the transactions test tries to INSERT into a table
which does not exist, and pgpool-II causes PostgreSQL to acquire the
lock before that. The transaction will be aborted, and the following
INSERT statement produces the above error message.</p>
<dt>recovery_user
<dd>
<p>
This parameter specifies a PostgreSQL username for online recovery.
It can be changed on service.
</p>
<dt>recovery_password
<dd>
<p>
This parameter specifies a PostgreSQL password for online recovery.
It can be changed on service.
</p>
<dt>recovery_1st_stage_command
<dd>
<p>
This parameter specifies a command at the first stage of online
recovery. The command file must be put on database cluster directory
because of a security issue.
For example, if recovery_1st_stage_command = 'sync-command', then
pgpool-II executes $PGDATA/sync-command.
Note that pgpool-II <b>accepts</b> connections and queries while
recovery_1st_stage command is executed. You can retrieve and update
data.
</p>
<p>
It can be changed on service.
</p>
<dt>recovery_2nd_stage_command
<dd>
<p>
This parameter specifies a command at the second stage of online
recovery. The command file must be put on database cluster directory
because of a security issue.
For example, if recovery_2nd_stage_command = 'sync-command', then
pgpool-II executes $PGDATA/sync-command.
Note that pgpool-II <b>do not accept</b> connections and queries while
recovery_2nd_stage_command is executed. Thus if a client exists for a
long time, the command is not executed. pgpool-II waits until all
clients close their connections. The command is executed when no client
connects to pgpool-II.
</p>
<p>
It can be changed on service.
</p>
<dt>recovery_timeout
<dd>
<p>
pgpool does not accept connections at second stage. If a client
connect to pgpool during recovery processing, pgpool wait to be
close.
</p>
<p>
This parameter specifies recovery timeout in sec. If timeout, pgpool
cancels online recovery and accepts connections. 0 means no wait.
</p>
<p>
It can be changed on service.
</p>
<dt>client_idle_limit_in_recovery
<dd>
<p> Similar to client_idle_limit but only takes effect in recovery 2nd
stage. Disconnect the connection to a client being idle for
client_idle_limit_in_recovery seconds since the last query has
been sent. This is usefull for preventing for pgpool recovery
disturbed by a lazy client or TCP/IP connection between client and
pgpool is accidentally down. The default value for
client_idle_limit_in_recovery is 0, which means the functionality is turned
off. You need to reload pgpool.conf if you change
client_idle_limit_in_recovery.</p>
<dt>lobj_lock_table
<dd>
<p>
Specify table name used for large object replication control. If
it is specified, pgpool will lock the table specified by
lobj_lock_table and generate large object id by looking into
pg_largeobject system catalog and call lo_create to create large object.
This way, it's guaranteed to use same large object id in all DB nodes in
replication mode. Please note that PostgreSQL 8.0 or before does not
have lo_create, thus this functionality will not work.
</p>
<p>
Libpq function lo_creat() call will trigger this functionality. Also
large object creation through Java API (JDBC driver), PHP
API(pg_lo_create, or similar API in PHP library such as PDO) and same
API in various programing languages are known to use similar protocol,
thus should work.
</p>
<p>
Following large object create operation will not work:
<p>
<ul>
<li>lo_create of libpq
<li>Any API of any language using lo_create
<li>lo_import function in backend
<li>SELECT lo_creat
</ul>
</p>
<p>
It does not matter what lobj_lock_table's schema is, but it should be
writable by any user. Here is an example to create such a table:
</p>
<p>
<pre>
CREATE TABLE public.my_lock_table ();
GRANT ALL ON public.my_lock_table TO PUBLIC;
</pre>
</p>
<p>
The table specified by lobj_lock_table must be created beforehand. If
you create the table in template1, any database created afterward will
have the table.
</p>
<p>
If lobj_lock_table has empty string(''), the functionality is disabled
(thus large object replication will not work). The default value for
lobj_lock_table is ''.
</p>
</dl>
<h4><p>condition for load balance</p></h4>
<p>
For the query to be load balanced, all the requirements below
must be met:
<ul>
<li>PostgreSQL version 7.4 or later</li>
<li>the query must not be in an explicitly declared
transaction (i.e. not in a BEGIN ~ END block)</li>
<li>it's not SELECT nextval or SELECT setval
<li>it's not SELECT INTO
<li>it's not SELECT FOR UPDATE nor FOR SHARE
<li>start with "SELECT" (ignore_leading_white_space = true will
ignore leading white space) or one of COPY TO STDOUT, EXPLAIN, EXPLAIN ANALYZE SELECT...
</ul>
</p>
<p>
Note that you could supress load balancing by inserting arbitary
comments just in front of the SELECT query:
<pre>
/*REPLICATION*/ SELECT ...
</pre>
</p>
<p>
Please refer to <a href="#replicate_select">replicate_select</a> as well.
See also a <a href="load_balance.png">flow chart</a>.
</p>
<p>
<font color="red">
Note: JDBC driver has autocommit option. If autocommit is false, JDBC driver
send "BEGIN" and "COMMIT" internally. So pgpool cannot load balancing.
You need to call setAutoCommit(true) to enable autocommit.
</font>
</p>
<h4><p>Failover in the Replication Mode</p></h4>
<p> pgpool-II degenerates a dead backend and continues the
service. The service can be continued if there is at least one backend
alive.</p>
<h3>Master/Slave Mode</h3>
<p>This mode is for using pgpool-II with another master/slave
replication software (like Slony-I), so it really does the actual data
replication. DB nodes' information must be set as the replication mode.
In addtion to that, set
<code>master_slave_mode</code> and <code>load_balance_mode</code> to
true. pgpool-II will send queries that need to be replicated to the
Master DB, and others will be load balanced if possible.</p>
<p>In master/slave mode, DDL and DML for temporary table can be executed on master only.
SELECT should be executed on master only as well but for this you need to use comment /*NO LOAD BALANCE*/ before the SELECT statement.</p>
<p>In the master/slave mode, <code>replication_mode</code> must be set
to false, and <code>master_slave_mode</code> to true.</p>
<h3>Parallel Mode</h3>
<p>This mode enables parallel execution of queries.The table is divided, and data can be given to each node. Moreover, the replication and the loadbalance function can be used at the same time. In parallel mode, replication_mode and loadbalance_mode are set to true in pgpool.conf, master_slave is set to false, and parallel_mode is set to true. When you change this parameter, please reactivate pgpool-II.
</p>
<h4><p>Configuring the System DB</p></h4>
<p>To use the parallel mode, the System DB must be configured
properly. The System DB maintains rules, in the format of the database
table, to choose an appropriate backend to send partitioned
data. The System DB does not need to be created on the same host as
pgpool-II. The System DB's configuration is done in
<code>pgpool.conf</code>.</p>
<dl>
<dt>system_db_hostname</dt>
<dd>
<p>The hostname where the System DB is created. Specifying the
empty string ('') means the System DB is at the same host as
pgpool-II, and will be connected via UNIX domain socket.</p>
</dd>
<dt>system_db_port</dt>
<dd>
<p>The port number for the System DB</p>
</dd>
<dt>system_dbname</dt>
<dd>
<p>The partitioning rules and other information will be defined
in the database specified here. Default value is
<code>'pgpool'</code>.</p>
</dd>
<dt>system_db_schema</dt>
<dd>
<p>The partitioning rules and other information will be defined
in the schema specified here. Default value is
<code>'pgpool_catalog'</code>.</p>
</dd>
<dt>system_db_user</dt>
<dd>
<p>The username to connect to the System DB.</p>
</dd>
<dt>system_db_password</dt>
<dd>
<p>The password for the System DB. If no password is set,
set the empty string ('').</p>
</dd>
<dt>ssl_ca_cert</dt>
<dd>
<p>
The path to a PEM format file containing one or more CA root
certificates, which can be used to verify the backend server
certificate. This is analagous to the <code>-CAfile</code> option
to the OpenSSL <code>verify(1)</code> command.
</p>
<p>
The default value for this option is unset, such that no
verification takes place. Verification will still occur if
this option is not set but a value has been given for
<code>ssl_ca_cert_dir</code>.
</p>
</dd>
<dt>ssl_ca_cert_dir</dt>
<dd>
<p>
The path to a directory containing PEM format CA certficate
files, which can be used to verify the backend server
certificate. This is analagous to the <code>-CApath</code> option
to the OpenSSL <code>verify(1)</code> command.
</p>
<p>
The default value for this option is unset, such that no
verification takes place. Verification will still occur if
this option is not set but a value has been given for
<code>ssl_ca_cert</code>.
</p>
</dd>
</dl>
<h4><p>Initial Configuration of the System DB</p></h4>
<p>First, create the database and schema specified in the
<code>pgpool.conf</code> file. A sample script can be found in
<code>$prefix/share/system_db.sql</code>. If you have specified a
different database name or schema, change them accordingly.
<pre>
psql -f $prefix/share/system_db.sql pgpool
</pre>
</p>
<h4><p>Registering a Partitioning Rule</p></h4>
<p>The rules for data partitioning must be registered to
<code>pgpool_catalog.dist_def</code> table.</p>
<pre>
CREATE TABLE pgpool_catalog.dist_def(
dbname TEXT, -- database name
schema_name TEXT, -- schema name
table_name TEXT, -- table name
col_name TEXT NOT NULL CHECK (col_name = ANY (col_list)), -- partitioning key column name
col_list TEXT[] NOT NULL, -- names of table attributes
type_list TEXT[] NOT NULL, -- types of table attributes
dist_def_func TEXT NOT NULL, -- name of the partitioning rule function
PRIMARY KEY (dbname,schema_name,table_name)
);
</pre>
<h4><p>Registering a Replication Rule</p></h4>
<p>
When the table that does the replication of one SQL sentence that specifies the table registered in the partitioning rule with JOIN etc. is specified, information on the table that does the replication is registered in the table named pgpool_catalog.replicate_def beforehand.
</p>
<pre>
CREATE TABLE pgpool_catalog.replicate_def(
dbname TEXT, --database name
schema_name TEXT, --schema name
table_name TEXT, --teble name
col_list TEXT[] NOT NULL, -- names of table attributes
type_list TEXT[] NOT NULL, -- types of table attributes
PRIMARY KEY (dbname,schema_name,table_name)
);
</pre>
<h4><p>Example for partitioning pgbench tables</p></h4>
<p>
It divides into the accounts table in this example, and the replication will be done to the branches table and the tellers table. Moreover, the accounts table and the banches table assume uniting with bid The branches table registers the rule of the replication table. When three tables of the accounts table and the branches table and the tellers table are united, it is necessary to register the rule of the replication table to the tellers table beforehand.
</p>
<pre>
INSERT INTO pgpool_catalog.dist_def VALUES (
'pgpool',
'public',
'accounts',
'aid',
ARRAY['aid','bid','abalance','filler'],
ARRAY['integer','integer','integer','character(84)'],
'pgpool_catalog.dist_def_accounts'
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'pgpool',
'public',
'branches',
ARRAY['bid','bbalance','filler'],
ARRAY['integer','integer','character(84)']
);
</pre>
<p>The partitioning rule function (namely,
pgpool_catalog.dist_def_accounts) takes a value for the partitioning
key column, and returns the corresponding DB node ID. Note that ID
must start from 0. Below is the example of each function for pgbench.
</p>
<pre>
CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts (val ANYELEMENT) RETURNS INTEGER AS '
SELECT CASE WHEN $1 >= 1 and $1 <= 30000 THEN 0
WHEN $1 > 30000 and $1 <= 60000 THEN 1
ELSE 2
</pre>
<h2><a name="hba"></a>Setting up pool_hba.conf for client authentication (HBA)</h2>
<p>
Just like pg_hba.conf with PostgreSQL, pgpool supports a similar
client authentication function using a configuration file called
"pool_hba.conf".
</p>
<p>
When you install pgpool, pool_hba.conf.sample will be installed in
"/usr/local/etc", which is the default directory for configuration
files. Copy pool_hba.conf.sample as pool_hba.conf and edit it if necessary.
By default, pool_hba authentication is enabled. See "6. Setting up
pgpool.conf" for more detail.
</p>
<p>
The format of pool_hba.conf file follows very closely pg_hba.conf's format.
</p>
<pre>
local DATABASE USER METHOD [OPTION]
host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
</pre>
<p>
See "pool_hba.conf.sample" for details of each field.
</p>
<p>
Here are the limitations of pool_hba.
<ul>
<li>"hostssl" connection type is not supported</li>
<p>
"hostssl" is invalid since pgpool currently does not support SSL
connections.
</p>
<li>"samegroup" for DATABASE field is not supported</li>
<p>
Since pgpool does not know anything about users in the backend server,
database name is simply checked against the entries in the DATABASE
field of pool_hba.conf.
</p>
<li>group names following "+" for USER field is not supported</li>
<p>
This is the same reason as in the "samegroup" described above. A
user name is simply checked against the entries in the USER field
of pool_hba.conf.
</p>
<li>IPv6 for IP address/mask is not supported</li>
<p>
pgpool currently does not support IPv6.
</p>
<li>Only "trust", "reject" and "pam" for METHOD field are supported</li>
<p>
Again, this is the same reason as in the "samegroup" described above.
pgpool does not hold user/password information.
</p>
</ul>
<p>
Note that everything described in this section is about a client
authentication between a client and pgpool; a client still have to go through
an authentication process with PostgreSQL. As far as pool_hba is concerned,
it does not matter if a user name and/or database name given by a client
(i.e. psql -U testuser testdb) really exist in the backend. pool_hba only
cares if a match in the pool_hba.conf is found or not.
</p>
<p>
PAM authentication is supported using user information on the host where
pgpool is executed. To enable PAM support in pgpool, specify "--with-pam"
option to configure:
</p>
<pre>
configure --with-pam
</pre>
<p>
To enable PAM authentication, you need to create a
service-configuration file for pgpool in the system's PAM
configuration directory (which is usually at "/etc/pam.d"). A sample
service-configuration file is installed as "share/pgpool.pam" under
the install directory.
</p>
<h2>Setting method of Query cache</h2>
<p>The Query cache can be used in all modes in pgpool-II. The setting of pgpool.conf is set as follows.</p>
<pre>
enable_query_cache = true
</pre>
<p>
Moreover, please make the following tables in the System DB.
</p>
<pre>
CREATE TABLE pgpool_catalog.query_cache (
hash TEXT,
query TEXT,
value bytea,
dbname TEXT,
create_time TIMESTAMP WITH TIME ZONE,
PRIMARY KEY(hash, dbname)
);
</pre>
<p>
However, please rewrite it suitably when you use a different schema because the schema name is "pgpool_catalog" in this example.
</p>
<h1>Starting/Stopping pgpool-II<a name="start"></a></h1>
<p>All the backends and the System DB (if necessary) must be started
before starting pgpool-II.
</p>
<pre>
pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file][-n][-d]
</pre>
<p>
<table>
<tr><td>-c<br/>--clear-cache</td><td>deletes query cache</tr>
<tr><td>-f config_file<br/>--config-file config-file</td><td>specifies pgpool.conf</tr>
<tr><td>-a hba_file<br/>--hba-file hba_file</td><td>specifies pool_hba.conf</tr>
<tr><td>-F pcp_config_file<br/>--pcp-password-file</td><td>specifies pcp.conf</tr>
<tr><td>-n<br/>--no-daemon</td><td>no daemon mode (terminal is not detached)</tr>
<tr><td>-d<br/>--debug</td><td>debug mode</tr>
</table>
There are two ways to stop pgpool-II. One is via PCP command
(described later) or pgpool-II command. Below is an example of the
pgpool-II command.
</p>
<pre>
pgpool [-f config_file][-F pcp_config_file] [-m {s[mart]|f[ast]|i[mmediate]}] stop
</pre>
<p>
<table>
<tr><td><code>-m s[mart]</code><br/><code>--mode s[mart]</code></td>
<td>waits for clients to disconnect, and shutdown (default)</td></tr>
<tr><td><code>-m f[ast]</code><br/><code>--mode f[ast]</code></td>
<td>does not wait for clients; shutdown immediately</td></tr>
<tr><td><code>-m i[mmediate]</code><br/><code>--mode i[mmediate]</code></td>
<td>the same as <code>'-m f'</code></td></tr> </table>
</p>
<p>
pgpool records backend status into [logdir]/pgpool_status file. When pgpool restarts it reads the file and restore the backend status. This will prevent data difference among DB nodes which might be caused by following scenario:
<ol>
<li>One of backend suddenly stopps and pgpool execute fail over
<li>Update one of active DB through pgpool
<li>Our administrator decides to stop pgpool
<li>Someone decides to restart the stopping DB without letting our admin know
<li>Our poor administrator restarts pgpool
</ol>
</p>
<p>
If for some reason, for example, the stopping DB is synched with the active DB by hand, you could remove pgpool_status safely before starting pgpool.
</p>
<h1>Reloading pgpool-II configuration files<a name="reload"></a></h1>
<p>pgpool-II can reload configuration files without restarting it.
</p>
<pre>
pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file] reload
</pre>
<p>
<table>
<tr><td>-f config_file<br/>--config-file config-file</td><td>specifies pgpool.conf</tr>
<tr><td>-a hba_file<br/>--hba-file hba_file</td><td>specifies pool_hba.conf</tr>
<tr><td>-F pcp_config_file<br/>--pcp-password-file</td><td>specifies pcp.conf</tr>
</table>
<p>
Please note that some configuration items are not changed with
relaoding. Also configuration reflects its changes after new session starts.
</p>
<h1><a name="online-recovery"></a>Online Recovery</h1>
<h2>Overview</h2>
<p>
pgpool-II, with replication mode, can sync database and attach a node
in service. We call that "online recovery".
</p>
<p>
A recovery target node must have detached before doing online recovery.
If you wish to add PostgreSQL server dynamically, add backend_hostname etc.
parameters and reload pgpool.conf. pgpool-II registers a new node as a
detached node.
</p>
<p>
<font color="red">caution: Stop autovacuum on the master node (the
first node which is up and running). Autovacuum may change the contents
of the database and might cause inconsistency after online recovery if
it's runnung.</font>
</p>
<p>
If PostgreSQL have already started, you need to shutdown PostgreSQL
process.
</p>
<p>
pgpool-II performs online recovery in separated two phase. It has a few seconds
or minutes client wait connecting to pgpool-II while a recovery
node synchronizes database. It follows these steps:
<ol>
<li> CHECKPOINT
<li> First stage of online recovery
<li> Waiting until all clients have disconnected
<li> CHECKPOINT
<li> Second stage of online recovery
<li> Starting up postmaster (perform pgpool_remote_start)
<li> Node attaching
</ol>
</p>
<p>
We call the first step of data sync "first stage". We synchronize
data in the first stage. In the first stage, you <b>can</b> update or
retrieve all tables concurrently.
</p>
<p>
You can specify a script executed at the first stage. pgpool-II
passes three arguments to the script.
<ol>
<li> Database cluster path of a master node.
<li> Hostname of a recovery target node.
<li> Database cluster path of a recovery target node.
</ol>
</p>
<p>
We synchronize data. We call it "second
stage". Before entering the second stage, pgpool-II waits until all
clients have disconnected. It blocks any connection until finishing
the second stage.
After all connections are disconnected, merge updated data between
the first stage and the second stage. We perform final data
synchronization.
</p>
<p>
<font color="red">
Note that there is a restriction about online recovery. If pgpool-II
works on multiple hosts, online recovery does not work
correctly, because pgpool-II stops clients on the 2nd stage of online
recovery. If there are some pgpool hosts, pgpool-II excepted for
receiving online recovery request cannot block connections.
</font>
</p>
<h2> Configuration for online recovery</h2>
<p>
Set the following parameters for online recovery in pgpool.conf.
<ul>
<li> backend_data_directory
<li> recovery_user
<li> recovery_password
<li> recovery_1st_stage_command
<li> recovery_2nd_stage_command
</ul>
</p>
<h2> Installation of C language function</h2>
<p>
You need to install the C language function for online recovery to
"template1" database of all backend nodes.
Source code is in pgpool-II tar ball.
</p>
<pre>
pgpool-II-x.x.x/sql/pgpool-recovery/
</pre>
<p>
Change the directory and do "make install".
</p>
<pre>
% cd pgpool-II-x.x.x/sql/pgpool-recovery/
% make install
</pre>
<p>
Then, install SQL function.
</p>
<pre>
% cd pgpool-II-x.x.x/sql/pgpool-recovery/
% psql -f pgpool-recovery.sql template1
</pre>
<h2> Recovery script deployment </h2>
<p>
We must deploy data sync scripts and a remote start script into
database cluster($PGDATA). Sample script files are available in
pgpool-II-x.x.x/sample directory.
</p>
<h3>Online recovery by PITR</h3>
<p>
We explain how to do online recovery by Point In Time Recovery(PITR).
Note that all PostgreSQL servers need to enable PITR.
</p>
<p>
We prepare a script to get base backup on a master node and copy to
recovery target node on the first stage. The script is named
"copy-base-backup" for example. Here is the sample script.
</p>
<pre>
#! /bin/sh
DATA=$1
RECOVERY_TARGET=$2
RECOVERY_DATA=$3
psql -c "select pg_start_backup('pgpool-recovery')" postgres
echo "restore_command = 'scp $HOSTNAME:/data/archive_log/%f %p'" > /data/recovery.conf
tar -C /data -zcf pgsql.tar.gz pgsql
psql -c 'select pg_stop_backup()' postgres
scp pgsql.tar.gz $RECOVERY_TARGET:$RECOVERY_DATA
</pre>
<p>
The script generates the following recovery.conf.
</p>
<pre>
restore_command = 'scp master:/data/archive_log/%f %p'
</pre>
</p>
<p>
Then, we prepare a script to switch XLOG on the second stage.
The script is named "pgpool_recovery_pitr" for example. Here is the sample script.
</p>
<p>
<pre>
#! /bin/sh
# Online recovery 2nd stage script
#
datadir=$1 # master dabatase cluster
DEST=$2 # hostname of the DB node to be recovered
DESTDIR=$3 # database cluster of the DB node to be recovered
port=5432 # PostgreSQL port number
# Force to flush current value of sequences to xlog
psql -p $port -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1|
while read i
do
if [ "$i" != "" ];then
psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i
fi
done
psql -p $port -c 'SELECT pg_switch_xlog()' postgres
</pre>
</p>
<p>
While loop in the script forces to emit current value of sequences in all
databases in the master node to the transaction log so that it
propagates to the recovery target node.
</p>
<p>
We deploy these scripts into $PGDATA.
</p>
<p>
Finally, we edit pgpool.conf.
<pre>
recovery_1st_stage_command = 'copy-base-backup'
recovery_2nd_stage_command = 'pgpool_recovery_pitr'
</pre>
</p>
<p>
We have finished preparing online recovery by PITR.
</p>
<h4><p>pgpool_remote_start</p></h4>
<p>
The script is to start up postmaster process from remote host.
pgpool-II executes as the following way.
</p>
<pre>
% pgpool_remote_start remote_host remote_datadir
remote_host: Hostname of a recovery target.
remote_datadir: Database cluster path of a recovery target.
</pre>
<p>
In the sample script, we start up postmaster process over ssh.
So you need to connect over ssh without pass .
</p>
<p>
If you recover with PITR, you need to expand a base backup. Then,
postmaster automatically starts up with PITR. Then it accepts
connections.
</p>
<pre>
#! /bin/sh
DEST=$1
DESTDIR=$2
PGCTL=/usr/local/pgsql/bin/pg_ctl
# Expand a base backup
ssh -T $DEST 'cd /data/; tar zxf pgsql.tar.gz' 2>/dev/null 1>/dev/null < /dev/null
# Startup PostgreSQL server
ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &
</pre>
<h3>Online recovery with rsync.</h3>
<p>
PostgreSQL 7.4 does not have PITR. So we use rsync to do online
recovery. In "sample" directory of pgpool-II tar ball,
there is a recovery script named "pgpool_recovery". It uses rsync
command. pgpool-II calls the script with three arguments.
</p>
<pre>
% pgpool_recovery datadir remote_host remote_datadir
datadir: Database cluster path of a master node.
remote_host: Hostname of a recovery target node.
remote_datadir: Database cluster path of a recovery target node.
</pre>
<p>
The script copy physical file with rsync over ssh. So you need to
connect over ssh without pass .
</p>
<p>
Note of rsync:
<ul>
<li>-z (or --compress) option does compression before transmitting
data. This will be great for slower connection, but it might be a
CPU overhead for a 100M or faster connectons. In this case you
might want not to use the option.
<li>The latest version rsync 3.0.5 has great speed performance
improvements (50% faster according to a report from pgpool-general
mailing list.
</ul>
</p>
<p>
If you use pgpool_recovery, add the following lines into pgpool.conf.
<pre>
recovery_1st_stage_command = 'pgpool_recovery'
recovery_2nd_stage_command = 'pgpool_recovery'
</pre>
</p>
<h2>How to perform online recovery</h2>
<p>
For doing online recovery, you use pcp_recovery_node command or
operate on pgpoolAdmin.
</p>
<p>
Note that you need to pass a greater number to the first argument of
pcp_recovery_node. It is a timeout parameter. If you use pgpoolAdmin,
set "_PGPOOL2_PCP_TIMEOUT " parameter to a greater number in
pgmgt.conf.php.
</p>
<h1>Restrictions<a name="restriction"></a></h1>
<p>
<h2>Authentication / Access Controls</h2>
<p>
<ul>
<li>In the replication mode or master/slave mode, trust, clear text
password, pam methods are supported.</li>
<li>In all the other modes, trust, clear text password, crypt, md5,
pam methods are supported.</li>
<li>pgpool-II does not support pg_hba.conf-like access controls. If
the TCP/IP connection is enabled, pgpool-II accepts all the
connections from any host. If needed, use iptables and such to
control access from other hosts. (PostgreSQL server accepting
pgpool-II connections can use pg_hba.conf, of course).</li>
</ul>
</p>
<h2>Large object</h2>
<p>
pgpool-II 2.3.2 or later supports large object replication. For this, you need to
enable lobj_lock_table directive in pgpool.conf. Proper large object
replication using backend function lo_import is not supported, however.
</p>
<h2>Functions, etc. In Replication Mode</h2>
<p>There is no guarantee that the data, which returns different values
each time even though the same query was executed (e.g. random number,
transaction ID, OID, SERIAL, sequence), will be
replicated correctly on multiple backends.</p>
<p>For SERIAL, enabling insert_lock will help replicating data.
insert_lock also helps SELECT setval() and SELECT nextval().</p>
<p>In pgpool-II 2.3 or later, INSET/UPDATE using CURRENT_TIMESTAMP,
CURRENT_DATE, now() can be replicated. Also INSET/UPDATE for tables
using CURRENT_TIMESTAMP, CURRENT_DATE, now() as their DEFAULT values can
be replicated.
This is done by replacing those functions by constrants fetched from master at query execution time.
There are a few limitations however:
<ul>
<li>The calculation of temporal data in table default value is not accurate in some cases. For example, following table definition:
<pre>
CREATE TABLE rel1(
d1 date DEFAULT CURRENT_DATE + 1
)
is treated same as:
CREATE TABLE rel1(
d1 date DEFAULT CURRENT_DATE
)
</pre>
<li>Suppose we have following table:
<pre>
CREATE TABLE rel1(
c1 int,
c2 timestamp default now()
)
</pre>
We can replicate
<pre>
INSERT INTO rel1(c1) VALUES(1)
</pre>
since this turn into
<pre>
INSERT INTO rel1(c1, c2) VALUES(1, '2009-01-01 23:59:59.123456+09')
</pre>
However,
<pre>
INSERT INTO rel1(c1) SELECT 1
</pre>
cannot to be transformed thus cannot be properly replicated in the current implementation.
</ul>
</p>
<p>Tables created by <code>CREATE TEMP TABLE</code> will be deleted at the end of
session by specifying DISCARD ALL in reset_query_list if you are using PostgreSQL 8.3 or later.
</p>
<p>
For 8.2.x or before <code>CREATE TEMP TABLE</code> will not be
deleted after exitting a session. It is because of the connection
pooling which, from the backend's point of view, seems that the
session is still alive. To avoid this, you must explicitly drop the
temporary tables by <code>DROP TABLE</code>, or use <code>CREATE TEMP
TABLE ... ON COMMIT DROP</code> inside the transaction block.</p>
<h2>Queries</h2>
<p>Here are the queries which cannot be processed by pgpool-II</p>
<h3>INSERT (for parallel mode)</h3>
<p>You cannot use <code>DEFAULT</code> with the
partitioning key column. For example, if the column x in the table t
was the partitioning key column,
<pre>
INSERT INTO t(x) VALUES (DEFAULT);
</pre>
<p>
is invalid. Also, functions cannot be used as the value either.
</p>
<pre>
INSERT INTO t(x) VALUES (func());
</pre>
<p>
Constant values must be used to INSERT with the partitioning
key. <code>SELECT INTO</code> and <code>INSERT INTO ... SELECT</code>
are also not supported.
</p>
<h3>UPDATE (for parallel mode)</h3>
<p>Data consistency between the backends may be lost if the
partitioning key column values are updated. pgpool-II does not
re-partition the updated data.</p>
<p>A transaction cannot be rolled back if a query has caused an error
on some backends due to the constraint violation.</p>
<p>If a function is called in the <code>WHERE</code> clause, that
query might not be executed correctly.
<pre>
e.g.) UPDATE branches set bid = 100 where bid = (select max(bid) from beances);
</pre>
</p>
<h3>SELECT ... FOR UPDATE (for parallel mode)</h3>
<p>If a function is called in the <code>WHERE</code> clause, that
query might not be executed correctly.
<pre>
e.g.) SELECT * FROM branches where bid = (select max(bid) from beances) FOR UPDATE;
</pre>
</p>
<h3>COPY (for parallel mode)</h3>
<p><code>COPY BINARY</code> is not supported. Copying from files are
also not supported. Only <code>COPY FROM STDIN</code> and <code>COPY
TO STDOUT</code> are supported.</p>
<h3>ALTER/CREATE TABLE (for parallel mode)</h3>
<p>To update the partitioning rule, pgpool-II must be restarted in
order to read them from the System DB.</p>
<h3>Transaction (for parallel mode)</h3>
<p><code>SELECT</code> statements executed inside a transaction block
will be executed in the separate transaction. Here is an example:
<pre>
BEGIN;
INSERT INTO t(a) VALUES (1);
SELECT * FROM t ORDER BY a; <-- INSERT above is not visible from this SELECT statement
END;
</pre>
<h3>Views / Rules (for parallel mode)</h3>
<p>
The same definition will be created on all the backends for views and rules.
</p>
<pre>
SELECT * FROM a, b where a.i = b.i
</pre>
<p>
<code>JOIN</code>s like above will be executed within one backend, and
then merged with the results returned by each backend. Views and Rules
that joins across the nodes cannot be created.
However, to JOIN tables that divide data only in the same node, VIEW can be made.
VIEW is registered in the pgpool_catalog.dist_def table. Moreover, please register
a col_name and a dist_def_func. These are used when Insert for View was used.
</p>
<h3>Functions / Triggers (for parallel mode)</h3>
<p>The same definition will be created on all the backends for
functions. Joins across the nodes, and data on the other nodes cannot
be manipulated inside the functions.</p>
<h3>Extended Query Protocol (for parallel mode)</h3>
<p>The extended query protocol used by JDBC drivers, etc. is not
supported. The simple query protocol must be used.</p>
<h3>Natural Join (for parallel mode)</h3>
<p>The Natural Join is not supported.
"ON join condition" or "USING (join_column)" must be needed.</p>
<h3>USING CLAUSE (for parallel mode)</h3>
<p>The USING CLAUSE is converted to ON CLAUSE by query rewrite process.
Therefore, when "*" is used at target list, the row of the same column name appears.</p>
<h3>Multi-byte Characters (for all modes)</h3>
<p>pgpool-II does not translate between different multi-byte
characters. The encoding for the client, backend and System DB must be
the same.</p>
<h3>Multi-statement Query (for all modes)</h3>
<p>
pgpool-II cannot process multi-statement query.
</p>
<h3>Deadlocks (for parallel mode)</h3>
<p>Deadlocks across the backends cannot be detected. For example:
</p>
<pre>
(tellers table is partitioned using the following rule)
tid <= 10 --> node 0
tid >= 10 --> node 1
A) BEGIN;
B) BEGIN;
A) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;
B) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
A) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
B) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;
</pre>
<p>
In the case above, a single node cannot detect the deadlock, so
pgpool-II will still wait for the response. This phenomenon
occurs with other queries that acquire row level locks.
</p>
<p>Also, if a deadlock occurs in one node, transaction states in each
node will not be consistent. Therefore, pgpool-II terminates the
process if a deadlock is detected.
</p>
<pre>
pool_read_kind: kind does not match between master(84) slot[1] (69)
</pre>
<h3>Schemas (for parallel mode)</h3>
<p>Objects in a schema other than public must be fully qualified like:
</p>
<pre>
schema.object
</pre>
<p>
pgpool-II cannot resolve the correct schema when the path was set as
follows:
</p>
<pre>
set search_path = xxx
</pre>
<p>
and the schema name was omitted in a query.
</p>
<h3>table name - column name(for parallel mode)</h3>
<p>
Limitation object:Parallel mode
</p>
<p>
A table or a column name cannot starts by pool_.
When rewriting the query, the name is used by internal processing.
</p>
</p>
<h2>System DB</h2>
<h3>Partitioning Rules</h3>
<p>Only one (1) partitioning key column can be defined in one
partitioning rule. Conditions like 'x or y' are not supported.</p>
<h2>Environment Requirements</h2>
<h3>libpq</h3>
<p><code>libpq</code> is linked while building pgpool-II. libpq
version must be 3.0. Building pgpool-II with libpq version 2.0 will
fail. Also, the System DB must be PostgreSQL 7.4 or later.</p>
<h2>Query Cache</h2>
<p>Currently, query cache must be deleted manually. pgpool-II does not
invalidate old query cache automatically when the data is updated.</p>
<h2>Compatibility with pgpool</h2>
<h1>References<a name="reference"></a></h1>
<h2>PCP Command Reference</h2>
<h3>PCP Command List</h3>
<p>PCP commands are UNIX commands which manipulate pgpool-II via network.
<pre>
* pcp_node_count - retrieves the number of nodes
* pcp_node_info - retrieves the node information
* pcp_proc_count - retrieves the process list
* pcp_proc_info - retrieves the process information
* pcp_systemdb_info - retrieves the System DB information
* pcp_detach_node - detaches a node from pgpool-II
* pcp_attach_node - attaches a node to pgpool-II
* pcp_stop_pgpool - stops pgpool-II
</pre>
</p>
<h2>Common Command-line Arguments</h2>
<p>There are five arguments common to all of the PCP commands. They
give information about pgpool-II and authentication. Extra
arguments may be needed for some commands.
<pre>
e.g.) $ pcp_node_count 10 localhost 9898 postgres hogehoge
First argument - timeout value in seconds. PCP disconnects if
pgpool-II does not respond in so many seconds.
Second argument - pgpool-II's hostname
Third argument - pgpool-II's port number for PCP server
Fourth argument - PCP username
Fifth argument - PCP password
</pre>
<p>PCP usernames and passwords must use ones in the
<code>pcp.conf</code> in <code>$prefix/etc</code>
directory. <code>-F</code> option can be used when starting pgpool-II
if <code>pcp.conf</code> is placed somewhere else. The password does
not need to be in md5 format when passing it to the PCP commands.</p>
<h2>PCP Commands</h2>
<p>All PCP commands display the results to the standard output.</p>
<h3>pcp_node_count</h3>
<pre>
Format:
pcp_node_count _timeout_ _host_ _port_ _userid_ _passwd_
</pre>
<p>
Displays the number of total nodes defined in <code>pgpool.conf</code>. It does
not distinguish nodes status, ie attached/detached. ALL nodes are counted.
</p>
<h3>pcp_node_info</h3>
<pre>
Format:
pcp_node_info _timeout_ _host_ _port_ _userid_ _passwd_ _nodeid_
</pre>
<p>
Displays the information on the given node ID. The output example is
as follows:
</p>
<pre>
$ pcp_node_info 10 localhost 9898 postgres hogehoge 0
host1 5432 1 1073741823.500000
The result is in the following order:
1. hostname
2. port number
3. status
4. load balance weight
Status is represented by a digit from [0 to 3].
0 - This state is only used during the initialization. PCP will never display it.
1 - Node is up. No connections yet.
2 - Node is up. Connections are pooled.
3 - Node is down.
</pre>
<p>
The load balance weight is displayed in normalized format.
</p>
<p>Specifying an invalid node ID will result in an error with exit
status 12, and BackendError will be displayed.</p>
<h3>pcp_proc_count</h3>
<p>
<pre>
Format:
pcp_proc_count _timeout_ _host_ _port_ _userid_ _passwd_
</pre>
<p>
Displays the list of pgpool-II child process IDs. If there is more than
one process, IDs will be delimitted by a white space.
</p>
<h3>pcp_proc_info</h3>
<p>
<pre>
Format:
pcp_proc_info _timeout_ _host_ _port_ _userid_ _passwd_ _processid_
</pre>
<p>
Displays the information on the given pgpool-II child process ID. The
output example is as follows:
</p>
<pre>
$ pcp_proc_info 10 localhost 9898 postgres hogehoge 3815
postgres_db postgres 1150769932 1150767351 3 0 1
The result is in the following order:
1. connected database name
2. connected username
3. process start-up timestamp
4. connection created timestamp
5. protocol major version
6. protocol minor version
7. connection-reuse counter
</pre>
<p>
If there is no connection to the backends, nothing will be
displayed. If there are multiple connections, one connection's
information will be displayed on each line multiple
times. Timestamps are displayed in EPOCH format.
</p>
<p>Specifying an invalid node ID will result in an error with exit
status 12, and BackendError will be displayed.</p>
<h3>pcp_systemdb_info</h3>
<p>
<pre>
Format:
pcp_systemdb_info _timeout_ _host_ _port_ _userid_ _passwd_
</pre>
<p>
Displays the System DB information. The output example is as follows:
</p>
<pre>
$ pcp_systemdb_info 10 localhost 9898 postgres hogehoge
localhost 5432 yamaguti '' pgpool_catalog pgpool 3
yamaguti public accounts aid 4 aid bid abalance filler integer integer integer character(84) dist_def_accounts
yamaguti public branches bid 3 bid bbalance filler integer integer character(84) dist_def_branches
yamaguti public tellers bid 4 tid bid tbalance filler integer integer integer character(84) dist_def_tellers
First, the System DB information will be displayed on the first
line. The result is in the following order:
1. hostname
2. port number
3. username
4. password. '' for no password.
5. schema name
6. database name
7. number of partioning rules defined
</pre>
<p>
Second, partioning rules will be displayed on the following lines. If
there are multiple definitions, one definition will be displayed on
each line multiple times. The result is in the following order:
</p>
<pre>
1. targeted partitioning database name
2. targeted partitioning schema name
3. targeted partitioning table name
4. partitioning key column name
5. number of columns in the targeted table
6. column names (displayed as many as said in 5.)
7. column types (displayed as many as said in 5.)
8. partitioning rule function name
</pre>
<p>
If the System DB is not defined (i.e. not in pgpool-II mode, and query
cache is disabled), it results in error with exit status 12, and
BackendError will be displayed.</p>
<h3>pcp_detach_node</h3>
<pre>
Format:
pcp_detach_node _timeout_ _host_ _port_ _userid_ _passwd_ _nodeid_
</pre>
<p>
Detaches the given node from pgpool-II.
</p>
<h3>pcp_attach_node</h3>
<p>
<pre>
Format:
pcp_attach_node _timeout_ _host_ _port_ _userid_ _passwd_ _nodeid_
Attaches the given node to pgpool-II.
</pre>
</p>
<h3>pcp_stop_pgpool</h3>
<pre>
Format:
pcp_stop_pgpool _timeout_ _host_ _port_ _userid_ _passwd_ _mode_
</pre>
<p>
Terminate pgpool-II process with the given shutdown mode. The availabe
modes are as follows:
</p>
<pre>
s - smart mode
f - fast mode
i - immediate mode
</pre>
<p>
If pgpool-II process does not exist, it results in error with exit
status 8, and ConnectionError will be displayed.
</p>
<p>
* Currently, there is no difference between the fast and immediate
mode. pgpool-II terminates all the processes wheter there are clients
connected to the backends.</p>
<h2>Exit Status</h2>
<p>PCP command exits with status 0 when everything goes well. If any
error had occured, it will exit with the following error status.
<pre>
UNKNOWNERR 1 Unknown Error (should not occur)
EOFERR 2 EOF Error
NOMEMERR 3 Memory shortage
READERR 4 Error while reading from the server
WRITEERR 5 Error while writing to the server
TIMEOUTERR 6 Timeout
INVALERR 7 Argument(s) to the PCP command was invalid
CONNERR 8 Server connection error
NOCONNERR 9 No connection exists
SOCKERR 10 Socket error
HOSTERR 11 Hostname resolution error
BACKENDERR 12 PCP process error on the server (specifying an invalid ID, etc.)
AUTHERR 13 Authorization failure
</pre>
</p>
<h1>Inside information<a name="internal"></a></h1>
<p>
On The pgpool-II version 2.0.x, Extensive modifications are added compared with the version 1.x
Please note incompatibility with information on the version 1.x.
</p>
<h2>Parallel execution engine</h2>
<p>
The parallel execution engine is built into pgpool-II.
This engine inquires same Query as each node, and indicates the engine that transmits the result to the front end in order of the response of the node.
</p>
<h2>Query Rewriting</h2>
<p>
It explains the Query rewriting that pgpool-II does in a parallel mode.
</p>
<p>
In a parallel mode, the inquiry of the retrieval system (SELECT processing) that the client transmitted is following it divided roughly Two processing is done.
</p>
<pre>
Analysis of Query
Rewriting of Query
</pre>
<p>
I will sequentially explain these two processing.
</p>
<h3>Analysis of Query</h3>
<h4><p>Introduction</p></h4>
<p>
The inquiry of the retrieval system that the client transmitted analyzes Query
based on information registered in system DB after SQL parser is passed. It
evaluates it to the analysis of Query by the transition of the execution status.
The execution status is the one that the set of a certain data acquires where or
it is treatable is judged here. For instance, because data is divided as for
the entire data sets of tables registered in the pgpool_catalog.dist_def table,
it is necessary to acquire it from all nodes. Oppositely, the entire data sets
of tables registered in the pgpool_catalog.replicate_def table are enough if
it does not acquire from all nodes but it acquires it from either of the
nodes. The state that should be processed here by all nodes The state of
P and the state that should be processed by one node It defines it as a
state of L. As special ..another.. There is a state of S. This shows
the state when processing it to all data acquired from all nodes. For
instance, the sorting application. After data is acquired from all
nodes, it is necessary to execute the sorting application to the data of
the table registered in the pgpool_catalog.dist_def table.
</p>
<p>
Retrieval system Query is analyzed in the following order of
processing, and the execution status changes. In the process to which
the execution status changes the following processing : as for the state
of S It enters the state of S. Also whether it is processed with DB
where is decided by the state of the final execution status of the last
SELECT.
</p>
<ol>
<li>Whether UNION, EXTRACT, and INTERCECT are used or not?
<li>Execution status of FROM Clause
<li>Change in execution status by TARGETLIST
<li>Change in execution status accouding to WHERE Clause
<li>Change in execution status according to GROUP BY Clause
<li>Change in execution status according to HAVING Clause
<li>Change in execution status according to ORDER BY Clause
<li>It changes into the LIMIT OFFSET predicate in the execution status.
<li>Acquisition of the final execution status of SELECT
</ol>
<p>
The relation between the final execution status of SELECT and the processed place is as follows.
</p>
<p>
<table border>
<tr><td>Execution status</td><td>Processed place</td></tr>
<tr><td align=center>L</td><td>It inquires of either of node. </td></tr>
<tr><td align=center>P</td><td>It returns it to the client through all the same node inquiries and a parallel execution engines.</td></tr>
<tr><td align=center>S</td><td>After it processes it with system DB, it returns it to the client. </td></tr>
</table>
</p>
<p>
Moreover, the above-mentioned rule adjusts to Sub-Query. In simple following Query, When p1-table is registered in pgpool_catalog.dist_def table of system DB, that is, when data is divided, the final execution status of Sub-Query : It becomes P, and call origin of Sub-Query as a result Execution status of SELECT also : It becomes P
</p>
<pre>
SELECT * FROM (SELECT * FROM P1-table) as P2-table;
</pre>
<p>
Next, it explains how the execution status changes concretely. Two first of all. It explains from the execution status of the From .
</p>
<h4><p>Execution status of FROM Clause</p></h4>
<p>
Retrieval system Query (SELECT) The set of data is defined according to the FROMCluase. ..the state of P and the state of L.. The state of S is taken. The execution status of the table : simply when the table specified for the FROM Clause is one It becomes the execution status of the entire data set composed of the FROM Cluase. The execution status is decided according to the JOIN method as follows when there is two or more tables or Sub-Query in the FROM Clause.
</p>
<p>
<table border>
<tr><td>JOIN type</td><td align = center colspan = 3> LEFT OUTER JOIN </td><td align = center colspan = 3> RIGHT OUTER JOIN </td><td align = center colspan = 3>FULL OUTER JOIN</td><td align = center colspan = 3> others </td></tr>
<tr><td align = center>left＼right</td><td> P </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td></tr>
<tr><td align = center> P </td><td> S </td><td> P </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> P </td><td> S </td></tr>
<tr><td align = center> L </td><td> S </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td><td> S </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td></tr>
<tr><td align = center> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td></tr>
</td></tr>
</table>
</p>
<p>
In the following examples, P1-table with the table in the state of P :
L1-table and L2-table : It is assumed that the table in the state of L.
P1-table (left) and L1-table (right) according to the above-mentioned table then JOIN : In addition ..entering the state of P.., With the state of P L2-table in the state of L joins and the execution status of the FROM Clause It enters the state of P.
</p>
<pre>
SELECT * FROM P1-table,L1-table,L2-table;
</pre>
<h4><p>Change in execution status by TARGETLIST and Where Clause</p></h4>
<p>
In a basic Query, the same execution status as the FROM Clause is succeeded to. However, with TARGETLIST, te execution status of the WHERE Clause changes in the following cases.
</p>
<ol>
<li>When there is a subQuery
<li>The FROM Clause When and, there are an aggregate function and DISTINCT in TARGETLIST for P
<li>When the column that does not exist in the table (data set)
defined by the FROM Clause is used
</ol>
<p>
The final execution status of the subQuery The execution status of TARGETLIST and the WHERE Clause enters the state of S for the state of P or S. In the following example, when the table used by the subQuery is P, the final execution status of the subQuery : It enters the state of P. Therefore, The execution status of the WHERE Clause : without depending on the execution status of L1-table It enters the state of S, and the execution place of this Query becomes system DB.
</p>
<pre>
SELECT * FROM L1-table where L1-table.column IN (SELECT * FROM P1-table);
</pre>
<p>
The FROM Clause It changes in the state of S to have to total it after data is acquired when and, there is an aggregate function in TARGETLIST for P. Moreover, optimization by the aggregate function is done under a specific condition.
</p>
<p>
The column that does not exist in the table and the subQuery defined by the FROM Clause might be used for the WHERE Clause. This is generated in following correlation Sub-Query.
</p>
<pre>
SELECT * FROM L1-table FROM L1-table.col1 IN (SELECT * FROM P1-table WHERE P1-table.col = L1-table.col1);
</pre>
<p>
It is used for the above-mentioned Sub-Query External refer to L1-table for L1-table.col1. The execution status of the WHERE Clause of the Sub-Query : in this case It enters the state of S.
</p>
<h4><p>Change in execution status by GROUP BY, HAVING, ORDER BY and LIMIT OFFSET</p></h4>
<p>
The execution status of the WHERE Clause It changes in the state of S when there are GROUP BY, HAVING Clause, and ORDER BY Clause and LIMIT OFFSET predicate for P. The Query where the GROUP BY Clause does not exist succeeds to the execution status of the WHERE Clause. Moreover, the execution status of the GROUP BY Clause is succeeded to when there is no HAVING Clause. The ORDER BY Clause and the LIMIT OFFSET predicate are also similar.
</p>
<h4><p>When UNION, EXTRACT, and INTERSECT are used</p></h4>
<p>
The Query that UNION, EXTRAT, and INTERSECT use depends on the final execution status of a left SELECT sentence and right SELECT sentence. The final execution status of a left, right SELECT sentence both : It enters the state of L at the state of L. Moreover, the final execution status of a left, right SELECT sentence both : For the state of P and UNION ALL It enters the state of P. It enters the state of S for other combinations.
</p>
<h4><p>Acquisition of the final execution status of SELECT</p></h4>
<p>
Everything the execution status Everything ..the state of L.. for L It enters the state of P for P. Besides, it enters the state of S. The load is distributed when loadbalance_mode of pgpool.conf is true for L, and it inquires of MASTER besides. Moreover, the parallel processing is done for P with a parallel execution engine. The Query rewriting at S that is the following phase is done.
</p>
<h3>Query rewriting</h3>
<p>
The Query is rewritten by using the execution status acquired in an analyzing phase of the Query. As an example the state of P With P1-table the state of L It explains by the Query that uses L1-table.
</p>
<pre>
SELECT P1-table.col, L1-table.col FROM P1-table,L1-table where P1-table.col = L1-table.col order by P1-table.col;
</pre>
<p>
In this Query Because there is ORDER BY Clause It enters the state of S, and the FROM Clause, the WHERE Clause, and TARGETLIST : It enters the state of P. It is rewritten in such a Query as follows.
</p>
<pre>
SELECT P1-table.col, L1-table.col FROM
dblink(select pool_parallel(SELECT P1-table.col, L1-table.col FROM P1-table,L1-table where P1-table.col = L1-table.col))
order by P1-table.col;
</pre>
<p>
the dblink transmits the inquiry to pgpool-II here. Moreover, pool_parallel is the function which send the Query of arguments to the parallel execution engine. The above-mentioned is an image to the end and no actually executable Query.
</p>
<p>
The FROM Clause, the WHERE Clause, and TARGETLIST all : like the above-mentioned example The parallel processing is done bringing the FROM Clause, the WHERE Clause, and TARGETLIST together for P.
</p>
<p>
The following example is seen.
</p>
<pre>
SELECT L1-table.col FROM L1-table WHERE L1-table.col % 2 = 0 AND L1-table.col IN (SELECT P1-table FROM P1-table) ;
</pre>
<p>
In this example, the FROM Clause : The state of L and TARGETLIST also : The state of L and the WHERE Clause : Because it has the subQuery in the state of P It enters the state of S. As for this, rewriting is done as follows.
</p>
<pre>
SELECT L1-table.col FROM dblink(SELECT loadbalance(SELECT L1-table.col FROM L1-table WHERE L1-table.col % 2 = 0 AND TRUE))
WHERE
L1-table.col %2 = 0 AND
L1-table.col IN
(
SELECT P1-Table FROM
dblink(select pool_parallel(SELECT P1-table FROM P1-table))
) ;
</pre>
<p>
Here, pool_loadbalance is a function that transmits Queries to either of node.
</p>
<h3>Query rewriting by aggregate functions</h3>
<p>
As for the totaled Query (aggregate function and GROUP BY), it calculates, and the decreasing performance also improves the load of system DB to each node by recounting it with system DB.
</p>
<p>
First of all, rewriting the Query that pgpool-II actually does first is seen.
</p>
<p>
The Query which have state P in FROM Clause and count(*) in TARGETLIST, The Rewriting Query is done as follows
</p>
<pre>
select count(*) from P1-table;
-> rewrite
SELECT
sum(pool_c$1) as count
FROM
dblink(select pool_parallel('select count(*) from P1-table'))
AS pool_$1g (pool_c$1 bigint);
</pre>
<p>
The condition that the Query rewriting like the above-mentioned is done is the following.
</p>
<ol>
<li>The FROM Clause is in the state of P.
<li>The column specified for the aggregate function (Only count, sum, min, max, and avg correspond) and GROUP BY is used for the target list.
<li>The WHERE Clause is in the state of P.
<li>Only the column defined by the aggregate function (Only count, sum, min, max, and avg correspond) used for the HAVING Clause and the FROM Clause and the column specified for GROUP BY are used.
</ol>
<h3>Notes of parallel mode</h3>
<p>
When the Query is analyzed, the column name and the type are needed in a parallel mode. Therefore, when the expression and the function are used for TARGETLIST of the subQuery, it is necessary to give the alias and the type name in Cast. Please note processing as the text type when there are no Cast of the type in the expression and the function. As for count, when the Query rewriting by the case of the aggregate function and consolidating is done, the bigint type and sum become numeric types. It is calculated as a date type when the argument is a date type for min and max, and, besides, it is calculated as numeric. Avg is processed as sum/count.
</p>
<h3>About the performance of a parallel mode</h3>
<p>The final execution status of SELECT and a rough standard of the performance are as follows.
</p>
<p>
<table border>
<tr><td>Execution status</td><td>Performance</td></tr>
<tr><td align = center>L</td><td>There is no performance deterioration with a single purpose node excluding the overhead of pgpool-II because it does not use a parallel Query. </td></tr>
<tr><td align = center>P</td><td>The parallel processing is done with high-speed, especially the sequential scanning. Moreover, it becomes easy to get on cash because the size of the table becomes small by dividing data. </td></tr>
<tr><td align = center>S</td><td>When the Query rewriting by aggregate functions is done, it is fast. </td></tr>
</td></tr>
</table>
</p>
<h1>Tutorial</h1>
<p>
<a href="tutorial-en.html">A tutorial for pgpool-II</a> is available.
</p>
<div class="copyright">
<hr>
<copyright>
Copyright © 2003 – 2008 PgPool Global Development Group
</copyright>
</div>
</body>
</html>
