<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html">
<link rel="stylesheet" href="./pgpool.css" type="text/css">
<title>pgpool-II user manual</title>
</head>
<!-- hhmts start -->
Last modified: Mon Aug 24 18:19:17 JST 2015
<!-- hhmts end -->
<body bgcolor="#ffffff">
<div id="top" class="header_text">Welcome to pgpool -II page</div>
<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="#license">License</a></li>
<li><a href="#platform">Supported Platforms</a></li>
<li><a href="#install">pgpool-II Installation</a></li>
<li><a href="#config">Configuring pgpool-II</a></li>
<li><a href="#common">Configuring common part</a></li>
<li><a href="#connection_pool_mode">Connection pool mode</a></li>
<li><a href="#replication_mode">Replication mode</a></li>
<li><a href="#master_slave_mode">Master slave mode</a></li>
<li><a href="#stream">Streaming Replication</a></li>
<li><a href="#hba">Client authentication</a></li>
<li><a href="#query_cache">Setting Query cache method</a></li>
<li><a href="#memqcache">In memory query Cache</a></li>
<li><a href="#start">Starting/Stopping pgpool-II</a></li>
<li><a href="#reload">Reloading pgpool-II configuration files</a></li>
<li><a href="#show-commands">SHOW commands</a></li>
<li><a href="#online-recovery">Online recovery</a></li>
<li><a href="#backup">Backup</a></li>
<li><a href="#deploy">Deploying pgpool-II</a></li>
<li><a href="#watchdog">Watchdog</a></li>
<li><a href="#pcp_command">PCP commands</a></li>
<li><a href="#troubleshooting">Troubleshooting</a></li>
<li><a href="#restriction">Restrictions</a></li>
<li><a href="#internal">internal</a></li>
</ul>
</div>
<br />
<div class="header_small" align="center">
[<a href="pgpool-ja.html">Japanese page</a>]
</div>
</div>
<div id="manual">
<!-- ================================================================================ -->
<h1 style="margin-top: 20px">What is pgpool-II?<a name="whatis"></a></h1>
<p> pgpool-II is a middle ware that sits between PostgreSQL servers
and a PostgreSQL database client. It provides the following features:</p>
<p>
<ul>
<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. user name, 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 real time 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 replication 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>
</ul>
</p>
<p>pgpool-II speaks PostgreSQL's backend and frontend protocol, and
relays messages between a backend and a frontend. Therefore, a database application
(frontend) thinks that pgpool-II is the actual PostgreSQL server, and
the server (backend) sees pgpool-II as one of its clients. Because
pgpool-II is transparent to both the server and the client, an
existing database application can be used with pgpool-II almost
without a change to its source code.</p>
<p>
<strong>There are some restrictions to using SQL via pgpool-II.
See <a href="#restriction">Restrictions</a> for more details.</strong>
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>License<a name="license"></a></h1>
<p>Copyright (c) 2003-2013 PgPool Global Development Group</p>
<p><br></p>
<p>
Permission to use, copy, modify, and distribute this software and
its documentation for any purpose and without fee is hereby
granted, provided that the above copyright notice appear in all
copies and that both that copyright notice and this permission
notice appear in supporting documentation, and that the name of the
author not be used in advertising or publicity pertaining to
distribution of the software without specific, written prior
permission. The author makes no representations about the
suitability of this software for any purpose. It is provided "as
is" without express or implied warranty.
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>Supported Platforms<a name="platform"></a></h1>
<p>pgpool-II works on Linux, Solaris, FreeBSD, and most of the
UNIX-like architectures. Windows is not supported. Supported
PostgreSQL server's versions are 6.4 and higher. </p>
<p>If you are using PostgreSQL 7.3 or older, some features of pgpool-II
won't be available. But you shouldn't use such an old release anyway.</p>
<p>You must also make sure that all of your PostgreSQL servers are using the same
major PostgreSQL version.
In addition to this, we do not recommend mixing different PostgreSQL installation with different build options: including supporting SSL or not, to use --disable-integer-datetimes or not, different block size.
These might affect part of functionality of pgpool-II.
The difference of PostgreSQL minor versions is not usually a problem. However we do not test every occurrence of minor versions and we recommend to use exact same minor version of PostgreSQL.
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>pgpool-II Installation<a name="install"></a></h1>
<p>
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://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
make. Also, pgpool-II links with the libpq library, so the libpq library and its development
headers must be installed on the machine used to build pgpool-II. Additionally
the OpenSSL library and its development headers must be present in order to
enable OpenSSL support in pgpool-II.</p>
<h2 id="install_pgpool">Installing pgpool-II</h2>
<dl>
<dt>configure</dt>
<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>
<table border>
<tr><th class="nodec"><code>--prefix=path</code></th>
<td>pgpool-II binaries and docs will be installed in this
directory. Default value is <code>/usr/local</code></td></tr>
<tr><th class="nodec"><code>--with-pgsql=path</code></th>
<td>The top directory where PostgreSQL's client libraries are
installed. Default value is provided by <code>pg_config</code></td></tr>
<tr><th class="nodec"><code>--with-openssl</code></th>
<td>pgpool-II binaries will be built with OpenSSL support. OpenSSL support
is disabled by default. <span class="version">V2.3 -</span></td></tr>
<tr><th class="nodec"><code>--enable-sequence-lock</code></th>
<td>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.
<span class="version">V3.1 -</span></td></tr>
<tr><th class="nodec"><code>--enable-table-lock</code></th>
<td>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.
<span class="version">V3.1 -</span></td></tr>
<tr><th class="nodec"><code>--with-memcached=path</code></th>
<td>pgpool-II binaries will use <a href="#memcached_params">memcached</a>
for <a href="#memqcache">in memory query cache</a>.
You have to install <a href="http://libmemcached.org/libMemcached.html">libmemcached</a>.
<span class="version">V3.2 -</span></td>
</tr>
</table>
</dd>
<dt>make</dt>
<dd>
<pre>
make
make install
</pre>
<p>
will install pgpool-II. (If you use Solaris or FreeBSD, replace make with gmake)
</p>
</dd>
</dl>
<h2 id="install_functions">Installing functions</h2>
<h3>Installing pgpool_regclass (recommended) <span class="version">V3.0 -</span></h3>
<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
</pre>
<p>After this:<p>
<pre>
$ psql -f pgpool-regclass.sql template1
</pre>
<p>or<p>
<pre>
$ psql template1
=# CREATE EXTENSION pgpool_regclass;
</pre>
</p>
<p>
Executing pgpool-regclass.sql or <code>CREATE EXTENSION</code> 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 "<code>psql -f pgpool-regclass.sql template1</code>" or <code>CREATE EXTENSION</code>,
as this template database will be cloned to create new databases.
</p>
<h3>Creating insert_lock table <span class="version">V3.0 -</span></h3>
<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>
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
"<code>psql -f insert_lock.sql template1</code>", as this template database will be
cloned to create new databases.
</p>
<h3>Installing pgpool_recovery</h3>
<p>If you use <a href="#online_recovery_in_stream_mode">online recovery</a>, some functions are needed:
pgpool_recovery, pgpool_remote_start, pgpool_switch_xlog.</p>
<p>And, pgpoolAdmin of the tool to control pgpool-II can stop, restart and reload the backend PostgreSQL nodes,
and it needs the function named pgpool_pgctl.</p>
<p>
Also pgpoolAdmin needs function pgpool_pgctl to stop/restart/reload config PostgreSQL.
</p>
<p>
You can install those 4 functions in a same manner as pgpool_regclass.
However, unlike pgpool_regclass, you only need to install those functions to template1 database.
</p>
<p>Here is the way to install those functions.</p>
<p>
<pre>
$ cd pgpool-II-x.x.x/sql/pgpool-recovery
$ make
$ make install
</pre>
<p>After this:<p>
<pre>
$ psql -f pgpool-recovery.sql template1
</pre>
<p>or<p>
<pre>
$ psql template1
=# CREATE EXTENSION pgpool_recovery;
</pre>
</p>
<h4>Configuring pgpool.pg_ctl <span class="version">V3.3 -</span></h4>
<p>The function pgpool_pgctl executes the command whose path is specified by "pgpool.pg_ctl" in postgresql.conf.
To use this function, you have to specify this parameter.</p>
<pre>
ex)
$ cat >> /usr/local/pgsql/postgresql.conf
pgpool.pg_ctl = '/usr/local/pgsql/bin/pg_ctl'
$ pg_ctl reload -D /usr/local/pgsql/data
</pre>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>Configuring pgpool-II<a name="config"></a></h1>
<p>Default configuration files for pgpool-II are
<code>/usr/local/etc/pgpool.conf</code> and
<code>/usr/local/etc/pcp.conf</code>. Several operation modes are available
in pgpool-II. Each mode has associated features which
can be enabled or disabled, and specific configuration parameters to
control their behaviors.</p>
<table border>
<tr class="header">
<th>Function/Mode</th>
<th>Raw Mode (*2)</th>
<th>Replication Mode</th>
<th>Master/Slave Mode</th>
</tr>
<tr>
<th>Connection Pool</th>
<td align="center">X</td>
<td align="center">O</td>
<td align="center">O</td>
</tr>
<tr>
<th>Replication</th>
<td align="center">X</td>
<td align="center">O</td>
<td align="center">X</td>
</tr>
<tr>
<th>Load Balance</th>
<td align="center">X</td>
<td align="center">O</td>
<td align="center">O</td>
</tr>
<tr>
<th>Failover</th>
<td align="center">O</td>
<td align="center">O</td>
<td align="center">O</td>
</tr>
<tr>
<th>Online recovery</th>
<td align="center">X</td>
<td align="center">0</td>
<td align="center">(*1)</td>
</tr>
<tr>
<th>Required # of Servers</th>
<td align="center">1 or higher</td>
<td align="center">2 or higher</td>
<td align="center">2 or higher</td>
</tr>
</table>
<ul>
<li> O means 'available', X means 'unavailable'</li>
<li>(*1) Online recovery can be used with Master slave+Streaming replication.</li>
<li>(*2) 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.
<code>pcp.conf</code> is the user/password file used for
authentication by this interface. All operation modes require the
<code>pcp.conf</code> file to be set. A <code>$prefix/etc/pcp.conf.sample</code>
file is created during the installation of pgpool-II. Rename the file to
<code>pcp.conf</code> and add your user name and password to it.
</p>
<pre>
cp $prefix/etc/pcp.conf.sample $prefix/etc/pcp.conf
</pre>
<p>
An empty line or a line starting with "<code>#</code>" is treated as a
comment and will be ignored. A user name and its associated password must be
written as one line using the following format:
</p>
<pre>
username:[password encrypted in md5]
</pre>
<p>
<code>[password encrypted in md5]</code> can be produced with the
<code>$prefix/bin/pg_md5</code> command.
</p>
<pre>
pg_md5 -p
password: <your password>
</pre>
<p>
or
</p>
<pre>
./pg_md5 foo
acbd18db4cc2f85cedef654fccc4a4d8
</pre>
<p>
The <code>pcp.conf</code> file must be readable by the user who
executes pgpool-II.</p>
<h2 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
<code>$prefix/etc/pgpool.conf.sample</code> file is created during the
installation of pgpool-II. Rename the file to
<code>pgpool.conf</code> and edit its contents.
<pre>
cp $prefix/etc/pgpool.conf.sample $prefix/etc/pgpool.conf
</pre>
<p>
There are additional sample pgpool.conf for each mode. <span class="version">V2.3 -</span>
</p>
<table border>
<tr class="header">
<th>Mode</th>
<th>sample file</th>
</tr>
<tr>
<td>replication mode</td>
<td>pgpool.conf.sample-replication</td>
</tr>
<tr>
<td>master/slave mode(Slony-I)</td>
<td>pgpool.conf.sample-master-slave</td>
</tr>
<tr>
<td>master/slave mode(Streaming replication)</td>
<td>pgpool.conf.sample-stream</td>
</tr>
</table>
<p>
An empty line or a line starting with "#" is treated as a
comment and will be ignored.</p>
</p>
<h3>Connections</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.
</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. <code>''</code> disables unix socket
connections. 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_LISTEN_ADDRESS"></a>pcp_listen_addresses</dt>
<dd>
<p>Specifies the hostname or IP address, on which pcp process will accept
TCP/IP connections. <code>'*'</code> accepts
all incoming connections. <code>''</code> disables TCP/IP
connections. Default is <code>'*'</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="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. <code>''</code> disables unix socket
connections. 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>
<h3>Pools</h3>
<dl>
<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 listen_backlog_multiplier*num_init_children can be queued.
</p>
<p>
The queued is inside the kernel called "listen queue". The length of the listen queue is called "backlog".
There is an upper limit of the backlog in some systems, and if num_init_children*listen_backlog_multiplier exceeds the number, you need to set the backlong higher. Otherwise, following problems may occur in heavy loaded systems: 1) connecting to pgpool-II fails 2) connecting to pgpool-II is getting slow because of retries in the kernel.
You can check if the listen queue is actually overflowed by using "netstat -s" command.
If you find something like:
</p>
<pre>
535 times the listen queue of a socket overflowed
</pre>
</p>
<p>
then the listen queue is definitely overflowed.
You should increase the backlog in this case (you will be required a super user privilege).
</p>
<p>
<pre>
# sysctl net.core.somaxconn
net.core.somaxconn = 128
# sysctl -w net.core.somaxconn = 256
</pre>
</p>
<p>
You could add following to /etc/sysctl.conf instead.
</p>
<p>
<pre>
net.core.somaxconn = 256
</pre>
</p>
<p>
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>
<p>
This parameter can only be set at server start.</p>
</dd>
<dt><a name="LISTEN_BACKLOG_MULTIPLIERE"></a>listen_backlog_multiplier <span class="version">V3.4 -</span></dt>
<dd>
<p>
Controls the length of connection queue from frontend to pgpool-II. The default is 2.
The queue length (actually "backlog" parameter of listen system call) is defined as listen_backlog_multiplier * num_init_children.
If the queue length is not long enough, you should increase the parameter.
Some systems have upper limit of the backlog parameter of listen system call.
See <a href="#NUM_INIT_CHILDREN">num_init_children</a> for more details.
</p>
<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 useful 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="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>
You need to reload pgpool.conf if you change this value.
</p>
</dd>
<dt><a name="POOL_PASSWD"></a>pool_passwd</dt>
<dd>
<p>
Specify the file name of pool_passwd for md5 authentication.
Default value is "pool_passwd". "" disables to read pool_passwd.
See <a href="#md5">Authentication / Access Controls</a> for more details.
</p>
<p>
You need to restart pgpool-II if you change this value.
</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 reload pgpool.conf if you change this value.
</dd>
</dl>
<h3>Logs</h3>
<dl>
<dt><a name="LOG_DESTINATION"></a>log_destination <span class="version">V3.1 -</span></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="PRINT_TIMESTAMP"></a>print_timestamp <span class="version">- V3.4 (REMOVED)</span></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="PRINT_USER"></a>print_user <span class="version">- V3.4 (REMOVED)</span></dt>
<dd>
<p>Add session user names to the logs when set to true. Default is false.
</p>
<p>
You need to reload pgpool.conf if you change print_user.
</p>
</dd>
<dt><a name="LOG_LINE_PREFIX"></a>log_line_prefix</dt>
<dd>
<p>
This is a printf-style string that is output at the beginning of each log line.
% characters begin "escape sequences" that are replaced with information outlined below.
All unrecognized escapes are ignored. Other characters are copied straight to the log line.
Default is '%t: pid %p: ', which prints timestamp and process
id, which keeps backward compatibily with pre-3.4.
</p>
<table border>
<tr class="header"><th>Escape</th><th>Effect</th></tr>
<tr><td>%a</td><td>Client application name.</td></tr>
<tr><td>%p</td><td>Process ID (PID).</td></tr>
<tr><td>%P</td><td>Process name.</td></tr>
<tr><td>%t</td><td>Time stamp.</td></tr>
<tr><td>%d</td><td>Database name.</td></tr>
<tr><td>%u</td><td>User name.</td></tr>
<tr><td>%l</td><td>Log line number for each process.</td></tr>
<tr><td>%%</td><td>'%' character</td></tr>
</table>
<p>
You need to reload pgpool.conf if you change log_line_prefix
</p>
</dd>
<dt><a name="LOG_ERROR_VERBOSITY"></a>log_error_verbosity</dt>
<dd>
<p>
Controls the amount of detail emitted for each message that is logged.
Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages.
TERSE excludes the logging of DETAIL, HINT, and CONTEXT error information.
</p>
<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="CLIENT_MIN_MESSAGES"></a>client_min_messages</dt>
<dd>
<p>
Controls which minimum message levels are sent to the client.
Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE,
WARNING and ERROR. Each level includes all the levels that follow it.
The default is NOTICE.
</p>
<p>
You need to reload pgpool.conf if you change this value.
</p>
</dd>
<dt><a name="LOG_MIN_MESSAGES"></a>log_min_messages</dt>
<dd>
<p>
Controls which minimum message levels are emitted to log.
Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE,
WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the
levels that follow it. The default is WARNING.
</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 <a href="#LOG_CONNECTIONS">log_connections</a> is enabled,
hostname will be logged.
</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 start up.
</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 <span class="version">V2.3 -</span></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="SYSLOG_FACILITY"></a>syslog_facility <span class="version">V3.1 -</span></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 <span class="version">V3.1 -</span></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="DEBUG_LEVEL"></a>debug_level <span class="version">V3.0 -</span></dt>
<dd>
<p>
Debug message verbosity level. 0 means no message, greater than 1
means more verbose message. Default value is 0.
</p>
</dd>
</dl>
<h3>File locations</h3>
<dl>
<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="LOGDIR"></a>logdir</dt>
<dd>
<p>pgpool_status is written into this directory.</p>
</dd>
</dl>
<h3>Connection pooling</h3>
<dl>
<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>
</dl>
<h3>Health check</h3>
<dl>
<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 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 <span class="version">V3.1 -</span></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 <span class="version">V3.2 -</span></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 <span class="version">V3.2 -</span></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).
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>
<dt><a name="CONNECT_TIMEOUT"></a>connect_timeout <span class="version">V3.4 -</span></dt>
<dd>
<p>Timeout value in milliseconds before giving up connecting to
backend using connect() system call. Default is 10000 ms (10
second). Flaky network user may want to increase the value. 0
means no timeout. Note that this value is not only used for
health check, but also for creating ordinary conection pools.
</p>
<p>
You need to reload pgpool.conf if you change connect_timeout.
</p>
</dd>
<dt><a name="SEARCH_PRIMARY_NODE_TIMEOUT"></a>search_primary_node_timeout <span class="version">V3.3 -</span></dt>
<dd>
<p>The parameter specifies the maximum amount of time in seconds to search
for a primary node when a failover scenario occurs.
The default value for the parameter is 10.
pgpool-II will search for the primary node for the amount of time given in case of
failover before giving up trying to search for a primary node.
0 means keep trying forever.
This parameter will be ignored if running in other than streaming replication mode.
</p>
<p>
You need to reload pgpool.conf if you change search_primary_node_timeout.
</p>
</dd>
</dl>
<h3>Failover and failback</h3>
<dl>
<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>
<table border>
<tr class="header"><th>Special character</th><th>Description</th></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>%r</td><td>New master port number.</td></tr>
<tr><td>%R</td><td>New master database cluster directory.</td></tr>
<tr><td>%%</td><td>'%' character</td></tr>
</table>
<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>
</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>
<table border>
<tr class="header"><th>Special character</th><th>Description</th></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>%r</td><td>New master port number.</td></tr>
<tr><td>%R</td><td>New master database cluster directory.</td></tr>
<tr><td>%%</td><td>'%' character</td></tr>
</table>
<p>
You need to reload pgpool.conf if you change failback_command.
</p>
</dd>
<dt><a name="FOLLOW_MASTER_COMMAND"></a>follow_master_command <span class="version">V3.1 -</span></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>
<table border>
<tr class="header"><th>Special character</th><th>Description</th></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>%r</td><td>New master port number.</td></tr>
<tr><td>%R</td><td>New master database cluster directory.</td></tr>
<tr><td>%%</td><td>'%' character</td></tr>
</table>
<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 <span class="version">V2.3 -</span></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>
</dl>
<h3>Load balancing mode</h3>
<dl>
<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 id="ALLOW_SQL_COMMENTS">allow_sql_comments <span class="version">V3.4 〜</span></dt>
<dd>
<p>
If on, ignore SQL comments when judging if load balance or
query cache is possible.
If off, SQL comments effectively prevent the judgment
(pre 3.4 behavior).
</p>
<p>
You need to reload pgpool.conf if you change this value.
</p>
</dd>
</dl>
<h3>Backends</h3>
<dl>
<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 meaning
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>
<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 <span class="version">V3.1 -</span></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 flags can be specified by using "|".
</p>
<table border>
<tr><th class="nodec">ALLOW_TO_FAILOVER</th>
<td>Allow to failover or detaching backend. This is the default.
You cannot specify with DISALLOW_TO_FAILOVER at a same time.
</td></tr>
<tr><th class="nodec">DISALLOW_TO_FAILOVER</th>
<td>Disallow to failover or detaching backend.
This is useful 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.
</td></tr>
</table>
</dt>
</dl>
<h3>SSL</h3>
<dl>
<dt><a name="SSL">ssl</a> <span class="version">V2.3 -</span></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>
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>
<p>
The pgpool-II daemon must be restarted when updating SSL related settings.
</p>
</dd>
<dt><a name="SSL_KEY"></a>ssl_key <span class="version">V2.3 -</span></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 <span class="version">V2.3 -</span></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="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 certificate
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>
<h3>Other</h3>
<dl>
<dt><a name="RELCACHE_EXPIRE"></a>relcache_expire <span class="version">V3.1 -</span></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>
<dt><a name="RELCACHE_SIZE"></a>relcache_size <span class="version">V3.2 -</span></dt>
<dd>
<p>
Number of relcache entries. Default is 256.
If you see following message frequently, increase the number.
</p>
<pre>
"pool_search_relcache: cache replacement happened"
</pre>
</dd>
<dt><a name="CHECK_TEMP_TABLE"></a>check_temp_table <span class="version">V3.2 -</span></dt>
<dd>
<p>
If on, enable temporary table check in SELECT statements. This
initiates queries against system catalog of primary/master thus
increases load of primary/master. If you are absolutely sure that your
system never uses temporary tables and you want to save access
to primary/master, you could turn this off. Default is on.
</p>
</dd>
<dt><a name="CHECK_UNLOGGED_TABLE"></a>check_unlogged_table <span class="version">V3.4 -</span></dt>
<dd>
<p>
If on, enable unlogged table check in SELECT statements. This
initiates queries against system catalog of primary/master thus
increases load of primary/master. If you are absolutely sure that your
system never uses unlogged (for example, you are using PostgreSQL 9.0 or before) tables and you want to save access
to primary/master, you could turn this off. Default is on.
</p>
</dd>
</dl>
<h2>Generating SSL certificates</h2>
<p>
Certificate handling is outside the scope of this document. The
<a href="http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html">
Secure TCP/IP Connections with SSL</a> page at postgresql.org has pointers
with sample commands for how to generate self-signed certificates.
</p>
<h2 id="failover_in_raw_mode">Failover in the raw Mode</h2>
<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>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<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 "<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 user name.
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.
<pre>
reset_query_list = 'ABORT; DISCARD ALL'
</pre>
<p>
Commands differ in each PostgreSQL versions. Here are the recommended settings.
</p>
<table border>
<tr class="header"><th>PostgreSQL version</th><th>reset_query_list value</th></tr>
<tr><th>7.1 or before</th><td>ABORT</td></tr>
<tr><th>7.2 to 8.2</th><td>ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT</td></tr>
<tr><th>8.3 or later</th><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>
<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>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<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="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,
<a href="#REPLICATE_SELECT">replicate_select</a> 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="FAILOVER_IF_AFFECTED_TUPLES_MISMATCH"></a>failover_if_affected_tuples_mismatch
<span class="version">V3.0 -</span></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="WHITE_FUNCTION_LIST"></a>white_function_list <span class="version">V3.0 -</span></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
(to which added automatically ^ and $),
for example if you have prefixed all your read only function with 'get_' or 'select_'
</p>
<pre>
white_function_list = 'get_.*,select_.*'
</pre>
</dd>
<dt><a name="BLACK_FUNCTION_LIST"></a>black_function_list <span class="version">V3.0 -</span></dt>
<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
(to which added automatically ^ and $)
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>
<pre>
white_function_list = ''
black_function_list = 'nextval,setval,lastval,currval'
</pre>
<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 accidentally 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 replication mode. If false,
pgpool-II sends SELECTs without writing function to the Master DB only. 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>
<table border>
<tr class="center">
<th class="nodec">replicate_select is true</th>
<td>Y</td><td colspan="4">N</td>
</tr>
<tr class="center">
<th class="nodec">load_balance_mode is true</th>
<td>any</td><td colspan="3">Y</td><td>N</td>
<tr class="center">
<th class="nodec">SELECT is inside a transaction block</th>
<td>any</td><td colspan="2">Y</td><td>N</td><td>any</td>
</tr>
<tr class="center">
<th class="nodec">transaction isolation level is SERIALIZABLE and<br>
the transaction has issued a write query</th>
<td>any</td><td>Y</td><td>N</td><td>any</td><td>any</td>
</tr>
<tr class="footer center">
<th class="nodec">results(R:replication, M: send only to master, L: load balance)</th>
<td>R</td><td>M</td><td>L</td><td>L</td><td>M</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>
<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 was 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>
<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>
<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>
<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>
<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 user name 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>
<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 4 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>
<li>master database port number</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>
<font color="red">
CAUTION:
</font>
recovery_1st_stage_command runs as an SQL command from PostgreSQL's point of view. If you enable PostgreSQL's statement_time_out and it's shorter than the execution time of the recovery_1st_stage_command, PostgreSQL cancels the command.
Typical symptoms of this is, rsync used in the command is killed by signal 2 for example.
</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 4 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>
<li>port number of 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>
<font color="red">
CAUTION:
</font>
recovery_2nd_stage_command runs as an SQL command from PostgreSQL's point of view. If you enable PostgreSQL's statement_time_out and it's shorter than the execution time of the recovery_2nd_stage_command, PostgreSQL cancels the command.
Typical symptoms of this is, rsync used in the command is killed by signal 2 for example.
</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>
</dd>
<dt><a name="CLIENT_IDLE_LIMIT_IN_RECOVERY"></a>client_idle_limit_in_recovery
<span class="version">V2.2 -</span></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 <span class="version">V2.2 -</span></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>
<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>
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>
<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:
</p>
<ul>
<li>PostgreSQL version 7.4 or later</li>
<li>either in replication mode or master slave mode</li>
<li>the query must not be in an explicitly declared
transaction (i.e. not in a BEGIN ~ END block) when operated in replication mode
<ul>
<li>However, if following conditions are met, load balance is possible even if in an explicit transaction
<ul>
<li>transaction isolation level is not SERIALIZABLE</li>
<li>the transaction has not issued a write query yet (until a write query is issued, load balance is possible. Here "write query" means non SELECT DML or DDL. SELECTs having write functions as specified in black or white function list is not regarded as a write query. This may be changed in the future.)</li>
<li>If black and white function list is empty, SELECTs having functions is regarded as a read only query.</li>
</ul>
</li>
</ul>
</li>
<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.
(Except for SELECTs using writing functions specified in <a href="#BLACK_FUNCTION_LIST">black_list</a> or
<a href="#WHITE_FUNCTION_LIST">white_list</a>)
<li><span class="version">V3.0 -</span>in master slave mode, in addition to above, following conditions must be met:
<ul>
<li>does not use temporary tables</li>
<li>does not use unlogged tables</li>
<li>does not use system catalogs</li>
</ul>
</li>
</ul>
<p>
Note that you could suppress load balancing by inserting arbitrary
comments just in front of the SELECT query:
<pre>
/*REPLICATION*/ SELECT ...
</pre>
</p>
<p>
If you want to use comments without supressing load balancing, you can set <a href="#ALLOW_SQL_COMMENTS">allow_sql_comments</a> to on.
</p>
<p>
Please refer to <a href="#replicate_select">replicate_select</a> as well.
See also a <a href="where_to_send_queries.pdf">flow chart</a>.
</p>
<p>
<font color="red">
Note: the JDBC driver has an autocommit option. If the autocommit is false, the JDBC
driver sends "BEGIN" and "COMMIT" by itself. In this case <a href="#condition_for_load_balance">the same restriction above regarding load balancing</a> will be applied.
</font>
</p>
<h2 id="failover_in_replication_mode">Failover in Replication Mode</h2>
<p>
pgpool-II detaches a dead backend from the pool, maintaining the database
service via the remaining backends provided that there is at least one healthy
backend.
</p>
<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
(fail-over 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
</pre>
<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
2010-07-22 13:23:25 LOG: pid 5490: ReadyForQuery: Number of affected tuples are: 0 1
</pre>
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<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.
</p>
<p>
Please note that the number of slaves is not necessarily limited to just 1.
Actually you could have up to 127 slaves (0 slaves is allowed).
</p>
<p>
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 functionality) 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 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
suitable for Slony-I. You can also set it to 'stream', which should be
set if you want to work with PostgreSQL's built-in replication system
(Streaming Replication).
The sample configuration file for the Slony-I sub-mode is
pgpool.conf.sample-master-slave and the sample for the streaming replication
sub-module is pgpool.conf.sample-stream.
</p>
<p>
Please restart pgpool-II if you change any of the above parameters.
</p>
<p>
You can set <a href="#WHITE_FUNCTION_LIST">white_function_list</a> and
<a href="#BLACK_FUNCTION_LIST">black_function_list</a> to control load
balancing in master/slave mode.
See <a href="#WHITE_FUNCTION_LIST">white_function_list</a> for more details.
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1><a name="stream"></a>Streaming Replication <span class="version">V3.1 -</span></h1>
<p>
As stated above, pgpool-II can work together with Streaming Replication, which
is available since PostgreSQL 9.0. To use it, enable '<a href="#MASTER_SLAVE_MODE">master_slave_mode</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>
<dl>
<dt id="DELAY_THRESHOLD">delay_threshold <span class="version">V3.0 -</span></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>
<dt id="SR_CHECK_PERIOD">sr_check_period <span class="version">V3.1 -</span></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>
<dt id="SR_CHECK_USER">sr_check_user <span class="version">V3.1 -</span></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 <span class="version">V3.1 -</span></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 <span class="version">V3.1 -</span></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 the replication delay is checked.
If 'if_over_threshold' is specified, the log is written when the delay
exceeds <a href="#DELAY_THRESHOLD">delay_threshold</a>.
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 "<a href="#pool_status">show pool_status</a>"
command as well.
The column name is "standby_delay#"(where '#' should be replaced by DB node id).
</p>
</dd>
</dl>
<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.
</p>
<p>
<strong>Caution: If you plan to use multiple standby nodes, we recommend
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>
If a second standby took over primary when the first standby has already
taken over too, you would get bogus data from the second standby.
We recommend not to plan this kind of configuration.
</strong>
</p>
<p>
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.
<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.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.
failed_node=$1
new_master=$2
trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi
# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file
exit 0;
chmod 755 failover_stream.sh
</pre>
</li>
<li>Set <a href="#FAILOVER_COMMAND">failover_commmand</a> 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.
<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.
<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.
<pre>
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.
</p>
<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,
and which one should not be sent to the standby.
pgpool-II's Streaming Replication mode carefully takes care of this.
In this chapter we'll explain how pgpool-II accomplishes this.
</p>
<p>
We distinguish which query should be sent to which node by looking
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>
<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>DECLARE, FETCH, CLOSE</li>
<li>SHOW</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>
</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:
</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>
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.
</li>
</ul>
<p>
In the extended protocol, it is possible to determine if the query can
be sent to standby or not in load balance mode while parsing the query.
The rules are the same as for the non extended protocol.
For example, INSERTs are sent to the primary node.
Following bind, describe and execute will be sent to the primary node as well.
</p>
<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.]
</p>
<p>
Lastly, queries that pgpool-II's parser thinks to be an error are sent to the
primary node.
</p>
<p>
You can use database name and application name for smaller granularity
in specifying load balance.
</p>
<p>
<dl>
<dt id="DATABASE_REDIRECT_PREFERENCE_LIST">database_redirect_preference_list <span class="version">V3.4 〜</span></dt>
<dd>
<p>
you can set "database name:node id" pair to specify the node id when connecting to the database.
For example, by specifying "test:1", pgpool-II always redirects SELECT to node 1 in case of connecting to database "test".
You can specify multiple "database name:node id" pair by separating them using comma (,).
Regular expressions are accepted for database name.
Special keyword "primary" indicates the primary node and "standby" indicates one of standby nodes.
</p>
<p>
Here is an example.
<pre>
database_redirect_preference_list = 'postgres:primary,mydb[01]:1,mydb2:standby'
</pre>
</p>
<p>
SELECTs will be redirected to primary if you connect to postgres database.
Connecting to mydb0 or mydb1 will redirect SELECTs to node 1.
Connecting to mydb2 will redirect SELECTs to one of standby nodes.
</p>
<p>
You need to reload pgpool.conf if you change this directive.
</p>
</dd>
<dt id="APP_NAME_REDIRECT_PREFERENCE_LIST">app_name_redirect_preference_list <span class="version">V3.4 〜</span></dt>
<dd>
<p>
you can set "application name:node id" pair to specify the node id when the application is used.
"Application name" is a name specified by a client when it connects to database.
You can use it in PostgreSQL 9.0 or later.
For example, application of psql command is "psql".
pgpool-II recognize application names only when clients sends a start up packet.
Clients can send application names later on but pgpool-II will not recognize them.
</p>
<p>
The notion of app_name_redirect_preference_list is same as database_redirect_preference_list.
Thus you can use regular expressions for application name.
</p>
<p>
Here is an example.
<pre>
app_name_redirect_preference_list = 'psql:primary,myapp1:1,myapp2:standby'
</pre>
</p>
<p>
In this example, psql sends SELECTs to primary node, myapp1 sends to node 1, and myapp2 sends to one of standby nodes.
</p>
<p>
app_name_redirect_preference_list takes precedence over database_redirect_preference_list.
See the next example.
<pre>
database_redirect_preference_list = 'bigdb:primary'
app_name_redirect_preference_list = 'myapp:2'
</pre>
</p>
<p>
Applications connecting to bigdb database send SELECTs to primary node.
However myapp sends SELECTs to node 2 even if it connects to bigdb.
This is useful in a scenario: myapp2 sends very heavy SELECTs to execute analysis jobs.
You want to use node 2 solely for analysis purpose.
</p>
<p>
You need to reload pgpool.conf if you change this directive.
</p>
</dd>
</dl>
</p>
<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 the online recovery procedure, primary server acts as a master server and recovers 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
pgpool-II and all PostgreSQL servers and recover them manually.
</p>
<p>
<ol>
<li>Set <a href="#RECOVERY_USER">recovery_user</a>. Usually it's "postgres".
<pre>
recovery_user = 'postgres'
</pre>
</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>
<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>
<li>Leave <a href="#RECOVERY_2ND_STAGE_COMMAND">recovery_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>
</p>
<pre>
# cd pgpool-II-x.x.x/sql/pgpool-recovery
# make
# make install
# 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>
</ol>
<p>
That's it.
Now you should be able to use <a href="#pcp_recovery_node">pcp_recovery_node</a> (as long as the standby
node stops) or push "recovery" button of pgpoolAdmin to perform online recovery.
If something goes wrong, please examine 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 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>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<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".
</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 disabled.
Change <a href="#ENABLE_POOL_HBA">enable_pool_hba</a> to on to enable it.
</p>
<p>
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]
</pre>
<p>
See "pool_hba.conf.sample" for a detailed explanation of each field.
</p>
<p>
Here are the limitations of pool_hba.
</p>
<ul>
<li>"samegroup" for DATABASE field is not supported</li>
<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>
<li>group names following "+" for USER field is not supported</li>
<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>
<li>IPv6 for IP address/mask is not supported</li>
<p>
pgpool currently does not support IPv6.
</p>
<li>Only "trust", "reject", "md5" and "pam" for METHOD field are supported</li>
<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.
</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.
</p>
<p>
PAM authentication is supported using user information on the host where
pgpool is executed. To enable PAM support in pgpool, specify "--with-pam"
option to configure:
</p>
<pre>
configure --with-pam
</pre>
<p>
To enable PAM authentication, you need to create a
service-configuration file for pgpool in the system's PAM
configuration directory (which is usually at "/etc/pam.d").
A sample service-configuration file is installed as "share/pgpool.pam" under the install directory.
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1><a name="memqcache"></a>In memory query Cache <span class="version">V3.2 -</span></h1>
<p>
You can use in memory query cache in any mode.
It is different from the above query cache on the point that
in memory query cache is faster because cache storage is in memory.
Moreover you don't need to restart pgpool-II when the cache is outdated
because the underlying table gets updated.
</p>
<p>
In memory cache saves pair of SELECT statements
(with its Bind parameters if the SELECT is an extended query).
If the same SELECTs comes in, it returns the value from cache.
Since no SQL parsing nor access to PostgreSQL are involved, it's extremely fast.
</p>
<p>
On the other hand, it might be slower than the normal path because it adds some overhead to store cache.
Moreover when a table is updated, pgpool automatically deletes all the caches related to the table.
So the performance will be degraded by a system with a lot of updates.
If the cache_hit_ratio is lower than 70%, you might want to disable in memory cache.
</p>
<h2 id="MEMORY_CACHE_RESTRICTIONS">Restrictions</h2>
<ul>
<li>
In memory query cache deletes the all cache of an updated table automatically
with monitoring if the executed query is UPDATE, INSERT, ALTER TABLE and so on.
But pgpool-II isn't able to recognize implicit updates due to triggers, foreign keys and
DROP TABLE CASCADE.
You can avoid this problem with <a href="#MEMQCACHE_EXPIRE">memqcache_expire</a>
by which pgpool deletes old cache in a fixed time automatically, or with
<a href="#BLACK_MEMQCACHE_TABLE_LIST">black_memqcache_table_list</a>
by which pgpool's memory cache flow ignores the tables.
</li>
<li>
If you want to use multiple instances of pgpool-II with online memory cache which uses shared memory,
it could happen that one pgpool deletes cache,
and the other one doesn't do it thus finds old cached result when a table gets updated.
Memcached is the better cache storage in this case.
</li>
</ul>
<h2 id="MEMORY_CACHE_ENABLED">Enabling in memory query cache</h2>
<p>
To enable the memory cache functionality, set this to on (default is off).
</p>
<pre>
memory_cache_enabled = on
</pre>
<h2 id="MEMQCACHE_METHOD">Choosing cache storage</h2>
<p>
You can choose a cache storage: shared memory or <a href="http://memcached.org">memcached</a>
(you can't use the both).
Query cache with shared memory is fast and easy because you don't have to install and configure memcached,
but restricted the max size of cache by the one of shared memory.
Query cache with memcached needs a overhead to access network, but you can set the size as you like.
</p>
<p>
Memory cache behavior can be specified by memqcache_method directive.
Either "shmem"(shared memory) or "memcached". Default is shmem.
</p>
<pre>
memqcache_method = 'shmem'
</pre>
<h2 id="memqcache_cases">When in memory query cache is prohibited</h2>
<p>
Not All of SELECTs and WITH can be cached. In some cases including followings,
cache is avoided to keep consistency between caches and databases.
</p>
<ul>
<li>SELECT starting with "/*NO QUERY CACHE*/" comment</li>
<li>SELECT including tables in <a href="#BLACK_MEMQCACHE_TABLE_LIST">black_memqcache_table_list</a></li>
<li>SELECT FOR SHARE / UPDATE</li>
<li>SELECT including un-immutable functions</li>
<li>SELECT including TEMP TABLE</li>
<li>SELECT including system catalogs</li>
<li>SELECT including VIEWs or unlogged tables.
However if the table is in <a href="#WHITE_MEMQCACHE_TABLE_LIST">white_memqcache_table_list</a>, the result will be cached.</li>
<li>SELECT including VIEWs</li>
<li>SELECT in an aborted explicit transaction</li>
<li>SELECT with the result larger than <a href="#MEMQCACHE_MAXCACHE">memqcache_maxcache</a></li>
</ul>
<h2 id="non_memqcache_case">When cache is not used</h2>
<p>
It can happen that even if the matched query cache exists, pgpool doesn't return it.
</p>
<ul>
<li>If an updating query is executed in an explicit transaction, during the transaction,
pgpool doesn't use any query cache.</li>
<li>The matched query cache is made by the other user (for security reason)</li>
<li>The matched query cache has to be deleted due to <a href="#MEMQCACHE_EXPIRE">memqcache_expire</a>.</li>
</ul>
<h2 id="memqcache_params">Configuring</h2>
<p>
These are the parameters used with both of shmem and memcached.
</p>
<dl>
<dt id="MEMQCACHE_EXPIRE">memqcache_expire <span class="version">V3.2 -</span></dt>
<dd>
<p>
Life time of query cache in seconds. Default is 0.
0 means no cache expiration, and cache have been enabled until a table is updated.
This parameter and <a href="#MEMQCACHE_AUTO_CACHE_INVALIDATION">memqcache_auto_cache_invalidation</a>
are orthogonal.
</p>
</dd>
<dt id="MEMQCACHE_AUTO_CACHE_INVALIDATION">memqcache_auto_cache_invalidation
<span class="version">V3.2 -</span></dt>
<dd>
<p>
If on, automatically deletes cache related to the updated tables.
If off, does not delete caches. Default is on.
This parameter and <a href="#MEMQCACHE_EXPIRE">memqcache_expire</a>. are orthogonal.
</p>
</dd>
<dt id="MEMQCACHE_MAXCACHE">memqcache_maxcache <span class="version">V3.2 -</span></dt>
<dd>
<p>
If the size of a SELECT result is larger than memqcache_maxcache bytes,
it is not cached and the messages is shown:
</p>
<pre>
2012-05-02 15:08:17 LOG: pid 13756: pool_add_temp_query_cache: data size exceeds memqcache_maxcache. current:4095 requested:111 memq_maxcache:4096
</pre>
<p>
To avoid this problem, you have to set memqcache_maxcache larger.
But if you use shared memory as the cache storage,
it must be lower than <a href="#MEMQCACHE_CACHE_BLOCK_SIZE">memqcache_cache_block_size</a>.
If memqcached, it must be lower than the size of slab (default is 1 MB).
</p>
</dd>
<dt id="WHITE_MEMQCACHE_TABLE_LIST">white_memqcache_table_list <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specify a comma separated list of table names whose SELECT results are to be cached even if
they are VIEWs or unlogged tables. You can use regular expression (to which added automatically ^ and $).
</p>
<p>
TABLEs and VIEWs in both of white_memqcache_table_list and
<a href="#BLACK_MEMQCACHE_TABLE_LIST">black_memqcache_table_list</a> are cached.
</p>
<p>
You need to add both non schema qualified name and schema qualified name if you plan to use both of them in your query. For exmaple, if you want to use both "table1" and "public.table1" in your query, you need to add "table1,public.table1", not just "table1".
</p>
</dd>
<dt id="BLACK_MEMQCACHE_TABLE_LIST">black_memqcache_table_list <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specify a comma separated list of table names whose SELECT results are <strong>NOT</strong> to be cached.
You can use regular expression (to which added automatically ^ and $).
</p>
<p>
You need to add both non schema qualified name and schema qualified name if you plan to use both of them in your query. For exmaple, if you want to use both "table1" and "public.table1" in your query, you need to add "table1,public.table1", not just "table1".
</p>
</dd>
<dt id="MEMQCACHE_OIDDIR">memqcache_oiddir <span class="version">V3.2 -</span></dt>
<dd>
<p>
Full path to the directory where oids of tables used by SELECTs are stored.
Under memqcache_oiddir there are directories named database oids,
and under each of them there are files named table oids used by SELECTs.
In the file pointers to query cache are stored.
They are used as keys to delete caches.
</p>
<p>
Directories and files under memqcache_oiddir are not deleted whenever pgpool-II restarts.
If you start pgpool by "<a href="#start">pgpool -C</a>", pgpool starts without the old oidmap.
</p>
</dd>
</dl>
<h2 id="monitoring_memqcache">Monitoring caches</h2>
<p>
This explains how to monitor in memory query cache.
To know if a SELECT result is from query cache or not,
enable <a href="#LOG_PER_NODE_STATEMENT">log_per_node_statement</a>.
</p>
<pre>
2012-05-01 15:42:09 LOG: pid 20181: query result fetched from cache. statement: select * from t1;
</pre>
<p>
<a href="#pool_status">pool_status</a> command shows the cache hit ratio.
</p>
<pre>
memqcache_stats_start_time | Tue May 1 15:41:59 2012 | Start time of query cache stats
memqcache_no_cache_hits | 80471 | Number of SELECTs not hitting query cache
memqcache_cache_hits | 36717 | Number of SELECTs hitting query cache
</pre>
<p>
In this example, you can calculate like the below:
</p>
<p>
<pre>
(memqcache_cache_hits) / (memqcache_no_cache_hits+memqcache_cache_hits) = 36717 / (36717 + 80471) = 31.3%
</pre>
<p>
<a href="#pool_cache">show pool_cache</a> commands shows the same one.
</p>
<h2 id="shmem_params">Configuring to use shared memory</h2>
<p>
These are the parameters used with shared memory as the cache storage.
</p>
<dl>
<dt id="MEMQCACHE_TOTAL_SIZE">memqcache_total_size <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specify the size of shared memory as cache storage in bytes.
</p>
</dd>
<dt id="MEMQCACHE_MAX_NUM_CACHE">memqcache_max_num_cache <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specify the number of cache entries.
This is used to define the size of cache management space
(you need this in addition to <a href="#MEMQCACHE_TOTAL_SIZE">memqcache_total_size</a>).
The management space size can be calculated by:
<a href="#MEMQCACHE_MAX_NUM_CACHE">memqcache_max_num_cache</a> * 48 bytes.
Too small number will cause an error while registering cache.
On the other hand too large number is just a waste of space.
</p>
</dd>
<dt id="MEMQCACHE_CACHE_BLOCK_SIZE">memqcache_cache_block_size <span class="version">V3.2 -</span></dt>
<dd>
<p>
If cache storage is shared memory, pgpool uses the memory divided by memqcache_cache_block_size.
SELECT result is packed into the block.
However because the SELECT result cannot be placed in several blocks, it cannot be cached if it is larger
than memqcache_cache_block_size.
memqcache_cache_block_size must be greater or equal to 512.
</p>
</dd>
</dl>
<h2 id="memcached_params">Configuring to use memcached</h2>
<p>
These are the parameters used with memcached as the cache storage.
</p>
<dl>
<dt id="MEMQCACHE_MEMCACHED_HOST">memqcache_memcached_host <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specify the host name or the IP address in which memcached works.
If it is the same one as pgpool-II, set 'localhost'.
</p>
</dd>
<dt id="MEMQCACHE_MEMCACHED_PORT">memqcache_memcached_port <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specify the port number of memcached. Default is 11211.
</p>
</dd>
</dl>
<h3 id="install_memcached">memcached Installation</h3>
<p>
To use memcached as cache storage, pgpool-II needs a working memcached and
the client library: libmemcached.
It is easy to install them by rpms. This explains how to install from source codes.
</p>
<p>
memcached's source code can be downloaded from:
<a href="http://memcached.org/">memcached development page</a>
</p>
<dl>
<dt>configure</dt>
<dd>
<p>
After extracting the source tarball, execute the configure script.
</p>
<pre>
./configure
</pre>
</dd>
<dt>make</dt>
<dd>
<pre>
make
make install
</pre>
</dd>
</dl>
<h3 id="install_libmemcached">libmemcached Installation</h3>
<p>
Libmemcached is a client library for memcached.
You need to install libmemcached after installing memcached.
</p>
<p>
libmemcached's source code can be downloaded from:
<a href="http://libmemcached.org/libMemcached.html">libmemcached development page</a>
</p>
<dl>
<dt>configure</dt>
<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>
</ul>
</dd>
<dt>make</dt>
<dd>
<pre>
make
make install
</pre>
</dd>
</dl>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>Starting/Stopping pgpool-II<a name="start"></a></h1>
<h2 id="start_pgpool">Start pgpool-II</h2>
<p>All the backends must be started before starting pgpool-II.</p>
<pre>
pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file][-n][-D][-d][x]
</pre>
<table border>
<tr><td>-c</td><td>--clear-cache</td>
<td>deletes query cache</td></tr>
<tr><td>-f config_file</td><td>--config-file config-file</td>
<td>specifies pgpool.conf</td></tr>
<tr><td>-a hba_file</td><td>--hba-file hba_file</td>
<td>specifies pool_hba.conf</tr>
<tr><td>-F pcp_config_file</td><td>--pcp-password-file</td>
<td>specifies pcp.conf</td></tr>
<tr><td>-n</td><td>--no-daemon</td>
<td>no daemon mode (terminal is not detached)</td></tr>
<tr><td>-D</td><td>--discard-status</td>
<td>Discard pgpool_status file and do not restore previous status
<span class="version">V3.0 -</span></td></tr>
<tr><td>-C</td><td>--clear-oidmaps</td>
<td>Discard oid maps in <a href="#MEMQCACHE_OIDDIR">memqcache_oiddir</a> for in memory query cache
(only when <a href="#MEMQCACHE_METHOD">memqcache_method</a> is 'memcached',
if shmem, discard whenever pgpool starts).
<span class="version">V3.2 -</span></td></tr>
<tr><td>-d</td><td>--debug</td><td>debug mode</tr>
<tr><td>-x</td><td>--debug-assertions</td>
<td>Turns on various assertion checks, This is a debugging aid</td></tr>
</table>
<h2 id="stop_pgpool">Stop pgpool-II</h2>
<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.
</p>
<pre>
pgpool [-f config_file][-F pcp_config_file] [-m {s[mart]|f[ast]|i[mmediate]}] stop
</pre>
<table border>
<tr><td><code>-m s[mart]</code></td><td><code>--mode s[mart]</code></td>
<td>waits for clients to disconnect, and shutdown (default)</td></tr>
<tr><td><code>-m f[ast]</code></td><td><code>--mode f[ast]</code></td>
<td>does not wait for clients; shutdown immediately</td></tr>
<tr><td><code>-m i[mmediate]</code></td><td><code>--mode i[mmediate]</code></td>
<td>the same as <code>'-m f'</code></td></tr>
</table>
<p>
pgpool records backend status into the [logdir]/pgpool_status file.
From pgpool-II 3.4.0 the file format of pgpool_status has been changed: it's an ordinary ASCII file and you can read and edit the contents by using your favorite text editor.
For example, if you add new backend and restart pgpool-II, you might have wait for long time until pgpool-II detects the new backend and performs failover.
By editing pgpool_status to set the backend to be down status, you could avoid the situation because pgpool-II's health check skips down nodes.
Each line in the file corresponding to each backend node status.
The first backend status is the first line, and and the seconds backend status is the second line and so on.
The backend status is represented by any of "up", "down", "unused" (case ignored).
Here is an example of pgpool_status:
<pre>
up
down
up
</pre>
</p>
<p>
Note that pre-3.4.0 pgpool-II uses binary format pgpool_status. Pgpool-II 3.4.0 or later can read the file as well. However pre-3.4.0 pgpool-II cannot read ASCII format pgpool_status file.
</p>
<p>
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>
<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>
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>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>Reloading pgpool-II configuration files<a name="reload"></a></h1>
<p>pgpool-II can reload configuration files without restarting.
</p>
<pre>
pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file] reload
</pre>
<p>
<table border>
<tr><td>-f config_file</td><td>--config-file config-file</td><td>specifies pgpool.conf</tr>
<tr><td>-a hba_file</td><td>--hba-file hba_file</td><td>specifies pool_hba.conf</tr>
<tr><td>-F pcp_config_file</td><td>--pcp-password-file</td><td>specifies pcp.conf</tr>
</table>
<p>
Please note that some configuration items cannot be changed by
reloading. New configuration takes effect after a change for new sessions.
</p>
<p class="top_link"><a href="#Top">back to top</a></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 <span class="version">V3.0 -</span></li>
<li>pool_processes, to get information on pgPool-II processes <span class="version">V3.0 -</span></li>
<li>pool_pools, to get information on pgPool-II pools <span class="version">V3.0 -</span></li>
<li>pool_version, to get the pgPool_II release version <span class="version">V3.0 -</span></li>
</ul>
<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>the "pool_status" SQL statement was already available in previous releases,
but the other ones have appeared in release 3.0.</p>
<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:
</p>
<pre>
benchs2=# show pool_status;
item | value | description
--------------------------------------+--------------------------------+------------------------------------------------------------------
listen_addresses | localhost | host name(s) or IP address(es) to listen to
port | 9999 | pgpool accepting port number
socket_dir | /tmp | pgpool socket directory
pcp_port | 9898 | PCP port # to bind
pcp_socket_dir | /tmp | PCP socket directory
</pre>
<h2 id="pool_nodes">pool_nodes <span class="version">V3.0 -</span></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), the role and the SELECT query counts issued to each backend. The possible values in the status column
are explained in the <a href="#pcp_node_info">pcp_node_info reference</a>.
If the hostname is something like "/tmp", that means pgpool-II is connecting to backend by using UNIX domain sockets. The SELECT count does not include internal queries used by pgoool-II. Also the counters are reset to zero upon starting up of pgpool-II.
</p>
<pre>
benchs2=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt
---------+----------+-------+--------+-----------+---------+------------
0 | /tmp | 11002 | 2 | 0.500000 | primary | 9231
1 | /tmp | 11003 | 2 | 0.500000 | standby | 9469
(2 rows)
</pre>
<h2>pool_processes <span class="version">V3.0 -</span></h2>
<p>"SHOW pool_processes" sends back a list of all pgPool-II processes waiting
for connections and dealing with a connection.
</p>
<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>
</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
----------+---------------------+----------+-----------+---------------------+--------------
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 | | | |
(5 lines)
</pre>
<h2 id="pool_pools">pool_pools <span class="version">V3.0 -</span></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>
</ul>
</p>
<p>It'll always return <a href="#NUM_INIT_CHILDREN">num_init_children</a> * <a href="#MAX_POOL">max_pool</a> * number_of_backends lines.</p>
<pre>
pool_pid | start_time | pool_id | backend_id | database | username | create_time | majorversion | minorversion | pool_counter | pool_backendpid | pool_connected
----------+---------------------+---------+------------+----------+-----------+---------------------+--------------+--------------+--------------+-----------------+----------------
8465 | 2010-08-14 08:35:40 | 0 | 0 | | | | | | | |
8465 | 2010-08-14 08:35:40 | 1 | 0 | | | | | | | |
8465 | 2010-08-14 08:35:40 | 2 | 0 | | | | | | | |
8465 | 2010-08-14 08:35:40 | 3 | 0 | | | | | | | |
8466 | 2010-08-14 08:35:40 | 0 | 0 | benchs | guillaume | 2010-08-14 08:35:43 | 3 | 0 | 1 | 8473 | 1
8466 | 2010-08-14 08:35:40 | 1 | 0 | | | | | | | |
8466 | 2010-08-14 08:35:40 | 2 | 0 | | | | | | | |
8466 | 2010-08-14 08:35:40 | 3 | 0 | | | | | | | |
8467 | 2010-08-14 08:35:40 | 0 | 0 | | | | | | | |
8467 | 2010-08-14 08:35:40 | 1 | 0 | | | | | | | |
8467 | 2010-08-14 08:35:40 | 2 | 0 | | | | | | | |
8467 | 2010-08-14 08:35:40 | 3 | 0 | | | | | | | |
8468 | 2010-08-14 08:35:40 | 0 | 0 | | | | | | | |
8468 | 2010-08-14 08:35:40 | 1 | 0 | | | | | | | |
8468 | 2010-08-14 08:35:40 | 2 | 0 | | | | | | | |
8468 | 2010-08-14 08:35:40 | 3 | 0 | | | | | | | |
8469 | 2010-08-14 08:35:40 | 0 | 0 | | | | | | | |
8469 | 2010-08-14 08:35:40 | 1 | 0 | | | | | | | |
8469 | 2010-08-14 08:35:40 | 2 | 0 | | | | | | | |
8469 | 2010-08-14 08:35:40 | 3 | 0 | | | | | | | |
(20 lines)
</pre>
</p>
<h2 id="pool_version">pool_version <span class="version">V3.0 -</span></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
------------------------
3.0-dev (umiyameboshi)
(1 line)
</pre>
<h2 id="pool_cache">pool_cache <span class="version">V3.0 -</span></h2>
<p>"SHOW pool_cache" displays cache storage statistics if <a href="#memqcache">in memory query cache</a> is enabled.
Here is an example of it:
</p>
<pre>
test=# \x
\x
Expanded display is on.
test=# show pool_cache;
show pool_cache;
-[ RECORD 1 ]---------------+---------
num_cache_hits | 891703
num_selects | 99995
cache_hit_ratio | 0.90
num_hash_entries | 131072
used_hash_entries | 99992
num_cache_entries | 99992
used_cache_enrties_size | 12482600
free_cache_entries_size | 54626264
fragment_cache_entries_size | 0
</pre>
<ul>
<li>num_cache_hits means the number of SELECTs which hit cache.</li>
<li>num_selects means the number of SELECTs which do not hit cache.</li>
<li>
cache_hit_ratio means cache hit ratio, calculated from num_cache_hits/(num_cache_hits+num_selects)
Anything below num_hash_entries are valid only when cache storage is on shared memory.
</li>
<li>
num_hash_entries means number of entries in hash table, which is used for index to cache storage
and should be equal to <a href="#MEMQCACHE_MAX_NUM_CACHE">memqcache_max_num_cache</a> in pgpool.conf.
This is the upper limit for number of cache entries.
</li>
<li>used_hash_entries means number of already used entries in num_hash_entries.</li>
<li>
num_cache_entries means number of valid cache entries in the cache storage and
should be equal to used_hash_entries.
</li>
<li>used_cache_entries_size means total size of cache storage in bytes which is already used.</li>
<li>free_cache_entries_size means total size of cache storage in bytes which is not used yet or can be usable.</li>
<li>fragment_cache_entries_size means total size of cache storage in bytes which cannot be used because of fragmentation.</li>
<li>The fragmented area can be reused later if free_cache_entries_size becomes 0 (or there's no enough space for the SELECT result).</li>
</ul>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1><a name="online-recovery"></a>Online Recovery</h1>
<h2>Overview</h2>
<p>
pgpool-II, while in replication mode, can sync a database and attach a node
while still servicing clients. We call this feature "online recovery".
</p>
<p>
A recovery target node must be in the detached state before doing online
recovery.
If you wish to add a PostgreSQL server dynamically, add 'backend_hostname' and
its associated parameters and reload pgpool.conf. pgpool-II registers this new
node as a detached node.
</p>
<p>
<font color="red">caution: Stop autovacuum on the master node (the
first node which is up and running). Autovacuum may change the contents
of the database and might cause inconsistency after online recovery if
it's running. This applies only if you're recovering with a simple copy mechanism, such
as the rsync one explained below. This doesn't apply if you're using PostgreSQL's
PITR mechanism.</font>
</p>
<p>
If the target PostgreSQL server has already started, you need to shut it down.
</p>
<p>
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:
</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.
</p>
<p>
You can specify a script executed during the first stage. pgpool-II
passes three arguments to the script.
</p>
<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>
<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>
</p>
<h2 id="online_recovery_params">Configuration for online recovery</h2>
<p>
Set the following parameters for online recovery in pgpool.conf.
</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
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/
</pre>
<p>
Change directory there and do "make install".
</p>
<pre>
% cd pgpool-II-x.x.x/sql/pgpool-recovery/
% make install
</pre>
<p>
Then, install the SQL function.
</p>
<pre>
% cd pgpool-II-x.x.x/sql/pgpool-recovery/
% psql -f pgpool-recovery.sql template1
</pre>
<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.
</p>
<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
available in PostgreSQL 8.2 and later versions.
Note that all PostgreSQL servers involved need to have PITR enabled.
</p>
<h4>1st stage</h4>
<p>
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
</pre>
<p>
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>
<h4>2nd stage</h4>
<p>
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.
</p>
<p><span class="version">V3.1 -</span>
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
until the transaction log switching is actually finished.
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>
<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
# Force to flush current value of sequences to xlog
psql -p $port -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1|
while read i
do
if [ "$i" != "" ];then
psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i
fi
done
psql -p $port -c "SELECT pgpool_switch_xlog('$archdir')" template1
</pre>
</p>
<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.
</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
propagated to the recovery target node.
<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>
We have finished preparing online recovery by PITR.
</p>
<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.
</pre>
<p>
In this sample script, we start up the postmaster process over ssh.
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.
</p>
<pre>
#! /bin/sh
DEST=$1
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
# Startup PostgreSQL server
ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &
</pre>
<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.
So rsync can be used to do online recovery.
In the "sample" directory of pgpool-II's tarball,
there is a recovery script named "pgpool_recovery". It uses the rsync
command. pgpool-II calls the script with three arguments.
</p>
<pre>
% pgpool_recovery datadir remote_host remote_datadir
datadir: Database cluster path of a master node.
remote_host: Hostname of a recovery target node.
remote_datadir: Database cluster path of a recovery target node.
</pre>
<p>
This script copies physical files with rsync over ssh. So you need to
be able to connect over ssh without a password.
</p>
<p>
Note about rsync:
<ul>
<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>
<h2 id="perform_online_recovery">How to perform online recovery</h2>
<p>
In order to do online recovery, use the <a href="pcp_recovery_node">pcp_recovery_node</a> command or
pgpoolAdmin.
</p>
<p>
Note that you need to pass a large number to the first argument of
<a href="#pcp_recovery_node">pcp_recovery_node</a>. It is the timeout parameter in seconds. If you use
pgpoolAdmin, set "_PGPOOL2_PCP_TIMEOUT " parameter to a large number in
pgmgt.conf.php.
</p>
<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.
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>
<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>
</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>
<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.
</p>
<pre>
$ mv /usr/local/pgsql /usr/local/pgsql-old
</pre>
</li>
<li><p>
Create a symbolic link to the location where newer version of PostgreSQL installed.
This allow you to continue to use command search path you currently use.
Installing 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>
</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.
</p>
<pre>
$ ln -s /usr/local/pgsql-old/data /usr/local/pgsql/data
</pre>
</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 necessary.
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 <a href="#pcp_recovery_node">pcp_recovery_node</a> 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>
</ol>
<h3>If you are using streaming replication</h3>
<p>
You can update standby PostgreSQL server without stopping pgpool-II.
</p>
<p>
The procedure to update standby PostgreSQL servers are same as the one of replication mode.
Please refer to "Online recovery with Streaming Replication" to set up recovery_1st_stage_command and recovery_2nd_stage_command.
</p>
<p>
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>
<li>Stop primary PostgreSQL</li>
<li>Update primary PostgreSQL</li>
<li>Start primary PostgreSQL</li>
<li>Start pgpool-II</li>
</ol>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1><a name="backup"></a>Backup</h1>
<p>
To back up backend PostgreSQL servers, you can use physical
backup, logical backup (pg_dump, pg_dumpall) and PITR in the same manner as PostgreSQL.
Please note that using logical backup and PITR should be performed directory with PostgreSQL,
rather than via pgpool-II to avoid errors caused by <a href="#LOAD_BALANCE_MODE">load_balance_mode</a> and
<a href="#REPLICATE_SELECT">replicate_select</a>.
</p>
<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.
</p>
<p>
pg_dump takes ACCESS SHARE lock on database. Commands taking ACCESS
EXECUTE lock, such as ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX,
CLUSTER and VACUUM FULL will wait for the completion of pg_dump because
of lock conflict. Also this may affect the primary node even if you are
doing pg_dump on standby.
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1><a name="deploy"></a>Deploying pgpool-II</a></h1>
<p>
pgpool-II can run on a dedicated server, on the server where application
server is running on or other servers. In this section we discuss how
to make those deployments and pros and cons.
</p>
<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 buy more hardware.
Also pgpool-II can be a single point of failure with this configuration
(you can avoid this by enabling <a href="#watchdog">watchdog</a> or using pgpool-HA described described below).
</p>
</dd>
<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 instance except the <a href="#watchdog">watchdog section</a>).
</p>
<p>
We strongly recommend to enable <a href="#watchdog">watchdog</a> to avoid following concerns 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.
Otherwise 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 of which DB server they should connect to.
To solve the problem you can use virtual IP with <a href="#watchdog">watchdog</a> or pgpool-HA.
</p>
</dd>
</dl>
<h2 id="pgpool_ha">About pgpool-HA</h2>
<p>
Pgpool-HA is a high availability software for pgpool-II using heartbeat.
Pgpool-HA is a sub-project of the pgpool project as well as pgpool-II.
Pgpool-HA can be available from the pgpool development site as an open source software.
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1 id="watchdog">Watchdog <span class="version">V3.2 -</span></h1>
<h2 id="what_is_watchdog">What is watchdog</h2>
<p>
"Watchdog" is a sub process of pgpool-II to add high availability.
This resolve the single point of failure by coordinating multiple pgpool-IIs.
watchdog adds the following features to pgpool-II.
</p>
<h3>Life checking of pgpool-II</h3>
<p>
Watchdog monitors pgpool-IIs by either of two methods, "heartbeat" mode or "query" mode.
</p>
<ul>
<li>
In heartbeat mode, watchdog monitors other pgpool-II processes by using heartbeat signal.
Watchdog receives heartbeat signals sent by other pgpool-II periodically.
If there are no signal for a certain period, watchdog regards this as failure of the pgpool-II.
For redundancy you can use multiple network connections for heartbeat exchange between pgpool-IIs.
This is the default mode and recommended.
</li>
<li>
In query mode, watchdog monitors pgpool-II's service rather than process.
watchdog sends queries to other pgpool-II and checks the response.
Note that this method requires connections from other pgpool-IIs, so it would fail motoring if
<a href="#NUM_INIT_CHILDREN">num_init_children </a>isn't large enough.
This mode is deprecated and left for backward compatibility.
</li>
</ul>
<p>
Also watchdog monitors connections to upstream servers
(application servers etc.) from the pgpool-II, and checks whether the pgpool-II can serves to the servers.
If the monitoring fails, watchdog treats the pgpool-II as down.
</p>
<h3>Coordinating multiple pgpool-IIs</h3>
<p>
Watchdog coordinates multiple pgpool-IIs by exchanging information with each other.
</p>
<ul>
<li>
When backend node status changes by failover etc., watchdog notifies the information to other pgpool-IIs and synchronizes them.
When online recovery occurs, watchdog restricts client connections to other pgpool-IIs for avoiding inconsistency between backends.
</li>
<li>
Commands on failback or failover (<a href="#FAILOVER_COMMAND">failover_command</a>,
<a href="#FAILBACK_COMMAND">failback_command</a>, <a href="#FOLLOW_MASTER_COMMAND">follow_master_command</a>)
are executed by only one pgpool-II by interlocking.
</li>
</ul>
<h3>Changing active/standby state in case of certain faults detected</h3>
<p>
When a fault of pgpool-II is detected, watchdog notifies the other watchdogs of it.
If this is the active pgpool-II, watchdogs decides the new active pgpool-II by voting and change active/standby state.
</p>
<h3>Automatic virtual IP address assigning synchronous to server switching</h3>
<p>
When a standby pgpool-II server promotes to active, the new active server brings up virtual IP interface.
Meanwhile, the previous active server brings down the virtual IP interface.
This enables the active pgpool-II to work using the same IP address even when servers are switched.
</p>
<h3>Automatic registration of a server as standby in recovery</h3>
<p>
When broken server recovers or new server is attached,
the watchdog process notifies the other watchdog process along with information of the new server,
and the watchdog process receives information on the active server and other servers.
Then, the attached server is registered as a standby.
</p>
</dl>
<h2 id="watchdog_server_composition">Server composition</h2>
<p>
Figure below describes how pgpool-II and watchdog process is configured.
</p>
<p><img src="wd-en.jpg" alt="watchdog server composition" hight=70% width=70%></p>
<h2 id="start_watchdog">Starting/stopping watchdog</h2>
<p>
Watchdog process starts/stops automatically as sub-processes of pgpool-II,
therefore there is no dedicated command to start/stop it.
</p>
<p>
Watchdog requires <font color="red">root privilege</font> for controling the virtual IP interface.
One method is to start pgpool-II by root privilege.
However, for security reason, to set custom commands to <a href="#IF_UP_CMD">if_up_cmd</a>,
<a href="#IF_DOWN_CMD">if_up_cmd</a>, <a href="#ARPING_CMD">if_up_cmd</a> using sudo or setuid is recommended method.
</p>
<p>
Watchdog's life-checking starts after after all of the pgpool-IIs has started.
This doesn't start if not all "other" nodes are alive
Until this, failover of the virtual IP never occurs.
</p>
<h2>Configuring watchdog (pgpool.conf)</h2>
<p>
Watchdog configuration parameters are described in pgpool.conf.
There is sample configuration in WATCHDOG section in pgpool.conf.sample file.
</p>
<p>
All following options are required to be specified in watchdog process.
</p>
<h3>Enabling</h3>
<dl>
<dt><a name="USE_WATCHDOG"></a>use_watchdog <span class="version">V3.2 -</span></dt>
<dd>
<p>
If on, activates watchdog. Default is off.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
</dl>
<h3>Watchdog communication</h3>
<dl>
<dt><a name="WD_HOSTNAME"></a>wd_hostname <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specifies the hostname or IP address of pgpool-II server.
This is used for sending/receiving queries and packets, and also as identifier of watchdog.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="WD_PORT"></a>wd_port <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specifies the port number for watchdog communication.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="WD_AUTHKEY"></a>wd_authkey <span class="version">V3.3 -</span></dt>
<dd>
<p>
This option specifies the authentication key used in watchdog communication.
All the pgpool-II must have the same key.
Packets from watchdog of wrong key will be rejects.
This authentication is applied also for heatrbeat singals if lifecheck method is heartbeat mode.
If this is empty (default), watchdog doesn't conduct authenticate.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
</dl>
<h3>Connection to up stream servers</h3>
<dl>
<dt><a name="TRUSTED_SERVERS"></a>trusted_servers <span class="version">V3.2 -</span></dt>
<dd>
<p>
The list of trusted servers to check the up stream connections.
Each server is required to respond to ping.
Specify a comma separated list of servers such as "hostA,hostB,hostC".
If none of the server are pingable, watchdog regards it as failure of the pgpool-II.
Therefore, it is recommended to specify multiple servers.
</p>
<p>
If this option is empty, watchdog doesn't check up stream connections.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="PING_PATH"></a>ping_path <span class="version">V3.2 -</span></dt>
<dd>
<p>
This parameter specifies a path of ping command for monitoring connection
to the upper servers.
Set the only path such as "/bin".
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
</dl>
<h3>Virtual IP control</h3>
<p>
Configuration about virtual IP interface control
</p>
<dl>
<dt><a name="DELEGATE_IP"></a>delegate_IP <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specifies the virtual IP address (VIP) of pgpool-II that is connected from
client servers (application servers etc.).
When a pgpool-II is switched from standby to active, the pgpool-II takes over this VIP.
If this option is emply, virtual IP is never brought up.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="IF_CMD_PATH"></a>if_cmd_path <span class="version">V3.5 -</span></dt>
<dd>
<p>
This parameter specifies a path of a command to switch the IP address.
Set the only path such as "/sbin".
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="IF_UP_CMD"></a>if_up_cmd <span class="version">V3.2 -</span></dt>
<dd>
<p>
This parameter specifies a command to bring up the virtual IP.
Set the command and parameters such as
"ip addr add $_IP_$/24 dev eth0 label eth0:0"
$_IP_$ is replaced by the IP address specified in <a href="#DELEGATE_IP">delegate_IP</a>.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="IF_DOWN_CMD"></a>if_down_cmd <span class="version">V3.2 -</span></dt>
<dd>
<p>
This parameter specifies a command to bring down the virtual IP.
Set the command and parameters such as
"ip addr del $_IP_$/24 dev eth0"
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="ARPING_PATH"></a>arping_path <span class="version">V3.2 -</span></dt>
<dd>
<p>
This parameter specifies a path of a command to send an ARP request
after the virtual IP is switched.
Set the only path such as "/usr/sbin".
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="ARPING_CMD"></a>arping_cmd <span class="version">V3.2 -</span></dt>
<dd>
<p>
This parameter specifies a command to send an ARP request
after the virtual IP is switched.
Set the command and parameters such as
"arping -U $_IP_$ -w 1".
$_IP_$ is replaced by the IP address specified in <a href="#DELEGATE_IP">delegate_IP</a>.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
</dl>
<h3>Behaivor on escalation</h3>
<p> Configuration about behavior when pgpool-II escalates to active (virtual IP holder)
<dl>
<dt><a name="CLEAR_MEMQCACHE_ON_ESCALATION"></a>clear_memqcache_on_escalation <span class="version">V3.3 -</span></dt>
<dd>
<p>
If this is on, watchdog clears all the query cache in the shared memory when pgpool-II escaltes to active.
This prevents the new active pgpool-II from using old query caches inconsistence to the old active.
Default is on.
</p>
<p>
This works only if <a href="#MEMQCACHE_METHOD">memqcache_method</a> is 'shmem'.
</p>
</dd>
<dt><a name="WD_ESCALATION_COMMAND"></a>wd_escalation_command <span class="version">V3.3 -</span></dt>
<dd>
<p>
Watchdog executes this command on the new active when pgpool-II escaltes to active.
The timing is just before virtual IP brings up.
</p>
</dd>
</dl>
<h3>Life checking pgpool-II</h3>
<p>Watchdog checks pgpool-II status periodically. This is called "life check".
<h4>Common</h4>
<dl>
<dt><a name="WD_LIFECHECK_METHOD"></a>wd_lifecheck_method <span class="version">V3.3 -</span></dt>
<dd>
<p>
This parameter specifies the method of life check. This is either of 'heartbeat' (default) or 'query'.
</p>
<p>
In 'heartbeat' mode, watchdog sends heartbeat singals (UDP packets) periodically to other pgpool-II.
Watchdog also receives the signals from other pgpool-II.
If there are no signal for a certain period, watchdog regards is as failure of the pgpool-II.
</p>
<p>
In 'query' mode, watchdog sends monitoring queries to other pgpool-II and checks the response.
</p>
<p>
<font color="red">CAUTION: In query mode, you need to set <a href="#NUM_INIT_CHILDREN">num_init_children</a>
large enough if you plan to use watchdog. This is because the watchdog process connects to pgpool-II as a client.</font>
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="WD_INTERVAL"></a>wd_interval <span class="version">V3.2 -</span></dt>
<dd>
<p>
This parameter specifies the interval between life checks of pgpool-II
in second.
(A number greater than or equal to 1)
Default is 10.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
</dl>
<h4>Configuration of heartbeat mode</h4>
<dl>
<dt><a name="WD_HEARTBEAT_PORT"></a>wd_heartbeat_port <span class="version">V3.3 -</span></dt>
<dd>
<p>
This option specifies the port number to receive heartbeat signals.
This works only heartbeat mode.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="WD_HEARTBEAT_KEEPALIVE"></a>wd_heartbeat_keepalive <span class="version">V3.3 -</span></dt>
<dd>
<p>
This option specifies the interval time (sec.) of sending heartbeat signals.
Default is 2.
This works only heartbeat mode.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="WD_HEARTBEAT_DEADTIME"></a>wd_heartbeat_deadtime <span class="version">V3.3 -</span></dt>
<dd>
<p>
If there are no heartbeat signal for the period specified by this option, watchdog regards it as failure of the remote pgpool-II.
This works only heartbeat mode.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="HEARTBEAT_DESTINATION"></a>heartbeat_destination<font color="red">0</font> <span class="version">V3.3 -</span></dt>
<dd>
<p>
This option specifies the destination of heartbeat signals by IP address or hostname.
You can use multiple destination.
The number at the end of the parameter name is referred as "destination number",
and it starts from 0.
This works only heartbeat mode.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="HEARTBEAT_DESTINATION_PORT"></a>heartbeat_destination_port<font color="red">0</font> <span class="version">V3.3 -</span></dt>
<dd>
<p>
This option specifies the port number of destination of heartbeat signals
which is specified by <a href="HEARTBEAT_DESTINATION">heartbeat_destinationX</a>.
This is usually the same value as <a href="WD_HEARTBEAT_PORT">wd_heartbeat_port</a>
You must use another value if the port number is unusable on a certain host or
there are more than two pgpool-IIs in a host.
The number at the end of the parameter name is referred as "destination number",
and it starts from 0.
This works only heartbeat mode.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="HEARTBEAT_DEVICE"></a>heartbeat_device<font color="red">0</font> <span class="version">V3.3 -</span></dt>
<dd>
<p>
This option specifies the network device name for sending heartbeat signals to
destination specified by <a href="HEARTBEAT_DESTINATION">heartbeat_destinationX</a>.
You can use the same device for different distinations.
The number at the end of the parameter name is referred as "destination number",
and it starts from 0.
This works only heartbeat mode. This is ignored when the value is empty.
In addition, this works only when pgpool-II has root privilege and are running on Linux,
because this uses SO_BINDTODEVICE socket option.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
</dl>
<h4>Configuration of query mode</h4>
<dl>
<dt><a name="WD_LIFE_POINT"></a>wd_life_point <span class="version">V3.2 -</span></dt>
<dd>
<p>
The times to retry a failed life check of pgpool-II.
(A number greater than or equal to 1)
Default is 3.
This works only query mode.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="WD_LIFECHECK_QUERY"></a>wd_lifecheck_query <span class="version">V3.2 -</span></dt>
<dd>
<p>
Actual query to check pgpool-II.
Default is "SELECT 1".
This works only query mode.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="WD_LIFECHECK_DBNAME"></a>wd_lifecheck_dbname <span class="version">V3.3 -</span></dt>
<dd>
<p>
The database name connected for checking pgpool-II.
Default is "template1".
This works only query mode.
</p>
</dd>
<dt><a name="WD_LIFECHECK_USER"></a>wd_lifecheck_user <span class="version">V3.3 -</span></dt>
<dd>
<p>
The user name to check pgpool-II.
This user must exist in all the PostgreSQL backends.
Default is "nobody".
This works only query mode.
</p>
</dd>
<dt><a name="WD_LIFECHECK_PASSWORD"></a>wd_lifecheck_password <span class="version">V3.3 -</span></dt>
<dd>
<p>
The password of the user to check pgpool-II.
Default is "".
This works only query mode.
</p>
</dd>
</dl>
<h3>Servers to monitor</h3>
<dl>
<dt><a name="OTHER_PGPOOL_HOSTNAME"></a>other_pgpool_hostname<font color="red">0</font>
<span class="version">V3.2 -</span></dt>
<dd>
<p>
Specifies the hostname pgpool-II server to be monitored.
This is used for sending/receiving queries and packets, and also as identifier of watchdog.
The number at the end of the parameter name is referred as "server id",
and it starts from 0.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="OTHER_PGPOOL_PORT"></a>other_pgpool_port<font color="red">0</font>
<span class="version">V3.2 -</span></dt>
<dd>
<p>
Specifies the port number for pgpool service of pgpool-II server to be monitored.
In query mode, the queries specified in <a href="#WD_LIFECHECK_QUERY">wd_lifecheck_query</a> is sent to this port.
The number at the end of the parameter name is referred as "server id",
and it starts from 0.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
<dt><a name="OTHER_WD_PORT"></a>other_wd_port<font color="red">0</font> <span class="version">V3.2 -</span></dt>
<dd>
<p>
Specifies the port number for watchdog on pgpool-II server to be monitored.
The number at the end of the parameter name is referred as "server id",
and it starts from 0.
</p>
<p>
You need to restart pgpool-II if you change this value.
</p>
</dd>
</dl>
<h2 id="watchdog_restrictions">Restrictions on watchdog</h2>
<ul>
<li>
In query mode, when all the DB nodes are detached from a pgpool-II due to PostgreSQL server failure or
pcp_detach_node issued, watchdog regards that the pgpool-II service is in down status and
brings the virtual IP assigned to watchdog down. Thus clients of pgpool cannot connect to
pgpool-II using the virtual IP any more. This is neccessary to avoid <i>split-brain</i>,
that is, situations where there are multiple active pgpool-II.
</li>
<li>
Don't connect to pgpool-II in down status using the real IP. Because a pgpool-II in down status
can't receive information from watchdog, its backend status may be different from other pgpool-II.
</li>
<li>
pgpool-II in down status can't become active nor standby pgpool-II.
Recovery from down status requires restart of pgpool-II.
</li>
<li>
Please note that after the active pgpool-II stops, it will take a few seconds until the
standby pgpool-II promote to new active, to make sure that the former virtual IP is brought
down before a down notification packet is sent to other pgpool-IIs.
</li>
</ul>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1 id="pcp_command">PCP Commands</h1>
<h2>PCP commands list</h2>
<p>PCP commands are UNIX commands which manipulate pgpool-II via the network.
Please note that the parameter format for all PCP commands has been changed since pgpool-II 3.5</p>
<table border>
<tr><th><a href="#pcp_node_count">pcp_node_count</a></th>
<td>retrieves the number of nodes</td></tr>
<tr><th><a href="#pcp_node_info">pcp_node_info</a></th>
<td>retrieves the node information</td></tr>
<tr><th><a href="#pcp_watchdog_info">pcp_watchdog_info</a> <span class="version">V3.3 -</span></th>
<td>retrieves the watchdog information</td></tr>
<tr><th><a href="#pcp_proc_count">pcp_proc_count</a></th>
<td>retrieves the process list</td></tr>
<tr><th><a href="#pcp_proc_info">pcp_proc_info</a></th>
<td>retrieves the process information</td></tr>
<tr><th><a href="#pcp_pool_status ">pcp_pool_status</a> <span class="version">V3.1 -</span></th>
<td>retrieves parameters in pgpool.conf</td></tr>
<tr><th><a href="#pcp_detach_node">pcp_detach_node</a></th>
<td>detaches a node from pgpool-II</td></tr>
<tr><th><a href="#pcp_attach_node">pcp_attach_node</a></th>
<td>attaches a node to pgpool-II</td></tr>
<tr><th><a href="#pcp_promote_node">pcp_promote_node</a> <span class="version">V3.1 -</span></th>
<td>promote a new master node to pgpool-II</td></tr>
<tr><th><a href="#pcp_stop_pgpool">pcp_stop_pgpool</a></th>
<td>stops pgpool-II</td></tr>
</table>
<h2 id="pcp_comand_password">PCP connection authentication</h2>
<p>PCP user names and passwords must be declared in
<code>pcp.conf</code> in <code>$prefix/etc</code>
directory. <code>-F</code> option can be used when starting pgpool-II
if <code>pcp.conf</code> is placed somewhere else.</p>
<h3 id="password_file">The Password File</h3>
<p>The file .pcppass in a user's home directory or the file referenced by environment variable
PCPPASSFILE can contain passwords to be used if no password has been specified for the pcp connection.</p>
<p>This file should contain lines of the following format:/<p>
<pre>
hostname:port:username:password
</pre>
<p>
(You can add a reminder comment to the file by copying the line above and preceding it with #.)
Each of the first three fields can be a literal value, or *, which matches anything. The password
field from the first line that matches the current connection parameters will be used. (Therefore,
put more-specific entries first when you are using wildcards.) If an entry needs to contain : or \,
escape this character with \. A host name of localhost matches both TCP (host name localhost) and
Unix domain socket connections coming from the local machine.</p>
<p>The permissions on .pcppass must disallow any access to world or group; achieve this by the command
chmod 0600 ~/.pcppass. If the permissions are less strict than this, the file will be ignored.</p>
<h2 id="pcp_common_options">Common options</h2>
<p>There are some arguments common to all PCP commands.
Most of these are for authentication and
the rest are about verbose mode, debug message, and so on.
<pre>
e.g.) $ pcp_node_count -h localhost -p 9898 -u postgres -w -d -v
</pre>
<dl class="options">
<dt><code>
-h <strong>hostname</strong>, --host=<strong>hostname</strong>
</code></dt>
<dd><p>
The host name of the machine on which the server is running. If the
value begins with a slash, it is used as the directory for the Unix-domain socket.
</p></dd>
<dt><code>
-p <strong>port</strong>, --port=<strong>port</strong>
</code></dt>
<dd><p>
The PCP port number (default:"9898").
</p></dd>
<dt><code>
-u <strong>username</strong>, --username=<strong>username</strong>
</code></dt>
<dd><p>
The user name for PCP authentication (default: OS user name).
</p></dd>
<dt><code>
-w, --no-password
</code></dt>
<dd><p>
Never prompt for password. And if a password is not available by a .pcppass file,
the connection attempt will fail. This option can be useful in batch jobs and scripts
where no user is present to enter a password.
</p></dd>
<dt><code>
-W, --password
</code></dt>
<dd><p>
Force password prompt (should happen automatically).
</p></dd>
<dt><code>
-d, --debug
</code></dt>
<dd><p>
Enable debug message.
</p></dd>
<dt><code>
-v, --verbose
</code></dt>
<dd><p>
Enable verbose output.
</p></dd>
<dt><code>
-V, --version
</code></dt>
<dd><p>
Print the command version, then exit.
</p></dd>
<dt><code>
-?, --help
</code></dt>
<dd><p>
Shows help for the command line arguments, then exit.
</p></dd>
</dl>
<h2>PCP Commands to know status and configuration</h2>
<p>All PCP commands display the results to the standard output.</p>
<h3 id="pcp_node_count">pcp_node_count</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_node_count [options...] </code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Displays the total number of nodes defined in <code>pgpool.conf</code>. It does
not distinguish between nodes status, ie attached/detached. ALL nodes are counted.</p>
</dd>
<dt>Options:</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
<h3 id="pcp_node_info">pcp_node_info</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_node_info [options...] [node_id] </code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Displays the information on the given node ID.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-n <strong>node_id</strong>, --node-id=<strong>node_id</strong></code></dt>
<dd>
<p>The index of backend node to get information of.</p>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
</dd>
<dt>Output example:</dt>
<dd>
<pre>
$ pcp_node_info -h localhost -U postgres 0
host1 5432 1 1073741823.500000
</pre>
<p>The result is in the following order:</p>
<ol>
<li>hostname</li>
<li>port number</li>
<li>status</li>
<li>load balance weight</li>
</ol>
<p>Status is represented by a digit from [0 to 3].</p>
<ul>
<li>0 - This state is only used during the initialization. PCP will never display it.</li>
<li>1 - Node is up. No connections yet.</li>
<li>2 - Node is up. Connections are pooled.</li>
<li>3 - Node is down.</li>
</ul>
<p>The load balance weight is displayed in normalized format.</p>
<p>The <code>--verbose</code> option can help understand the output. For example:</p>
<pre>
$ pcp_node_info --verbose -h localhost -U postgres 0
Hostname: host1
Port : 5432
Status : 1
Weight : 0.5
</pre>
</dd>
</dl>
<h3><a name="pcp_watchdog_info"/></a>pcp_watchdog_info <span class="version">V3.3 -</span></h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_watchdog_info [options...] [watchdog_id]</code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Displays the watchdog status of the pgpool-II.
<code>watchdog_id</code> is the index of other_pgpool_hostname parameter in pgpool.conf.
If this is omitted, display the watchdog status of local pgpool-II.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-n <strong>watchdog_id</strong>, --node-id=<strong>watchdog_id</strong></code></dt>
<dd>
<p>The index of other pgpool-II to get information for.</p>
<p>If ommitted then gets one's self watchdog information'.</p>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
</dd>
<dt>Output example:</dt>
<dd>
<pre>
$ pcp_watchdog_info -h localhost -p 9898 -u postgres 0
host1 9999 9000 2
Here is an output example:
</pre>
<p>The result is in the following order:</p>
<ol>
<li>hostname</li>
<li>port number for pgpool-II</li>
<li>port number for watchdog</li>
<li>watchdog Status</li>
</ol>
<p>Status is represented by a digit from [1 to 4].</p>
<ul>
<li>1 - watchdog is not started</li>
<li>2 - Standby: not holding the virtual IP</li>
<li>3 - Active: holding the virtual IP</li>
<li>4 - Down</li>
</ul>
</dd>
</dl>
<h3 id="pcp_proc_count">pcp_proc_count</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_proc_count [options...] </code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>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>
</dd>
<dt>Options:</dt>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
<h3 id="pcp_proc_info">pcp_proc_info</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_proc_info [options...] [processid] </code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Displays the information on the given pgpool-II child process ID.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-P <strong>PID</strong>, --process-id=<strong>PID</strong></code></dt>
<dd>
<p>PID of pgpool-II child process.</p>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dd>
<dt>Output example:</dt>
<dd>
<pre>
$ pcp_proc_info -h localhost -p 9898 -U postgres 3815
postgres_db postgres 1150769932 1150767351 3 0 1 1467 1
postgres_db postgres 1150769932 1150767351 3 0 1 1468 1
</pre>
<p>The result is in the following order:</p>
<ol>
<li>connected database name</li>
<li>connected user name</li>
<li>process start-up timestamp</li>
<li>connection created timestamp</li>
<li>protocol major version</li>
<li>protocol minor version</li>
<li>connection-reuse counter</li>
<li>PostgreSQL backend process id</li>
<li>1 if frontend conncted 0 if not</li>
</ol>
<p>
If there is no connection to the backends, nothing will be
displayed. If there are multiple connections, one connection's
information will be displayed on each line multiple
times. Timestamps are displayed in EPOCH format.
</p>
<p>
The </code>--verbose</code> option can help understand the output. For example:
</p>
<pre>
$ pcp_proc_info --verbose -U postgres 3815
Database : postgres_db
Username : postgres
Start time : 1150769932
Creation time: 1150767351
Major : 3
Minor : 0
Counter : 1
PID : 1467
Connected : 1
Database : postgres_db
Username : postgres
Start time : 1150769932
Creation time: 1150767351
Major : 3
Minor : 0
Counter : 1
PID : 1468
Connected : 1
</pre>
</dd>
</dl>
<h3 id="pcp_pool_status">pcp_pool_status <span class="version">V3.1 -</span></h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_pool_status [options...]</code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Displays the parameter values as defined in pgpool.conf.</p>
</dd>
<dt>Options:</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
<dt>Output example:</dt>
<dd>
<pre>
$ pcp_pool_status -h localhost -U postgres
name : listen_addresses
value: localhost
desc : host name(s) or IP address(es) to listen to
name : port
value: 9999
desc : pgpool accepting port number
name : socket_dir
value: /tmp
desc : pgpool socket directory
name : pcp_port
value: 9898
desc : PCP port # to bind
</pre>
</dd>
</dl>
<h2>PCP Commands to control backend nodes, etc.</h2>
<h3 id="pcp_detach_node">pcp_detach_node</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_detach_node [options...] [node_id] [gracefully]</code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Detaches the given node from pgpool-II. Exisiting connections to pgpool-II are forced to be disconnected.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-n <strong>node_id</strong>, --node-id=<strong>node_id</strong></code></dt>
<dd>
<p>The index of backend node to detach.</p>
</dd>
<dt><code>-g, --gracefully </code></dt>
<dd>
<p>Wait until all clients are disconnected (unless
client_idle_limit_in_recovery is -1 or recovery_timeout is expired).</p>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
</dd>
</dl>
<h3 id="pcp_attach_node">pcp_attach_node</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_attach_node [options...] [node_id]</code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Attaches the given node to pgpool-II.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-n <strong>node_id</strong>, --node-id=<strong>node_id</strong></code></dt>
<dd>
<p>The index of backend node to attach.</p>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
</dd>
</dl>
<h3 id="pcp_promote_node">pcp_promote_node <span class="version">V3.1 -</span></h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_promote_node [options...] [node_id] [gracefully]</code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Promotes the given node as new master to pgpool-II. In master/slave streaming replication only. Please note that this command does not actually promote standby PostgreSQL backend: it just changes the internal status of pgpool-II and trigger failover and users have to promote standby PostgreSQL outside pgpool-II.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-n <strong>node_id</strong>, --node-id=<strong>node_id</strong></code></dt>
<dd>
<p>The index of backend node to promote as new master.</p>
</dd>
<dt><code>-g, --gracefully </code></dt>
<dd>
<p>Wait until all clients are disconnected (unless
client_idle_limit_in_recovery is -1 or recovery_timeout is expired).</p>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
</dd>
</dl>
<h3 id="pcp_stop_pgpool">pcp_stop_pgpool</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_stop_pgpool [options...] [mode]</code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Terminate the pgpool-II process.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-m <strong>mode</strong>, --mode=<strong>mode</strong></code></p>
<dd>
<p>Shutdown mode for terminating the pgpool-II process.</p>
<p>The available modes are as follows:</p>
<ul>
<li>s, smart : smart mode</li>
<li>f, fast : fast mode</li>
<li>i, immediate : immediate mode</li>
</ul>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
</dl>
<h3 id="pcp_recovery_node">pcp_recovery_node</h3>
<dl>
<dt>Format:</dt>
<dd>
<pre><code>pcp_recovery_node [options...] [node_id] </code></pre>
</dd>
<dt>Description:</dt>
<dd>
<p>Attaches the given backend node with recovery.</p>
</dd>
<dt>Options:</dt>
<dd>
<dl class="options">
<dt><code>-n <strong>node_id</strong>, --node-id=<strong>node_id</strong></code></dt>
<dd>
<p>The index of backend node.</p>
</dd>
<dt>Other options</dt>
<dd>
<p>See <a href="#pcp_common_options">common options</a>. </p>
</dd>
</dl>
</dd>
</dl>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1><a name="troubleshooting"></a>Troubleshooting</h1>
<p>
This section describes problems and their workarounds while you are using
pgpool-II.
</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
2010-07-23 16:42:57 LOG: pid 20031: starting degeneration. shutdown host foo(5432)
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>
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>
</dd>
<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>
<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 re-sync them using online recovery if you find
that they are out of sync.
</p>
</dd>
<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.
</p>
</dd>
</dl>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>Restrictions<a name="restriction"></a></h1>
<p>
<h2>Functionality of PostgreSQL</h2>
<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.
</li>
</ul>
<p>
<h2><a name="md5"></a>Authentication / Access Controls</h2>
<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 a authencitaion file ("pool_passwd").
"pool_passwd" is default name of the authentication file.
You can change the file name using <a href="#POOL_PASSWD">pool_passwd</a>.
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 pgpool 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>
</ul>
<h2>Large objects</h2>
<p>
pgpool-II 2.3.2 or later supports large object replication if the backend is
PostgreSQL 8.1 or later.
For this, you need to
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 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).
With pgpool-II 3.0 or later, SELECT on these tables is executed on master as well.
However if the temporary table name is used as a literal in SELECT, there's no
way to detect it, and the SELECT will be load balanced.
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>
<pre>
Sample SELECT which causes a problem:
SELECT 't1'::regclass::oid;
</pre>
<p>
psql's \d command uses literal table names.
pgpool-II 3.0 or later checks if the SELECT includes any access to system
catalogs and always send these queries to the master.
Thus we avoid the problem.
</p>
<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
replicated correctly on multiple backends.</p>
<p>For SERIAL, enabling insert_lock will help replicating data.
insert_lock also helps SELECT setval() and SELECT nextval().</p>
<p>In pgpool-II 2.3 or later, INSERT/UPDATE using CURRENT_TIMESTAMP,
CURRENT_DATE, now() will be replicated correctly. INSERT/UPDATE for tables
using CURRENT_TIMESTAMP, CURRENT_DATE, now() as their DEFAULT values will also
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:
<pre>
CREATE TABLE rel1(
d1 date DEFAULT CURRENT_DATE + 1
)
</pre>
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>
<pre>
foo bigint default (date_part('epoch'::text,('now'::text)::timestamp(3) with time zone) * (1000)::double precision)
</pre>
</li>
<li>Suppose we have the following table:
<pre>
CREATE TABLE rel1(
c1 int,
c2 timestamp default now()
)
</pre>
We can replicate
<pre>
INSERT INTO rel1(c1) VALUES(1)
</pre>
since this turn into
<pre>
INSERT INTO rel1(c1, c2) VALUES(1, '2009-01-01 23:59:59.123456+09')
</pre>
However,
<pre>
INSERT INTO rel1(c1) SELECT 1
</pre>
cannot to be transformed, thus cannot be properly replicated in the current implementation.
Values will still be inserted, with no transformation at all.
</li>
</ul>
<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.
</p>
<p>
For 8.2.x or earlier, <code>CREATE TEMP TABLE</code> will not be
deleted after exiting a session. It is because of the connection
pooling which, from PostgreSQL's backend point of view, keeps the
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 id="ng_queries">Queries</h2>
<p>Here are the queries which cannot be processed by pgpool-II</p>
<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, backends must be
the same.</p>
<h3 id="ng_multi_statement">Multi-statement Query (for all modes)</h3>
<p>
pgpool-II cannot process multi-statement queries.
</p>
<h2>Environment Requirements</h2>
<h3>libpq</h3>
<p><code>libpq</code> is linked while building pgpool-II. libpq
version must be 3.0. Building pgpool-II with libpq version 2.0 will
fail.</p>
<h2 id="caution_query_cache">Query Cache</h2>
<p>With <a href="#query_cache">on disk query cache, invalidation must be done manually.
This does not apply to <a href="#memqcache">in memory query cache</a>.
Cache invalidation is automatically done with on memory query cache.</p>
<p class="top_link"><a href="#Top">back to top</a></p>
<!-- ================================================================================ -->
<h1>Tutorial</h1>
<p>
<a href="tutorial-en.html">A tutorial for pgpool-II</a> is available.
</p>
<p class="top_link"><a href="#Top">back to top</a></p>
</div>
<div class="copyright" style="clear: both">
<hr>
<copyright>
Copyright © 2003 – 2015 pgpool Global Development Group
</copyright>
</div>
</body>
</html>