pgpool2.git / summary / log / commit / refs
commit a4ca1d7612ba6ade2c1ade167302407bb40dbc8a
Author: Nozomi Anzai <anzai@sraoss.co.jp>
Date: Wed May 02 08:49:16 2012 +0000
- Fix page width which is expanded due to long <pre> sentences.
- Arrange indents in one rule, and add lacked end tags.
- Add id attributes to <h1>, <h2>, ... and the disctiptions of all parameters.
doc/pgpool-en.html | 4320 +++++++++++++++++++++++++++-------------------------
1 file changed, 2269 insertions(+), 2051 deletions(-)
diff --git a/doc/pgpool-en.html b/doc/pgpool-en.html
index 2a666ebf1..233b83e42 100644
--- a/doc/pgpool-en.html
+++ b/doc/pgpool-en.html
@@ -2,7 +2,7 @@
<html>
<head>
<meta http-equiv="content-type" content="text/html">
-<link rel="stylesheet" href="./pgpool.css" type="text/css">
+<link rel="stylesheet" href="./pgpool-ja.css" type="text/css">
<title>pgpool-II user manual</title>
</head>
@@ -11,17 +11,10 @@ Last modified: Sun Mar 4 11:18:24 JST 2012
<!-- hhmts end -->
<body bgcolor="#ffffff">
-<a name="top"></a>
-<table border="0" cellpadding="2" cellspacing="1">
- <tr>
+<div id="top" class="header_text">Welcome to pgpool -II page</div>
- <td colspan="2" valign="top"><div class="header_text">Welcome to the pgpool -II page</div></td>
- </tr>
- <tr>
- <td valign="top" style="border-right:1px dotted #cccccc;">
- <br />
-
- <div id="navcontainer">
+<div id="menu">
+ <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>
@@ -38,18 +31,20 @@ Last modified: Sun Mar 4 11:18:24 JST 2012
<li><a href="#troubleshooting">Troubleshooting</a></li>
<li><a href="#restriction">Restrictions</a></li>
<li><a href="#reference">References</a></li>
- <li><a href="#internal">internal</a></li>
+ <li><a href="#internal">internal</a></li>
</ul>
</div>
- <br />
+ <br />
- <div class="header_small" align="center">
+ <div class="header_small" align="center">
+ [<a href="pgpool-ja.html">Japanese page</a>]
+ </div>
+</div>
- [<a href="pgpool-ja.html">Japanese page</a>] </div> </td>
- <td valign="top" style="border-left:1px dotted #cccccc;">
-
+<div id="manual">
+<!-- ================================================================================ -->
<h1>What is pgpool-II?<a name="whatis"></a></h1>
@@ -62,39 +57,40 @@ and a PostgreSQL database client. It provides the following features:</p>
<li>Connection Pooling</li>
<p>pgpool-II maintains established connections to the PostgreSQL servers, and
reuses them whenever a new connection with the same properties
-(i.e. username, database, protocol version) comes in. It reduces the
-connection overhead, and improves system's overall throughput.</p>
+ (i.e. username, database, protocol version) comes in. It reduces the
+ connection overhead, and improves system's overall throughput.</p>
<li>Replication</li>
<p>pgpool-II can manage multiple PostgreSQL servers. Activating the
-replication feature makes it possible to create a realtime backup on 2 or
-more PostgreSQL clusters, so that the service can continue without interruption
-if one of those clusters fails.</p>
+ replication feature makes it possible to create a realtime backup on 2 or
+ more PostgreSQL clusters, so that the service can continue without interruption
+ if one of those clusters fails.</p>
<li>Load Balance</li>
- <p>If a database is replicated(because running in either replcation mode or master/slave mode), performing a SELECT query on any
-server will return the same result. pgpool-II takes advantage of
-the replication feature in order to reduce the load on each PostgreSQL server.
-It does that by distributing SELECT queries among available servers, improving
-the system's overall throughput. In an ideal scenario, read performance could
-improve proportionally to the number of PostgreSQL servers. Load balancing works
-best in a scenario where there are a lot of users executing many read-only
-queries at the same time.</p>
+ <p>If a database is replicated(because running in either replcation mode or master/slave mode),
+ performing a SELECT query on any
+ server will return the same result. pgpool-II takes advantage of
+ the replication feature in order to reduce the load on each PostgreSQL server.
+ It does that by distributing SELECT queries among available servers, improving
+ the system's overall throughput. In an ideal scenario, read performance could
+ improve proportionally to the number of PostgreSQL servers. Load balancing works
+ best in a scenario where there are a lot of users executing many read-only
+ 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 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.</p>
+ 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.</p>
<li>Parallel Query</li>
<p>Using the parallel query feature, data can be split
-among multiple servers, so that a query can be executed on all the
-servers concurrently, reducing the overall execution time. Parallel query
-works best when searching large-scale data.</p>
+ among multiple servers, so that a query can be executed on all the
+ servers concurrently, reducing the overall execution time. Parallel query
+ works best when searching large-scale data.</p>
</ul>
</p>
@@ -112,6 +108,8 @@ without a change to its source code.</p>
See <a href="#restriction">Restrictions</a> for more details.</strong>
</p>
+<!-- ================================================================================ -->
+
<h1>Supported Platforms<a name="platform"></a></h1>
<p>pgpool-II works on Linux, Solaris, FreeBSD, and most of the
@@ -128,17 +126,19 @@ In addition to this, hardware architectures and OSes must be identical if you
want to use online recovery.
</p>
+<!-- ================================================================================ -->
+
<h1>pgpool-II Installation<a name="install"></a></h1>
<p>
-pgpool-II can be downloaded from the <a href="http://pgfoundry.org/projects/pgpool/">pgpool Development page</a>.
+pgpool-II can be downloaded from the <a href="http://pgpool.net/">pgpool Development page</a>.
Packages are also provided for various platforms including CentOS, RedHat Enterprise Linux, Fedora and Debian.
Check appropriate repository.
</p>
<p>
pgpool-II's source code can be downloaded from:
-<a href="http://pgfoundry.org/projects/pgpool/">pgpool development page</a>
+<a href="http://pgpool.net/mediawiki/index.php/Downloads">pgpool development page</a>
</p>
<p>Installing pgpool-II from source code requires gcc 2.9 or higher, and GNU
@@ -149,101 +149,102 @@ enable OpenSSL support in pgpool-II</p>
<dl>
<dt>configure</dt>
-<dd>
-<p>
-After extracting the source tarball, execute the configure script.
+ <dd>
+ <p>After extracting the source tarball, execute the configure script.</p>
<pre>
./configure
</pre>
-If you want non-default values, some options can be set:
-
-<ul>
- <li><code>--prefix=path</code><br/>
- pgpool-II binaries and docs will be installed in this
-directory. Default value is <code>/usr/local</code></li>
- <li><code>--with-pgsql=path</code><br/>
- The top directory where PostgreSQL's client libraries are
-installed. Default value is provided by <code>pg_config</code></li>
- <li><code>--with-openssl</code><br/>
- pgpool-II binaries will be built with OpenSSL support. OpenSSL support
- is disabled by default.</li>
- <li><code>--enable-sequence-lock</code><br/>
- Use insert_lock compatible with pgpool-II 3.0 series(until 3.0.4).
- pgpool-II locks against a row in the sequence table.
- PostgreSQL 8.2 or later which was released after June 2011 cannot use this lock method.
- <li><code>--enable-table-lock</code><br/>
- Use insert_lock compatible with pgpool-II 2.2 and 2.3 series.
- pgpool-II locks against the insert target table.
- This lock method is deprecated because it causes a lock conflict with VACUUM.
-</ul>
-</p>
-</dd>
+ <p>If you want non-default values, some options can be set:</p>
+ <ul>
+ <li><code>--prefix=path</code><br/>
+ pgpool-II binaries and docs will be installed in this
+ directory. Default value is <code>/usr/local</code></li>
+ <li><code>--with-pgsql=path</code><br/>
+ The top directory where PostgreSQL's client libraries are
+ installed. Default value is provided by <code>pg_config</code></li>
+ <li><code>--with-openssl</code><br/>
+ pgpool-II binaries will be built with OpenSSL support. OpenSSL support
+ is disabled by default.</li>
+ <li><code>--enable-sequence-lock</code><br/>
+ Use insert_lock compatible with pgpool-II 3.0 series(until 3.0.4).
+ pgpool-II locks against a row in the sequence table.
+ PostgreSQL 8.2 or later which was released after June 2011 cannot use this lock method.
+ <li><code>--enable-table-lock</code><br/>
+ Use insert_lock compatible with pgpool-II 2.2 and 2.3 series.
+ pgpool-II locks against the insert target table.
+ This lock method is deprecated because it causes a lock conflict with VACUUM.
+ </ul>
+ </dd>
<dt>make</dt>
-<dd>
-<p>
+ <dd>
<pre>
make
make install
</pre>
-will install pgpool-II. (If you use Solaris or FreeBSD, replace make with gmake)
-</p>
-</dd>
+ <p>
+ will install pgpool-II. (If you use Solaris or FreeBSD, replace make with gmake)
+ </p>
+ </dd>
<dt>Installing pgpool_regclass</dt>
-<dd>
-<p>
-If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II.
-Without this, handling of duplicate table names in different schema
-might cause trouble (temporary tables aren't a problem).
-</p>
-<p>
+ <dd>
+ <p>
+ If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL
+ to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II.
+ Without this, handling of duplicate table names in different schema
+ might cause trouble (temporary tables aren't a problem).
+ </p>
+
<pre>
cd pgpool-II-x.x.x/sql/pgpool-regclass
make
make install
psql -f pgpool-regclass.sql template1
</pre>
-</p>
-<p>
-Executing pgpool-regclass.sql should be performed on every databases accessed
-with pgpool-II.
-You do not need to do this for a database created after the
-execution of "psql -f pgpool-regclass.sql template1", as this template database
-will be cloned to create new databases.
-</p>
+
+ <p>
+ Executing pgpool-regclass.sql should be performed on every databases accessed
+ with pgpool-II.
+ You do not need to do this for a database created after the
+ execution of "psql -f pgpool-regclass.sql template1", as this template database
+ will be cloned to create new databases.
+ </p>
+ </dd>
<dt>Creating insert_lock table</dt>
-<dd>
-<p>
-If you use insert_lock in replication mode, creating pgpool_catalog.insert_lock
-table for mutual exclusion is strongly recommended.
-Without this, insert_lock works so far. However in that case pgpool-II locks
-against the insert target table. This behavior is same as pgpool-II 2.2 and 2.3
-series. The table lock conflicts with VACUUM. So INSERT processing may be thereby
-kept waiting for a long time.
-</p>
-<p>
+ <dd>
+ <p>
+ If you use insert_lock in replication mode, creating pgpool_catalog.insert_lock
+ table for mutual exclusion is strongly recommended.
+ Without this, insert_lock works so far. However in that case pgpool-II locks
+ against the insert target table. This behavior is same as pgpool-II 2.2 and 2.3
+ series. The table lock conflicts with VACUUM. So INSERT processing may be thereby
+ kept waiting for a long time.
+ </p>
<pre>
cd pgpool-II-x.x.x/sql
psql -f insert_lock.sql template1
</pre>
-</p>
-<p>
-Executing insert_lock.sql should be performed on every databases accessed
-with pgpool-II.
-You do not need to do this for a database created after the execution of
-"psql -f insert_lock.sql template1", as this template database will be
-cloned to create new databases.
-</p>
-
+ <p>
+ Executing insert_lock.sql should be performed on every databases accessed
+ with pgpool-II.
+ You do not need to do this for a database created after the execution of
+ "psql -f insert_lock.sql template1", as this template database will be
+ cloned to create new databases.
+ </p>
+ </dd>
</dl>
<p>
-Installing is done. If you are using Solaris or FreeBSD, you need to replace "make" with "gmake" in the above description because those operating systems requires GNU make.
+Installing is done. If you are using Solaris or FreeBSD,
+you need to replace "make" with "gmake" in the above description
+because those operating systems requires GNU make.
</p>
+<!-- ================================================================================ -->
+
<h1>Configuring pgpool-II<a name="config"></a></h1>
<p>Default configuration files for pgpool-II are
@@ -265,85 +266,84 @@ control their behaviors.</p>
<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">X</td>
+ <td align="center">O</td>
+ <td align="center">O</td>
+ <td align="center">O</td>
</tr>
<tr>
<td>Replication</td>
- <td align="center">X</td>
- <td align="center">O</td>
- <td align="center">X</td>
- <td align="center">(*1)</td>
+ <td align="center">X</td>
+ <td align="center">O</td>
+ <td align="center">X</td>
+ <td align="center">(*1)</td>
</tr>
<tr>
<td>Load Balance</td>
- <td align="center">X</td>
- <td align="center">O</td>
- <td align="center">O</td>
- <td align="center">(*1)</td>
+ <td align="center">X</td>
+ <td align="center">O</td>
+ <td align="center">O</td>
+ <td align="center">(*1)</td>
</tr>
<tr>
<td>Failover</td>
- <td align="center">O</td>
- <td align="center">O</td>
- <td align="center">O</td>
- <td align="center">X</td>
+ <td align="center">O</td>
+ <td align="center">O</td>
+ <td align="center">O</td>
+ <td align="center">X</td>
</tr>
<tr>
<td>Online recovery</td>
- <td align="center">X</td>
- <td align="center">0</td>
- <td align="center">(*2)</td>
- <td align="center">X</td>
+ <td align="center">X</td>
+ <td align="center">0</td>
+ <td align="center">(*2)</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">O</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">2 or higher</td>
- <td align="center">2 or higher</td>
- <td align="center">2 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">yes</td>
+ <td align="center">no</td>
+ <td align="center">no</td>
+ <td align="center">no</td>
+ <td align="center">yes</td>
</tr>
</table>
-<p>
<ul>
- <li> O means 'available', X means 'unavailable'
- <li>(*1) Parallel Query Mode requires the replication or the load-balancing
- turned on, however replication and load-balancing cannot be used for a
- distributed table in Parallel Query Mode.
- <li>(*2) Online recovery can be used with Master slave+Streaming replication.
- <li>(*3) 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.
-</ul>
-</p>
-
-<h2>Configuring <code>pcp.conf</code></h2>
+ <li> O means 'available', X means 'unavailable'</li>
+ <li>(*1) Parallel Query Mode requires the replication or the load-balancing
+ turned on, however replication and load-balancing cannot be used for a
+ distributed table in Parallel Query Mode.
+ </li>
+ <li>(*2) Online recovery can be used with Master slave+Streaming replication.</li>
+ <li>(*3) 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.
+ </li>
+ </ul>
+
+<h2 id="pcp_conf">Configuring <code>pcp.conf</code></h2>
<p>pgpool-II provides a control interface where an administrator
can collect pgpool-II status, and terminate pgpool-II processes remotely.
@@ -385,7 +385,7 @@ acbd18db4cc2f85cedef654fccc4a4d8
The <code>pcp.conf</code> file must be readable by the user who
executes pgpool-II.</p>
-<h2>Configuring <code>pgpool.conf</code></h2>
+<h2 id="pgpool_conf">Configuring <code>pgpool.conf</code></h2>
<p>As already explained, each operation mode has its specific
configuration parameters in <code>pgpool.conf</code>. A
@@ -430,601 +430,658 @@ comment and will be ignored.</p>
<h3><a name="common"></a>Common settings</h3>
<dl>
- <dt><a name="LISTEN_ADDRESS"></a>listen_addresses</dt>
- <dd>
- <p>Specifies the hostname or IP address, on which pgpool-II will accept
- TCP/IP connections. <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><a name="PORT"></a>port</dt>
- <dd>
- <p>The port number used by pgpool-II to listen for connections. Default
- is 9999. This parameter can only be set at server start.</p>
- </dd>
-
- <dt><a name="SOCKET_DIR"></a>socket_dir</dt>
- <dd>
- <p>The directory where the UNIX domain socket accepting connections for
- pgpool-II will be created. Default is <code>'/tmp'</code>. Be
- aware that this socket might be deleted by a cron job. 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><a name="PCP_PORT"></a>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><a name="LISTEN_ADDRESS"></a>listen_addresses</dt>
+ <dd>
+ <p>Specifies the hostname or IP address, on which pgpool-II will accept
+ TCP/IP connections. <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.
+ </p>
+ <p>
+ This parameter can only be set at server start.</p>
+ </dd>
+
+<dt><a name="PORT"></a>port</dt>
+ <dd>
+ <p>The port number used by pgpool-II to listen for connections. Default is 9999.
+ </p>
+ <p>
+ This parameter can only be set at server start.</p>
+ </dd>
+
+<dt><a name="SOCKET_DIR"></a>socket_dir</dt>
+ <dd>
+ <p>The directory where the UNIX domain socket accepting connections for
+ pgpool-II will be created. Default is <code>'/tmp'</code>. Be
+ aware that this socket might be deleted by a cron job. We recommend to
+ set this value to <code>'/var/run'</code> or such directory.
+ </p>
+ <p>
+ This parameter can only be set at server start.</p>
+ </dd>
+
+<dt><a name="PCP_PORT"></a>pcp_port</dt>
+ <dd>
+ <p>The port number where PCP process accepts connections. Default is 9898.
+ </p>
+ <p>
+ This parameter can only be set at server start.</p>
+ </dd>
<dt><a name="PCP_SOCKET_DIR"></a>pcp_socket_dir</dt>
- <dd>
- <p>The directory path of the UNIX domain socket accepting
- connections for the 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><a name="BACKEND_SOCKET_DIR"></a>backend_socket_dir</dt>
- <dd>
- <font color="red"><em>DEPRECATED</em><br>
- This parameter is deprecated for consistency with the default libpq policy.
- See the backend_hostname parameter definition to adapt your configuration accordingly.
- </font>
- <p>This parameter was defining the PostgreSQL server's UNIX domain socket directory.
- Default is <code>'/tmp'</code>.
- This parameter can only be set at server start.</p>
- </dd>
-
- <dt><a name="PCP_TIMEOUT"></a>pcp_timeout</dt>
- <dd>
- <p>PCP connection's 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 can be changed
- with a reload.</p>
- </dd>
-
- <dt><a name="NUM_INIT_CHILDREN"></a>num_init_children</dt>
- <dd>
- <p>The number of preforked pgpool-II server processes. Default
- is 32. num_init_children is also the concurrent connections limit to
- pgpool-II from clients. If more than num_init_children clients
- try to connect to pgpool-II, they are blocked (not rejected)
- until a connection to any pgpool-II process is closed.
- Up to 2*num_init_children can be queued.
- Number of connections to each PostgreSQL is roughly max_pool*num_init_children</p>
-
- <p>Some hints in addition to above:</p>
- <p>
- <ul>
- <li>Canceling a query creates another
- connection to the backend; thus, a query cannot be canceled if
- all the connections are in use. If you want to ensure that queries can
- be canceled, set this value to twice the expected
- connections.</li>
-
- <li>PostgreSQL allows concurrent connections for non superusers up
- to max_connections - superuser_reserved_connections.</li>
- </ul>
- </p>
- <p>In summary, max_pool, num_init_children, max_connections,
- superuser_reserved_connections must satisfy the following formula:
- </p>
- <p>
- <pre>
-max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
-max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) (query canceling needed)
- </pre>
- <p>
- This parameter can only be set at server start.</p>
- </dd>
-
- <dt><a name="CHILD_LIFE_TIME"></a>child_life_time</dt>
- <dd>
- <p>A pgpool-II child process' life time in seconds. When a child
- is idle for that many seconds, it is terminated and a 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 feature. Note that this doesn't apply for
- processes that have not accepted any connection yet.
- You need to reload pgpool.conf if you change this value.
- </p>
- </dd>
-
- <dt><a name="CHILD_MAX_CONNECTIONS"></a>child_max_connections</dt>
- <dd>
- <p>A pgpool-II child process will be terminated after this many
- connections from clients. This parameter is useful on a server
- if it is so busy that child_life_time and connection_life_time
- are never triggered. Thus this is also usefull to prevent PostgreSQL servers from getting too big.
- You need to reload pgpool.conf if you change this value.
- </p>
- </dd>
-
- <dt><a name="CLIENT_IDLE_LIMIT"></a>client_idle_limit</dt>
- <dd>
- <p> Disconnect a client if it has been idle for
- client_idle_limit seconds after the last query has completed.
- This is useful to prevent pgpool childs from being
- occupied by a lazy client or a broken TCP/IP connection between client and
- pgpool. The default value for client_idle_limit is 0, which means
- the feature is turned off. this value. This parameter is ignored in the
- second stage of online recovery. You need to reload pgpool.conf if you
- change client_idle_limit.</p>
-
- <dt><a name="AUTHENTICATION_TIMEOUT"></a>authentication_timeout</dt>
- <dd>
- <p>Specify the timeout for pgpool authentication. 0 disables the time
- out. Default value is 60. You need to restart pgpool-II if you
- change authentication_timeout.</p>
-
- <dt><a name="LOGDIR"></a>logdir</dt>
- <dd>
- <p>pgpool_status is written into this directory.
- </p>
- </dd>
- <dt><a name="LOG_DESTINATION"></a>log_destination</dt>
- <dd>
- <p>PgPool II supports several methods for logging server messages,
- including stderr and syslog. The default is to log to stderr.
- </p>
- <p>Note: you will need to alter the configuration of your system's syslog daemon in order to make use of the syslog option for log_destination. PgPool can log to syslog facilities LOCAL0 through LOCAL7 (see syslog_facility), but the default syslog configuration on most platforms will discard all such messages. You will need to add something like
- </p>
- <pre>
- local0.* /var/log/pgpool.log
- </pre>
- <p>to the syslog daemon's configuration file to make it work.
- </p>
- </dd>
-
- <dt><a name="SYSLOG_FACILITY"></a>syslog_facility</dt>
- <dd>
- <p>When logging to syslog is enabled, this parameter determines the syslog "facility" to be used. You can choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7; the default is LOCAL0. See also the documentation of your system's syslog daemon.
- </p>
- </dd>
-
- <dt><a name="SYSLOG_IDENT"></a>syslog_ident</dt>
- <dd>
- <p>When logging to syslog is enabled, this parameter determines the program name used to identify PgPool messages in syslog logs. The default is pgpool.
- </p>
- </dd>
-
- <dt><a name="PID_FILE_NAME"></a>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 this value.
- </p>
- </dd>
-
- <dt><a name="PRINT_TIMESTAMP"></a>print_timestamp</dt>
- <dd>
- <p>Add timestamps to the logs when set to true. Default is
- true.
- You need to reload pgpool.conf if you change print_timestamp.
- </p>
- </dd>
-
- <dt><a name="CONNECTION_CACHE"></a>connection_cache</dt>
- <dd>
- <p>Caches connections to backends when set to true. Default is
- true. You need to restart pgpool-II if you change this value.</p>
- </dd>
-
- <dt><a name="HEALTH_CHECK_TIMEOUT"></a>health_check_timeout</dt>
- <dd>
- <p>pgpool-II periodically tries to connect to the backends to
- detect any error 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 serves to prevent the health check from waiting for a long
- time in a case such as un unplugged network cable. The
- timeout value is in seconds. Default value is 20. 0 disables
- timeout (waits until TCP/IP timeout).
-
- This health check requires one 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 this value.
- </p>
- </dd>
-
- <dt><a name="HEALTH_CHECK_PERIOD"></a>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><a name="HEALTH_CHECK_USER"></a>health_check_user</dt>
- <dd>
- <p>The user name to perform health check. This user must exist
- in all the PostgreSQL backends.
- Otherwise, health check causes an error.
- You need to reload pgpool.conf if you change health_check_user.
- </p>
- </dd>
-
- <dt><a name="HEALTH_CHECK_PASSWORD"></a>health_check_password</dt>
- <dd>
- <p>The password of the user to perform health check.
- You need to reload pgpool.conf if you change health_check_password.
- </p>
- </dd>
-
- <dt><a name="HEALTH_CHECK_MAX_RETRIES"></a>health_check_max_retries</dt>
- <dd>
- <p>The maximum number of times to retry a failed health check
- before giving up and initiating failover. This setting can
- be useful in spotty networks, when it is expected that health
- checks will fail occasionally even when the master is fine.
- Default is 0, which means do not retry.
- It is advised that you disable<a href="#FAIL_OVER_ON_BACKEND_ERROR">fail_over_on_backend_error</a> if you want to enable health_check_max_retries</dt>.
- You need to reload pgpool.conf if you change health_check_max_retries.
- </p>
- </dd>
+ <dd>
+ <p>The directory path of the UNIX domain socket accepting
+ connections for the 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.
+ </p>
+ <p>
+ This parameter can only be set at server start.</p>
+ </dd>
+
+<dt><a name="BACKEND_SOCKET_DIR"></a>backend_socket_dir</dt>
+ <dd>
+ <font color="red"><em>DEPRECATED</em><br>
+ This parameter is deprecated for consistency with the default libpq policy.
+ See the backend_hostname parameter definition to adapt your configuration accordingly.
+ </font>
+ <p>This parameter was defining the PostgreSQL server's UNIX domain socket directory.
+ Default is <code>'/tmp'</code>.
+ </p>
+ <p>
+ This parameter can only be set at server start.</p>
+ </dd>
+
+<dt><a name="PCP_TIMEOUT"></a>pcp_timeout</dt>
+ <dd>
+ <p>PCP connection's 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.
+ </p>
+ <p>
+ This can be changed with a reload.</p>
+ </dd>
+
+<dt><a name="NUM_INIT_CHILDREN"></a>num_init_children</dt>
+ <dd>
+ <p>The number of preforked pgpool-II server processes. Default is 32.
+ num_init_children is also the concurrent connections limit to pgpool-II from clients.
+ If more than num_init_children clients try to connect to pgpool-II,
+ they are blocked (not rejected) until a connection to any pgpool-II process is closed.
+ Up to 2*num_init_children can be queued.
+ Number of connections to each PostgreSQL is roughly max_pool*num_init_children</p>
+
+ <p>Some hints in addition to above:</p>
+ <ul>
+ <li>Canceling a query creates another
+ connection to the backend; thus, a query cannot be canceled if
+ all the connections are in use. If you want to ensure that queries can
+ be canceled, set this value to twice the expected connections.</li>
+
+ <li>PostgreSQL allows concurrent connections for non superusers up
+ to max_connections - superuser_reserved_connections.</li>
+ </ul>
+
+ <p>In summary, max_pool, num_init_children, max_connections,
+ superuser_reserved_connections must satisfy the following formula:
+ </p>
+<pre>
+max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
+max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) (query canceling needed)
+</pre>
- <dt><a name="HEALTH_CHECK_RETRY_DELAY"></a>health_check_retry_delay</dt>
- <dd>
+ <p>
+ This parameter can only be set at server start.</p>
+ </dd>
+
+<dt><a name="CHILD_LIFE_TIME"></a>child_life_time</dt>
+ <dd>
+ <p>A pgpool-II child process' life time in seconds.
+ When a child is idle for that many seconds, it is terminated and a 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 feature. Note that this doesn't apply for processes
+ that have not accepted any connection yet.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="CHILD_MAX_CONNECTIONS"></a>child_max_connections</dt>
+ <dd>
+ <p>A pgpool-II child process will be terminated after this many connections from clients.
+ This parameter is useful on a server
+ if it is so busy that <a href=#CHILD_LIFE_TIME">child_life_time</a> and
+ <a href="#CONNECTION_LIFE_TIME">connection_life_time</a> are never triggered.
+ Thus this is also usefull to prevent PostgreSQL servers from getting too big.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="CLIENT_IDLE_LIMIT"></a>client_idle_limit</dt>
+ <dd>
+ <p> Disconnect a client if it has been idle for client_idle_limit seconds
+ after the last query has completed.
+ This is useful to prevent pgpool childs from being
+ occupied by a lazy client or a broken TCP/IP connection between client and pgpool.
+ The default value for client_idle_limit is 0, which means
+ the feature is turned off. this value.
+ This parameter is ignored in the second stage of online recovery.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change client_idle_limit.</p>
+ </dd>
+
+<dt><a name="AUTHENTICATION_TIMEOUT"></a>authentication_timeout</dt>
+ <dd>
+ <p>Specify the timeout for pgpool authentication. 0 disables the time out.
+ Default value is 60.
+ </p>
+ <p>
+ You need to restart pgpool-II if you change authentication_timeout.</p>
+ </dd>
+
+<dt><a name="LOGDIR"></a>logdir</dt>
+ <dd>
+ <p>pgpool_status is written into this directory.</p>
+ </dd>
+
+<dt><a name="LOG_DESTINATION"></a>log_destination</dt>
+ <dd>
+ <p>PgPool II supports several methods for logging server messages,
+ including stderr and syslog. The default is to log to stderr.
+ </p>
+ <p>Note: you will need to alter the configuration of your system's syslog daemon
+ in order to make use of the syslog option for log_destination.
+ PgPool can log to syslog facilities LOCAL0 through LOCAL7 (see syslog_facility),
+ but the default syslog configuration on most platforms will discard all such messages.
+ You will need to add something like
+ </p>
+<pre>
+local0.* /var/log/pgpool.log
+</pre>
+ <p>to the syslog daemon's configuration file to make it work.</p>
+ </dd>
+
+<dt><a name="SYSLOG_FACILITY"></a>syslog_facility</dt>
+ <dd>
+ <p>When logging to syslog is enabled, this parameter determines the syslog "facility" to be used.
+ You can choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7;
+ the default is LOCAL0. See also the documentation of your system's syslog daemon.
+ </p>
+ </dd>
+
+<dt><a name="SYSLOG_IDENT"></a>syslog_ident</dt>
+ <dd>
+ <p>When logging to syslog is enabled, this parameter determines the program name
+ used to identify PgPool messages in syslog logs. The default is pgpool.
+ </p>
+ </dd>
+
+<dt><a name="PID_FILE_NAME"></a>pid_file_name</dt>
+ <dd>
+ <p>Full path to a file which contains pgpool's process id.
+ Default is "/var/run/pgpool/pgpool.pid".
+ </p>
+ <p>
+ You need to restart pgpool-II if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="PRINT_TIMESTAMP"></a>print_timestamp</dt>
+ <dd>
+ <p>Add timestamps to the logs when set to true. Default is true.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change print_timestamp.
+ </p>
+ </dd>
+
+<dt><a name="CONNECTION_CACHE"></a>connection_cache</dt>
+ <dd>
+ <p>Caches connections to backends when set to true. Default is true.
+ </p>
+ <p>
+ You need to restart pgpool-II if you change this value.</p>
+ </dd>
+
+<dt><a name="HEALTH_CHECK_TIMEOUT"></a>health_check_timeout</dt>
+ <dd>
+ <p>pgpool-II periodically tries to connect to the backends
+ to detect any error 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.
+ </p>
+ <p>
+ This parameter serves to prevent the health check from waiting for a long
+ time in a case such as un unplugged network cable.
+ The timeout value is in seconds. Default value is 20.
+ 0 disables timeout (waits until TCP/IP timeout).
+ </p>
+ <p>
+ This health check requires one extra connection to each backend,
+ so <code>max_connections</code> in the
+ <code>postgresql.conf</code> needs to be incremented as needed.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="HEALTH_CHECK_PERIOD"></a>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.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change health_check_period.
+ </p>
+ </dd>
+
+<dt><a name="HEALTH_CHECK_USER"></a>health_check_user</dt>
+ <dd>
+ <p>The user name to perform health check.
+ This user must exist in all the PostgreSQL backends.
+ Otherwise, health check causes an error.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change health_check_user.
+ </p>
+ </dd>
+
+<dt><a name="HEALTH_CHECK_PASSWORD"></a>health_check_password</dt>
+ <dd>
+ <p>The password of the user to perform health check.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change health_check_password.
+ </p>
+ </dd>
+
+<dt><a name="HEALTH_CHECK_MAX_RETRIES"></a>health_check_max_retries</dt>
+ <dd>
+ <p>The maximum number of times to retry a failed health check
+ before giving up and initiating failover.
+ This setting can be useful in spotty networks, when it is expected that health
+ checks will fail occasionally even when the master is fine.
+ Default is 0, which means do not retry.
+ It is advised that you disable<a href="#FAIL_OVER_ON_BACKEND_ERROR">fail_over_on_backend_error</a>
+ if you want to enable health_check_max_retries</dt>.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change health_check_max_retries.
+ </p>
+ </dd>
+
+<dt><a name="HEALTH_CHECK_RETRY_DELAY"></a>health_check_retry_delay</dt>
+ <dd>
<p>The amount of time (in seconds) to sleep between failed health check
- retries (not used unless health_check_max_retries is > 0).
+ retries (not used unless health_check_max_retries is > 0).
If 0, then retries are immediate (no delay).
+ </p>
+ <p>
You need to reload pgpool.conf if you change health_check_retry_delay.
</p>
- </dd>
+ </dd>
<dt><a name="FAILOVER_COMMAND"></a>failover_command</dt>
-<dd>
-<p>
-This parameter specifies a command to run when a node is detached.
-pgpool-II replaces the following special characters with backend specific
-information.
-</p>
+ <dd>
+ <p>
+ This parameter specifies a command to run when a node is detached.
+ pgpool-II replaces the following special characters with backend specific
+ information.
+ </p>
-<blockquote>
-<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>%H</td><td>Hostname of the new master node.</td></tr>
-<tr><td>%P</td><td>Old primary node ID.</td></tr>
-<tr><td>%%</td><td>'%' character</td></tr>
-</table>
-</blockquote>
-<p>
-You need to reload pgpool.conf if you change failover_command.
-</p>
+ <blockquote>
+ <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>%H</td><td>Hostname of the new master node.</td></tr>
+ <tr><td>%P</td><td>Old primary node ID.</td></tr>
+ <tr><td>%%</td><td>'%' character</td></tr>
+ </table>
+ </blockquote>
+ <p>
+ 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 invokes
-the failover_command and waits for its completion.
-After this, pgpool starts new child processes and is ready again to accept
-connections from clients.
-</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 invokes
+ the failover_command and waits for its completion.
+ After this, pgpool starts new child processes and is ready again to accept
+ connections from clients.
+ </p>
+ </dd>
<dt><a name="FAILBACK_COMMAND"></a>failback_command</dt>
-<dd>
-<p>
-This parameter specifies a command to run when a node is attached.
-pgpool-II replaces special the following characters with backend specific
-information.
-</p>
-<blockquote>
-<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>%H</td><td>Hostname of the new master node.</td></tr>
-<tr><td>%P</td><td>Old primary node ID.</td></tr>
-<tr><td>%%</td><td>'%' character</td></tr>
-</table>
-</blockquote>
-<p>
- You need to reload pgpool.conf if you change failback_command.
-</p>
+ <dd>
+ <p>
+ This parameter specifies a command to run when a node is attached.
+ pgpool-II replaces special the following characters with backend specific
+ information.
+ </p>
+ <blockquote>
+ <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>%H</td><td>Hostname of the new master node.</td></tr>
+ <tr><td>%P</td><td>Old primary node ID.</td></tr>
+ <tr><td>%%</td><td>'%' character</td></tr>
+ </table>
+ </blockquote>
+ <p>
+ You need to reload pgpool.conf if you change failback_command.
+ </p>
+ </dd>
<dt><a name="FOLLOW_MASTER_COMMAND"></a>follow_master_command</dt>
-<dd>
-<p>
-This parameter specifies a command to run in master/slave streaming replication mode only after a master failover.
-pgpool-II replaces the following special characters with backend specific information.
-</p>
+ <dd>
+ <p>
+ This parameter specifies a command to run in master/slave streaming replication mode
+ only after a master failover.
+ pgpool-II replaces the following special characters with backend specific information.
+ </p>
-<blockquote>
-<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>%H</td><td>Hostname of the new master node.</td></tr>
-<tr><td>%P</td><td>Old primary node ID.</td></tr>
-<tr><td>%%</td><td>'%' character</td></tr>
-</table>
-</blockquote>
-<p>
-You need to reload pgpool.conf if you change follow_master_command.
-</p>
-
-<p>
-If follow_master_command is not empty, when a master failover is completed in master/slave streaming replication,
-pgpool degenerate all nodes excepted the new master and starts new child processes to be ready again to accept
-connections from clients. After this, pgpool run the command set into the 'follow_master_command' for each
-degenerated nodes. Typically the command should be used to recover the slave from the new master by call the
-pcp_recovery_node command for example.
-</p>
-
- <dt><a name="FAIL_OVER_ON_BACKEND_ERROR"></a>fail_over_on_backend_error</dt>
- <dd>
- <p>
-If true, and an error occurs when reading/writing to the backend communication,
-pgpool-II will trigger the fail over procedure.
-If set to false, pgpool will report an error and disconnect the session.
-If you set this parameter to off, it is recommended that you turn on health checking.
-Please note that even if this parameter is set to off, however, pgpool will also do the fail over when
-pgpool detects the administrative shutdown of
-postmaster. You need to reload pgpool.conf if you change this value.
-</p>
-</dd>
-
- <dt><a name="IGNORE_LEADING_WHITE_SPACE"></a>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 if used with
- APIs like DBI/DBD:Pg which adds white spaces against the user's
- will.
- You need to reload pgpool.conf if you change this value.
- </p>
- </dd>
-
- <dt><a name="LOG_STATEMENT"></a>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 this value.
- </p>
- </dd>
-
- <dt><a name="LOG_PER_NODE_STATEMENT"></a>log_per_node_statement</dt>
- <dd>
- <p>Similar to log_statement, except that it prints logs for each DB node separately.
- It can be useful to make sure that replication is working, for example.
- You need to reload pgpool.conf if you change this value.
- </p>
- </dd>
-
- <dt><a name="LOG_HOSTNAME"></a>log_hostname</dt>
- <dd>
+ <blockquote>
+ <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>%H</td><td>Hostname of the new master node.</td></tr>
+ <tr><td>%P</td><td>Old primary node ID.</td></tr>
+ <tr><td>%%</td><td>'%' character</td></tr>
+ </table>
+ </blockquote>
+ <p>
+ You need to reload pgpool.conf if you change follow_master_command.
+ </p>
+
+ <p>
+ If follow_master_command is not empty, when a master failover is
+ completed in master/slave streaming replication,
+ pgpool degenerate all nodes excepted the new master and starts new child processes
+ to be ready again to accept connections from clients.
+ After this, pgpool run the command set into the 'follow_master_command' for each
+ degenerated nodes. Typically the command should be used to recover the slave from the new master
+ by call the <a href="pcp_recovery_node">pcp_recovery_node</a> command for example.
+ </p>
+ </dd>
+
+<dt><a name="FAIL_OVER_ON_BACKEND_ERROR"></a>fail_over_on_backend_error</dt>
+ <dd>
+ <p>
+ If true, and an error occurs when reading/writing to the backend communication,
+ pgpool-II will trigger the fail over procedure.
+ If set to false, pgpool will report an error and disconnect the session.
+ If you set this parameter to off, it is recommended that you turn on health checking.
+ Please note that even if this parameter is set to off, however, pgpool will also do the fail over when
+ pgpool detects the administrative shutdown of postmaster.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="IGNORE_LEADING_WHITE_SPACE"></a>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 if used with
+ APIs like DBI/DBD:Pg which adds white spaces against the user's will.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="LOG_STATEMENT"></a>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.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="LOG_PER_NODE_STATEMENT"></a>log_per_node_statement</dt>
+ <dd>
+ <p>Similar to <a href="#LOG_PER_NODE_STATEMENT">log_statement</a>,
+ except that it prints logs for each DB node separately.
+ It can be useful to make sure that replication is working, for example.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="LOG_HOSTNAME"></a>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 this value.
+ of an IP address. Also, if <a href="#LOG_CONNECTIONS">log_connections</a> is enabled,
+ hostname will be logged.
</p>
- </dd>
-
- <dt><a name="LOG_CONNECTIONS"></a>log_connections</dt>
- <dd>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
+<dt><a name="LOG_CONNECTIONS"></a>log_connections</dt>
+ <dd>
<p>
If true, all incoming connections will be printed to the log.
+ </p>
+ <p>
You need to reload pgpool.conf if you change this value.
</p>
- </dd>
-
- <dt><a name="ENABLE_POOL_HBA"></a>enable_pool_hba</dt>
- <dd>
+ </dd>
+
+<dt><a name="ENABLE_POOL_HBA"></a>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>.
+ </p>
<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 this value.
</p>
- </dd>
+ </dd>
- <dt><a name="BACKEND_HOSTNAME"></a>backend_hostname</dt>
- <dd>
- <p>Specifies where to connect with the PostgreSQL backend.
- It is used by pgpool-II to communicate with the server.
- </p>
- <p>
- For TCP/IP communication, this parameter can take a hostname or an IP address.
- If this begins with a slash, it specifies Unix-domain communication
- rather than TCP/IP; the value is the name of the directory
- in which the socket file is stored. The default behavior when backend_hostname
- is empty (<code>''</code>) is to connect to a Unix-domain socket in <code>/tmp</code>.
- </p>
- <p>
- 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>
- Please note that the DB node which has id 0 has no special meaming
- if operated in streaming replication mode.
- Rather, you should care about if the DB node is the "primary node" or not.
- See <a href="#stream">Streaming Replication</a> for more details.
- </p>
- <p>If you plan to use only one PostgreSQL server, specify it by
- <code>backend_hostname0</code>.</p>
-
- <p>
- New nodes can be added in this parameter by reloading a configuration
- file. However, values cannot be updated so you must restart
- pgpool-II in that case.
- </p>
- </dd>
-
- <dt><a name="BACKEND_PORT"></a>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>
- New backend ports can be added in this parameter by reloading a configuration
- file. However, values cannot be updated so you must restart
- pgpool-II in that case.
- </p>
- </dd>
-
- <dt><a name="BACKEND_WEIGHT"></a>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>
- New backend weights can be added in this parameter by reloading a configuration
- file.
- </p>
- <p>
- From pgpool-II 2.2.6/2.3 or later, you can change this value by re-loading the configuration file.
- This will take effect only for new established client sessions.
- This is useful if you want to prevent any query sent to slaves to perform
- some administrative work in master/slave mode.
- </p>
- </dd>
-
- <dt><a name="BACKEND_DATA_DIRECTORY"></a>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 don't plan to use online recovery, you do not need to
- specify this parameter.
- </p>
+<dt><a name="BACKEND_HOSTNAME"></a>backend_hostname</dt>
+ <dd>
+ <p>Specifies where to connect with the PostgreSQL backend.
+ It is used by pgpool-II to communicate with the server.
+ </p>
+ <p>
+ For TCP/IP communication, this parameter can take a hostname or an IP address.
+ If this begins with a slash, it specifies Unix-domain communication
+ rather than TCP/IP; the value is the name of the directory
+ in which the socket file is stored. The default behavior when backend_hostname
+ is empty (<code>''</code>) is to connect to a Unix-domain socket in <code>/tmp</code>.
+ </p>
+ <p>
+ 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>
+ Please note that the DB node which has id 0 has no special meaming
+ if operated in streaming replication mode.
+ Rather, you should care about if the DB node is the "primary node" or not.
+ See <a href="#stream">Streaming Replication</a> for more details.
+ </p>
+ <p>If you plan to use only one PostgreSQL server, specify it by
+ <code>backend_hostname0</code>.</p>
- <p>
- New backend data directories can be added in this parameter by reloading a configuration
- file. However, values cannot be updated so you must restart
- pgpool-II in that case.
- </p>
- </dd>
-
- <dt><a name="BACKEND_FLAG"></a>backend_flag</dt>
- <dd>
- <p>Controls various backend behavior.
- Multiple backends can be specified by adding a number
- at the end of the parameter name
- (e.g. <code>backend_flag0</code>).
- </p>
- <p>
- Currently followings are allowed.
- Multiple falgs can be specified by using "|".
- </p>
- <dl>
- <dt>ALLOW_TO_FAILOVER</dt>
- <dd>
- Allow to failover or detaching backend.
- This is the default.
- You cannot specify with DISALLOW_TO_FAILOVER at a same time.
- </dd>
- <dt>DISALLOW_TO_FAILOVER</dt>
- <dd>
- Disallow to failover or detaching backend.
- This is usefull when you protect backend by using HA(High Availability) softwares
- such as Heartbeat or Pacemaker.
- You cannot specify with ALLOW_TO_FAILOVER at a same time.
- </dd>
- </dl>
-
- </dt>
-
- <dt><a name="SSL">ssl</a></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 with frontend connections.
- </p>
+ <p>
+ New nodes can be added in this parameter by reloading a configuration
+ file. However, values cannot be updated so you must restart pgpool-II in that case.
+ </p>
+ </dd>
- <p>
- SSL is off by default. Note that OpenSSL support must also
- have been configured at compilation time, as mentioned in the
- <a href="#install">installation</a> section.
- </p>
+<dt><a name="BACKEND_PORT"></a>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>
- The pgpool-II daemon must be restarted when updating SSL related settings.
- </p>
- </dd>
+ <p>
+ New backend ports can be added in this parameter by reloading a configuration
+ file. However, values cannot be updated so you must restart
+ pgpool-II in that case.
+ </p>
+ </dd>
+
+<dt><a name="BACKEND_WEIGHT"></a>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>
+ New backend weights can be added in this parameter by reloading a configuration file.
+ </p>
+ <p>
+ From pgpool-II 2.2.6/2.3 or later, you can change this value by re-loading the configuration file.
+ This will take effect only for new established client sessions.
+ This is useful if you want to prevent any query sent to slaves to perform
+ some administrative work in master/slave mode.
+ </p>
+ </dd>
+
+<dt><a name="BACKEND_DATA_DIRECTORY"></a>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 don't plan to use online recovery, you do not need to specify this parameter.
+ </p>
- <dt><a name="SSL_KEY"></a>ssl_key</dt>
- <dd>
- <p>
- The path to the private key file to use for incoming frontend connections.
- </p>
+ <p>
+ New backend data directories can be added in this parameter by reloading a configuration file.
+ However, values cannot be updated so you must restart pgpool-II in that case.
+ </p>
+ </dd>
- <p>
- There is no default value for this option, and if left unset SSL will
- be disabled for incoming frontend connections.
- </p>
- </dd>
+<dt><a name="BACKEND_FLAG"></a>backend_flag</dt>
+ <dd>
+ <p>Controls various backend behavior.
+ Multiple backends can be specified by adding a number at the end of the parameter name
+ (e.g. <code>backend_flag0</code>).
+ </p>
+ <p>
+ Currently followings are allowed.
+ Multiple falgs can be specified by using "|".
+ </p>
- <dt><a name="SSL_CERT"></a>ssl_cert</dt>
- <dd>
- <p>
- The path to the public x509 certificate file to use for incoming
- frontend connections.
- </p>
+ <dl>
+ <dt>ALLOW_TO_FAILOVER</dt>
+ <dd>
+ Allow to failover or detaching backend. This is the default.
+ You cannot specify with DISALLOW_TO_FAILOVER at a same time.
+ </dd>
+ <dt>DISALLOW_TO_FAILOVER</dt>
+ <dd>
+ Disallow to failover or detaching backend.
+ This is usefull when you protect backend by using HA(High Availability) softwares
+ such as Heartbeat or Pacemaker.
+ You cannot specify with ALLOW_TO_FAILOVER at a same time.
+ </dd>
+ </dl>
+ </dt>
+
+<dt><a name="SSL">ssl</a></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 with 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>
+ <p>
+ SSL is off by default. Note that OpenSSL support must also
+ have been configured at compilation time, as mentioned in the
+ <a href="#install">installation</a> section.
+ </p>
- <dt><a name="DEBUG_LEVEL"></a>debug_level</dt>
- <dd>
- <p>
- Debug message verbosity level. 0 means no message, greater than 1
- means more verbose message. Default value is 0.
- </p>
- </dd>
+ <p>
+ The pgpool-II daemon must be restarted when updating SSL related settings.
+ </p>
+ </dd>
+
+<dt><a name="SSL_KEY"></a>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><a name="SSL_CERT"></a>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>
+
+<dt><a name="DEBUG_LEVEL"></a>debug_level</dt>
+ <dd>
+ <p>
+ Debug message verbosity level. 0 means no message, greater than 1
+ means more verbose message. Default value is 0.
+ </p>
+ </dd>
<dt><a name="RELCACHE_EXPIRE"></a>relcache_expire</dt>
- <dd>
- <p>
- Life time of relation cache in seconds. 0 means no cache
- expiration(the default).
- The relation cache is used for cache the query result against PostgreSQL
- system catalog to obtain various information including table structures
- or if it's a temporary table or not. The cache is maintained in a pgpool
- child local memory and being kept as long as it survives.
- If someone modify the table by using ALTER TABLE or some such, the relcache
- is not consistent anymore.
- For this purpose, relcache_expiration controls the life time of the cache.
- </p>
- </dd>
- </dl>
+ <dd>
+ <p>
+ Life time of relation cache in seconds. 0 means no cache
+ expiration(the default).
+ The relation cache is used for cache the query result against PostgreSQL
+ system catalog to obtain various information including table structures
+ or if it's a temporary table or not. The cache is maintained in a pgpool
+ child local memory and being kept as long as it survives.
+ If someone modify the table by using ALTER TABLE or some such, the relcache
+ is not consistent anymore.
+ For this purpose, relcache_expiration controls the life time of the cache.
+ </p>
+ </dd>
</dl>
<h4>Generating SSL certificates</h4>
@@ -1035,510 +1092,518 @@ Secure TCP/IP Connections with SSL</a> page at postgresql.org has pointers
with sample commands for how to generate self-signed certificates.
</p>
-<h4>Failover in the raw Mode</h4>
+<h4 id="failover_in_raw_mode">Failover in the raw Mode</h4>
<p>Failover can be performed in raw mode if multiple servers are
defined. pgpool-II usually accesses the backend specified by
-<code>backend_hostname0</code> during 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>
+<code>backend_hostname0</code> during 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><a name="connection_pool_mode"></a>Connection Pool Mode</h3>
+<h1><a name="connection_pool_mode"></a>Connection Pool Mode</h1>
<p>In connection pool mode, all functions in raw mode and the
connection pool function can be used.
-To enable this mode, you need to turn on "connection_cache".
+To enable this mode, you need to turn on "<a href="#CONNECTION_CACHE">connection_cache</a>".
Following parameters take effect to connection pool.</p>
<dl>
- <dt><a name="MAX_POOL"></a>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 with 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 backends may reach
- <code>num_init_children</code> * <code>max_pool</code>.
- This parameter can only be set at server start. </p>
- </dd>
-
- <dt><a name="CONNECTION_LIFE_TIME"></a>connection_life_time</dt>
- <dd>
- <p>Cached connections expiration time in seconds. An expired
- cached connection will be disconnected. Default is 0, which
- means the cached connections will not be disconnected.</p>
- </dd>
-
- <dt><a name="RESET_QUERY_LIST"></a>reset_query_list</dt>
- <dd>
- <p>Specifies the SQL commands sent to reset the connection
- to the backend when exiting a session. Multiple commands can be
- specified by delimiting 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 the 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>
+<dt><a name="MAX_POOL"></a>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 with 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.
+ </p>
+ <p>
+ Default value is 4. Please be aware that the number of
+ connections from pgpool-II processes to the backends may reach
+ <code><a href="#NUM_INIT_CHILDREN">num_init_children</a></code> *
+ <code><a href="#MAX_POOL">max_pool</a></code>.
+ </p>
+ <p>
+ This parameter can only be set at server start. </p>
+ </dd>
+
+<dt><a name="CONNECTION_LIFE_TIME"></a>connection_life_time</dt>
+ <dd>
+ <p>Cached connections expiration time in seconds. An expired
+ cached connection will be disconnected. Default is 0, which
+ means the cached connections will not be disconnected.</p>
+ </dd>
+
+<dt><a name="RESET_QUERY_LIST"></a>reset_query_list</dt>
+ <dd>
+ <p>Specifies the SQL commands sent to reset the connection
+ to the backend when exiting a session. Multiple commands can be
+ specified by delimiting each by ";".
+ Default is the following, but can be changed to suit your system.
-<p>
-You need to reload pgpool.conf upon modification of this directive.
-</p>
-</dd>
+<pre>
+reset_query_list = 'ABORT; DISCARD ALL'
+</pre>
+
+ <p>
+ Commands differ in each PostgreSQL versions. Here are the recommended settings.
+ </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>
+
+ <ul>
+ <li>"ABORT" is not issued when not in a transaction block for 7.4 or later.</li>
+ </ul>
+
+ <p>
+ You need to reload pgpool.conf upon modification of this directive.
+ </p>
+ </dd>
</dl>
-<h4><p>Failover in the Connection Pool Mode</p></h4>
+<h2 id="failover_in_cp_mode">Failover in the Connection Pool Mode</h2>
<p>Failover in the connection pool mode is the same as in the raw mode.</p>
-<h3><a name="replication_mode"></a>Replication Mode</h3>
+<!-- ================================================================================ -->
+
+<h1><a name="replication_mode"></a>Replication Mode</h1>
<p>This mode enables data replication between the backends. The
configuration parameters below must be set in addition to everything above.</p>
<dl>
- <dt><a name="REPLICATION_MODE"></a>replication_mode</dt>
- <dd>
- <p>Setting to true enables replication mode. Default
- is false.</p>
- </dd>
-
- <dt><a name="LOAD_BALANCE_MODE"></a>load_balance_mode</dt>
- <dd>
- <p>When set to true, SELECT queries will be
- distributed to each backend for load balancing. Default is
- false.</p>
- <p>This parameter can only be set at server start. </p>
- </dd>
-
- <dt><a name="FAILOVER_IF_AFFECTED_TUPLES_MISMATCH"></a>failover_if_affected_tuples_mismatch</dt>
- <dd>
- <p>When set to true, if backends don't return the same number of affected
- tuples during an INSERT/UPDATE/DELETE,
- the backends that differ from most frequent result set are degenerated.
- If the frequencies are same, the group which includes master DB node(a DB node having the youngest node id) is remained and other groups are degenerated.
- </p>
- <p>
- If set to false, the session is terminated and the backends are not
- degenerated. Default is false.</p>
- </dd>
-
- <dt><a name="REPLICATION_STOP_ON_MISMATCH"></a>replication_stop_on_mismatch</dt>
- <dd>
- <p>When set to true, if all backends don't return the same packet kind,
- the backends that differ from most
- frequent result set are degenerated. A typical use case is a SELECT
- statement being part of a transaction, replicate_select set to
- true, and SELECT returning a different number of rows among backends.
- Non-SELECT statements might trigger this though.
- For example, a backend succeeded in an UPDATE, while others
- failed.
- Note that pgpool does NOT examine the content of records
- returned by SELECT.
- If set to false, the session is terminated and the backends are not
- degenerated. Default is false.</p>
- </dd>
+<dt><a name="REPLICATION_MODE"></a>replication_mode</dt>
+ <dd>
+ <p>Setting to true enables replication mode. Default is false.</p>
+ </dd>
+
+<dt><a name="LOAD_BALANCE_MODE"></a>load_balance_mode</dt>
+ <dd>
+ <p>When set to true, SELECT queries will be
+ distributed to each backend for load balancing. Default is false.</p>
+ <p>This parameter can only be set at server start. </p>
+ </dd>
+
+<dt><a name="FAILOVER_IF_AFFECTED_TUPLES_MISMATCH"></a>failover_if_affected_tuples_mismatch</dt>
+ <dd>
+ <p>When set to true, if backends don't return the same number of affected
+ tuples during an INSERT/UPDATE/DELETE,
+ the backends that differ from most frequent result set are degenerated.
+ If the frequencies are same, the group which includes master DB node
+ (a DB node having the youngest node id) is remained and other groups are degenerated.
+ </p>
+ <p>
+ If set to false, the session is terminated and the backends are not
+ degenerated. Default is false.</p>
+ </dd>
+
+<dt><a name="REPLICATION_STOP_ON_MISMATCH"></a>replication_stop_on_mismatch</dt>
+ <dd>
+ <p>When set to true, if all backends don't return the same packet kind,
+ the backends that differ from most frequent result set are degenerated.
+ </p>
+ <p>
+ A typical use case is a SELECT statement being part of a transaction,
+ replicate_select set to true, and SELECT returning a different number of rows among backends.
+ Non-SELECT statements might trigger this though.
+ For example, a backend succeeded in an UPDATE, while others failed.
+ Note that pgpool does NOT examine the content of records returned by SELECT.
+ </p>
+ <p>
+ If set to false, the session is terminated and the backends are not degenerated.
+ Default is false.</p>
+ </dd>
<dt><a name="WHITE_FUNCTION_LIST"></a>white_function_list</dt>
-<dd>
-<p>
-Specify a comma separated list of function names that <strong>do not</strong>
-update the database. SELECTs using functions not specified in this list are
-neither load balanced, nor replicated if in replication mode.
-In master slave mode, such SELECTs are sent to master (primary) only.
-</p>
-<p>You can use regular expression into the list to match function name, for example if you have prefixed all your read only function with 'get_' or 'select_'
-</p>
+ <dd>
+ <p>
+ Specify a comma separated list of function names that <strong>do not</strong>
+ update the database. SELECTs using functions not specified in this list are
+ neither load balanced, nor replicated if in replication mode.
+ In master slave mode, such SELECTs are sent to master (primary) only.
+ </p>
+ <p>You can use regular expression into the list to match function name,
+ for example if you have prefixed all your read only function with 'get_' or 'select_'
+ </p>
<pre>
white_function_list = 'get_.*,select_.*'
</pre>
-</dd>
+ </dd>
<dt><a name="BLACK_FUNCTION_LIST"></a>black_function_list
-<dd>
-<p>
-Specify a comma separated list of function names that <strong>do</strong>
-update the database. SELECTs using functions specified in this list are neither
-load balanced, nor replicated if in replication mode.
-In master slave mode, such SELECTs are sent to master(primary) only.
-</p>
-<p>You can use regular expression into the list to match function name, for example if you have prefixed all your updating functions with 'set_', 'update_', 'delete_' or 'insert_':
-</p>
+ <dd>
+ <p>
+ Specify a comma separated list of function names that <strong>do</strong>
+ update the database. SELECTs using functions specified in this list are neither
+ load balanced, nor replicated if in replication mode.
+ In master slave mode, such SELECTs are sent to master(primary) only.
+ </p>
+ <p>You can use regular expression into the list to match function name,
+ for example if you have prefixed all your updating functions with 'set_', 'update_', 'delete_' or 'insert_':
+ </p>
<pre>
black_function_list = 'nextval,setval,set_.*,update_.*,delete_.*,insert_.*'
</pre>
-<p>
-Only one of these two lists can be filled in a configuration.
-</p>
-<p>
-Prior to pgpool-II 3.0, nextval() and setval() were known to do
-writes to the database. You can emulate this by using white_function_list and
-black_function_list:
-</p>
-<p>
+ <p>
+ Only one of these two lists can be filled in a configuration.
+ </p>
+ <p>
+ Prior to pgpool-II 3.0, nextval() and setval() were known to do
+ writes to the database. You can emulate this by using white_function_list and
+ black_function_list:
+ </p>
<pre>
white_function_list = ''
black_function_list = 'nextval,setval,lastval,currval'
</pre>
-</p>
-<p>
-Please note that we have lastval and currval in addition to nextval and setval.
-Though lastval() and currval() are not writing functions, it is wise to add lastval() and currval() to avoid errors in the case when these functions are accidentaly load balanced to other DB node.
-Because adding to black_function_list will prevent load balancing.
-</p>
-</dd>
-
-<dt><a name="REPLICATE_SELECT"></a>replicate_select</dt>
-<dd>
-<p>When set to true, pgpool-II replicates SELECTs in replication mode. If false,
-pgpool-II only sends them to the Master DB. Default is false.
-</p>
-<p>
-If a SELECT query is inside an explicit transaction block, replicate_select and
-load_balance_mode will have an effect on how replication works.
-Details are shown below.
-</p>
-<p>
-<table border>
+ <p>
+ Please note that we have lastval and currval in addition to nextval and setval.
+ Though lastval() and currval() are not writing functions,
+ it is wise to add lastval() and currval() to avoid errors
+ in the case when these functions are accidentaly load balanced to other DB node.
+ Because adding to black_function_list will prevent load balancing.
+ </p>
+ </dd>
-<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>
+<dt><a name="REPLICATE_SELECT"></a>replicate_select</dt>
+ <dd>
+ <p>When set to true, pgpool-II replicates SELECTs in replication mode. If false,
+ pgpool-II only sends them to the Master DB. Default is false.
+ </p>
+ <p>
+ If a SELECT query is inside an explicit transaction block, replicate_select and
+ <a href="#LOAD_BALANCE_MODE">load_balance_mode</a> will have an effect on how replication works.
+ Details are shown below.
+ </p>
-<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>
+ <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>R</td>
+ <td>R</td>
+ <td>M</td>
+ <td>L</td>
+ </tr>
+ </table>
+ </dd>
+
+<dt><a name="INSERT_LOCK"></a>insert_lock</dt>
+ <dd>
+ <p>If 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 severely degraded).
+ To achieve this, however, the following change must be made:
+ </p>
+<pre>
+INSERT INTO ...
+</pre>
-<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>
+ <p>
+ to
+ </p>
-<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>R</td>
-<td>R</td>
-<td>M</td>
-<td>L</td>
-</tr>
-</table>
-</p>
- </dd>
-
- <dt><a name="INSERT_LOCK"></a>insert_lock</dt>
- <dd>
- <p>If 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 severely degraded). To achieve this, however, the following change must be
-made:
-
- <pre>
- INSERT INTO ...
- </pre>
- <p>
- to
- </p>
- <pre>
- BEGIN;
- LOCK TABLE ...
- INSERT INTO ...
- COMMIT;
- </pre>
-
- <p>When <code>insert_lock</code> is true, pgpool-II
- automatically adds the above queries each time an INSERT is
- executed (if already in transaction, it simply adds LOCK TABLE
- ....).
- </p>
- <p>pgpool-II 2.2 or later, it automatically detects whether the table
- has a SERIAL columns or not, so it will never lock the table if it does
- not use SERIAL columns.
- </p>
- <p>
- pgpool-II 3.0 series until 3.0.4 uses a row lock against the sequence
- relation, rather than table lock. This is intended to minimize lock
- conflict with VACUUM (including autovacuum). However this will lead to
- another problem. After transaction wraparound happens, row locking
- against the sequence relation causes PostgreSQL internal error (more
- precisely, access error on pg_clog, which keeps transaction status).
- To prevent this, PostgreSQL core developers decided to disallow row
- locking against sequences and this will break pgpool-II of course (the
- "fixed" version of PostgreSQL will be released as 9.0.5,
- 8.4.9, 8.3.16 and 8.2.22).
- </p>
- <p>
- pgpool-II 3.0.5 or later uses a row lock against pgpool_catalog.insert_lock
- table because new PostgreSQL disallows a row lock against the sequence
- relation. So creating insert_lock table in all databases which are
- accessed via pgpool-II beforehand is required. See <a href="#install">
- Creating insert_lock table</a> for more details. If does not
- exist insert_lock table, pgpool-II locks the insert target
- table. This behavior is same as pgpool-II 2.2 and 2.3 series.
- If you want to use insert_lock which is compatible with older
- releases, you can specify lock method by configure script. See
- <a href="#install">configure</a> for more details.
- </p>
- <p>
- You might want to have a finer (per statement) control:
- </p>
+<pre>
+BEGIN;
+LOCK TABLE ...
+INSERT INTO ...
+COMMIT;
+</pre>
- <ol>
- <li>set <code>insert_lock</code> to true, and add <code>/*NO
- INSERT LOCK*/</code> at the beginning of an INSERT
- statement for which you do not want to acquire the table
- lock.</li>
+ <p>When <code>insert_lock</code> is true, pgpool-II automatically adds
+ the above queries each time an INSERT is executed
+ (if already in transaction, it simply adds LOCK TABLE ....).
+ </p>
+ <p>pgpool-II 2.2 or later, it automatically detects whether the table
+ has a SERIAL columns or not, so it will never lock the table if it does not use SERIAL columns.
+ </p>
+ <p>
+ pgpool-II 3.0 series until 3.0.4 uses a row lock against the sequence
+ relation, rather than table lock.
+ This is intended to minimize lock conflict with VACUUM (including autovacuum).
+ However this will lead to another problem.
+ After transaction wraparound happens, row locking against the sequence relation
+ causes PostgreSQL internal error
+ (more precisely, access error on pg_clog, which keeps transaction status).
+ To prevent this, PostgreSQL core developers decided to disallow row locking
+ against sequences and this will break pgpool-II of course
+ (the "fixed" version of PostgreSQL will be released as 9.0.5,
+ 8.4.9, 8.3.16 and 8.2.22).
+ </p>
+ <p>
+ pgpool-II 3.0.5 or later uses a row lock against pgpool_catalog.insert_lock
+ table because new PostgreSQL disallows a row lock against the sequence relation.
+ So creating insert_lock table in all databases which are accessed via pgpool-II beforehand is required.
+ See <a href="#install">Creating insert_lock table</a> for more details.
+ If does not exist insert_lock table, pgpool-II locks the insert target table.
+ This behavior is same as pgpool-II 2.2 and 2.3 series.
+ If you want to use insert_lock which is compatible with older releases,
+ you can specify lock method by configure script.
+ See <a href="#install">configure</a> for more details.
+ </p>
+ <p>
+ You might want to have a finer (per statement) control:
+ </p>
- <li>set <code>insert_lock</code> to false, and add
- <code>/*INSERT LOCK*/</code> at the beginning of an INSERT
- statement for which you want to acquire the table lock.</li>
- </ol>
+ <ol>
+ <li>set <code>insert_lock</code> to true, and add <code>/*NO INSERT LOCK*/</code>
+ at the beginning of an INSERT statement for which
+ you do not want to acquire the table lock.</li>
- <p>
- Default value is false. If <code>insert_lock</code> is enabled,
- the regression tests for PostgreSQL 8.0 will fail in transactions,
- privileges, rules, and alter_table. The reason for this is that
- pgpool-II tries to LOCK the VIEW for the rule test, and will
- produce the following error message:</p>
+ <li>set <code>insert_lock</code> to false, and add <code>/*INSERT LOCK*/</code>
+ at the beginning of an INSERT statement for which
+ you want to acquire the table lock.</li>
+ </ol>
- <pre>
- ! ERROR: current transaction is aborted, commands ignored until
- end of transaction block
- </pre>
+ <p>
+ Default value is false. If <code>insert_lock</code> is enabled,
+ the regression tests for PostgreSQL 8.0 will fail in transactions,
+ privileges, rules, and alter_table. The reason for this is that
+ pgpool-II tries to LOCK the VIEW for the rule test, and will
+ produce the following error message:</p>
- <p>For example, the transactions test tries an 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>
+<pre>
+! ERROR: current transaction is aborted, commands ignored until
+end of transaction block
+</pre>
+ <p>For example, the transactions test tries an 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>
+ </dd>
<dt><a name="RECOVERY_USER"></a>recovery_user</dt>
-<dd>
-<p>
-This parameter specifies a PostgreSQL username for online recovery.
-It can be changed without restarting.
-</p>
+ <dd>
+ <p>
+ This parameter specifies a PostgreSQL username for online recovery.
+ It can be changed without restarting.
+ </p>
+ </dd>
<dt><a name="RECOVERY_PASSWORD"></a>recovery_password</dt>
-<dd>
-<p>
-This parameter specifies a PostgreSQL password for online recovery.
-It can be changed without restarting.
-</p>
+ <dd>
+ <p>
+ This parameter specifies a PostgreSQL password for online recovery.
+ It can be changed without restarting.
+ </p>
+ </dd>
<dt><a name="RECOVERY_1ST_STAGE_COMMAND"></a>recovery_1st_stage_command</dt>
-<dd>
-<p>
-This parameter specifies a command to be run by master(primary) PostgreSQL server at the first stage of online
-recovery. The command file must be put in the database cluster directory
-for security reasons.
-For example, if recovery_1st_stage_command = 'sync-command', then
-pgpool-II executes $PGDATA/sync-command.
-</p>
-<p>
-recovery_1st_stage_command will receive 3 parameters as follows:
-</p>
-<ol>
-<li>path to master(primary) database cluster</li>
-<li>PostgreSQL host name to be recovered</li>
-<li>path to database cluster to be recovered</li>
-</ol>
-</p>
-<p>
-Note that pgpool-II <b>accepts</b> connections and queries while
-recovery_1st_stage command is executed. You can retrieve and update
-data during this stage.
-</p>
-<p>
-This parameter can be changed without restarting.
-</p>
+ <dd>
+ <p>
+ This parameter specifies a command to be run by master(primary) PostgreSQL server
+ at the first stage of online recovery.
+ The command file must be put in the database cluster directory for security reasons.
+ For example, if recovery_1st_stage_command = 'sync-command',
+ then pgpool-II executes $PGDATA/sync-command.
+ </p>
+ <p>
+ recovery_1st_stage_command will receive 3 parameters as follows:
+ </p>
+ <ol>
+ <li>path to master(primary) database cluster</li>
+ <li>PostgreSQL host name to be recovered</li>
+ <li>path to database cluster to be recovered</li>
+ </ol>
+ <p>
+ Note that pgpool-II <b>accepts</b> connections and queries while
+ recovery_1st_stage command is executed. You can retrieve and update data during this stage.
+ </p>
+ <p>
+ This parameter can be changed without restarting.
+ </p>
+ </dd>
<dt><a name="RECOVERY_2ND_STAGE_COMMAND"></a>recovery_2nd_stage_command</dt>
-<dd>
-<p>
-This parameter specifies a command to be run by master(primary) PostgreSQL server at the second stage of online
-recovery. The command file must be put in the database cluster directory
-for security reasons.
-
-For example, if recovery_2nd_stage_command = 'sync-command', then
-pgpool-II executes $PGDATA/sync-command.
-</p>
-<p>
-recovery_2nd_stage_command will receive 3 parameters as follows:
-</p>
-<ol>
-<li>path to master(primary) database cluster</li>
-<li>PostgreSQL host name to be recovered</li>
-<li>path to database cluster to be recovered</li>
-</ol>
-</p>
-<p>
-Note that pgpool-II <b>does not accept</b> connections and queries while
-recovery_2nd_stage_command is running. Thus if a client stays connected for a
-long time, the recovery command won't be executed. pgpool-II waits until all
-clients have closed their connections. The command is only executed when no
-client is connected to pgpool-II anymore.
-</p>
-<p>
-This parameter can be changed without restarting.
-</p>
+ <dd>
+ <p>
+ This parameter specifies a command to be run by master(primary) PostgreSQL server
+ at the second stage of online recovery.
+ The command file must be put in the database cluster directory for security reasons.
+ For example, if recovery_2nd_stage_command = 'sync-command', then
+ pgpool-II executes $PGDATA/sync-command.
+ </p>
+ <p>
+ recovery_2nd_stage_command will receive 3 parameters as follows:
+ </p>
+ <ol>
+ <li>path to master(primary) database cluster</li>
+ <li>PostgreSQL host name to be recovered</li>
+ <li>path to database cluster to be recovered</li>
+ </ol>
+ </p>
+ <p>
+ Note that pgpool-II <b>does not accept</b> connections and queries while
+ recovery_2nd_stage_command is running.
+ Thus if a client stays connected for a long time,
+ the recovery command won't be executed. pgpool-II waits
+ until all clients have closed their connections. The command is only executed
+ when no client is connected to pgpool-II anymore.
+ </p>
+ <p>
+ This parameter can be changed without restarting.
+ </p>
+ </dd>
<dt><a name="RECOVERY_TIMEOUT"></a>recovery_timeout</dt>
-<dd>
-<p>
-pgpool does not accept new connections during the second stage. If a client
-connects to pgpool during recovery processing, it will have to wait for the end
-of the recovery.
-</p>
-<p>
-This parameter specifies recovery timeout in sec. If this timeout is reached,
-pgpool cancels online recovery and accepts connections. 0 means no wait.
-</p>
-<p>
-This parameter can be changed without restarting.
-</p>
-
- <dt><a name="CLIENT_IDLE_LIMIT_IN_RECOVERY"></a>client_idle_limit_in_recovery</dt>
- <dd>
- <p> Similar to client_idle_limit but only takes effect in the second
- stage of recovery. A client being idle for client_idle_limit_in_recovery
- seconds since its last query will get disconnected.
- This is useful for preventing the pgpool recovery from being
- disturbed by a lazy client or if the TCP/IP connection between the client
- and pgpool is accidentally down (a cut cable for instance).
- If set to -1, disconnect the client immediately.
- The default value for client_idle_limit_in_recovery is 0,
- which means the feature is turned off.
- </p>
- <p>
- If your clients are very busy, pgpool-II cannot enter the second stage of
- recovery whatever value of client_idle_limit_in_recovery you may choose. In
- this case, you can set client_idle_limit_in_recovery to -1 so that pgpool-II
- immediately disconnects such busy clients before entering the second stage.
- </p>
- <p>
- You need to reload pgpool.conf if you change client_idle_limit_in_recovery.</p>
+ <dd>
+ <p>
+ pgpool does not accept new connections during the second stage. If a client
+ connects to pgpool during recovery processing, it will have to wait for the end of the recovery.
+ </p>
+ <p>
+ This parameter specifies recovery timeout in sec. If this timeout is reached,
+ pgpool cancels online recovery and accepts connections. 0 means no wait.
+ </p>
+ <p>
+ This parameter can be changed without restarting.
+ </p>
+ </dd>
+
+<dt><a name="CLIENT_IDLE_LIMIT_IN_RECOVERY"></a>client_idle_limit_in_recovery</dt>
+ <dd>
+ <p> Similar to client_idle_limit but only takes effect in the second
+ stage of recovery. A client being idle for client_idle_limit_in_recovery
+ seconds since its last query will get disconnected.
+ This is useful for preventing the pgpool recovery from being
+ disturbed by a lazy client or if the TCP/IP connection between the client
+ and pgpool is accidentally down (a cut cable for instance).
+ If set to -1, disconnect the client immediately.
+ The default value for client_idle_limit_in_recovery is 0,
+ which means the feature is turned off.
+ </p>
+ <p>
+ If your clients are very busy, pgpool-II cannot enter the second stage of
+ recovery whatever value of client_idle_limit_in_recovery you may choose.
+ In this case, you can set client_idle_limit_in_recovery to -1
+ so that pgpool-II immediately disconnects such busy clients before entering the second stage.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change client_idle_limit_in_recovery.</p>
+ </dd>
<dt><a name="LOBJ_LOCK_TABLE"></a>lobj_lock_table</dt>
-<dd>
-<p>
-This parameter specifies a table name used for large object replication control.
-If it is specified, pgpool will lock the table specified by
-lobj_lock_table and generate a large object id by looking into
-pg_largeobject system catalog and then call lo_create to create the large object.
-This procedure guarantees that pgpool will get the same large object id in all DB
-nodes in replication mode. Please note that PostgreSQL 8.0 or older does not
-have lo_create, thus this feature will not work.
-</p>
-<p>
-A call to the libpq function lo_creat() will trigger this feature. Also
-large object creation through Java API (JDBC driver), PHP
-API (pg_lo_create, or similar API in PHP library such as PDO), and this same
-API in various programming languages are known to use a similar protocol,
-and thus should work.
-</p>
-<p>
-The 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 schema lobj_lock_table is stored in, but this table
-should be writable by any user. Here is an example showing how to create such
-a table:
-</p>
-<p>
+ <dd>
+ <p>
+ This parameter specifies a table name used for large object replication control.
+ If it is specified, pgpool will lock the table specified by
+ lobj_lock_table and generate a large object id by looking into
+ pg_largeobject system catalog and then call lo_create to create the large object.
+ This procedure guarantees that pgpool will get the same large object id in all DB
+ nodes in replication mode. Please note that PostgreSQL 8.0 or older does not
+ have lo_create, thus this feature will not work.
+ </p>
+ <p>
+ A call to the libpq function lo_creat() will trigger this feature. Also
+ large object creation through Java API (JDBC driver), PHP
+ API (pg_lo_create, or similar API in PHP library such as PDO), and this same
+ API in various programming languages are known to use a similar protocol,
+ and thus should work.
+ </p>
+ <p>
+ The following large object create operation will not work:
+ <p>
+ <ul>
+ <li>lo_create of libpq</li>
+ <li>Any API of any language using lo_create</li>
+ <li>lo_import function in backend</li>
+ <li>SELECT lo_create</li>
+ </ul>
+ </p>
+ <p>
+ It does not matter what schema lobj_lock_table is stored in, but this table
+ should be writable by any user. Here is an example showing how to create such a table:
+ </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 it.
-</p>
-<p>
-If lobj_lock_table has empty string(''), the feature is disabled
-(thus large object replication will not work). The default value for
-lobj_lock_table is ''.
-</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 it.
+ </p>
+ <p>
+ If lobj_lock_table has empty string(''), the feature is disabled
+ (thus large object replication will not work). The default value for
+ lobj_lock_table is ''.
+ </p>
+ </dd>
</dl>
-<h4><p>condition for load balancing</p></h4>
+<h2 id="condition_for_load_balance">condition for load balancing</h2>
<p>
For a query to be load balanced, all the following requirements
- must be met:
+must be met:
+</p>
<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>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>it starts with "SELECT" or one of COPY TO STDOUT, EXPLAIN, EXPLAIN ANALYZE SELECT...
ignore_leading_white_space = true will ignore leading white space.
</ul>
-</p>
+
<p>
Note that you could suppress load balancing by inserting arbitrary
comments just in front of the SELECT query:
<pre>
- /*REPLICATION*/ SELECT ...
+/*REPLICATION*/ SELECT ...
</pre>
</p>
@@ -1549,29 +1614,35 @@ See also a <a href="where_to_send_queries.pdf">flow chart</a>.
<p>
<font color="red">
-Note: the JDBC driver has an autocommit option. If autocommit is false, the JDBC
-driver sends "BEGIN" and "COMMIT" by itself. So pgpool cannot do any load
+Note: the JDBC driver has an autocommit option. If autocommit is false, the JDBC
+driver sends "BEGIN" and "COMMIT" by itself. So pgpool cannot do any load
balancing. You need to call setAutoCommit(true) to enable autocommit.
</font>
</p>
-<h4><p>Failover in Replication Mode</p></h4>
+<h2 id="failover_in_replication_mode">Failover in Replication Mode</h2>
-<p> pgpool-II degenerates a dead backend and continues the
+<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>
-<h4><p>Specific errors in replication mode</p></h2>
+<h2 id="errors_in_replication_mode">Specific errors in replication mode</h2>
<p>
-In replication mode, if pgpool finds that the number of affected tuples by INSERT, UPDATE, DELETE are not same, it sends erroneous SQL statement to all DB nodes to abort the transaction if failover_if_affected_tuples_mismatch is set to false
+In replication mode, if pgpool finds that the number of affected tuples
+by INSERT, UPDATE, DELETE are not same,
+it sends erroneous SQL statement to all DB nodes to abort the transaction
+if failover_if_affected_tuples_mismatch is set to false
(degeneration occurs if it is set to true).
In this case you will see following error messages on client terminal:
+</p>
<pre>
- =# UPDATE t SET a = a + 1;
- ERROR: pgpool detected difference of the number of update tuples Possible last query was: "update t1 set i = 1;"
- HINT: check data consistency between master and other db node
+=# UPDATE t SET a = a + 1;
+ERROR: pgpool detected difference of the number of update tuples Possible last query was: "update t1 set i = 1;"
+HINT: check data consistency between master and other db node
</pre>
-You will see number of updated rows in PostgreSQL log(in this case DB node 0 has 0 updated row and DB node 1 has 1 updated row)
+<p>
+You will see number of updated rows in PostgreSQL log
+(in this case DB node 0 has 0 updated row and DB node 1 has 1 updated row)
<pre>
2010-07-22 13:23:25 LOG: pid 5490: SimpleForwardToFrontend: Number of affected tuples are: 0 1
2010-07-22 13:23:25 LOG: pid 5490: ReadyForQuery: Degenerate backends: 1
@@ -1579,29 +1650,33 @@ You will see number of updated rows in PostgreSQL log(in this case DB node 0 has
</pre>
</p>
-<h3><a name="master_slave_mode"></a>Master/Slave Mode</h3>
+<!-- ================================================================================ -->
+
+<h1><a name="master_slave_mode"></a>Master/Slave Mode</h1>
<p>This mode is used to couple pgpool-II with another master/slave
replication software (like Slony-I and Streaming replication), which is responsible
for doing the actual data replication.
-DB nodes' information (backend_hostname, backend_port,
-backend_weight, backend_flag and backend_data_directory if you need the
-online recovery fonctionality) must be set, in the same way as in the replication mode.
-In addition to that, set
-<code>master_slave_mode</code> and <code>load_balance_mode</code> to
-true. pgpool-II will then send queries that need to be replicated to the
-Master DB, and other queries will be load balanced if possible. Queries sent to
-Master DB because they cannot be balanced are of course accounted for in the
+DB nodes' information (<a href="#BACKEND_HOSTNAME">backend_hostname</a>,
+<a href="#BACKEND_PORT">backend_port</a>, <a href="#BACKEND_WEIGHT">backend_weight</a>,
+<a href="#BACKEND_FLAG">backend_flag</a> and <a href="#BACKEND_DATA_DIRECTORY">backend_data_directory</a>
+if you need the online recovery fonctionality) must be set, in the same way as in the replication mode.
+In addition to that, set <code><a href="#MASTER_SLAVE_MODE">master_slave_mode</a></code> and
+<code><a href="#LOAD_BALANCE_MODE">load_balance_mode</a></code> to true.
+</p>
+<p>
+pgpool-II will then send queries that need to be replicated to the
+Master DB, and other queries will be load balanced if possible.
+Queries sent to Master DB because they cannot be balanced are of course accounted for in the
load balancing algorithm.</p>
-<p>In master/slave mode, DDL and DML for temporary table can be executed on the
-master node only.
-SELECT can be forced to be executed on the master as well, but for this you
-need to put a /*NO LOAD BALANCE*/ comment before the SELECT statement.</p>
+<p>In master/slave mode, DDL and DML for temporary table can be executed on the master node only.
+SELECT can be forced to be executed on the master as well,
+but for this you need to put a /*NO LOAD BALANCE*/ comment 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>
+<p>In the master/slave mode, <code><a href="#REPLICATION_MODE">replication_mode</a></code> must be set
+to false, and <code><a href="#MASTER_SLAVE_MODE">master_slave_mode</a></code> to true.</p>
<p>The master/slave mode has a 'master_slave_sub mode'.
The default is 'slony' which is
@@ -1621,86 +1696,106 @@ balancing in master/slave mode.
See <a href="#white_function_list">white_function_list</a> for more details.
</p>
-<h4><a name="stream">Streaming Replication</h4>
+<!-- ================================================================================ -->
+
+<h1><a name="stream"></a>Streaming Replication</h1>
<p>
As stated above, pgpool-II can work together with Streaming Replication, which
-is available since PostgreSQL 9.0. To use it, enable 'master_slave' and
-set 'master_slave_sub_mode' to 'stream'. pgpool-II assumes that Streaming
-Replication is used with Hot Standby at present, which means that the standby
-database is open read-only.
+is available since PostgreSQL 9.0. To use it, enable '<a href="#MASTER_SLAVE_MODE">master_slave</a>' and
+set '<a href="#MASTER_SLAVE_SUB_MODE">master_slave_sub_mode</a>' to 'stream'.
+pgpool-II assumes that Streaming Replication is used with Hot Standby at present,
+which means that the standby database is open read-only.
The following directives can be used with this mode:
</p>
<p>
-<ul>
-<li>delay_threshold
-<p>
-Specifies the maximum tolerated replication delay of the standby against the
-primary server in WAL bytes.
-If the delay exceeds delay_threshold, pgpool-II does not send SELECT queries to
-the standby server anymore. Everything is sent to the primary server
-even if load balance mode is enabled, until the standby has caught-up.
-If delay_threshold is 0 or sr checking is disabled, the delay
-checking is not performed. This check is performed every 'sr_check_period'.
-The default value for delay_threshold is 0.
-You need to reload pgpool.conf if you change this directive.
-</p>
-<li>sr_check_period
-<p>
-This parameter specifies the interval between the streaming replication
-delay checks in seconds. Default is 0, which means the check is disabled.
-You need to reload pgpool.conf if you change sr_check_period.
-</p>
+<dl>
+<dt id="DELAY_THRESHOLD">delay_threshold</dt>
+ <dd>
+ <p>
+ Specifies the maximum tolerated replication delay of the standby against the
+ primary server in WAL bytes.
+ If the delay exceeds delay_threshold, pgpool-II does not send SELECT queries to
+ the standby server anymore. Everything is sent to the primary server
+ even if load balance mode is enabled, until the standby has caught-up.
+ If delay_threshold is 0 or sr checking is disabled, the delay
+ checking is not performed. This check is performed every '<a href=#SR_CHECK_PERIOD">sr_check_period</a>'.
+ The default value for delay_threshold is 0.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this directive.
+ </p>
+ </dd>
-<li>sr_check_user</dt>
-<p>
-The user name to perform streaming replication check. This user must
-exist in all the PostgreSQL backends. Otherwise, the check causes
-an error. Note that sr_check_user and sr_check_password are used
-even sr_check_period is 0. To identify the primary server,
-pgpool-II sends function call request to each backend.
-sr_check_user and sr_check_password are used for this session. You
-need to reload pgpool.conf if you change sr_check_user.
-</p>
+<dt id="SR_CHECK_PERIOD">sr_check_period</dt>
+ <dd>
+ <p>
+ This parameter specifies the interval between the streaming replication
+ delay checks in seconds. Default is 0, which means the check is disabled.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change sr_check_period.
+ </p>
+ </dd>
-<li>sr_check_password</dt>
-<p>
-The password of the user to perform streaming replication check.
-If no password is required, specify empty string('').
-You need to reload pgpool.conf if you change sr_check_password.
-</p>
+<dt id="SR_CHECK_USER">sr_check_user</dt>
+ <dd>
+ <p>
+ The user name to perform streaming replication check. This user must
+ exist in all the PostgreSQL backends.
+ Otherwise, the check causes an error.
+ Note that sr_check_user and sr_check_password are used even sr_check_period is 0.
+ To identify the primary server, pgpool-II sends function call request to each backend.
+ sr_check_user and sr_check_password are used for this session.
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change sr_check_user.
+ </p>
+ </dd>
+
+<dt id="SR_CHECK_PASSWORD">sr_check_password</dt>
+ <dd>
+ <p>
+ The password of the user to perform streaming replication check.
+ If no password is required, specify empty string('').
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change sr_check_password.
+ </p>
+ </dd>
+
+<dt id="LOG_STANDBY_DELAY">log_standby_delay</dt>
+ <dd>
+ <p>
+ Specifies how to log the replication delay.
+ If 'none' is specified, no log is written.
+ If 'always', log the delay every time health checking is performed.
+ If 'if_over_threshold' is specified, the log is written when the delay
+ exceeds delay_threshold.
+ The default value for log_standby_delay is 'none'.
+ You need to reload pgpool.conf if you change this directive.
+ </p>
+ <p>
+ You could monitor the replication delay by using the "show pool_status"
+ command as well.
+ The column name is "standby_delay#"(where '#' should be replaced by DB node id).
+ </p>
+ </dd>
+</dl>
-<li>log_standby_delay
-<p>
-Specifies how to log the replication delay.
-If 'none' is specified, no log is written.
-If 'always', log the delay every time health checking is performed.
-If 'if_over_threshold' is specified, the log is written when the delay
-exceeds delay_threshold.
-The default value for log_standby_delay is 'none'.
-You need to reload pgpool.conf if you change this directive.
-</p>
-<p>
-You could monitor the replication delay by using the "show pool_status"
-command as well.
-The column name is "standby_delay#"(where '#' should be replaced by DB node id).
-</p>
-</ul>
-</p>
-<h4>Failover with Streaming Replication</h4>
+<h2 id="failover_in_stream_mode">Failover with Streaming Replication</h2>
<p>
In master/slave mode with streaming replication, if the primary or standby
node goes down, pgpool-II can be set up to trigger a failover.
Nodes can be detached automatically without further setup.
-While doing streaming replication, the standby node checks for the presence of a
-"trigger file" and on finding it, the standby stops continuous recovery and goes
-into read-write mode. By using this, you can have the standby database take over
-when the primary goes down.
+While doing streaming replication, the standby node checks for the presence of a "trigger file"
+and on finding it, the standby stops continuous recovery and goes into read-write mode.
+By using this, you can have the standby database take over when the primary goes down.
</p>
<p>
<strong>Caution: If you plan to use multiple standby nodes, we recommend
-to set a delay_threshold to prevent any query directed to other standby
+to set a <a href="#DELAY_THRESHOLD">delay_threshold</a> to prevent any query directed to other standby
nodes from retrieving older data.
</p>
<p>
@@ -1714,15 +1809,14 @@ How to setup a failover configuration is as follows.
</p>
<p>
<ol>
- <li>Put a failover script somewhere (for example
- /usr/local/pgsql/bin) and give it execute permission.
+ <li>Put a failover script somewhere (for example /usr/local/pgsql/bin) and give it execute permission.
<pre>
$ cd /usr/loca/pgsql/bin
$ cat failover_stream.sh
#! /bin/sh
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
-#
+#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
@@ -1735,7 +1829,7 @@ trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
- exit 0;
+ exit 0;
fi
# Create the trigger file.
@@ -1745,45 +1839,50 @@ exit 0;
chmod 755 failover_stream.sh
</pre>
-<li>Set failover_commmand in pgpool.conf.
+ </li>
+
+ <li>Set failover_commmand in pgpool.conf.
<pre>
failover_command = '/usr/local/src/pgsql/9.0-beta/bin/failover_stream.sh %d %H /tmp/trigger_file0'
</pre>
+ </li>
-<li>Set recovery.conf on the standby node.
-<a href="recovery.conf.sample">A sample recovery.conf</a> can be found
- under the PostgreSQL installation directory. Its name is
- "share/recovery.conf.sample".
-Copy recovery.conf.sample as recovery.conf inside the database cluster
- directory and edit it.
+ <li>Set recovery.conf on the standby node.
+ <a href="recovery.conf.sample">A sample recovery.conf</a> can be found
+ under the PostgreSQL installation directory. Its name is
+ "share/recovery.conf.sample".
+ Copy recovery.conf.sample as recovery.conf inside the database cluster directory and edit it.
<pre>
standby_mode = 'on'
primary_conninfo = 'host=name of primary_host user=postgres'
trigger_file = '/tmp/trigger_file0'
</pre>
+ </li>
-<li>Set postgresql.conf on the primary node.
-Below is just an example. You will need to tweak it for your environment.
+ <li>Set postgresql.conf on the primary node.
+ Below is just an example. You will need to tweak it for your environment.
<pre>
wal_level = hot_standby
max_wal_senders = 1
</pre>
+ </li>
-<li>Set pg_hba.conf on the primary node.
-Below is just an example. You will need to tweak it for your environment.
+ <li>Set pg_hba.conf on the primary node.
+ Below is just an example. You will need to tweak it for your environment.
<pre>
-host replication postgres 192.168.0.10/32 trust
+host replication postgres 192.168.0.10/32 trust
</pre>
+ </li>
</ol>
<p>
Start primary and secondary PostgreSQL nodes to initiate Streaming replication.
-If the primary node goes down, the standby node will automatically start as a
-normal PostgreSQL and will be ready to accept write queries.
+If the primary node goes down, the standby node will automatically start as a normal PostgreSQL
+and will be ready to accept write queries.
</p>
-<h4>Streaming Replication</h4>
+<h2 id="load_balance_in_stream_mode">Streaming Replication</h2>
<p>
While using Streaming replication and Hot Standby, it is important to
determine which query can be sent to the primary or the standby,
@@ -1797,60 +1896,67 @@ at the query itself.
</p>
<p>
<ul>
- <li>These queries should be sent to the primary node only
- <ul>
- <li>INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE, CREATE, DROP, ALTER, COMMENT
- <li>SELECT ... FOR SHARE | UPDATE
- <li>SELECT in transaction isolation level SERIALIZABLE
- <li>LOCK command more strict than ROW EXCLUSIVE MODE
- <li>Some transactional commands:
- <ul>
- <li>BEGIN READ WRITE, START TRANSACTION READ WRITE
- <li>SET TRANSACTION READ WRITE, SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE
- <li>SET transaction_read_only = off
- </ul>
- <li>Two phase commit commands: PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED
- <li>LISTEN, UNLISTEN, NOTIFY
- <li>VACUUM
- <li>Some sequence functions (nextval and setval)
- <li>Large objects creation commands
- </ul>
- <li>These queries can be sent to both the primary node and the standby node.
-If load balancing is enabled, these types of queries can be sent to the
-standby node.
-However, if delay_threshold is set and the replication delay is higher than
-delay_threshold, queries are sent to the primary node.
- <ul>
- <li>SELECT not listed above
- <li>COPY TO
- <li>DECLARE, FETCH, CLOSE
- <li>SHOW
- </ul>
- <li>These queries are sent to both the primary node and the standby node
- <ul>
- <li>SET
- <li>DISCARD
- <li>DEALLOCATE ALL
- </ul>
+ <li>These queries should be sent to the primary node only
+ <ul>
+ <li>INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE, CREATE, DROP, ALTER, COMMENT</li>
+ <li>SELECT ... FOR SHARE | UPDATE</li>
+ <li>SELECT in transaction isolation level SERIALIZABLE</li>
+ <li>LOCK command more strict than ROW EXCLUSIVE MODE</li>
+ <li>Some transactional commands:
+ <ul>
+ <li>BEGIN READ WRITE, START TRANSACTION READ WRITE</li>
+ <li>SET TRANSACTION READ WRITE, SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE</li>
+ <li>SET transaction_read_only = off</li>
+ </ul>
+ </li>
+ <li>Two phase commit commands: PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED</li>
+ <li>LISTEN, UNLISTEN, NOTIFY</li>
+ <li>VACUUM</li>
+ <li>Some sequence functions (nextval and setval)</li>
+ <li>Large objects creation commands</li>
+ </ul>
+ </li>
+
+ <li>These queries can be sent to both the primary node and the standby node.
+ If load balancing is enabled, these types of queries can be sent to the standby node.
+ However, if delay_threshold is set and the replication delay is higher than
+ <a href="#DELAY_THRESHOLD">delay_threshold</a>, queries are sent to the primary node.
+ <ul>
+ <li>SELECT not listed above</li>
+ <li>COPY TO</li>
+ <li>DECLARE, FETCH, CLOSE</li>
+ <li>SHOW</li>
+ </ul>
+ </li>
+
+ <li>These queries are sent to both the primary node and the standby node
+ <ul>
+ <li>SET</li>
+ <li>DISCARD</li>
+ <li>DEALLOCATE ALL</li>
+ </ul>
+ </li>
</ul>
</p>
<p>
In an explicit transaction:
-<ul>
- <li>Transaction starting commands such as BEGIN are sent to the primary node.
- <li>Following SELECT and some other queries that can be sent to both
- primary or standby are executed in the transaction or on
- the standby node.
- <li><p>
-Commands which cannot be executed on the standby such as INSERT are sent
-to the primary.
-After one of these commands, even SELECTs are sent to the primary node,
-This is because these SELECTs might want to see the result of an INSERT
-immediately. This behavior continues until the transaction closes or aborts.
</p>
+
+<ul>
+ <li>Transaction starting commands such as BEGIN are sent to the primary node.
+ <li>Following SELECT and some other queries that can be sent to both
+ primary or standby are executed in the transaction or on the standby node.
+ </li>
+ <li><p>
+ Commands which cannot be executed on the standby such as INSERT are sent
+ to the primary.
+ After one of these commands, even SELECTs are sent to the primary node,
+ This is because these SELECTs might want to see the result of an INSERT immediately.
+ This behavior continues until the transaction closes or aborts.
+ </p>
+ </li>
</ul>
-</p>
<p>
In the extended protocol, it is possible to determine if the query can
@@ -1862,18 +1968,19 @@ Following bind, describe and execute will be sent to the primary node as well.
<p>
[Note: If the parse of a SELECT statement is sent to the standby node due to load
-balancing, and then a DML statement, such as an INSERT, is sent to pgpool-II,
-then the parsed SELECT will have to be executed on the primary node. Therefore,
-we re-parse the SELECT on the primary node.]
+balancing, and then a DML statement, such as an INSERT, is sent to pgpool-II,
+then the parsed SELECT will have to be executed on the primary node.
+Therefore, we re-parse the SELECT on the primary node.]
+</p>
+
<p>
Lastly, queries that pgpool-II's parser thinks to be an error are sent to the
primary node.
</p>
-<h4>Online recovery with Streaming Replication</h4>
+<h2 id="online_recovery_in_stream_mode">Online recovery with Streaming Replication</h2>
<p>
-In master/slave mode with streaming replication, online recovery can be
-performed.
+In master/slave mode with streaming replication, online recovery can be performed.
In the online recovery procedure, primary server acts as a master server and recoveres specified standby server.
Thus the recovery procedure requires that the primary server is up and running.
If the primary server goes down, and no standby server is promoted, you need to stop
@@ -1882,34 +1989,40 @@ pgpool-II and all PostgreSQL servers and recover them manually.
<p>
<ol>
- <li>Set recovery_user. Usually it's "postgres".
+ <li>Set <a href="#RECOVERY_USER">recovery_user</a>. Usually it's "postgres".
<pre>
recovery_user = 'postgres'
</pre>
-<li>Set recovery_password for recovery_user to login database.
+ </li>
+
+ <li>Set <a href="#RECOVERY_PASSWORD">recovery_password</a> for
+ <a href="#RECOVERY_USER">recovery_user</a> to login database.
<pre>
recovery_password = 't-ishii'
</pre>
-
-<li> Set recovery_1st_stage_command.
-The script for this stage should perform a base backup of the primary and
-restore it on the standby node.
-Place this script inside the primary database cluster directory and give it
-execute permission.
-Here is the sample script <a href="basebackup.sh">(basebackup.sh)</a> for
-a configuration of one primary and one standby.
-You need to setup ssh so that recovery_user can login from the primary to
-the standby without being asked for a password.
+ </li>
+
+ <li>Set <a href="#RECOVERY_1ST_STAGE_COMMAND">recovery_1st_stage_command</a>.
+ The script for this stage should perform a base backup of the primary and
+ restore it on the standby node.
+ Place this script inside the primary database cluster directory and give it
+ execute permission.
+ Here is the sample script <a href="basebackup.sh">(basebackup.sh)</a> for
+ a configuration of one primary and one standby.
+ You need to setup ssh so that recovery_user can login from the primary to
+ the standby without being asked for a password.
<pre>
recovery_1st_stage_command = 'basebackup.sh'
</pre>
-<li>Leave recovery_2nd_stage_command be empty.
+ </li>
+
+ <li>Leave <a href="#RECOVERY_2ND_STAGE_COMMAND">rrecovery_2nd_stage_command</a> be empty.
<pre>
recovery_2nd_stage_command = ''
</pre>
+ </li>
-<li>Install required C and SQL functions to perform online recovery
- into each DB nodes.
+ <li>Install required C and SQL functions to perform online recovery into each DB nodes.</li>
</p>
<pre>
@@ -1919,15 +2032,16 @@ recovery_2nd_stage_command = ''
# psql -f pgpool-recovery.sql template1
</pre>
-<li>After completing online recovery, pgpool-II will start PostgreSQL on
-the standby node.
-Install the script for this purpose on each DB nodes.
-<a href="pgpool_remote_start">Sample script</a> is included in "sample"
- directory of the source code.
-This script uses ssh. You need to allow recovery_user to login from the
-primary node to the standby node without being asked password.
+ <li>After completing online recovery, pgpool-II will start PostgreSQL on
+ the standby node.
+ Install the script for this purpose on each DB nodes.
+ <a href="pgpool_remote_start">Sample script</a> is included in "sample"
+ directory of the source code.
+ This script uses ssh. You need to allow recovery_user to login from the
+ primary node to the standby node without being asked password.
+ </li>
</ol>
-</p>
+
<p>
That's it.
@@ -1935,37 +2049,56 @@ Now you should be able to use pcp_recovery_node (as long as the standby
node stops) or push "recovery" button of pgpoolAdmin to perform online recovery.
If something goes wrong, please examin pgpool-II log, primary server log and standby server log(s).
</p>
+
<p>
For your reference, here are the steps taken in the recovery procedure.
-<ol>
-<li>Pgpool-II connects to primary server's template1 database as user = recovery_user, password = recovery_password.
-<li>Primary server executes pgpool_recovery function.
-<li>pgpool_recovery function executes recovery_1st_stage_command.
-Note that PostgreSQL executes functions with database cluster as the current directory.
-Thus recovery_1st_stage_command is executed in the database cluster directory.
-<li>Primary server executes pgpool_remote_start function.
-This function executes a script named "pgpool_remote_start" in the database cluster directory, and it executes pg_ctl command on the standby server to be recovered via ssh.
-pg_ctl will start postmaster in background.
-So we need to make sure that postmaster on the standby actually starts.
-<li>pgpool-II tries to connect to the standby PostgreSQL as user = recovery_user and password = recovery_password.
-The database to be connected is "postgres" if possible. Otherwise "template1" is used.
-pgpool-II retries for <a href="#RECOVERY_TIMEOUT">recovery_timeout</a> seconds.
-If success, go to next step.
-<li>If <a href="#FAILBACK_COMMAND">failback_command</a> is not empty, pgpool-II parent process executes the script.
-<li>After failback_command finishes, pgpool-II restart all child processes.
+<ol id="setting_for_online_recovery_in_stream_mode">
+ <li>Pgpool-II connects to primary server's template1 database
+ as user = <a href="#RECOVERY_USER">recovery_user</a>,
+ password = <a href="#RECOVERY_PASSWORD">recovery_password</a>.
+ </li>
+ <li>Primary server executes pgpool_recovery function.
+ </li>
+ <li>pgpool_recovery function executes <a href="#RECOVERY_1ST_STAGE_COMMAND">recovery_1st_stage_command</a>.
+ Note that PostgreSQL executes functions with database cluster as the current directory.
+ Thus recovery_1st_stage_command is executed in the database cluster directory.
+ </li>
+ <li>Primary server executes <a href="#pool_remote_start">pgpool_remote_start</a> function.
+ This function executes a script named "pgpool_remote_start" in the database cluster directory,
+ and it executes pg_ctl command on the standby server to be recovered via ssh.
+ pg_ctl will start postmaster in background.
+ So we need to make sure that postmaster on the standby actually starts.
+ </li>
+ <li>pgpool-II tries to connect to the standby PostgreSQL
+ as user = <a href="#RECOVERY_USER">recovery_user</a> and
+ password = <a href="#RECOVERY_PASSWORD">recovery_password</a>.
+ The database to be connected is "postgres" if possible. Otherwise "template1" is used.
+ pgpool-II retries for <a href="#RECOVERY_TIMEOUT">recovery_timeout</a> seconds.
+ If success, go to next step.
+ </li>
+ <li>If <a href="#FAILBACK_COMMAND">failback_command</a> is not empty,
+ pgpool-II parent process executes the script.
+ </li>
+ <li>After failback_command finishes, pgpool-II restart all child processes.
+ </li>
</ol>
</p>
-<h3>Parallel Mode</h3>
+<!-- ================================================================================ -->
+
+<h1 id="parallel">Parallel Mode</h1>
-<p>This mode activates parallel execution of queries. Tables can be split, and
-data distributed to each node. Moreover, the replication and the load balancing
-features can be used at the same time. In parallel mode, replication_mode and
-load_balance_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, restart pgpool-II.
+<p>This mode activates parallel execution of queries.
+Tables can be split, and data distributed to each node.
+Moreover, the replication and the load balancing features can be used at the same time.
+In parallel mode, <a href="#REPLICATION_MODE">replication_mode</a> and
+<a href="#LOAD_BALANCE_MODE">load_balance_mode</a> are set to true in pgpool.conf,
+<a href="#MASTER_SLAVE_MODE">master_slave</a> is set to false,
+and <a href="#PARALLEL_MODE">parallel_mode</a> is set to true.
+When you change this parameter, restart pgpool-II.
</p>
-<h4><p>Configuring the System DB</p></h4>
+<h2 id="system_db">Configuring the System DB</h2>
<p>To use the parallel mode, the System DB must be configured
properly. The System DB contains rules, stored in a table, to choose an
@@ -1975,80 +2108,78 @@ pgpool-II. The System DB's configuration is done in
<code>pgpool.conf</code>.</p>
<dl>
- <dt><a name="SYSTEM_DB_HOSTNAME"></a>system_db_hostname</dt>
- <dd>
- <p>The hostname where the System DB is. Specifying the
- empty string ('') means the System DB is on the same host as
- pgpool-II, and will be accessed via a UNIX domain socket.</p>
- </dd>
-
- <dt><a name="SYSTEM_DB_PORT"></a>system_db_port</dt>
- <dd>
- <p>The port number for the System DB</p>
- </dd>
-
- <dt><a name="SYSTEM_DBNAME"></a>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><a name="SYSTEM_DB_SCHEMA"></a>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><a name="SYSTEM_DB_USER"></a>system_db_user</dt>
- <dd>
- <p>The username to connect to the System DB.</p>
- </dd>
-
- <dt><a name="SYSTEM_DB_PASSWORD"></a>system_db_password</dt>
- <dd>
- <p>The password for the System DB. If no password is necessary,
- set the empty string ('').</p>
- </dd>
-
- <dt><a name="SSL_CA_CERT"></a>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 analogous to the <code>-CAfile</code> option
- of the OpenSSL <code>verify(1)</code> command.
- </p>
+<dt><a name="SYSTEM_DB_HOSTNAME"></a>system_db_hostname</dt>
+ <dd>
+ <p>The hostname where the System DB is. Specifying the
+ empty string ('') means the System DB is on the same host as
+ pgpool-II, and will be accessed via a UNIX domain socket.</p>
+ </dd>
+
+<dt><a name="SYSTEM_DB_PORT"></a>system_db_port</dt>
+ <dd>
+ <p>The port number for the System DB</p>
+ </dd>
+
+<dt><a name="SYSTEM_DBNAME"></a>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><a name="SYSTEM_DB_SCHEMA"></a>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><a name="SYSTEM_DB_USER"></a>system_db_user</dt>
+ <dd>
+ <p>The username to connect to the System DB.</p>
+ </dd>
+
+<dt><a name="SYSTEM_DB_PASSWORD"></a>system_db_password</dt>
+ <dd>
+ <p>The password for the System DB. If no password is necessary,
+ set the empty string ('').</p>
+ </dd>
+
+<dt><a name="SSL_CA_CERT"></a>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 analogous to the <code>-CAfile</code> option
+ of the OpenSSL <code>verify(1)</code> command.
+ </p>
- <p>
- The default value for this option is unset, so 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><a name="SSL_CA_CERT_DIR"></a>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 analogous to the <code>-CApath</code> option
- of the OpenSSL <code>verify(1)</code> command.
- </p>
+ <p>
+ The default value for this option is unset, so 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>
- <p>
- The default value for this option is unset, so 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>
+<dt><a name="SSL_CA_CERT_DIR"></a>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 analogous to the <code>-CApath</code> option
+ of the OpenSSL <code>verify(1)</code> command.
+ </p>
+
+ <p>
+ The default value for this option is unset, so 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>
+<h2 id="system_db_sql">Initial Configuration of the System DB</h2>
<p>First, create the database and schema specified in the
<code>pgpool.conf</code> file. A sample script can be found in
@@ -2061,7 +2192,7 @@ psql -f $prefix/share/system_db.sql pgpool
</p>
-<h4><p>Registering a Partitioning Rule</p></h4>
+<h3 id="distdef">Registering a Partitioning Rule</h3>
<p>The rules for data partitioning must be registered into the
<code>pgpool_catalog.dist_def</code> table.</p>
@@ -2080,7 +2211,7 @@ PRIMARY KEY (dbname,schema_name,table_name)
</pre>
-<h4><p>Registering a Replication Rule</p></h4>
+<h3 id="replicate_def">Registering a Replication Rule</h3>
<p>
Tables that are not distributed have to be replicated. When a query joins a
distributed table with another table, pgpool gets the replication information
@@ -2090,16 +2221,16 @@ or distributed.
<pre>
CREATE TABLE pgpool_catalog.replicate_def(
- dbname TEXT, --database name
- schema_name TEXT, --schema name
- table_name TEXT, --table 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)
+ dbname TEXT, -- database name
+ schema_name TEXT, -- schema name
+ table_name TEXT, --table 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 the pgbench tables</p></h4>
+<h2 id="example_partitioning">Example for partitioning the pgbench tables</h2>
<p>
In this example, the accounts table is partitioned, and the branches and tellers
@@ -2112,21 +2243,21 @@ the tellers table too.
</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'
+ '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)']
+ 'pgpool',
+ 'public',
+ 'branches',
+ ARRAY['bid','bbalance','filler'],
+ ARRAY['integer','integer','character(84)']
);
</pre>
@@ -2137,99 +2268,106 @@ must start from 0. Below is an example of this 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
+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>
+<!-- ================================================================================ -->
+
+<h1><a name="hba"></a>Setting up pool_hba.conf for client authentication (HBA)</h1>
<p>
- Just like the pg_hba.conf file for PostgreSQL, pgpool supports a similar
- client authentication function using a configuration file called
- "pool_hba.conf".
+Just like the pg_hba.conf file for 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.
+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 the pool_hba.conf file follows very closely PostgreSQL's
- pg_hba.conf format.
+The format of the pool_hba.conf file follows very closely PostgreSQL's
+pg_hba.conf format.
</p>
<pre>
- local DATABASE USER METHOD [OPTION]
- host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
+local DATABASE USER METHOD [OPTION]
+host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
</pre>
<p>
- See "pool_hba.conf.sample" for a detailed explanation of each field.
+See "pool_hba.conf.sample" for a detailed explanation of each field.
</p>
<p>
- Here are the limitations of pool_hba.
+Here are the limitations of pool_hba.
+</p>
+
<ul>
<li>"hostssl" connection type is not supported</li>
-<p>
+ <p>
Though "hostssl" is invalid, pgpool-II 2.3 or later supports SSL.
- See <a href="#ssl">SSL</a> for more details.
-</p>
+ See <a href="#ssl">SSL</a> for more details.
+ </p>
+
<li>"samegroup" for DATABASE field is not supported</li>
-<p>
+ <p>
Since pgpool does not know anything about users in the backend server,
the database name is simply checked against entries in the DATABASE
field of pool_hba.conf.
-</p>
+ </p>
+
<li>group names following "+" for USER field is not supported</li>
-<p>
+ <p>
This is for the same reason as for the "samegroup" described above. A
user name is simply checked against the entries in the USER field
of pool_hba.conf.
-</p>
+ </p>
+
<li>IPv6 for IP address/mask is not supported</li>
-<p>
+ <p>
pgpool currently does not support IPv6.
-</p>
+ </p>
+
<li>Only "trust", "reject", "md5" and "pam" for METHOD field are supported</li>
-<p>
+ <p>
Again, this is for the same reason as for the "samegroup" described above.
pgpool does not have access to user/password information.
-</p>
-<p>
-To use md5 authentication, you need to register your name and password in
-"pool_passwd".
-See <a href="#md5">Authentication / Access Controls</a> for more details.
+ </p>
+ <p>
+ To use md5 authentication, you need to register your name and password in "pool_passwd".
+ See <a href="#md5">Authentication / Access Controls</a> for more details.
</ul>
+
<p>
- Note that everything described in this section is about the authentication
- taking place between a client and pgpool; a client still has to go through
- the PostgreSQL's authentication process. 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 exists in the backend. pool_hba only
- cares if a match in the pool_hba.conf is found or not.
+Note that everything described in this section is about the authentication
+taking place between a client and pgpool;
+a client still has to go through the PostgreSQL's authentication process.
+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 exists 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:
+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
+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.
+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 id="memqcache">Setting Query cache method</h2>
+<!-- ================================================================================ -->
+
+<h1 id="query_cache">Setting Query cache method</h1>
<p>The Query cache can be used in all modes in pgpool-II.
The query cache allow to reuse the SELECT result to boost the performance.
-Activating it in
-pgpool.conf is done as follows:</p>
+Activating it in pgpool.conf is done as follows:</p>
<pre>
enable_query_cache = true
</pre>
@@ -2259,9 +2397,11 @@ You need to delete the cache data from the cache table or restart pgpool-II with
</strong>
</p>
-<h3>Using Shared memory or Memcached</h3>
+<!-- ================================================================================ -->
-<p>
+<h1 id="memqcache">Using Shared memory or Memcached</h1>
+
+<p id="MEMORY_CACHE_ENABLED">
To enable the memory cache functionality, set this to true (default is false)
</p>
@@ -2269,7 +2409,7 @@ To enable the memory cache functionality, set this to true (default is false)
memory_cache_enabled = true
</pre>
-<p>
+<p id="MEMQCACHE_METHOD">
Memory cache behavior can be specified by cache_param directive:<br>
Cache store method. Either "shmem"(shared memory) or "memcached". Default is shmem.
</p>
@@ -2277,28 +2417,29 @@ Cache store method. Either "shmem"(shared memory) or "memcached". Default is shm
memqcache_method = 'shmem'
</pre>
-<p>
-Memcached host name. Mandatory if memqcache_method=memcached. If this parameter is starting with "/", UNIX domain socket is assumed.
+<p id="MEMQCACHE_MEMCACHED_HOST">
+Memcached host name. Mandatory if memqcache_method=memcached.
+If this parameter is starting with "/", UNIX domain socket is assumed.
</p>
<pre>
memqcache_memcached_host = 'foo.com'
</pre>
-<p>
+<p id="MEMQCACHE_MEMCACHED_PORT">
Memcached port number. Mondatory if memqcache_method=memcached.
</p>
<pre>
memqcache_memcached_port = 11211
</pre>
-<p>
+<p id="MEMQCACHE_TOTAL_SIZE">
Total memory size in bytes for storing memory cache. Mandatory if memqcache_method=shmem.
</p>
<pre>
memqcache_total_size = 10240
</pre>
-<p>
+<p id="MEMQCACHE_MAX_NUM_CACHE">
Total number of cache entries. Mandatory if memqcache_method=shmem.
A cache entry consumes 48 bytes.
</p>
@@ -2306,14 +2447,14 @@ A cache entry consumes 48 bytes.
memqcache_max_num_cache = 100
</pre>
-<p>
+<p id="MEMQCACHE_EXPIRE">
Memory cache entry life time specified in seconds. Default is 60.
</p>
<pre>
memqcache_expire = 60
</pre>
-<p>
+<p id="MEMQCACHE_AUTO_CACHE_INVALIDATION">
If true, invalidation of query cache is triggered by corresponding
DDL/DML/DCL(and memqcache_expire). If false, it is only triggered
by memqcache_expire. True by default.
@@ -2322,7 +2463,7 @@ by memqcache_expire. True by default.
memqcache_auto_cache_invalidation = true
</pre>
-<p>
+<p id="MEMQCACHE_MAXCACHE">
Maximum SELECT result size in bytes. If exceeds this value, the result is not cached.
</p>
@@ -2330,7 +2471,7 @@ Maximum SELECT result size in bytes. If exceeds this value, the result is not ca
memqcache_maxcache = 512
</pre>
-<p>
+<p id="MEMQCACHE_CACHE_BLOCK_SIZE">
Cache block size in bytes<br>
Only meaning full when memqcache_method=shmem. Memory cache is devided into fixed size of block.<br>
If the block size is small, memory usage is efficient, but processing might be slow. On the other hand,<br>
@@ -2340,7 +2481,7 @@ if the block size is large, memory usage is not efficient but processing might b
memqcache_cache_block_size= 8192
</pre>
-<p>
+<p id="WHITE_MEMQCACHE_TABLE_LIST">
In white_memqcache_table_list specify a comma separated list of table names to be cached, and<br>
in black_memqcache_table_list specify the ones not to be cached.
</p>
@@ -2350,7 +2491,7 @@ white_memqcache_table_list = ''
black_memqcache_table_list = ''
</pre>
-<h3>memcached Installation</h3>
+<h2 id="install_memcached">memcached Installation</h2>
<p>
If you want to use memcached based query, you need to install memcached.
@@ -2363,25 +2504,25 @@ memcached's source code can be downloaded from:
<dl>
<dt>configure</dt>
-
-<dd>
-<p>
-After extracting the source tarball, execute the configure script.
-</p>
+ <dd>
+ <p>
+ After extracting the source tarball, execute the configure script.
+ </p>
<pre>
./configure
</pre>
-</dd>
+ </dd>
+
<dt>make</dt>
-<dd>
+ <dd>
<pre>
make
make install
</pre>
-</dd>
+ </dd>
</dl>
-<h3>libmemcached Installation</h3>
+<h2 id="install_libmemcached">libmemcached Installation</h2>
<p>
C client library used is libmemcached.
@@ -2395,35 +2536,35 @@ libmemcached's source code can be downloaded from:
<dl>
<dt>configure</dt>
-<dd>
-
-<p>
-After extracting the source tarball, execute the configure script.
-</p>
+ <dd>
+ <p>
+ After extracting the source tarball, execute the configure script.
+ </p>
<pre>
./configure
</pre>
-<p>
-If you want non-default values, some options can be set:
-</p>
-
-<ul>
- <li><code>--with-memcached=path</code><br/>
- The top directory where Memcached are installed.</li>
+ <p>
+ If you want non-default values, some options can be set:
+ </p>
-</ul>
+ <ul>
+ <li><code>--with-memcached=path</code><br/>
+ The top directory where Memcached are installed.</li>
+ </ul>
+ </dd>
-</dd>
<dt>make</dt>
-<dd>
+ <dd>
<pre>
make
make install
</pre>
-</dd>
+ </dd>
</dl>
+<!-- ================================================================================ -->
+
<h1>Starting/Stopping pgpool-II<a name="start"></a></h1>
<p>All the backends and the System DB (if necessary) must be started
@@ -2433,7 +2574,7 @@ before starting pgpool-II.
<pre>
pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file][-n][-D][-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>
@@ -2443,6 +2584,8 @@ pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file][-n][-D][-d]
<tr><td>-D<br/>--discard-status</td><td>Discard pgpool_status file and do not restore previous status</tr>
<tr><td>-d<br/>--debug</td><td>debug mode</tr>
</table>
+
+<p>
There are two ways to stop pgpool-II. One is using a PCP command
(described later), the other using a pgpool-II command. Below is an example of the
pgpool-II command.
@@ -2451,33 +2594,37 @@ pgpool-II command.
<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>
+ <td>the same as <code>'-m f'</code></td></tr>
+</table>
+
<p>
pgpool records backend status into the [logdir]/pgpool_status file. When pgpool
restarts, it reads this file and restores the backend status. This will prevent
a difference in data among DB nodes which might be caused by following scenario:
+</p>
<ol>
-<li>A backend suddenly stops and pgpool executes the fail over procedure
-<li>An update occurs on one of the active DBs through pgpool
-<li>The administrator decides to stop pgpool
-<li>Someone decides to restart the stopping DB without notifying the admin
-<li>The administrator restarts pgpool
+ <li>A backend suddenly stops and pgpool executes the fail over procedure</li>
+ <li>An update occurs on one of the active DBs through pgpool</li>
+ <li>The administrator decides to stop pgpool</li>
+ <li>Someone decides to restart the stopping DB without notifying the admin</li>
+ <li>The administrator restarts pgpool</li>
</ol>
-</p>
+
<p>
If for some reason, for example, the stopped DB has been synced with the
active DB by another means, pgpool_status can be removed 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.
</p>
@@ -2497,12 +2644,15 @@ Please note that some configuration items cannot be changed by
reloading. New configuration takes effect after a change for new sessions.
</p>
+<!-- ================================================================================ -->
+
<h1><a name="show-commands"></a>SHOW commands</h1>
<h2>Overview</h2>
<p>
pgpool-II provides some information via the SHOW command. SHOW is a real SQL
statement, but pgPool-II intercepts this command if it asks for specific
pgPool-II information. Available options are:
+</p>
<ul>
<li>pool_status, to get the configuration</li>
<li>pool_nodes, to get the nodes information</li>
@@ -2510,21 +2660,22 @@ pgPool-II information. Available options are:
<li>pool_pools, to get information on pgPool-II pools</li>
<li>pool_version, to get the pgPool_II release version</li>
</ul>
-</p>
+
<p>Other than "pool_status" are added since pgpool-II 3.0.
<p>
<u>Note</u> : The term 'pool' refers to the pool of PostgreSQL sessions owned by one pgpool process,
not the whole sessions owned by pgpool.
-</p>
+</p>
</p>
<p>the "pool_status" SQL statement was already available in previous releases,
but the other ones have appeared in release 3.0.</p>
-<h2>pool_status</h2>
+
+<h2 id="pool_status">pool_status</h2>
<p>"SHOW pool_status" sends back the list of configuration parameters with
their name, value, and description. Here is an excerpt of the result:
<pre>
benchs2=# show pool_status;
- item | value | description
+ item | value | description
-------------------------------+---------------------------------+------------------------------------------------------------------
listen_addresses | 127.0.0.1 | host name(s) or IP address(es) to listen to
port | 9999 | pgpool accepting port number
@@ -2533,14 +2684,15 @@ benchs2=# show pool_status;
child_life_time | 300 | if idle for this seconds, child exits
</pre>
</p>
-<h2>pool_nodes</h2>
+
+<h2 id="pool_nodes">pool_nodes</h2>
<p>"SHOW pool_nodes" sends back a list of all configured nodes. It
displays the node id, the hostname, the port, the status, the weight (only meaningful
if you use the load balancing mode) and the role. The possible values in the status column
are explained in the <a href="#pcp_node_info">pcp_node_info reference</a>.
<pre>
benchs2=# show pool_nodes;
- id | hostname | port | status | lb_weight | role
+ id | hostname | port | status | lb_weight | role
------+-------------+------+--------+-----------+---------
0 | 127.0.0.1 | 5432 | 2 | 0.5 | primary
1 | 192.168.1.7 | 5432 | 3 | 0.5 | standby
@@ -2554,47 +2706,49 @@ for connections and dealing with a connection.
<p>
It has 6 columns:
<ul>
-<li>pool_pid is the PID of the displayed pgPool-II process</li>
-<li>start_time is the timestamp of when this process was launched</li>
-<li>database is the database name of the currently active backend for this process</li>
-<li>username is the user name used in the connection of the currently active backend for this process</li>
-<li>create_time is the creation time and date of the connection</li>
-<li>pool_counter counts the number of times this pool of connections (process) has been used by clients</li>
+ <li>pool_pid is the PID of the displayed pgPool-II process</li>
+ <li>start_time is the timestamp of when this process was launched</li>
+ <li>database is the database name of the currently active backend for this process</li>
+ <li>username is the user name used in the connection of the currently active backend for this process</li>
+ <li>create_time is the creation time and date of the connection</li>
+ <li>pool_counter counts the number of times this pool of connections (process) has been used by clients</li>
</ul>
</p>
<p>This view will always return num_init_children lines.</p>
<pre>
benchs2=# show pool_processes;
- pool_pid | start_time | database | username | create_time | pool_counter
+ pool_pid | start_time | database | username | create_time | pool_counter
----------+---------------------+----------+-----------+---------------------+--------------
- 8465 | 2010-08-14 08:35:40 | | | |
+ 8465 | 2010-08-14 08:35:40 | | | |
8466 | 2010-08-14 08:35:40 | benchs | guillaume | 2010-08-14 08:35:43 | 1
- 8467 | 2010-08-14 08:35:40 | | | |
- 8468 | 2010-08-14 08:35:40 | | | |
- 8469 | 2010-08-14 08:35:40 | | | |
+ 8467 | 2010-08-14 08:35:40 | | | |
+ 8468 | 2010-08-14 08:35:40 | | | |
+ 8469 | 2010-08-14 08:35:40 | | | |
(5 lines)
</pre>
-<h2>pool_pools</h2>
+
+<h2 id="pool_pools">pool_pools</h2>
<p>"SHOW pool_pools" sends back a list of pools handled by pgPool-II.
their name, value, and description. Here is an excerpt of the result:
</p>
<p>
It has 11 columns:
<ul>
-<li>pool_pid is the PID of the pgPool-II process</li>
-<li>start_time is the time and date when this process was launched</li>
-<li>pool_id is the pool identifier (should be between 0 and max_pool-1)</li>
-<li>backend_id is the backend identifier (should be between 0 and the number of configured backends minus one)</li>
-<li>database is the database name for this process's pool id connection</li>
-<li>username is the user name for this process's pool id connection</li>
-<li>create_time is the creation time and date of this connection</li>
-<li>majorversion and minorversion are the version of the protocol used in this connection</li>
-<li>pool_counter counts the number of times this connection has been used by clients</li>
-<li>pool_backendpid is the PID of the PostgreSQL process</li>
-<li>pool_connected is a true (1) if a frontend is currently using this backend.</li>
+ <li>pool_pid is the PID of the pgPool-II process</li>
+ <li>start_time is the time and date when this process was launched</li>
+ <li>pool_id is the pool identifier (should be between 0 and max_pool-1)</li>
+ <li>backend_id is the backend identifier
+ (should be between 0 and the number of configured backends minus one)</li>
+ <li>database is the database name for this process's pool id connection</li>
+ <li>username is the user name for this process's pool id connection</li>
+ <li>create_time is the creation time and date of this connection</li>
+ <li>majorversion and minorversion are the version of the protocol used in this connection</li>
+ <li>pool_counter counts the number of times this connection has been used by clients</li>
+ <li>pool_backendpid is the PID of the PostgreSQL process</li>
+ <li>pool_connected is a true (1) if a frontend is currently using this backend.</li>
</ul>
</p>
-<p>It'll always return num_init_children*max_pool lines.</p>
+<p>It'll always return <a href="#NUM_INIT_CHILDREN">num_init_children</a> * <a href="#MAX_POOL">max_pool</a> lines.</p>
<pre>
pool_pid | start_time | pool_id | backend_id | database | username | create_time | majorversion | minorversion | pool_counter | pool_backendpid | pool_connected
----------+---------------------+---------+------------+----------+-----------+---------------------+--------------+--------------+--------------+-----------------+----------------
@@ -2621,17 +2775,21 @@ It has 11 columns:
(20 lines)
</pre>
</p>
-<h2>pool_version</h2>
+
+<h2 id="pool_version">pool_version</h2>
<p>"SHOW pool_version" displays a string containing the pgPool-II release
number. Here is an example of it:
<pre>
benchs2=# show pool_version;
- pool_version
+ pool_version
------------------------
3.0-dev (umiyameboshi)
(1 line)
</pre>
</p>
+
+<!-- ================================================================================ -->
+
<h1><a name="online-recovery"></a>Online Recovery</h1>
<h2>Overview</h2>
<p>
@@ -2665,80 +2823,80 @@ PITR mechanism.</font>
pgpool-II performs online recovery in two separated phases. There are a few
seconds or minutes when client will be waiting to connect to pgpool-II while a
recovery node synchronizes database. It follows these steps:
-
- <ol>
- <li> CHECKPOINT
- <li> First stage of online recovery
- <li> Wait until all clients have disconnected
- <li> CHECKPOINT
- <li> Second stage of online recovery
- <li> Start up postmaster (perform pgpool_remote_start)
- <li> Node attach
- </ol>
</p>
+<ol>
+ <li> CHECKPOINT</li>
+ <li> First stage of online recovery</li>
+ <li> Wait until all clients have disconnected</li>
+ <li> CHECKPOINT</li>
+ <li> Second stage of online recovery</li>
+ <li> Start up postmaster (perform <a href="#pool_remote_start">pgpool_remote_start</a>)</li>
+ <li> Node attach</li>
+</ol>
+
<p>
- The first step of data synchronization is called "first stage". Data is
- synchronized during the first stage. In the first stage, data <b>can</b> be
- updated or retrieved from any table concurrently.
+The first step of data synchronization is called "first stage". Data is
+synchronized during the first stage. In the first stage, data <b>can</b> be
+updated or retrieved from any table concurrently.
</p>
<p>
- You can specify a script executed during the first stage. pgpool-II
- passes three arguments to the script.
-
- <ol>
- <li> The database cluster path of a master node.
- <li> The hostname of a recovery target node.
- <li> The database cluster path of a recovery target node.
- </ol>
+You can specify a script executed during the first stage. pgpool-II
+passes three arguments to the script.
</p>
-<p>
- Data synchronization is finalized during what is called "second
- stage". Before entering the second stage, pgpool-II waits until all
- clients have disconnected. It blocks any new incoming connection until
- the second stage is over.
+<ol>
+ <li> The database cluster path of a master node.</li>
+ <li> The hostname of a recovery target node.</li>
+ <li> The database cluster path of a recovery target node.</li>
+</ol>
- After all connections have terminated, pgpool-II merges updated data between
- the first stage and the second stage. This is the final data
- synchronization step.
+<p>
+Data synchronization is finalized during what is called "second stage".
+Before entering the second stage, pgpool-II waits until all clients have disconnected.
+It blocks any new incoming connection until the second stage is over.
+</p>
+<p>
+After all connections have terminated, pgpool-II merges updated data between
+the first stage and the second stage. This is the final data
+synchronization step.
</p>
<p>
- <font color="red">
- Note that there is a restriction about online recovery. If pgpool-II itself
- is installed on multiple hosts, online recovery does not work
- correctly, because pgpool-II has to stop all clients during the 2nd stage of
- online recovery. If there are several pgpool hosts, only one will have
- received the online recovery command and will block connections.
+<font color="red">
+Note that there is a restriction about online recovery. If pgpool-II itself
+is installed on multiple hosts, online recovery does not work correctly,
+because pgpool-II has to stop all clients during the 2nd stage of
+online recovery. If there are several pgpool hosts, only one will have received
+the online recovery command and will block connections.
</font>
</p>
-<h2> Configuration for online recovery</h2>
+<h2 id="online_recovery_params">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>
+<ul>
+ <li><a href="#BACKEND_DATA_DIRECTORY">backend_data_directory</a></li>
+ <li><a href="#RECOVERY_USER">recovery_user</a></li>
+ <li><a href="#RECOVERY_PASSWORD">recovery_password</a></li>
+ <li><a href="#RECOVERY_1ST_STAGE_COMMAND">recovery_1st_stage_command</a></li>
+ <li><a href="#RECOVERY_2ND_STAGE_COMMAND">recovery_2nd_stage_command</a></li>
+</ul>
+
<h2><a name="installing-c-functions"></a>Installing C language functions</h2>
<p>
-You need to install the following C language function for online recovery into
+You need to install the following C language function for online recovery into
the "template1" database of all backend nodes.
Its source code is in pgpool-II tarball.
</p>
<pre>
- pgpool-II-x.x.x/sql/pgpool-recovery/
+pgpool-II-x.x.x/sql/pgpool-recovery/
</pre>
<p>
@@ -2746,8 +2904,8 @@ Change directory there and do "make install".
</p>
<pre>
- % cd pgpool-II-x.x.x/sql/pgpool-recovery/
- % make install
+% cd pgpool-II-x.x.x/sql/pgpool-recovery/
+% make install
</pre>
<p>
@@ -2755,21 +2913,21 @@ Then, install the SQL function.
</p>
<pre>
- % cd pgpool-II-x.x.x/sql/pgpool-recovery/
- % psql -f pgpool-recovery.sql template1
+% cd pgpool-II-x.x.x/sql/pgpool-recovery/
+% psql -f pgpool-recovery.sql template1
</pre>
-<h2> Recovery script deployment </h2>
+<h2 id="recovery_script">Recovery script deployment </h2>
<p>
We must deploy some data sync scripts and a remote start script into
the database cluster directory ($PGDATA). Sample script files are available in
-pgpool-II-x.x.x/sample directory.
+pgpool-II-x.x.x/sample directory.
</p>
-<h3>Online recovery by PITR</h3>
+<h3 id="recovery_pitr">Online recovery by PITR</h3>
<p>
-Here is how to do online recovery by Point In Time Recovery (PITR), which is
+Here is how to do online recovery by Point In Time Recovery (PITR), which is
available in PostgreSQL 8.2 and later versions.
Note that all PostgreSQL servers involved need to have PITR enabled.
</p>
@@ -2778,29 +2936,29 @@ Note that all PostgreSQL servers involved need to have PITR enabled.
A script to get a base backup on a master node and copy it to a
recovery target node on the first stage is needed. The script can be 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
+#! /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>
-This script puts the master database in backup mode, generates the following
-recovery.conf:
+This script puts the master database in backup mode, generates the following recovery.conf:
</p>
<pre>
restore_command = 'scp master:/data/archive_log/%f %p'
</pre>
+
+<p>
performs the backup, then puts the master database out of backup mode and copies
the backup on the chosen target node.
</p>
@@ -2810,26 +2968,28 @@ The second stage of the procedure is a script to force an XLOG file switch.
This script is named "pgpool_recovery_pitr" here.
It enforces a switch of the transaction log.
For this purpose, pg_switch_xlog could be used.
-However it may return <b>before</b> the switch is done and this might lead to
+</p>
+<p>
+However it may return <b>before</b> the switch is done and this might lead to
failure of the online recovery procedure.
-Pgpool-II provides a safer function called "pgpool_switch_xlog" which will wait
+Pgpool-II provides a safer function called "pgpool_switch_xlog" which will wait
until the transaction log switching is actually finished.
-pgpool_switch_xlog is installed during the procedure performed in the
+pgpool_switch_xlog is installed during the procedure performed in the
<a href="#installing-c-functions">Installing C functions</a> section.
</p>
+
<p>
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
-archdir=/data/archive_log # archive log directory
+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
+archdir=/data/archive_log # archive log directory
# 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|
@@ -2846,40 +3006,39 @@ psql -p $port -c "SELECT pgpool_switch_xlog('$archdir')" template1
<p>
This flushing of sequences is only useful in replication mode: in this case, sequences have to have the
-same starting point on all nodes. It's not useful in master-slave mode.
-
+same starting point on all nodes. It's not useful in master-slave mode.
+</p>
+<p>
The loop in the script forces PostgreSQL to emit current value of all sequences
-in all databases in the master node to the transaction log so that it is
+in all databases in the master node to the transaction log so that it is
propagated to the recovery target node.
-</p>
<p>
We deploy these scripts into the $PGDATA directory.
</p>
<p>
Finally, we edit pgpool.conf.
+</p>
<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>
+<h4 id="pool_remote_start">pgpool_remote_start</h4>
<p>
This script starts up the remote host's postmaster process.
pgpool-II executes it 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.
+% 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>
@@ -2888,9 +3047,8 @@ So you need to be able to connect over ssh without a password for it to work.
</p>
<p>
-If you recover with PITR, you need to deploy a base backup. PostgreSQL will
-automatically start up doing a PITR recovery. Then it will accept
-connections.
+If you recover with PITR, you need to deploy a base backup. PostgreSQL will
+automatically start up doing a PITR recovery. Then it will accept connections.
</p>
<pre>
@@ -2900,12 +3058,12 @@ DESTDIR=$2
PGCTL=/usr/local/pgsql/bin/pg_ctl
# Deploy a base backup
-ssh -T $DEST 'cd /data/; tar zxf pgsql.tar.gz' 2>/dev/null 1>/dev/null < /dev/null
+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 &
+ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &
</pre>
-<h3>Online recovery with rsync.</h3>
+<h3 id="recovery_rsync">Online recovery with rsync.</h3>
<p>
PostgreSQL 7.4 does not have PITR.
PostgreSQL 8.0 and 8.1 cannot force to switch transaction log.
@@ -2916,10 +3074,10 @@ 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.
+% 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>
@@ -2930,30 +3088,30 @@ be able to connect over ssh without a password.
<p>
Note about rsync:
<ul>
- <li>-c (or --checksum) option is required to enable reliable file tranmitting
-
- <li>-z (or --compress) option does compression before transmitting
- data. This will be great for slower connection, but it might add too much
- CPU overhead for a 100Mbit or faster connections. In this case you
- might want not to use this option.
-
- <li>rsync 3.0.5 has great speed performance
- improvements (50% faster according to a report from pgpool-general
- mailing list.
+ <li>-c (or --checksum) option is required to enable reliable file tranmitting
+ </li>
+ <li>-z (or --compress) option does compression before transmitting data.
+ This will be great for slower connection, but it might add too much
+ CPU overhead for a 100Mbit or faster connections. In this case you
+ might want not to use this option.
+ </li>
+ <li>rsync 3.0.5 has great speed performance
+ improvements (50% faster according to a report from pgpool-general mailing list.
+ </li>
</ul>
</p>
<p>
If you use pgpool_recovery, add the following lines into pgpool.conf.
+</p>
<pre>
recovery_1st_stage_command = 'pgpool_recovery'
recovery_2nd_stage_command = 'pgpool_recovery'
</pre>
-</p>
-<h2>How to perform online recovery</h2>
+<h2 id="perform_online_recovery">How to perform online recovery</h2>
<p>
In order to do online recovery, use the pcp_recovery_node command or
pgpoolAdmin.
@@ -2966,85 +3124,102 @@ pgpoolAdmin, set "_PGPOOL2_PCP_TIMEOUT " parameter to a large number in
pgmgt.conf.php.
</p>
-<h1>PostgreSQL version up using online recovery</h1>
-<h2>replication mode case</h2>
+<h2 id="minorup_w_online_recovery">PostgreSQL version up using online recovery</h2>
+<h3>replication mode case</h3>
<p>
You can update PostgreSQL on each node without stopping pgpool-II if pgpool-II operated in replication mode.
-Please note that active sessions from clients to pgpool-II will be disconnected while disconnecting and attaching DB nodes.
+Please note that active sessions from clients to pgpool-II will be disconnected
+while disconnecting and attaching DB nodes.
Also please note that you cannot do major version up in the method described below
(i.e. the version up should not require dump/restore).
</p>
<p>
<ol>
-<li><p>
-Prepare online recovery.</p>
-<li><p>Version up should perform nodes which are not master node first.
-Stop PostgreSQL on a non-master node.
-Pgpool-II will detect PostgreSQL termination and degenerate emitting logs below.
-At this point all sessions connected to pgpool-II disconnected.
+ <li><p>
+ Prepare online recovery.</p>
+ </li>
+
+ <li><p>Version up should perform nodes which are not master node first.
+ Stop PostgreSQL on a non-master node.
+ Pgpool-II will detect PostgreSQL termination and degenerate emitting logs below.
+ At this point all sessions connected to pgpool-II disconnected.
+ </p>
<pre>
2010-07-27 16:32:29 LOG: pid 10215: set 1 th backend down status
2010-07-27 16:32:29 LOG: pid 10215: starting degeneration. shutdown host localhost(5433)
2010-07-27 16:32:29 LOG: pid 10215: failover_handler: set new master node: 0
2010-07-27 16:32:29 LOG: pid 10215: failover done. shutdown host localhost(5433)
</pre>
-</p>
+ </li>
-<li><p>
-Version up PostgreSQL on the stopping node.
-You can overwrite old PostgreSQL, we recommend move old PostgreSQL somewhere so that you could recover it just in case however.
-</p>
-
-<li><p>
-If you install new PostgreSQL in different location from the old one and do not want to update your recovery script, you need to match the path by using tools including symbolic link.
-If you choose to overwrite, you can skip following steps till installation of C function step.
-You can execute online recovery immediately.
-</p>
+ <li><p>
+ Version up PostgreSQL on the stopping node.
+ You can overwrite old PostgreSQL, we recommend move old PostgreSQL somewhere
+ so that you could recover it just in case however.
+ </p>
+ </li>
+
+ <li><p>
+ If you install new PostgreSQL in different location from the old one and
+ do not want to update your recovery script,
+ you need to match the path by using tools including symbolic link.
+ If you choose to overwrite, you can skip following steps till installation of C function step.
+ You can execute online recovery immediately.
+ </p>
+ </li>
-<li><p>
-Change installation directory of old PostgreSQL.
-Installting directory of PostgreSQL is supposed to be /usr/local/pgsql in following description.
+ <li><p>
+ Change installation directory of old PostgreSQL.
+ Installting directory of PostgreSQL is supposed to be /usr/local/pgsql in following description.
+ </p>
<pre>
$ mv /usr/local/pgsql /usr/local/pgsql-old
</pre>
-</p>
+ </li>
-<li><p>
-Create a symblic link to the localtion where newer version of PostgreSQL installed.
-This allow you to continue to use command search path you currently use.
-Installting directory of newer PostgreSQL is supposed to be /usr/local/pgsql-new in following description.
+ <li><p>
+ Create a symblic link to the localtion where newer version of PostgreSQL installed.
+ This allow you to continue to use command search path you currently use.
+ Installting directory of newer PostgreSQL is supposed to be /usr/local/pgsql-new in following description.
+ </p>
<pre>
$ ln -s /usr/local/pgsql-new /usr/local/pgsql
</pre>
-</p>
+ </li>
-<li><p>
-If database directory is located under older PostgreSQL installation directory, you should create or copy so that newer PostgreSQL can access it.
-We use symbolic link in the following example.
+ <li><p>
+ If database directory is located under older PostgreSQL installation directory,
+ you should create or copy so that newer PostgreSQL can access it.
+ We use symbolic link in the following example.
+ </p>
<pre>
$ ln -s /usr/local/pgsql-old/data /usr/local/pgsql/data
</pre>
-</p>
+ </li>
-<li><p>
-Install C functions into PostgreSQL. "Installing C functions" section may help you.
-Because online recovery copies database cluster, the last step installing functions using psql is not neccessary.
-Do make install.
-</p>
+ <li><p>
+ Install C functions into PostgreSQL. "Installing C functions" section may help you.
+ Because online recovery copies database cluster, the last step installing functions
+ using psql is not neccessary.
+ Do make install.
+ </p>
+ </li>
-<li><p>
-Do online recovery. You are done with one node version up.
-To execute online recovery, you can use pcp_recovery_node or pgpoolAdmin.
-</p>
+ <li><p>
+ Do online recovery. You are done with one node version up.
+ To execute online recovery, you can use pcp_recovery_node or pgpoolAdmin.
+ </p>
+ </li>
-<li><p>
-Repeat steps above on each node. In the very last master node should be updated.
-You are done.
-</p>
+ <li><p>
+ Repeat steps above on each node. In the very last master node should be updated.
+ You are done.
+ </p>
+ </li>
</ol>
-<h2>If you are using streaming replication</h2>
+<h3>If you are using streaming replication</h3>
<p>
You can update standby PostgreSQL server without stopping pgpool-II.
</p>
@@ -3059,14 +3234,16 @@ You cannot version up primary server without stopping pgpool-II.
You need to stop pgpool-II while updating primary server.
The procedure to update primary PostgreSQL server is same as the one standby server.
The procedure to update primary PostgreSQL server is as follows:
+</p>
<ol>
-<li>Stop pgpool-II
-<li>Stop primary PostgreSQL
-<li>Update primary PostgreSQl
-<li>Start primary PostgreSQL
-<li>Start pgpool-II
+ <li>Stop pgpool-II</li>
+ <li>Stop primary PostgreSQL</li>
+ <li>Update primary PostgreSQL</li>
+ <li>Start primary PostgreSQL</li>
+ <li>Start pgpool-II</li>
</ol>
-</p>
+
+<!-- ================================================================================ -->
<h1><a name="backup"></a>Backup</h1>
<p>
@@ -3076,14 +3253,15 @@ Please note that using logical backup and PITR should be performed directory Pos
rather than via pgpool-II to avoid errors caused by load_balance_mode and replicate_select.
</p>
-<h2>replication mode and master/slave mode</h2>
+<h2 id="backup_rep_or_ms_mode">replication mode and master/slave mode</h2>
<p>
If pgpool-II is operated in replication mode or master/slave mode, take a backup on one DB nodes in the cluster.
</p>
<p>
If you are using master/slave mode and asynchronous replication
-systems(Slony-I and streaming replication) and need the latest backup, you should take a backup on the master node.
+systems(Slony-I and streaming replication) and need the latest backup,
+you should take a backup on the master node.
</p>
<p>
@@ -3094,7 +3272,7 @@ of lock conflict. Also this may affect the primary node even if you are
doing pg_dump on standby.
</p>
-<h2>Parallel mode</h2>
+<h2 id="backup_parallel">Parallel mode</h2>
<p>
If you are using parallel mode and need to take a consistent backup, you need to stop pgpool-II.
</p>
@@ -3105,20 +3283,23 @@ After finishing backup, start pgpool-II then start applications.
</p>
<p>
-To use PITR, please make sure that system times are identical on all
-nodes. Prepare archive logging and take base backup. After finishing
-backup, stop and restart applications or pgpool-II. Record the time of
-stop and start. This temporary stop will make consistent state among
-all over cluster. If you need to restore form the base backup and
-archive log, set recovery_target_time of recovery.conf in the middle of
-the start/stop time.
+To use PITR, please make sure that system times are identical on all nodes.
+Prepare archive logging and take base backup.
+After finishing backup, stop and restart applications or pgpool-II.
+Record the time of stop and start.
+This temporary stop will make consistent state among all over cluster.
+If you need to restore form the base backup and archive log,
+set recovery_target_time of recovery.conf in the middle of the start/stop time.
</p>
-<h2>Backuping of system DB</h2>
+<h2 id="backup_system_db">Backuping of system DB</h2>
<p>
You need to backup system DB if pgpool-II is operated in parallel query
mode or if you are using query cache.
Backup database specified by system_db_dbname in pgpool.conf.
+</p>
+
+<!-- ================================================================================ -->
<h1><a name="deploy"></a>Deploying pgpool-II</a></h1>
<p>
@@ -3128,72 +3309,80 @@ to make those deployments and pros and cons.
</p>
<p>
-<ul>
-<dt>Dedicated server
-<dd>
-<p>
-Pgpool-II is running on a dedicated server.
-It's simple and pgpool-II is not affected by other server softwares.
-Obvious cons is you need to by more hardware.
-Also pgpool-II can be a single point of failure with this configuration
-(you can avoid this by using pgpool-HA described somewhere below).
-</p>
-
-<dt>Deploying on a web server or application server
-<dd>
-<p>
-Deploying pgpool-II on a server where Apache, JBoss, Tomcat or other web
-server and application servers.
-Since communication between pgpool-II and web servers and application servers
-is within a local machine, socket communication can be faster than inter sever communication.
-Also if you are using multiple web serves or application servers, you can avoid
-the single point of failure problem
-(in this case you must have identical pgpool.conf on each pgpool-II instanace).
-There are seveal things you should care about in this configuration:
-</p>
-<ul>
-<li>If the communication between pgpool-II and DB servers is not stable, it is possible
-that DB node #1 is down from a point of a pgpool-II instance, while it is up from other pgpool-II instance's point of view.
-To avoid this, you can multiplex the network connection.
-<li>While executing online recovery in replication mode, you need to stop all pgpool-II instances except the one which is doing online recovery.
-Othewise DB can be running into inconsistent state.
-In master slave mode+streaming replication mode, you do not need to stop other pgpool-II instances.
-You should not execute online recovery at the same time on multiple pgpool-II instances however.
-</ul>
-</p>
+<dl>
+<dt>Dedicated server</dt>
+ <dd>
+ <p>
+ Pgpool-II is running on a dedicated server.
+ It's simple and pgpool-II is not affected by other server softwares.
+ Obvious cons is you need to by more hardware.
+ Also pgpool-II can be a single point of failure with this configuration
+ (you can avoid this by using pgpool-HA described somewhere below).
+ </p>
+ </dd>
-<dt>Running pgpool-II on DB server
-<dd>
-<p>
-Running pgpool-II on the server as PostgreSQL is running on.
-You can avoid the single point of failure problem of pgpool-II with configuration.
-And obviously you do need to buy additional dedicated server.
-Problem with this configuration is, application need to aware that which DB server they should connect to.
-To solve the problem you can use virtual IP with pgpool-HA.
-</ul>
-</p>
+<dt>Deploying on a web server or application server</dt>
+ <dd>
+ <p>
+ Deploying pgpool-II on a server where Apache, JBoss, Tomcat or other web
+ server and application servers.
+ Since communication between pgpool-II and web servers and application servers
+ is within a local machine, socket communication can be faster than inter sever communication.
+ Also if you are using multiple web serves or application servers, you can avoid
+ the single point of failure problem
+ (in this case you must have identical pgpool.conf on each pgpool-II instanace).
+ There are seveal things you should care about in this configuration:
+ </p>
+ <ul>
+ <li>If the communication between pgpool-II and DB servers is not stable, it is possible
+ that DB node #1 is down from a point of a pgpool-II instance,
+ while it is up from other pgpool-II instance's point of view.
+ To avoid this, you can multiplex the network connection.
+ </li>
+ <li>While executing online recovery in replication mode,
+ you need to stop all pgpool-II instances except the one which is doing online recovery.
+ Othewise DB can be running into inconsistent state.
+ In master slave mode+streaming replication mode, you do not need to stop other pgpool-II instances.
+ You should not execute online recovery at the same time on multiple pgpool-II instances however.
+ </li>
+ </ul>
+ </dd>
+
+<dt>Running pgpool-II on DB server</dt>
+ <dd>
+ <p>
+ Running pgpool-II on the server as PostgreSQL is running on.
+ You can avoid the single point of failure problem of pgpool-II with configuration.
+ And obviously you do need to buy additional dedicated server.
+ Problem with this configuration is, application need to aware that which DB server they should connect to.
+ To solve the problem you can use virtual IP with pgpool-HA.
+ </p>
+ </dd>
+</dl>
-<h2>About pgpool-HA</h2>
+<h2 id="pgpool_ha">About pgpool-HA</h2>
<p>
Pgpool-HA is a high availability software for pgpool-II using hearbeat.
Pgpool-HA is a sub probject of pgpool project as well as pgpool-II.
Pgpool-HA can be available from pgpool development site as an open source softeware.
</p>
+<!-- ================================================================================ -->
+
<h1><a name="troubleshooting"></a>Troubleshooting</h1>
<p>
This section describes problems and their workarounds while you are using
pgpool-II.
</p>
-<p>
-<ul>
-<dt>Health check failed
-<dd>
-<p>
-Pgpool-II's health checking feature detects DB nodes failure.
-</p>
-<p>
+<dl>
+
+<dt id="health_check_failed">Health check failed</dt>
+ <dd>
+ <p>
+ Pgpool-II's health checking feature detects DB nodes failure.
+ </p>
+
<pre>
2010-07-23 16:42:57 ERROR: pid 20031: health check failed. 1 th host foo at port 5432 is down
2010-07-23 16:42:57 LOG: pid 20031: set 1 th backend down status
@@ -3201,89 +3390,88 @@ Pgpool-II's health checking feature detects DB nodes failure.
2010-07-23 16:42:58 LOG: pid 20031: failover_handler: set new master node: 0
2010-07-23 16:42:58 LOG: pid 20031: failover done. shutdown host foo(5432)
</pre>
-</p>
-<p>
-The log shows that the DB node 1 (host foo) goes down and disconnected (shutdown)
-from pgpool, and then that DB node 0 becomes new master.
-Check DB node 1 and remove the cause of failure. After that perform an online
-recovery against DB node 1 if possible.
-</p>
-<dt>Failed to read kind from frontend
-<dd>
-<p>
+ <p>
+ The log shows that the DB node 1 (host foo) goes down and disconnected (shutdown)
+ from pgpool, and then that DB node 0 becomes new master.
+ Check DB node 1 and remove the cause of failure. After that perform an online
+ recovery against DB node 1 if possible.
+ </p>
+ </dd>
+
+<dt id="failed_to_read_kind_from_frontend">Failed to read kind from frontend</dt>
+ <dd>
<pre>
2010-07-26 18:43:24 LOG: pid 24161: ProcessFrontendResponse: failed to read kind from frontend. frontend abnormally exited
</pre>
-<p>
-This log indicates that the frontend program didn't disconnect properly from
-pgpool-II.
-The possible causes are: bugs of client applications, forced termination (kill)
-of a client application, or temporary network failure.
-This kind of events don't lead to a DB destruction or data consistency problem.
-It's only a warning about a protocol violation.
-It is advised that you check the applications and networks if the
-message keeps on occurring.
-</p>
+ <p>
+ This log indicates that the frontend program didn't disconnect properly from
+ pgpool-II.
+ The possible causes are: bugs of client applications, forced termination (kill)
+ of a client application, or temporary network failure.
+ This kind of events don't lead to a DB destruction or data consistency problem.
+ It's only a warning about a protocol violation.
+ It is advised that you check the applications and networks if the
+ message keeps on occurring.
+ </p>
+ </dd>
-<dt>Kind mismatch errors
-<dd>
-<p>
-It is possible that you get this error when pgpool-II operates in
-replication mode.
-</p>
+<dt id="kind_mismatch">Kind mismatch errors</dt>
+ <dd>
+ <p>
+ It is possible that you get this error when pgpool-II operates in replication mode.
+ </p>
<pre>
2010-07-22 14:18:32 ERROR: pid 9966: kind mismatch among backends. Possible last query was: "FETCH ALL FROM c;" kind details are: 0[T] 1[E: cursor "c" does not exist]
</pre>
-<p>
-Pgpool-II waits for responses from the DB nodes after sending an SQL command
-to them.
-This message indicates that not all DB nodes returned the same kind of response.
-You'll get the SQL statement which possibly caused the error after "Possible last query
-was:".
-Then the kind of response follows. If the response indicates an error, the
-error message from PostgreSQL is shown.
-Here you see "0[T]" displaying the DB node responses: "0[T]"(starting
-to send row description), and "1[E" indicates that DB node 1 returns an
-error with message "cursor "c" does not exist", while DB node 0 sends a row
-description.
-<p>
-Caution: You will see this error when operating in master/slave mode as
-well.
-For example, even in the master/slave mode, SET command will be basically sent
-to all DB nodes to keep all the DB nodes in the same state.
-</p>
-<p>
-Check the databases and resync them using online recovery if you find
-that they are out of sync.
-</p>
+ <p>
+ Pgpool-II waits for responses from the DB nodes after sending an SQL command to them.
+ This message indicates that not all DB nodes returned the same kind of response.
+ You'll get the SQL statement which possibly caused the error after "Possible last query was:".
+ Then the kind of response follows. If the response indicates an error, the
+ error message from PostgreSQL is shown.
+ Here you see "0[T]" displaying the DB node responses: "0[T]"
+ (starting to send row description), and "1[E" indicates that DB node 1 returns an error
+ with message "cursor "c" does not exist", while DB node 0 sends a row description.
+ </p>
+ <p>
+ Caution: You will see this error when operating in master/slave mode as well.
+ For example, even in the master/slave mode, SET command will be basically sent to all DB nodes
+ to keep all the DB nodes in the same state.
+ </p>
+ <p>
+ Check the databases and resync them using online recovery if you find
+ that they are out of sync.
+ </p>
+ </dd>
-<p>
-<dt>Pgpool detected difference of the number of inserted, updated or deleted tuples
-<dd>
-<p>
-In replication mode, pgpool-II detects a different number of
-INSERT/UPDATE/DELETE rows on affected nodes.
-</p>
-<p>
+<dt id="difference_of_number">Pgpool detected difference of the number of inserted, updated or deleted tuples
+ <dd>
+ <p>
+ In replication mode, pgpool-II detects a different number of
+ INSERT/UPDATE/DELETE rows on affected nodes.
+ </p>
<pre>
2010-07-22 11:49:28 ERROR: pid 30710: pgpool detected difference of the number of inserted, updated or deleted tuples. Possible last query was: "update t1 set i = 1;"
2010-07-22 11:49:28 LOG: pid 30710: ReadyForQuery: Degenerate backends: 1
2010-07-22 11:49:28 LOG: pid 30710: ReadyForQuery: Affected tuples are: 0 1
</pre>
-<p>
-In the example above, the returned number of updated rows by "update t1 set i = 1"
-was different among DB nodes.
-The next line indicates that DB 1 got degenerated (disconnected) as a
-consequence, and that the number of affected rows for DB node 0 was 0, while
-for DB node 1 that was 1.
-</p>
-<p>
-Stop the DB node which is suspected of having wrong data and do an online
-recovery.
-</ul>
+ <p>
+ In the example above, the returned number of updated rows by "update t1 set i = 1"
+ was different among DB nodes.
+ The next line indicates that DB 1 got degenerated (disconnected) as a consequence,
+ and that the number of affected rows for DB node 0 was 0,
+ while for DB node 1 that was 1.
+ </p>
+ <p>
+ Stop the DB node which is suspected of having wrong data and do an online recovery.
+ </p>
+ </dd>
+</dl>
+<!-- ================================================================================ -->
+
<h1>Restrictions<a name="restriction"></a></h1>
<p>
@@ -3291,10 +3479,11 @@ recovery.
<p>
<ul>
<li>If you use pg_terminate_backend() to stop a backend, this will trigger
- a failover.
- The reason why this happens is that PostgreSQL sends exactly the same
- message for a terminated backend as for a full postmaster shutdown.
- There is no workaround as of today. Please do not use this function.
+ a failover.
+ The reason why this happens is that PostgreSQL sends exactly the same
+ message for a terminated backend as for a full postmaster shutdown.
+ There is no workaround as of today. Please do not use this function.
+</li>
</ul>
<p>
@@ -3302,37 +3491,33 @@ recovery.
<p>
<ul>
- <li>In the replication mode or master/slave mode, trust, clear text
- password, and pam methods are supported. md5 in also supported since pgpool-II 3.0.
-
- md5 is supported by using "pool_passwd". Here are the steps to enable
- md5 authentication:
- <ol>
- <li>Login as the database's operating system user and type
- "pg_md5 --md5auth --username=<your_username> <your_passwd>"
- user name and md5 encrypted password are registered into
- pool_passwd.
- If pool_passwd does not exist yet, pg_md5 command will
- automatically create it for you.</li>
- <li> The format of pool_passwd is "username:encrypted_passwd".</li>
- <li>You also need to add an appropriate md5 entry to pool_hba.conf.
- See <a href="#hba">Setting up pool_hba.conf for client
- authentication (HBA)</a> for more details.</li>
- <li>Please note that the user name and password must be
- identical to those registered in PostgreSQL.</li>
- <li>After changing md5 password(in both pool_passwd and PostgreSQL of course), you need to execute "pgpool reload" or restart pgpool(if your pgoool is 3.1 or before).</li>
- </ol>
-
- </li>
- <li>In all the other modes, trust, clear text password, crypt, md5,
- and pam methods are supported.</li>
- <li>pgpool-II does not support pg_hba.conf-like access controls. If
- TCP/IP connections are 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>
+ <li>In the replication mode or master/slave mode, trust, clear text password,
+ and pam methods are supported. md5 in also supported since pgpool-II 3.0.
+ md5 is supported by using "pool_passwd". Here are the steps to enable md5 authentication:
+ <ol>
+ <li>Login as the database's operating system user and type
+ "pg_md5 --md5auth --username=<your_username> <your_passwd>"
+ user name and md5 encrypted password are registered into pool_passwd.
+ If pool_passwd does not exist yet, pg_md5 command will
+ automatically create it for you.</li>
+ <li>The format of pool_passwd is "username:encrypted_passwd".</li>
+ <li>You also need to add an appropriate md5 entry to pool_hba.conf.
+ See <a href="#hba">Setting up pool_hba.conf for client authentication (HBA)</a>
+ for more details.</li>
+ <li>Please note that the user name and password must be
+ identical to those registered in PostgreSQL.</li>
+ <li>After changing md5 password(in both pool_passwd and PostgreSQL of course),
+ you need to execute "pgpool reload" or restart pgpool(if your pgoool is 3.1 or before).</li>
+ </ol>
+ </li>
+ <li>In all the other modes, trust, clear text password, crypt, md5,
+ and pam methods are supported.</li>
+ <li>pgpool-II does not support pg_hba.conf-like access controls. If
+ TCP/IP connections are 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>
<h2>Large objects</h2>
<p>
@@ -3343,7 +3528,7 @@ enable <a href="#lobj_lock_table">lobj_lock_table</a> directive in pgpool.conf.
Large object replication using backend function lo_import is not supported, however.
</p>
-<h2>Temporary tables in master/slave mode</h2>
+<h2 id="temp_table_in_master_slave_mode">Temporary tables in master/slave mode</h2>
<p>
Creating/inserting/updating/deleting temporary tables are always executed on the
master(primary).
@@ -3354,12 +3539,12 @@ That will trigger a "not found the table" error or will find another table
having same name.
To avoid the problem, use /*NO LOAD BALANCE*/ SQL comment.
</p>
-<p>
+
<pre>
Sample SELECT which causes a problem:
SELECT 't1'::regclass::oid;
</pre>
-</p>
+
<p>
psql's \d command uses literal table names.
pgpool-II 3.0 or later checks if the SELECT includes any access to system
@@ -3367,7 +3552,7 @@ catalogs and always send these queries to the master.
Thus we avoid the problem.
</p>
-<h2>Functions, etc. In Replication Mode</h2>
+<h2 id="caution_in_replication_mode">Functions, etc. In Replication Mode</h2>
<p>There is no guarantee that any data provided using a context-dependent
mechanism (e.g. random number, transaction ID, OID, SERIAL, sequence), will be
@@ -3382,55 +3567,64 @@ be replicated correctly.
This is done by replacing those functions by constants fetched from master at
query execution time.
There are a few limitations however:
+</p>
+
<ul>
-<li>In pgpool-II 3.0 or before, the calculation of temporal data in table default value is not accurate in
-some cases. For example, the following table definition:
+ <li>In pgpool-II 3.0 or before, the calculation of temporal data in table default value is not accurate
+ in some cases. For example, the following table definition:
<pre>
CREATE TABLE rel1(
d1 date DEFAULT CURRENT_DATE + 1
)
</pre>
-is treated the same as:
+ is treated the same as:
<pre>
CREATE TABLE rel1(
d1 date DEFAULT CURRENT_DATE
)
</pre>
-<p>
-pgpool-II 3.1 or later handles these cases correctly.
-Thus the column "d1" will have tomorrow as the default value.
-However this enhancement does not apply if extended protocols(used in JDBC, PHP PDO for example) or PREPARE are used.
-</p>
-<p>
-Please note that if the column type is not a temporal one, rewriting is
-not performed. Such example:
+ <p>
+ pgpool-II 3.1 or later handles these cases correctly.
+ Thus the column "d1" will have tomorrow as the default value.
+ However this enhancement does not apply if extended protocols(used in JDBC,
+ PHP PDO for example) or PREPARE are used.
+ </p>
+ <p>
+ Please note that if the column type is not a temporal one, rewriting is
+ not performed. Such example:
+ </p>
<pre>
foo bigint default (date_part('epoch'::text,('now'::text)::timestamp(3) with time zone) * (1000)::double precision)
</pre>
-</p>
-<li>Suppose we have the following table:
+ </li>
+
+ <li>Suppose we have the following table:
<pre>
CREATE TABLE rel1(
c1 int,
c2 timestamp default now()
)
</pre>
-We can replicate
+
+ We can replicate
<pre>
-INSERT INTO rel1(c1) VALUES(1)
+INSERT INTO rel1(c1) VALUES(1)
</pre>
-since this turn into
+
+ since this turn into
<pre>
INSERT INTO rel1(c1, c2) VALUES(1, '2009-01-01 23:59:59.123456+09')
</pre>
-However,
+
+ However,
<pre>
INSERT INTO rel1(c1) SELECT 1
</pre>
-cannot to be transformed, thus cannot be properly replicated in the current implementation.
-Values will still be inserted, with no transformation at all.
+ cannot to be transformed, thus cannot be properly replicated in the current implementation.
+ Values will still be inserted, with no transformation at all.
+ </li>
</ul>
-</p>
+
<p>Tables created by <code>CREATE TEMP TABLE</code> will be deleted at the end of
the session by specifying DISCARD ALL in reset_query_list if you are using
PostgreSQL 8.3 or later.
@@ -3443,15 +3637,16 @@ session alive. To avoid this, you must explicitly drop the
temporary tables by issuing <code>DROP TABLE</code>, or use <code>CREATE TEMP
TABLE ... ON COMMIT DROP</code> inside the transaction block.</p>
-<h2>Queries</h2>
+<h2 id="ng_queries">Queries</h2>
<p>Here are the queries which cannot be processed by pgpool-II</p>
-<h3>INSERT (for parallel mode)</h3>
+<h3 id="ng_insert">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,
+</p>
<pre>
INSERT INTO t(x) VALUES (DEFAULT);
@@ -3469,7 +3664,7 @@ key. <code>SELECT INTO</code> and <code>INSERT INTO ... SELECT</code>
are also not supported.
</p>
-<h3>UPDATE (for parallel mode)</h3>
+<h3 id="ng_update">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
@@ -3485,7 +3680,7 @@ UPDATE branches set bid = 100 where bid = (select max(bid) from beances);
</pre>
</p>
-<h3>SELECT ... FOR UPDATE (for parallel mode)</h3>
+<h3 id="ng_select_for_update">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. For example:
@@ -3494,18 +3689,18 @@ SELECT * FROM branches where bid = (select max(bid) from beances) FOR UPDATE;
</pre>
</p>
-<h3>COPY (for parallel mode)</h3>
+<h3 id="ng_copy">COPY (for parallel mode)</h3>
<p><code>COPY BINARY</code> is not supported. Copying from files is
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>
+<h3 id="ng_create_table">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>
+<h3 id="ng_transaction">Transaction (for parallel mode)</h3>
<p><code>SELECT</code> statements executed inside a transaction block
will be executed in a separate transaction. Here is an example:
@@ -3513,11 +3708,11 @@ will be executed in a 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
+SELECT * FROM t ORDER BY a; <-- INSERT above is not visible from this SELECT statement
END;
</pre>
-<h3>Views / Rules (for parallel mode)</h3>
+<h3 id="ng_view">Views / Rules (for parallel mode)</h3>
<p>
The same definition will be created on all the backends for views and rules.
@@ -3536,7 +3731,7 @@ a dist_def_func will have to be registered too. These are used when an insert is
performed on the view.
</p>
-<h3>Functions / Triggers (for parallel mode)</h3>
+<h3 id="ng_fuction">Functions / Triggers (for parallel mode)</h3>
<p>The same definition will be created on all the backends for
functions. Joining across the nodes, and accessing data on the other nodes cannot
@@ -3548,59 +3743,60 @@ be performed inside the functions.</p>
supported. The simple query protocol must be used. This means you can't use
prepared statements.</p>
-<h3>Natural Join (for parallel mode)</h3>
+<h3 id="ng_natural_join">Natural Join (for parallel mode)</h3>
<p>The Natural Join is not supported.
"ON join condition" or "USING (join_column)" must be used.</p>
-<h3>USING CLAUSE (for parallel mode)</h3>
+<h3 id="ng_extended_protocol">USING CLAUSE (for parallel mode)</h3>
<p>The USING CLAUSE is converted to an ON CLAUSE by the query rewrite process.
Therefore, when "*" is used at target list, the joined column(s) appear twice.</p>
<p>
Here is an example:
</p>
-<p><pre>
- =# SELECT * FROM t1 JOIN t2 USING(id);
- id | t | t
- ----+-----+-------
- 1 | 1st | first
- (1 row)
-</pre></p>
+<pre>
+=# SELECT * FROM t1 JOIN t2 USING(id);
+ id | t | t
+----+-----+-------
+ 1 | 1st | first
+(1 row)
+</pre>
<p>
In the rewrite process "USING" is translated into "ON". So the
effective result is as follows:
-</p><p><pre>
- =# SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
- id | t | id | t
- ----+-----+----+-------
- 1 | 1st | 1 | first
- (1 row)
-</pre>
</p>
+<pre>
+=# SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
+ id | t | id | t
+----+-----+----+-------
+ 1 | 1st | 1 | first
+(1 row)
+</pre>
+
<p>
Notice that column "t" is duplicated.
</p>
-<h3>Multi-byte Characters (for all modes)</h3>
+<h3 id="ng_multibyte_chars">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>
+<h3 id="ng_multi_statement">Multi-statement Query (for all modes)</h3>
<p>
pgpool-II cannot process multi-statement queries.
</p>
-<h3>Deadlocks (for parallel mode)</h3>
+<h3 id="ng_dead_lock">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
+ tid <= 10 --> node 0
+ tid >= 10 --> node 1
A) BEGIN;
B) BEGIN;
@@ -3622,7 +3818,7 @@ process if a deadlock is detected.
pool_read_kind: kind does not match between master(84) slot[1] (69)
</pre>
-<h3>Schemas (for parallel mode)</h3>
+<h3 id="ng_schema">Schemas (for parallel mode)</h3>
<p>Objects in a schema other than public must be fully qualified like:
</p>
@@ -3639,7 +3835,8 @@ set search_path = xxx
<p>
and the schema name is omitted in a query.
</p>
-<h3>table name - column name(for parallel mode)</h3>
+
+<h3 id="ng_relname">table name - column name(for parallel mode)</h3>
<p>
Limitation object:Parallel mode
</p>
@@ -3648,8 +3845,7 @@ A table or a column name cannot start by pool_.
When rewriting queries, these names are used by internal processing.
</p>
-</p>
-<h2>System DB</h2>
+<h2 id="caution_system_db">System DB</h2>
<h3>Partitioning Rules</h3>
@@ -3664,17 +3860,19 @@ partitioning rule. Conditions like 'x or y' are not supported.</p>
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>
+<h2 id="caution_query_cache">Query Cache</h2>
<p>Currently, the 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>
+<h3 id="pcp_command">PCP Command List</h3>
<p>PCP commands are UNIX commands which manipulate pgpool-II via the network.
@@ -3692,7 +3890,7 @@ invalidate old query cache automatically when the data is updated.</p>
</p>
-<h2>Common Command-line Arguments</h2>
+<h2 id="pcp_comand_args">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
@@ -3721,7 +3919,7 @@ not need to be in md5 format when passing it to the PCP commands.</p>
<p>All PCP commands display the results to the standard output.</p>
-<h3>pcp_node_count</h3>
+<h3 id="pcp_node_count">pcp_node_count</h3>
<pre>
Format:
@@ -3734,7 +3932,7 @@ not distinguish between nodes status, ie attached/detached. ALL nodes are counte
</p>
-<h3><a name="pcp_node_info"/>pcp_node_info</h3>
+<h3><a name="pcp_node_info"/></a>pcp_node_info</h3>
<pre>
Format:
@@ -3780,7 +3978,7 @@ Weight : 0.5
<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>
+<h3 id="pcp_proc_count">pcp_proc_count</h3>
<p>
<pre>
Format:
@@ -3790,7 +3988,8 @@ pcp_proc_count _timeout_ _host_ _port_ _userid_ _passwd_
Displays the list of pgpool-II children process IDs. If there is more than
one process, IDs will be delimited by a white space.
</p>
-<h3>pcp_proc_info</h3>
+
+<h3 id="pcp_proc_info">pcp_proc_info</h3>
<p>
<pre>
Format:
@@ -3852,7 +4051,7 @@ Connected : 1
<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>
+<h3 id="pcp_systemdb_info">pcp_systemdb_info</h3>
<p>
<pre>
Format:
@@ -3899,7 +4098,7 @@ cache is disabled), it results in error with exit status 12, and
BackendError will be displayed.</p>
-<h3>pcp_detach_node</h3>
+<h3 id="pcp_detach_node">pcp_detach_node</h3>
<pre>
Format:
pcp_detach_node [-g] _timeout_ _host_ _port_ _userid_ _passwd_ _nodeid_
@@ -3911,7 +4110,7 @@ client_idle_limit_in_recovery is -1 or recovery_timeout is expired).
</p>
-<h3>pcp_attach_node</h3>
+<h3 id="pcp_attach_node">pcp_attach_node</h3>
<p>
<pre>
Format:
@@ -3921,7 +4120,7 @@ Attaches the given node to pgpool-II.
</pre>
</p>
-<h3>pcp_promote_node</h3>
+<h3 id="pcp_promote_node">pcp_promote_node</h3>
<p>
<pre>
Format:
@@ -3945,9 +4144,9 @@ modes are as follows:
</p>
<pre>
-s - smart mode
-f - fast mode
-i - immediate mode
+s - smart mode
+f - fast mode
+i - immediate mode
</pre>
<p>
If the pgpool-II process does not exist, it results in error with exit
@@ -3959,7 +4158,7 @@ mode. pgpool-II terminates all the processes whether there are clients
connected to the backends or not.</p>
-<h2>Exit Status</h2>
+<h2 id="exit_status">Exit Status</h2>
<p>PCP commands exits with status 0 when everything goes well. If any
error had occurred, it will exit with the following error status.
@@ -3981,20 +4180,22 @@ AUTHERR 13 Authorization failure
</pre>
</p>
+<!-- ================================================================================ -->
+
<h1>Inside information<a name="internal"></a></h1>
<p>
pgpool-II version 2.0.x brings extensive modifications, compared with the version 1.x
Please note that what follows doesn't apply to version 1.x.
</p>
-<h2>Parallel execution engine</h2>
+<h2 id="parallel_execute_engine">Parallel execution engine</h2>
<p>
The parallel execution engine is built into pgpool-II.
This engine performs the same Query on each node, and drives the engine that
transmits the result to the front end, depending on the nodes' answers.
</p>
-<h2>Query Rewriting</h2>
+<h2 id="rewrite_query">Query Rewriting</h2>
<p>
This explains the Query rewriting that pgpool-II does in parallel mode.
</p>
@@ -4009,20 +4210,27 @@ processing:
<p>
What follows explains these two processing steps:
</p>
-<h3>Analysis of Query</h3>
-<h4><p>Introduction</p></h4>
+
+<h3 id="analyze_query">Analysis of Query</h3>
+<h4>Introduction</h4>
+<p>
+The retrieval query submitted by the client goes through the SQL parser.
+It is then analyzed using information stored in the system DB. Execution status of each part
+of this query is updated using this information.
+</p>
<p>
-The retrieval query submitted by the client goes through the SQL parser. It is then analyzed
-using information stored in the system DB. Execution status of each part
-of this query is updated using this information. This execution status stores
-where this node can be treated. For instance, if a table's data is distributed
-on several nodes (as declared in the catalog's pgpool_catalog.dist_def table),
+This execution status stores where this node can be treated.
+For instance, if a table's data is distributed on several nodes
+(as declared in the catalog's pgpool_catalog.dist_def table),
it has to be retrieved from all nodes. On the other hand, data from a table
registered in pgpool_catalog.replicate_def is replicated, and can therefore
-be retrieved from any node. These states are 'P' when data has to be processed by
-all nodes, 'L' when it should be processed by one node. The 'S' status is a special
-case: it means that there is another step to be performed on the data after
-retrieving it from all nodes. For example, sorting data coming from a table
+be retrieved from any node.
+</p>
+<p>
+These states are 'P' when data has to be processed by all nodes,
+'L' when it should be processed by one node.
+The 'S' status is a special case: it means that there is another step to be performed
+on the data after retrieving it from all nodes. For example, sorting data coming from a table
registered in the pgpool_catalog.dist_def table.
</p>
@@ -4033,30 +4241,30 @@ final status of the main select.
</p>
<ol>
- <li>Are UNION, EXTRACT, and INTERSECT used or not?
- <li>What is the Execution status of FROM clause ?
- <li>Change the execution status by TARGETLIST
- <li>Change in execution status according 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>Changes into the LIMIT OFFSET predicate in the execution status.
- <li>Acquisition of the final execution status of SELECT
+ <li>Are UNION, EXTRACT, and INTERSECT used or not?</li>
+ <li>What is the Execution status of FROM clause ?</li>
+ <li>Change the execution status by TARGETLIST</li>
+ <li>Change in execution status according to WHERE clause</li>
+ <li>Change in execution status according to GROUP BY clause</li>
+ <li>Change in execution status according to HAVING clause</li>
+ <li>Change in execution status according to ORDER BY clause</li>
+ <li>Changes into the LIMIT OFFSET predicate in the execution status.</li>
+ <li>Acquisition of the final execution status of SELECT</li>
</ol>
<p>
The relation between the final execution status of SELECT and the processing
-place is as follows.
+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>Query issued on either node. </td></tr>
- <tr><td align=center>P</td><td>Returns data to the client by running the
- same query on all nodes and using the parallel execution engine.</td></tr>
- <tr><td align=center>S</td><td>After processing using the system DB, data
- is returned to the client. </td></tr>
+<tr><td>Execution status</td><td>Processed place</td></tr>
+<tr><td align=center>L</td><td>Query issued on either node. </td></tr>
+<tr><td align=center>P</td><td>Returns data to the client by running the
+same query on all nodes and using the parallel execution engine.</td></tr>
+<tr><td align=center>S</td><td>After processing using the system DB, data
+is returned to the client. </td></tr>
</table>
</p>
@@ -4076,7 +4284,7 @@ Next, let's explain how the execution status changes concretely.
Let's start with an example, to explain the FROM status.
</p>
-<h4><p>Execution status of FROM clause</p></h4>
+<h4>Execution status of FROM clause</h4>
<p>
This is a retrieval Query (SELECT). The data set and its status (P,L and S) is
defined according to the FROM clause
@@ -4090,8 +4298,12 @@ statuses, as show in the following table.
<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>Other</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>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>Other</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>
@@ -4099,48 +4311,49 @@ statuses, as show in the following table.
<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 is in the P status.
L1-table and L2-table are in the L status.
+</p>
<pre>
SELECT * FROM P1-table,L1-table,L2-table;
</pre>
-
+<p>
P1-table (left) and L1-table (right) are joined, and according to the table they
get the P status. With this P status, they are joined with the L2-table, in the L
status, which is now in the P status too.
</p>
-<h4><p>Changes in execution status because of TARGETLIST and WHERE clause</p></h4>
+<h4>Changes in execution status because of TARGETLIST and WHERE clause</h4>
<p>
In a basic Query, the execution status is the one from the FROM clause.
However, if there is a TARGETLIST, the execution status of the WHERE clause can
change in the following cases.
</p>
<ol>
- <li>When there is a subquery
- <li>When there is an aggregate function or DISTINCT in TARGETLIST marked as 'P'
- <li>When a column that does not exist in a table (data set)
- in the FROM clause is used
+ <li>When there is a subquery</li>
+ <li>When there is an aggregate function or DISTINCT in TARGETLIST marked as 'P'</li>
+ <li>When a column that does not exist in a table (data set)
+ in the FROM clause is used</li>
</ol>
<p>
-In these cases, the final execution status of the subquery, the execution status of
-TARGETLIST and the WHERE clause get the S status if the initial status was
-P or S.
+In these cases, the final execution status of the subquery, the execution status of TARGETLIST
+and the WHERE clause get the S status if the initial status was P or S.
+</p>
<p>
-In the following example, when the table used by the subquery is P, the final
-execution status of the subquery gets the P status. Therefore, The execution
-status of the WHERE clause gets the S status, whatever the execution status of
-L1 is, and this query is run in the system DB.
+In the following example, when the table used by the subquery is P,
+the final execution status of the subquery gets the P status.
+Therefore, The execution status of the WHERE clause gets the S status,
+whatever the execution status of L1 is, and this query is run in the system DB.
+</p>
<pre>
- SELECT * FROM L1-table where L1-table.column IN (SELECT * FROM P1-table);
+SELECT * FROM L1-table where L1-table.column IN (SELECT * FROM P1-table);
</pre>
<p>
The FROM clause changes to the S status when there is an aggregate function in
@@ -4153,13 +4366,14 @@ A column that does not exist in a table can be used in a query. For instance, in
the following correlated subquery:
</p>
<pre>
- SELECT * FROM L1-table WHEREﾂ�1-table.col1 IN (SELECT * FROM P1-table WHERE P1-table.col = L1-table.col1);
+SELECT * FROM L1-table WHERE L1-table.col1 IN (SELECT * FROM P1-table WHERE P1-table.col = L1-table.col1);
</pre>
<p>
This subquery refers to L1-table.col1, from the L1-table. The execution status
-of the WHERE clause of the subquery is 'S'.
+of the WHERE clause of the subquery is 'S'.
</p>
-<h4><p>Change in execution status because of GROUP BY, HAVING, ORDER BY and LIMIT/OFFSET</p></h4>
+
+<h4>Change in execution status because of GROUP BY, HAVING, ORDER BY and LIMIT/OFFSET</h4>
<p>
The execution status of the WHERE clause is changed to 'S' when there is any GROUP
@@ -4170,15 +4384,15 @@ used when there is no HAVING clause. The ORDER BY clause and the LIMIT/OFFSET
predicate are also similar in this behavior.
</p>
-<h4><p>When UNION, EXTRACT, and INTERSECT are used</p></h4>
+<h4>When UNION, EXTRACT, and INTERSECT are used</h4>
<p>
UNION, EXTRACT, and INTERSECT queries' status depends on the final execution
status of both the left and right SELECT statements. If both statements are L, the
combined statement is L. If both statements are P, and the query is a UNION ALL
-the combined statement is P. For any other combination, the resulting status is
-S.
+the combined statement is P. For any other combination, the resulting status is S.
</p>
-<h4><p>Acquisition of the final execution status of SELECT</p></h4>
+
+<h4>Acquisition of the final execution status of SELECT</h4>
<p>
If everything in the SELECT has a status of L, then the final execution status
is L. The same rule applies for P. For any other combination, the final status is S.
@@ -4187,17 +4401,16 @@ true and sent to the MASTER if false. For P, parallel processing is done with
the parallel execution engine. For S, the query rewriting presented below is done.
</p>
-<h3>Query rewriting</h3>
+<h3 id="step_rewrite_query">Query rewriting</h3>
<p>
The Query is rewritten by using the execution status acquired while analyzing
the query.
Here is an example. The P1-table has the P status, the L1-table has the L status.
-
</p>
<pre>
-SELECT P1-table.col, L1-table.col
-FROM P1-table,L1-table
-where P1-table.col = L1-table.col
+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>
@@ -4209,8 +4422,8 @@ The query is rewritten into something like this :
<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;
+ 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>
dblink transmits the query to pgpool-II here.
@@ -4235,18 +4448,18 @@ status. Because the subquery is in the P status, the query itself is in the S st
The rewriting is, as a consequence, performed 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))
- ) ;
+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>
pool_loadbalance is a function responsible for dispatching queries to either node.
@@ -4267,27 +4480,28 @@ So the rewriting is done as follows.
<pre>
select count(*) from P1-table;
- -> rewrite
+ -> 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);
+ AS pool_$1g (pool_c$1 bigint);
</pre>
<p>
Query rewriting like above is done in these conditions.
</p>
<ol>
- <li>The FROM clause is in the P status.
- <li>The column specified in the aggregate function (only count, sum, min,
- max, and avg) and GROUP BY is used in the target list.
- <li>The WHERE clause is in the P status.
- <li>Only the columns defined by the aggregate function (only count, sum,
- min, max, and avg), used in the HAVING clause and the FROM clause, and
- the column specified for GROUP BY are used.
+ <li>The FROM clause is in the P status.</li>
+ <li>The column specified in the aggregate function (only count, sum, min,
+ max, and avg) and GROUP BY is used in the target list.</li>
+ <li>The WHERE clause is in the P status.</li>
+ <li>Only the columns defined by the aggregate function (only count, sum,
+ min, max, and avg), used in the HAVING clause and the FROM clause, and
+ the column specified for GROUP BY are used.</li>
</ol>
-<h3>Notes on the parallel mode</h3>
+
+<h3 id="caution_in_parallel_mode">Notes on the parallel mode</h3>
<p>
The column names and types are needed when a query is analyzed in parallel mode.
Therefore, when an expression or a function is used in the TARGETLIST of
@@ -4297,12 +4511,12 @@ chosen as a default. For count(), bigint type is assumed, and for sum(),
numeric. For min()/max(), if the argument is a date type, returned datatype is date,
else, it is assumed numeric. avg() is processed as sum()/count() (sum divided by count).
-<h3>About the performance of a parallel mode</h3>
+<h3 id="performance_parallel_mode">About the performance of a parallel mode</h3>
<p>Here is a rough estimate of the query performance versus execution status:
</p>
<p>
<table border>
- <tr><td>Execution status</td><td>Performance</td></tr>
+ <tr><td>Execution status</td><td>Performance</td></tr>
<tr><td align = center>L</td><td>There is no performance deterioration with a
single node, excluding the overhead of pgpool-II, because there is no parallel
querying done at all.</td></tr>
@@ -4316,15 +4530,19 @@ parallel fashion, they are fast.</td></tr>
</table>
</p>
+<!-- ================================================================================ -->
+
<h1>Tutorial</h1>
<p>
<a href="tutorial-en.html">A tutorial for pgpool-II</a> is available.
</p>
+</div>
+
<div class="copyright">
<hr>
<copyright>
-Copyright © 2003 – 2011 pgpool Global Development Group
+Copyright © 2003 – 2012 pgpool Global Development Group
</copyright>
</div>
</body>
[parent: 63a4ee7ca92b]