1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
4 <meta http-equiv="content-type" content="text/html">
5 <link rel="stylesheet" href="./pgpool.css" type="text/css">
6 <title>pgpool-II user manual</title>
10 Last modified: Tue May 12 10:01:50 JST 2009
13 <body bgcolor="#ffffff">
15 <table border="0" cellpadding="2" cellspacing="1">
18 <td colspan="2" valign="top"><div class="header_text">Welcome to pgpool -II page</div></td>
21 <td valign="top" style="border-right:1px dotted #cccccc;">
24 <div id="navcontainer">
26 <li id="active"><a href="#Whatis" id="current">What is pgpool</a></li>
27 <li><a href="#platform">Platforms</a></li>
28 <li><a href="#install">pgpool-II Installation</a></li>
29 <li><a href="#config">Configuring pgpool-II</a></li>
30 <li><a href="#start">Starting/Stopping pgpool-II</a></li>
31 <li><a href="#reload">Reloading pgpool-II configuration files</a></li>
32 <li><a href="#online-recovery">Online recovery</a></li>
33 <li><a href="#restriction">Restrictions</a></li>
34 <li><a href="#reference">References</a></li>
35 <li><a href="#internal">internal</a></li>
40 <div class="header_small" align="center">
42 [<a href="pgpool-ja.html">Japanese page</a>] </div> </td>
43 <td valign="top" style="border-left:1px dotted #cccccc;">
48 <h1>What is pgpool-II?<a name="whatis"></a></h1>
50 <p> pgpool-II is a middleware that works between PostgreSQL servers
51 and a PostgreSQL database client. It provides the following features.</p>
56 <li>Connection Pooling</li>
57 <p>pgpool-II saves connections to the PostgreSQL servers, and reuse
58 them whenever a new connection with the same properties
59 (i.e. username, database, protocol version) comes in. It reduces
60 connection overhead, and improves system's overall throughput.</p>
63 <p>pgpool-II can manage multiple PostgreSQL servers. Using the
64 replication function enables creating a realtime backup on 2 or
65 more physical disks, so that the service can continue without stopping
66 servers in case of a disk failure.</p>
69 <p>If a database is replicated, executing a SELECT query on any
70 server will return the same result. pgpool-II takes an advantage of
71 the replication feature to reduce the load on each PostgreSQL server
72 by distributing SELECT queries among multiple servers, improving
73 system's overall throughput. At best, performance improves
74 proportionally to the number of PostgreSQL servers. Load balance works
75 best in a situation where there are a lot of users executing many
76 queries at the same time.</p>
78 <li>Limiting Exceeding Connections</li>
79 <p>There is a limit on the maximum number of concurrent
80 connections with PostgreSQL, and connections are rejected after this
81 many connections. Setting the maximum number of connections, however,
82 increases resource consumption and affect system
83 performance. pgpool-II also has a limit on the maximum number of
84 connections, but extra connections will be queued instead of returning
85 an error immediately.</p>
87 <li>Parallel Query</li>
88 <p>Using the parallel query function, data can be divided
89 among the multiple servers, so that a query can be executed on all the
90 servers concurrently to reduce the overall execution time. Parallel query
91 works the best when searching large-scale data.</p>
96 <p>pgpool-II talks PostgreSQL's backend and frontend protocol, and
97 relays a connection between them. Therefore, a database application
98 (frontend) thinks that pgpool-II is the actual PostgreSQL server, and
99 the server (backend) sees pgpool-II as one of its clients. Because
100 pgpool-II is transparent to both the server and the client, an
101 existing database application can be used with pgpool-II almost
102 without a change to its sources.</p>
105 <h1>Supported Platforms<a name="platform"></a></h1>
107 <p>pgpool-II works on Linux, Solaris, FreeBSD, and most of the
108 UNIX-like architectures. Windows is not supported. Supported
109 PostgreSQL server's versions are 6.4 and higher. To use the parallel
110 query, however, 7.4 and higher must be used.</p>
113 <h1>pgpool-II Installation<a name="install"></a></h1>
116 pgpool-II can be downloaded from <a href="http://pgfoundry.org/projects/pgpool/">pgpool Development page</a>.
119 <p>Installing pgpool-II requires gcc 2.9 or higher, and GNU
120 make. Also, pgpool-II links libpq library, so it must be installed on
121 a machine used to build pgpool-II.</p>
127 After extracting the source tar ball, execute the configure script.
132 There are options that you can set if you want values other
136 <li><code>--prefix=path</code><br/>
137 pgpool-II binaries and docs will be installed to this
138 directory. Default value is <code>/usr/local</code></li>
139 <li><code>--with-pgsql=path</code><br/>
140 Top directory where PostgreSQL's client libraries are
141 installed. Default value is obtained by <code>pg_config</code></li>
153 will install pgpool-II. (If you use FreeBSD, replace make with gmake)
158 <h1>Configuring pgpool-II<a name="config"></a></h1>
160 <p>Configuration files for pgpool-II are
161 <code>/usr/local/etc/pgpool.conf</code> and
162 <code>/usr/local/etc/pcp.conf</code> by default. There are several
163 operation modes in pgpool-II. Each mode has associated functions which
164 can be enabled or disabled, and specific configuration parameters to
165 control their behaviors.</p>
170 <th>Function/Mode</th>
172 <th>Connection Pool Mode</th>
173 <th>Replication Mode</th>
174 <th>Master/Slave Mode</th>
175 <th>Parallel Query Mode</th>
179 <td>Connection Pool</td>
180 <td align="center">X</td>
181 <td align="center">O</td>
182 <td align="center">O</td>
183 <td align="center">O</td>
184 <td align="center">0</td>
189 <td align="center">X</td>
190 <td align="center">X</td>
191 <td align="center">O</td>
192 <td align="center">X</td>
193 <td align="center">(*)</td>
197 <td>Load Balance</td>
198 <td align="center">X</td>
199 <td align="center">X</td>
200 <td align="center">O</td>
201 <td align="center">O</td>
202 <td align="center">(*)</td>
206 <td>Degeneration</td>
207 <td align="center">X</td>
208 <td align="center">X</td>
209 <td align="center">O</td>
210 <td align="center">O</td>
211 <td align="center">X</td>
216 <td align="center">O</td>
217 <td align="center">O</td>
218 <td align="center">X</td>
219 <td align="center">X</td>
220 <td align="center">X</td>
224 <td>Parallel Query</td>
225 <td align="center">X</td>
226 <td align="center">X</td>
227 <td align="center">X</td>
228 <td align="center">X</td>
229 <td align="center">O</td>
233 <td>Required # of Servers</td>
234 <td align="center">1 or higher</td>
235 <td align="center">1 or higher</td>
236 <td align="center">2 or higher</td>
237 <td align="center">2 or higher</td>
238 <td align="center">2 or higher</td>
242 <td>System DB required?</td>
243 <td align="center">no</td>
244 <td align="center">no</td>
245 <td align="center">no</td>
246 <td align="center">no</td>
247 <td align="center">yes</td>
251 (*)The function of the replication and the load-balancing cannot be used for the table preserved by dividing data in Parallel Query Mode.
252 <h2>Configuring <code>pcp.conf</code></h2>
254 <p>pgpool-II provides the control interface where an administrator
255 can collect pgpool-II status, and terminate pgpool-II processes via
256 network. The <code>pcp.conf</code> is the user/password file for
257 authentication with the interface. All modes require the
258 <code>pcp.conf</code> file to be set. After installing pgpool-II,
259 <code>$prefix/etc/pcp.conf.sample</code> is created. Change the name
260 of the file to <code>pcp.conf</code> and add your username and the
265 cp $prefix/etc/pcp.conf.sample $prefix/etc/pcp.conf
268 An empty line or a line starting with "<code>#</code>" is treated as a
269 comment and will be ignored. The username and the password must be
270 written on one line using the following format:
273 username:[password encrypted in md5]
276 <code>[password encrypted in md5]</code> can be produced with the
277 <code>$prefix/bin/pg_md5</code> command.
282 password: <your password>
289 acbd18db4cc2f85cedef654fccc4a4d8
292 The <code>pcp.conf</code> file must be readable by the user who
293 executes pgpool-II.</p>
295 <h2>Configuring <code>pgpool.conf</code></h2>
297 <p>As described already, each operation mode has specific
298 configuration parameters in <code>pgpool.conf</code>. After instaling
299 pgpool-II, <code>$prefix/etc/pgpool.conf.sample</code> is
300 created. Change the name of the file to <code>pgpool.conf</code> and
304 cp $prefix/etc/pgpool.conf.sample $prefix/etc/pgpool.conf
307 An empty line or a line starting with "#" is treated as a
308 comment and will be ignored.</p>
312 <p>In the raw mode, clients simply connect to the PostgreSQL servers
313 via pgpool-II. This mode is useful for simply limiting excess
314 connections to the servers, or enabling failover with multiple
318 <dt>listen_addresses</dt>
320 <p>Specifies the address in hostname or IP address, which will
321 be accepted by pgpool-II via TCP/IP network. <code>'*'</code> accepts
322 all incoming connections. <code>''</code> disables TCP/IP
323 connections. Default is <code>'localhost'</code>. Connections via UNIX
324 domain socket are always accepted. This parameter can only be set at server start.</p>
329 <p>The port number where pgpool-II accepts connections. Default
330 is 9999. This parameter can only be set at server start.</p>
335 <p>The directory path of the UNIX domain socket accepting
336 connections for pgpool-II. Default is <code>'/tmp'</code>. Be
337 aware that the socket might be deleted by cron. We recommend to
338 set this value to <code>'/var/run'</code> or such directory.
339 This parameter can only be set at server start.</p>
344 <p>The port number where PCP process accepts
345 connections. Default is 9898. This parameter can only be set at
349 <dt>pcp_socket_dir</dt>
351 <p>The directory path of the UNIX domain socket accepting
352 connections for PCP process. Default is <code>'/tmp'</code>. Be
353 aware that the socket might be deleted by cron. We recommend to
354 set this value to <code>'/var/run'</code> or such
355 directory. This parameter can only be set at server start.</p>
358 <dt>backend_socket_dir</dt>
360 <p>The directory path of the PostgreSQL server's UNIX domain
361 socket, which is used by pgpool-II to communicate with the
362 server. Default is <code>'/tmp'</code>. This parameter can only
363 be set at server start.<p>
368 <p>PCP connection timeout value in seconds. If a client does not
369 respond within the set seconds, PCP closes the connection with
370 the client. Default is 10 seconds. 0 means no timeout. This
371 parameter can be changed on service.</p>
374 <dt>num_init_children</dt>
376 <p>The number of preforked pgpool-II server processes. Default
377 is 32. Please note that cancelling a query creates another
378 connection to the backend; thus, a query cannot be cancelled if
379 the connections are full. If you want to ensure that queries can
380 be cancelled, set this value to twice the expected
381 connections. This parameter can only be set at server start.</p>
384 <dt>child_life_time</dt>
386 <p>A pgpool-II child process' life time in seconds. When a child
387 is idle for so many seconds, it is terminated and the new child
388 is created. This parameter is a measure to prevent memory
389 leaks and other unexpected errors. Default value is 300 (5
390 minutes). 0 disables this function. Note that processes that have
391 not accepted any connections are not applicable for this.
392 You need to reload pgpool.conf if you change the value.
396 <dt>child_max_connections</dt>
398 <p>A pgpool-II child process will be terminated after so many
399 connections from clients. This parameter is useful on the server
400 if it is too busy that child_life_time and connection_life_time
402 You need to reload pgpool.conf if you change the value.
406 <dt>client_idle_limit
408 <p>Disconnect the connection to a client being idle for
409 client_idle_limit seconds since the last query has completed.
410 This is usefull for preventing for pgpool childs from being
411 ocuppied by a lazy client or TCP/IP connection between client and
412 pgpool is accidentally down. The default value for
413 client_idle_limit is 0, which means the functionality is turned
414 off. You need to reload pgpool.conf This parameter is ignored in
415 the second stage of on line recovery. if you change
416 client_idle_limit.</p>
418 <dt>authentication_timeout
420 <p>Specify the timeout for pgpool authentication. 0 disables the time
421 out, which is the default. You need to restart pgpool-II if you
422 change authentication_timeout.</p>
426 <p>The directory path of the logs. pgpool-II 2.2 or later does not
427 use this directive. In the future pgpool-II might implement its
428 own logging system and we leave this directive as it is.
432 <dt>pid_file_name</dt>
434 <p>Full path to a file which contains pgpool's process id.
435 Default is "/var/run/pgpool/pgpool.pid".
436 You need to restart pgpool-II if you change the value.
440 <dt>print_timestamp</dt>
442 <p>Add timestamps to the logs when set to true. Default is
444 This parameter can be changed on service.
445 You need to reload pgpool.conf if you change print_timestamp.
449 <dt>connection_cache</dt>
451 <p>Caches connections when set to true. Default is
455 <dt>health_check_timeout</dt>
457 <p>pgpool-II periodically tries to connect to the backends to
458 detect any errors on the servers or networks. This error check
459 procedure is called "health check". If an error is detected,
460 pgpool-II tries to perform failover or degeneration.
462 This parameter is to prevent the health check to wait for a long
463 time in a case like network cable has been disconnected. The
464 timeout value is in seconds. Default value is 20. 0 disables
465 timeout (waits until TCP/IP timeout).
467 The health check requires one (1) extra connection to each
468 backend, so <code>max_connections</code> in the
469 <code>postgresql.conf</code> needs to be incremented as
471 You need to reload pgpool.conf if you change the value.
475 <dt>health_check_period</dt>
477 <p>This parameter specifies the interval between the health
478 checks in seconds. Default is 0, which means health check is
480 You need to reload pgpool.conf if you change health_check_period.
484 <dt>health_check_user</dt>
486 <p>The user name to perform health check. This user must exist
487 in all the PostgreSQL backends.
488 You need to reload pgpool.conf if you change health_check_user.
496 This parameter specifies a command when a node is detached.
497 pgpool-II replaces special characters to backend information.
501 <tr><td>Special character</td><td>Description</td></tr>
502 <tr><td>%d</td><td>Backend ID of a detached node.</td></tr>
503 <tr><td>%h</td><td>Hostname of a detached node.</td></tr>
504 <tr><td>%p</td><td>Port number of a detached node.</td></tr>
505 <tr><td>%D</td><td>Database cluster directory of a detached node.
507 <tr><td>%M</td><td>Old master node ID.</td></tr>
508 <tr><td>%m</td><td>New master node ID.</td></tr>
509 <tr><td>%%</td><td>'%' character</td></tr>
512 You need to reload pgpool.conf if you change failover_command.
516 When a failover is performed, pgpool kills all its child processes, which
517 will in turn terminate all active sessions to pgpool. Then pgpool invoke
518 failover_command and wait for its completion.
519 After this, pgpool starts new child processes and becomes ready to wait
520 for connections from clients.
526 This parameter specifies a command when a node is attached.
527 pgpool-II replaces special characters to backend information.
531 <tr><td>Special character</td><td>Description</td></tr>
532 <tr><td>%d</td><td>Backend ID of an attached node.</td></tr>
533 <tr><td>%h</td><td>Hostname of an attached node.</td></tr>
534 <tr><td>%p</td><td>Port number of an attached node.</td></tr>
535 <tr><td>%D</td><td>Database cluster path of an attached node.
537 <tr><td>%M</td><td>Old master node</td></tr>
538 <tr><td>%m</td><td>New master node</td></tr>
539 <tr><td>%%</td><td>'%' character</td></tr>
542 You need to reload pgpool.conf if you change failback_command.
545 <dt>ignore_leading_white_space</dt>
547 <p>pgpool-II ignores white spaces at the beginning of SQL
548 queries while in the load balance mode. It is useful for using
549 APIs like DBI/DBD:Pg which adds white spaces against the user's
551 You need to reload pgpool.conf if you change the value.
555 <dt>log_statement</dt>
557 <p>Produces SQL log messages when true. This is similar to the
558 log_statement parameter in PostgreSQL. It produces logs even if the
559 debug option was not passed to pgpool-II at startup.
560 You need to reload pgpool.conf if you change the value.
564 <dt>log_hostname</dt>
567 If true, ps command status will show the client's hostname instead
568 of an IP address. Also, if log_connections is enabled, hostname will
569 be logged. You need to reload pgpool.conf if you change the value.
573 <dt>log_connections</dt>
576 If true, all incoming connections will be printed to the log.
577 You need to reload pgpool.conf if you change the value.
581 <dt>enable_pool_hba</dt>
584 If true, use pool_hba.conf for client authentication. See <a href="#hba">
585 Setting up pool_hba.conf for client authentication</a>.
586 You need to restart pgpool-II if you change the value.
590 <dt>backend_hostname</dt>
592 <p>Specifies the host name of the PostgreSQL backend. The empty
593 string (<code>''</code>) means pgpool-II uses UNIX domain
596 Multiple backends can be specified by adding a number at the end
597 of the parameter name (e.g.<code>backend_hostname0</code>). This
598 number is referred to as "DB node ID", and it starts from 0. The
599 backend which was given the DB node ID of 0 will be called
600 "Master DB". When multiple backends are defined, the service can
601 be continued even if the Master DB is down (not true in some
602 modes). In this case, the youngest DB node ID alive will be the
604 <p>If you plan to use only one PostgreSQL server, specify it by
605 <code>backend_hostname0</code>.</p>
608 This parameter can be added by reloading a configuration
609 file. However, this cannot be updated so you must restart
614 <dt>backend_port</dt>
616 <p>Specifies the port number of the backends. Multiple backends
617 can be specified by adding a number at the end of the parameter
618 name (e.g. <code>backend_port0</code>). If you plan to use only
619 one PostgreSQL server, specify it by
620 <code>backend_port0</code>.</p>
623 This parameter can be added by reloading a configuration
624 file. However, this cannot be updated so you must restart
629 <dt>backend_weight</dt>
631 <p>Specifies the load balance ratio for the backends. Multiple
632 backends can be specified by adding a number at the end of the
633 parameter name (e.g. <code>backend_weight0</code>). If you plan
634 to use only one PostgreSQL server, specify it by
635 <code>backend_weight0</code>. In the raw mode, set to 1.</p>
637 This parameter can be added by reloading a configuration
638 file. However, this cannot be updated so you must restart
643 <dt>backend_data_directory</dt>
645 <p>Specifies the database cluster directory of the
646 backends. Multiple backends can be specified by adding a number
647 at the end of the parameter name
648 (e.g. <code>backend_data_directory0</code>).
649 If you plan not to use online recovery, you do not need to
650 specify this parameter.
654 This parameter can be added by reloading a configuration
655 file. However, this cannot be updated so you must restart
663 <h4><p>Failover in the raw Mode</p></h4>
665 <p>Failover can be performed in the raw mode if multiple servers are
666 defined. pgpool-II usually accesses the backend specified by
667 <code>backend_hostname0</code> during the normal operation. If the
668 backend_hostname0 fails for some reason, pgpool-II tries to access the
669 backend specified by backend_hostname1. If that fails, pgpool-II tries
670 the backend_hostname2, 3 and so on.</p>
672 <h3>Connection Pool Mode</h3>
674 <p>In the connection pool mode, all functions in raw mode and the
675 connection pool function can be used. To enable this mode, set
676 configuration parameters in the raw mode and below.</p>
681 <p>The maximum number of cached connections in pgpool-II
682 children processes. pgpool-II reuses the cached connection if an
683 incoming connection is connecting to the same database by the
684 same username. If not, pgpool-II creates a new connection to the
685 backend. If the number of cached connections exceeds max_pool,
686 the oldest connection will be discarded, and uses that slot for
689 Default value is 4. Please be aware that the number of
690 connections from pgpool-II processes to the backend will be
691 <code>num_init_children</code> * <code>max_pool</code>.
692 This parameter can only be set at server start. </p>
695 <dt>connection_life_time</dt>
697 <p>Cached connections expiration time in seconds. The expired
698 cached connection will be disconnected. Default is 0, which
699 means the cached connections will not be disconnected.</p>
702 <dt>reset_query_list</dt>
704 <p>Specifies the SQL commands sent to the backend when exitting
705 a session to reset the connection. Multiple commands can be
706 specified by delimitting each by ";". Default is
707 the following, but can be changed to suit your system.
710 reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
714 Commands differ in each PostgreSQL versions. Here are recommended settings.
718 <tr><th>PostgreSQL version</th><th>reset_query_list value</th></tr>
719 <tr><td>7.1 or before</td><td>ABORT</td></tr>
720 <tr><td>7.2 to 8.2</td><td>ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT</td></tr>
721 <tr><td>8.3 or later</td><td>ABORT; DISCARD ALL</td></tr>
725 <li>"ABORT" is not issued when not in a transaction block for 7.4 or later.
726 <li>DISCARD ALL degrades performance since it aquires an exclusive lock on pg_listeners.
727 If there will be many concurrent sessions, you might not want to use DISCARD ALL, rather you specify each object using DISCARD command.
731 You need to re-read pgpool.conf upon modification of this direrctive.
736 <h4><p>Failover in the Connection Pool Mode</p></h4>
738 <p>Failover in the connection pool mode is the same as in the raw mode.</p>
740 <h3>Replication Mode</h3>
742 <p>This mode enables data replication between the backends. The
743 configuration parameters below must be set in addtion to everything above.</p>
748 <p>Setting true enables replication mode. Default
752 <dt>load_balance_mode</dt>
754 <p>When set to true, SELECT queries will be
755 distributed to each backend for load balance. Default is
759 <dt>replication_stop_on_mismatch</dt>
761 <p>When set to true, pgpool-II degenerates the
762 backends and keeps the service only with the Master DB if data
763 mismatch occurs. If false, pgpool-II just
764 terminates the query. Default is false.</p>
767 <a name="replicate_select">
768 <dt>replicate_select</dt>
770 <p>When set to true, pgpool-II replicate SELECTs. If false,
771 pgpool-II only sends them to Master DB. Default is false.</p>
775 replicate_select, load_balance_mode, if the SELECT query is inside an
776 explicit transaction block will affect to how replication works. Details
784 <td>SELECT is inside a transaction block</td>
796 <td>replicate_select is true</td>
808 <td>load_balance_mode is true</td>
820 <td>results(R:replication, M: send only to master
\e$B!$
\e(BL: load balance</td>
835 <p>Replicating a table with SERIAL data type, the SERIAL column
836 value may differ between the backends. This problem is avoidable by
837 locking the table explicitly (although, transactions' parallelism will
838 be lost). To achieve this, however, the following change must be made:
853 Setting <code>insert_lock</code> to true, pgpool-II
854 automatically adds the above queries each time INSERT is
855 executed (if already in transaction, simply adds LOCK TABLE
858 As of pgpool-II 2.2, it automatically detects if the table has a
859 SERIAL columns or not, so only tables having SERIAL columns are locked.
861 Also you might want to make a fine control:
865 <li>set <code>insert_lock</code> to true, and add <code>/*NO
866 INSERT LOCK*/</code> at the beginning of an INSERT
867 statement which you do not want to acquire the table
870 <li>set <code>insert_lock</code> to false, and add
871 <code>/*INSERT LOCK*/</code> at the beginning of an INSERT
872 statement which you want to acquire the table lock.</li>
876 Default value is false. If <code>insert_lock</code> is enabled,
877 the regression test for PostgreSQL 8.0 will fail in transactions,
878 privileges, rules and alter_table. The reasons for this is that
879 pgpool-II tries to LOCK the VIEW for the rule test, and others will
880 produce the following error message.
883 ! ERROR: current transaction is aborted, commands ignored until
884 end of transaction block
887 For example, the transactions test tries to INSERT into a table
888 which does not exist, and pgpool-II causes PostgreSQL to acquire the
889 lock before that. The transaction will be aborted, and the following
890 INSERT statement produces the above error message.</p>
896 This parameter specifies a PostgreSQL username for online recovery.
897 It can be changed on service.
900 <dt>recovery_password
903 This parameter specifies a PostgreSQL password for online recovery.
904 It can be changed on service.
907 <dt>recovery_1st_stage_command
910 This parameter specifies a command at the first stage of online
911 recovery. The command file must be put on database cluster directory
912 because of a security issue.
914 For example, if recovery_1st_stage_command = 'sync-command', then
915 pgpool-II executes $PGDATA/sync-command.
917 Note that pgpool-II <b>accepts</b> connections and queries while
918 recovery_1st_stage command is executed. You can retrieve and update
922 It can be changed on service.
925 <dt>recovery_2nd_stage_command
928 This parameter specifies a command at the second stage of online
929 recovery. The command file must be put on database cluster directory
930 because of a security issue.
932 For example, if recovery_2nd_stage_command = 'sync-command', then
933 pgpool-II executes $PGDATA/sync-command.
935 Note that pgpool-II <b>do not accept</b> connections and queries while
936 recovery_2nd_stage_command is executed. Thus if a client exists for a
937 long time, the command is not executed. pgpool-II waits until all
938 clients close their connections. The command is executed when no client
939 connects to pgpool-II.
942 It can be changed on service.
948 pgpool does not accept connections at second stage. If a client
949 connect to pgpool during recovery processing, pgpool wait to be
953 This parameter specifies recovery timeout in sec. If timeout, pgpool
954 cancels online recovery and accepts connections. 0 means no wait.
957 It can be changed on service.
960 <dt>client_idle_limit_in_recovery
962 <p> Similar to client_idle_limit but only takes effect in recovery 2nd
963 stage. Disconnect the connection to a client being idle for
964 client_idle_limit_in_recovery seconds since the last query has
965 been sent. This is usefull for preventing for pgpool recovery
966 disturbed by a lazy client or TCP/IP connection between client and
967 pgpool is accidentally down. The default value for
968 client_idle_limit_in_recovery is 0, which means the functionality is turned
969 off. You need to reload pgpool.conf if you change
970 client_idle_limit_in_recovery.</p>
974 <h4><p>condition for load balance</p></h4>
976 For the query to be load balanced, all the requirements below
979 <li>PostgreSQL version 7.4 or later</li>
980 <li>the query must not be in an explicitly declared
981 transaction (i.e. not in a BEGIN ~ END block)</li>
982 <li>it's not SELECT nextval or SELECT setval
983 <li>it's not SELECT INTO
984 <li>it's not SELECT FOR UPDATE nor FOR SHARE
985 <li>start with "SELECT" (ignore_leading_white_space = true will
986 ignore leading white space) or one of COPY TO STDOUT, DECLARE..SELECT, FETCH, CLOSE
990 Note that you could supress load balancing by inserting arbitary
991 comments just in front of the SELECT query:
993 /*REPLICATION*/ SELECT ...
998 Please refer to <a href="#replicate_select">replicate_select</a> as well.
999 See also a <a href="load_balance.png">flow chart</a>.
1004 Note: JDBC driver has autocommit option. If autocommit is false, JDBC driver
1005 send "BEGIN" and "COMMIT" internally. So pgpool cannot load balancing.
1006 You need to call setAutoCommit(true) to enable autocommit.
1010 <h4><p>Failover in the Replication Mode</p></h4>
1012 <p> pgpool-II degenerates a dead backend and continues the
1013 service. The service can be continued if there is at least one backend
1016 <h3>Master/Slave Mode</h3>
1018 <p>This mode is for using pgpool-II with another master/slave
1019 replication software (like Slony-I), so it really does the actual data
1020 replication. DB nodes' information must be set as the replication mode.
1021 In addtion to that, set
1022 <code>master_slave_mode</code> and <code>load_balance_mode</code> to
1023 true. pgpool-II will send queries that need to be replicated to the
1024 Master DB, and others will be load balanced if possible.</p>
1026 <p>In the master/slave mode, <code>replication_mode</code> must be set
1027 to false, and <code>master_slave_mode</code> to true.</p>
1029 <h3>Parallel Mode</h3>
1031 <p>This mode enables parallel execution of queries.The table is divided, and data can be given to each node. Moreover, the replication and the loadbalance function can be used at the same time. In parallel mode, replication_mode and loadbalance_mode are set to true in pgpool.conf, master_slave is set to false, and parallel_mode is set to true. When you change this parameter, please reactivate pgpool-II.
1034 <h4><p>Configuring the System DB</p></h4>
1036 <p>To use the parallel mode, the System DB must be configured
1037 properly. The System DB maintains rules, in the format of the database
1038 table, to choose an appropriate backend to send partitioned
1039 data. The System DB does not need to be created on the same host as
1040 pgpool-II. The System DB's configuration is done in
1041 <code>pgpool.conf</code>.</p>
1044 <dt>system_db_hostname</dt>
1046 <p>The hostname where the System DB is created. Specifying the
1047 empty string ('') means the System DB is at the same host as
1048 pgpool-II, and will be connected via UNIX domain socket.</p>
1051 <dt>system_db_port</dt>
1053 <p>The port number for the System DB</p>
1056 <dt>system_dbname</dt>
1058 <p>The partitioning rules and other information will be defined
1059 in the database specified here. Default value is
1060 <code>'pgpool'</code>.</p>
1063 <dt>system_db_schema</dt>
1065 <p>The partitioning rules and other information will be defined
1066 in the schema specified here. Default value is
1067 <code>'pgpool_catalog'</code>.</p>
1070 <dt>system_db_user</dt>
1072 <p>The username to connect to the System DB.</p>
1075 <dt>system_db_password</dt>
1077 <p>The password for the System DB. If no password is set,
1078 set the empty string ('').</p>
1082 <h4><p>Initial Configuration of the System DB</p></h4>
1084 <p>First, create the database and schema specified in the
1085 <code>pgpool.conf</code> file. A sample script can be found in
1086 <code>$prefix/share/system_db.sql</code>. If you have specified a
1087 different database name or schema, change them accordingly.
1090 psql -f $prefix/share/system_db.sql pgpool
1095 <h4><p>Registering a Partitioning Rule</p></h4>
1097 <p>The rules for data partitioning must be registered to
1098 <code>pgpool_catalog.dist_def</code> table.</p>
1101 CREATE TABLE pgpool_catalog.dist_def(
1102 dbname TEXT, -- database name
1103 schema_name TEXT, -- schema name
1104 table_name TEXT, -- table name
1105 col_name TEXT NOT NULL CHECK (col_name = ANY (col_list)), -- partitioning key column name
1106 col_list TEXT[] NOT NULL, -- names of table attributes
1107 type_list TEXT[] NOT NULL, -- types of table attributes
1108 dist_def_func TEXT NOT NULL, -- name of the partitioning rule function
1109 PRIMARY KEY (dbname,schema_name,table_name)
1114 <h4><p>Registering a Replication Rule</p></h4>
1116 When the table that does the replication of one SQL sentence that specifies the table registered in the partitioning rule with JOIN etc. is specified, information on the table that does the replication is registered in the table named pgpool_catalog.replicate_def beforehand.
1120 CREATE TABLE pgpool_catalog.replicate_def(
1121 dbname TEXT, --database name
1122 schema_name TEXT, --schema name
1123 table_name TEXT, --teble name
1124 col_list TEXT[] NOT NULL, -- names of table attributes
1125 type_list TEXT[] NOT NULL, -- types of table attributes
1126 PRIMARY KEY (dbname,schema_name,table_name)
1130 <h4><p>Example for partitioning pgbench tables</p></h4>
1133 It divides into the accounts table in this example, and the replication will be done to the branches table and the tellers table. Moreover, the accounts table and the banches table assume uniting with bid The branches table registers the rule of the replication table. When three tables of the accounts table and the branches table and the tellers table are united, it is necessary to register the rule of the replication table to the tellers table beforehand.
1136 INSERT INTO pgpool_catalog.dist_def VALUES (
1141 ARRAY['aid','bid','abalance','filler'],
1142 ARRAY['integer','integer','integer','character(84)'],
1143 'pgpool_catalog.dist_def_accounts'
1146 INSERT INTO pgpool_catalog.replicate_def VALUES (
1150 ARRAY['bid','bbalance','filler'],
1151 ARRAY['integer','integer','character(84)']
1155 <p>The partitioning rule function (namely,
1156 pgpool_catalog.dist_def_accounts) takes a value for the partitioning
1157 key column, and returns the corresponding DB node ID. Note that ID
1158 must start from 0. Below is the example of each function for pgbench.
1161 CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts (val ANYELEMENT) RETURNS INTEGER AS '
1162 SELECT CASE WHEN $1 >= 1 and $1 <= 30000 THEN 0
1163 WHEN $1 > 30000 and $1 <= 60000 THEN 1
1167 <h2><a name="hba"></a>Setting up pool_hba.conf for client authentication (HBA)</h2>
1169 Just like pg_hba.conf with PostgreSQL, pgpool supports a similar
1170 client authentication function using a configuration file called
1174 When you install pgpool, pool_hba.conf.sample will be installed in
1175 "/usr/local/etc", which is the default directory for configuration
1176 files. Copy pool_hba.conf.sample as pool_hba.conf and edit it if necessary.
1177 By default, pool_hba authentication is enabled. See "6. Setting up
1178 pgpool.conf" for more detail.
1181 The format of pool_hba.conf file follows very closely pg_hba.conf's format.
1184 local DATABASE USER METHOD [OPTION]
1185 host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
1188 See "pool_hba.conf.sample" for details of each field.
1191 Here are the limitations of pool_hba.
1193 <li>"hostssl" connection type is not supported</li>
1195 "hostssl" is invalid since pgpool currently does not support SSL
1198 <li>"samegroup" for DATABASE field is not supported</li>
1200 Since pgpool does not know anything about users in the backend server,
1201 database name is simply checked against the entries in the DATABASE
1202 field of pool_hba.conf.
1204 <li>group names following "+" for USER field is not supported</li>
1206 This is the same reason as in the "samegroup" described above. A
1207 user name is simply checked against the entries in the USER field
1210 <li>IPv6 for IP address/mask is not supported</li>
1212 pgpool currently does not support IPv6.
1214 <li>Only "trust", "reject" and "pam" for METHOD field are supported</li>
1216 Again, this is the same reason as in the "samegroup" described above.
1217 pgpool does not hold user/password information.
1221 Note that everything described in this section is about a client
1222 authentication between a client and pgpool; a client still have to go through
1223 an authentication process with PostgreSQL. As far as pool_hba is concerned,
1224 it does not matter if a user name and/or database name given by a client
1225 (i.e. psql -U testuser testdb) really exist in the backend. pool_hba only
1226 cares if a match in the pool_hba.conf is found or not.
1229 PAM authentication is supported using user information on the host where
1230 pgpool is executed. To enable PAM support in pgpool, specify "--with-pam"
1231 option to configure:
1234 configure --with-pam
1237 To enable PAM authentication, you need to create a
1238 service-configuration file for pgpool in the system's PAM
1239 configuration directory (which is usually at "/etc/pam.d"). A sample
1240 service-configuration file is installed as "share/pgpool.pam" under
1241 the install directory.
1244 <h2>Setting method of Query cache</h2>
1245 <p>The Query cache can be used in all modes in pgpool-II. The setting of pgpool.conf is set as follows.</p>
1247 enable_query_cache = true
1251 Moreover, please make the following tables in the System DB.
1254 CREATE TABLE pgpool_catalog.query_cache (
1259 create_time TIMESTAMP WITH TIME ZONE,
1260 PRIMARY KEY(hash, dbname)
1264 However, please rewrite it suitably when you use a different schema because the schema name is "pgpool_catalog" in this example.
1267 <h1>Starting/Stopping pgpool-II<a name="start"></a></h1>
1269 <p>All the backends and the System DB (if necessary) must be started
1270 before starting pgpool-II.
1274 pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file][-n][-d]
1278 <tr><td>-c</td><td>deletes query cache</tr>
1279 <tr><td>-f config_file</td><td>specifies pgpool.conf</tr>
1280 <tr><td>-a hba_file</td><td>specifies pool_hba.conf</tr>
1281 <tr><td>-F pcp_config_file</td><td>specifies pcp.conf</tr>
1282 <tr><td>-n</td><td>no daemon mode (terminal is not detached)</tr>
1283 <tr><td>-d</td><td>debug mode</tr>
1285 There are two ways to stop pgpool-II. One is via PCP command
1286 (described later) or pgpool-II command. Below is an example of the
1291 pgpool [-f config_file][-F pcp_config_file] [-m {s[mart]|f[ast]|i[mmediate]}] stop
1295 <tr><td><code>-m s[mart]</code></td><td>waits for clients to
1296 disconnect, and shutdown (default)</td></tr>
1297 <tr><td><code>-m f[ast]</code></td><td>does not wait for clients;
1298 shutdown immediately</td></tr>
1299 <tr><td><code>-m i[mmediate]</code></td><td>the same as <code>'-m
1300 f'</code></td></tr> </table>
1303 <h1>Reloading pgpool-II configuration files<a name="reload"></a></h1>
1304 <p>pgpool-II can reload configuration files without restarting it.
1308 pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file] reload
1312 <tr><td>-f config_file<br/>--config-file config-file</td><td>specifies pgpool.conf</tr>
1313 <tr><td>-a hba_file<br/>--hba-file hba_file</td><td>specifies pool_hba.conf</tr>
1314 <tr><td>-F pcp_config_file<br/>--pcp-password-file</td><td>specifies pcp.conf</tr>
1318 Please note that some configuration items are not changed with
1319 relaoding. Also configuration reflects its changes after new session starts.
1322 <h1><a name="online-recovery"></a>Online Recovery</h1>
1325 pgpool-II, with replication mode, can sync database and attach a node
1326 in service. We call that "online recovery".
1330 A recovery target node must have detached before doing online recovery.
1332 If you wish to add PostgreSQL server dynamically, add backend_hostname etc.
1333 parameters and reload pgpool.conf. pgpool-II registers a new node as a
1338 <font color="red">caution: Stop autovacuum on the master node (the
1339 first node which is up and running). Autovacuum may change the contents
1340 of the database and might cause inconsistency after online recovery if
1341 it's runnung.</font>
1345 If PostgreSQL have already started, you need to shutdown PostgreSQL
1350 pgpool-II performs online recovery in separated two phase. It has a few seconds
1351 or minutes client wait connecting to pgpool-II while a recovery
1352 node synchronizes database. It follows these steps:
1356 <li> First stage of online recovery
1357 <li> Waiting until all clients have disconnected
1359 <li> Second stage of online recovery
1360 <li> Starting up postmaster (perform pgpool_remote_start)
1366 We call the first step of data sync "first stage". We synchronize
1367 data in the first stage. In the first stage, you <b>can</b> update or
1368 retrieve all tables concurrently.
1372 You can specify a script executed at the first stage. pgpool-II
1373 passes three arguments to the script.
1376 <li> Database cluster path of a master node.
1377 <li> Hostname of a recovery target node.
1378 <li> Database cluster path of a recovery target node.
1383 We synchronize data. We call it "second
1384 stage". Before entering the second stage, pgpool-II waits until all
1385 clients have disconnected. It blocks any connection until finishing
1388 After all connections are disconnected, merge updated data between
1389 the first stage and the second stage. We perform final data
1395 Note that there is a restriction about online recovery. If pgpool-II
1396 works on multiple hosts, online recovery does not work
1397 correctly, because pgpool-II stops clients on the 2nd stage of online
1398 recovery. If there are some pgpool hosts, pgpool-II excepted for
1399 receiving online recovery request cannot block connections.
1403 <h2> Configuration for online recovery</h2>
1405 Set the following parameters for online recovery in pgpool.conf.
1408 <li> backend_data_directory
1410 <li> recovery_password
1411 <li> recovery_1st_stage_command
1412 <li> recovery_2nd_stage_command
1417 <h2> Installation of C language function</h2>
1419 You need to install the C language function for online recovery to
1420 "template1" database of all backend nodes.
1422 Source code is in pgpool-II tar ball.
1426 pgpool-II-x.x.x/sql/pgpool-recovery/
1430 Change the directory and do "make install".
1434 % cd pgpool-II-x.x.x/sql/pgpool-recovery/
1439 Then, install SQL function.
1443 % cd pgpool-II-x.x.x/sql/pgpool-recovery/
1444 % psql -f pgpool-recovery.sql template1
1448 <h2> Recovery script deployment </h2>
1450 We must deploy data sync scripts and a remote start script into
1451 database cluster($PGDATA). Sample script files are available in
1452 pgpool-II-x.x.x/sample directory.
1455 <h3>Online recovery by PITR</h3>
1457 We explain how to do online recovery by Point In Time Recovery(PITR).
1458 Note that all PostgreSQL servers need to enable PITR.
1462 We prepare a script to get base backup on a master node and copy to
1463 recovery target node on the first stage. The script is named
1464 "copy-base-backup" for example. Here is the sample script.
1474 psql -c "select pg_start_backup('pgpool-recovery')" postgres
1475 echo "restore_command = 'scp $HOSTNAME:/data/archive_log/%f %p'" > /data/recovery.conf
1476 tar -C /data -zcf pgsql.tar.gz pgsql
1477 psql -c 'select pg_stop_backup()' postgres
1478 scp pgsql.tar.gz $RECOVERY_TARGET:$RECOVERY_DATA
1482 The script generates the following recovery.conf.
1485 restore_command = 'scp master:/data/archive_log/%f %p'
1491 Then, we prepare a script to switch XLOG on the second stage.
1492 We deploy these scripts into $PGDATA.
1497 psql -c 'select pg_switch_xlog()' postgres
1501 Finally, we edit pgpool.conf.
1504 recovery_1st_stage_command = 'copy-base-backup'
1505 recovery_2nd_stage_command = 'pgpool_recovery_pitr'
1511 We have finished preparing online recovery by PITR.
1514 <h4><p>pgpool_remote_start</p></h4>
1516 The script is to start up postmaster process from remote host.
1517 pgpool-II executes as the following way.
1521 % pgpool_remote_start remote_host remote_datadir
1522 remote_host: Hostname of a recovery target.
1523 remote_datadir: Database cluster path of a recovery target.
1527 In the sample script, we start up postmaster process over ssh.
1528 So you need to connect over ssh without pass .
1532 If you recover with PITR, you need to expand a base backup. Then,
1533 postmaster automatically starts up with PITR. Then it accepts
1541 PGCTL=/usr/local/pgsql/bin/pg_ctl
1543 # Expand a base backup
1544 ssh -T $DEST 'cd /data/; tar zxf pgsql.tar.gz' 2>/dev/null 1>/dev/null < /dev/null
1545 # Startup PostgreSQL server
1546 ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &
1549 <h3>Online recovery with rsync.</h3>
1551 PostgreSQL 7.4 does not have PITR. So we use rsync to do online
1552 recovery. In "sample" directory of pgpool-II tar ball,
1553 there is a recovery script named "pgpool_recovery". It uses rsync
1554 command. pgpool-II calls the script with three arguments.
1558 % pgpool_recovery datadir remote_host remote_datadir
1559 datadir: Database cluster path of a master node.
1560 remote_host: Hostname of a recovery target node.
1561 remote_datadir: Database cluster path of a recovery target node.
1565 The script copy physical file with rsync over ssh. So you need to
1566 connect over ssh without pass .
1573 <li>-z (or --compress) option does compression before transmitting
1574 data. This will be great for slower connection, but it might be a
1575 CPU overhead for a 100M or faster connectons. In this case you
1576 might want not to use the option.
1578 <li>The latest version rsync 3.0.5 has great speed performance
1579 improvements (50% faster according to a report from pgpool-general
1586 If you use pgpool_recovery, add the following lines into pgpool.conf.
1589 recovery_1st_stage_command = 'pgpool_recovery'
1590 recovery_2nd_stage_command = 'pgpool_recovery'
1594 <h2>How to perform online recovery</h2>
1596 For doing online recovery, you use pcp_recovery_node command or
1597 operate on pgpoolAdmin.
1601 Note that you need to pass a greater number to the first argument of
1602 pcp_recovery_node. It is a timeout parameter. If you use pgpoolAdmin,
1603 set "_PGPOOL2_PCP_TIMEOUT " parameter to a greater number in
1608 <h1>Restrictions<a name="restriction"></a></h1>
1610 <h2>Authentication / Access Controls</h2>
1614 <li>In the replication mode or master/slave mode, trust, clear text
1615 password, pam methods are supported.</li>
1616 <li>In all the other modes, trust, clear text password, crypt, md5,
1617 pam methods are supported.</li>
1618 <li>pgpool-II does not support pg_hba.conf-like access controls. If
1619 the TCP/IP connection is enabled, pgpool-II accepts all the
1620 connections from any host. If needed, use iptables and such to
1621 control access from other hosts. (PostgreSQL server accepting
1622 pgpool-II connections can use pg_hba.conf, of course).</li>
1626 <h2>Functions, etc. In Replication Mode</h2>
1628 <p>There is no guarantee that the data, which returns different values
1629 each time even though the same query was executed (e.g. random number,
1630 transaction ID, OID, SERIAL, sequence, CURRENT_TIMESTAMP), will be
1631 replicated correctly on multiple backends.</p>
1633 <p>Tables created by <code>CREATE TEMP TABLE</code> will not be
1634 deleted after exitting a session. It is because of the connection
1635 pooling which, from the backend's point of view, seems that the
1636 session is still alive. To avoid this, you must explicitly drop the
1637 temporary tables by <code>DROP TABLE</code>, or use <code>CREATE TEMP
1638 TABLE ... ON COMMIT DROP</code> inside the transaction block.</p>
1642 <p>Here are the queries which cannot be processed by pgpool-II</p>
1644 <h3>INSERT (for parallel mode)</h3>
1646 <p>You cannot use <code>DEFAULT</code> with the
1647 partitioning key column. For example, if the column x in the table t
1648 was the partitioning key column,
1651 INSERT INTO t(x) VALUES (DEFAULT);
1654 is invalid. Also, functions cannot be used as the value either.
1658 INSERT INTO t(x) VALUES (func());
1661 Constant values must be used to INSERT with the partitioning
1662 key. <code>SELECT INTO</code> and <code>INSERT INTO ... SELECT</code>
1663 are also not supported.
1666 <h3>UPDATE (for parallel mode)</h3>
1668 <p>Data consistency between the backends may be lost if the
1669 partitioning key column values are updated. pgpool-II does not
1670 re-partition the updated data.</p>
1672 <p>A transaction cannot be rolled back if a query has caused an error
1673 on some backends due to the constraint violation.</p>
1675 <p>If a function is called in the <code>WHERE</code> clause, that
1676 query might not be executed correctly.
1678 e.g.) UPDATE branches set bid = 100 where bid = (select max(bid) from beances);
1682 <h3>SELECT ... FOR UPDATE (for parallel mode)</h3>
1684 <p>If a function is called in the <code>WHERE</code> clause, that
1685 query might not be executed correctly.
1687 e.g.) SELECT * FROM branches where bid = (select max(bid) from beances) FOR UPDATE;
1691 <h3>COPY (for parallel mode)</h3>
1693 <p><code>COPY BINARY</code> is not supported. Copying from files are
1694 also not supported. Only <code>COPY FROM STDIN</code> and <code>COPY
1695 TO STDOUT</code> are supported.</p>
1697 <h3>ALTER/CREATE TABLE (for parallel mode)</h3>
1699 <p>To update the partitioning rule, pgpool-II must be restarted in
1700 order to read them from the System DB.</p>
1702 <h3>Transaction (for parallel mode)</h3>
1704 <p><code>SELECT</code> statements executed inside a transaction block
1705 will be executed in the separate transaction. Here is an example:
1709 INSERT INTO t(a) VALUES (1);
1710 SELECT * FROM t ORDER BY a; <-- INSERT above is not visible from this SELECT statement
1714 <h3>Views / Rules (for parallel mode)</h3>
1717 The same definition will be created on all the backends for views and rules.
1721 SELECT * FROM a, b where a.i = b.i
1724 <code>JOIN</code>s like above will be executed within one backend, and
1725 then merged with the results returned by each backend. Views and Rules
1726 that joins across the nodes cannot be created.
1727 However, to JOIN tables that divide data only in the same node, VIEW can be made.
1728 VIEW is registered in the pgpool_catalog.dist_def table. Moreover, please register
1729 a col_name and a dist_def_func. These are used when Insert for View was used.
1732 <h3>Functions / Triggers (for parallel mode)</h3>
1734 <p>The same definition will be created on all the backends for
1735 functions. Joins across the nodes, and data on the other nodes cannot
1736 be manipulated inside the functions.</p>
1738 <h3>Extended Query Protocol (for parallel mode)</h3>
1740 <p>The extended query protocol used by JDBC drivers, etc. is not
1741 supported. The simple query protocol must be used.</p>
1743 <h3>Natural Join (for parallel mode)</h3>
1745 <p>The Natural Join is not supported.
1746 "ON join condition" or "USING (join_column)" must be needed.</p>
1748 <h3>USING CLAUSE (for parallel mode)</h3>
1750 <p> The USING CLAUSE is converted to ON CLAUSE by query rewrite process.
1751 Therefore, when "*" is used at target list, the row of the same column name appears.</p>
1753 <h3>Multi-byte Characters (for all modes)</h3>
1755 <p>pgpool-II does not translate between different multi-byte
1756 characters. The encoding for the client, backend and System DB must be
1759 <h3>Multi-statement Query (for all modes)</h3>
1761 pgpool-II cannot process multi-statement query.
1764 <h3>Deadlocks (for parallel mode)</h3>
1766 <p>Deadlocks across the backends cannot be detected. For example:
1769 (tellers table is partitioned using the following rule)
1770 tid <= 10 --> node 0
1771 tid >= 10 --> node 1
1775 A) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;
1776 B) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
1777 A) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
1778 B) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;
1781 In the case above, a single node cannot detect the deadlock, so
1782 pgpool-II will still wait for the response. This phenomenon
1783 occurs with other queries that acquire row level locks.
1785 <p>Also, if a deadlock occurs in one node, transaction states in each
1786 node will not be consistent. Therefore, pgpool-II terminates the
1787 process if a deadlock is detected.
1790 pool_read_kind: kind does not match between master(84) slot[1] (69)
1793 <h3>Schemas (for parallel mode)</h3>
1795 <p>Objects in a schema other than public must be fully qualified like:
1801 pgpool-II cannot resolve the correct schema when the path was set as
1805 set search_path = xxx
1808 and the schema name was omitted in a query.
1810 <h3>table name - column name(for parallel mode)</h3>
1812 Limitation object:Parallel mode
1815 A table or a column name cannot starts by pool_.
1816 When rewriting the query, the name is used by internal processing.
1822 <h3>Partitioning Rules</h3>
1824 <p>Only one (1) partitioning key column can be defined in one
1825 partitioning rule. Conditions like 'x or y' are not supported.</p>
1827 <h2>Environment Requirements</h2>
1831 <p><code>libpq</code> is linked while building pgpool-II. libpq
1832 version must be 3.0. Building pgpool-II with libpq version 2.0 will
1833 fail. Also, the System DB must be PostgreSQL 7.4 or later.</p>
1835 <h2>Query Cache</h2>
1837 <p>Currently, query cache must be deleted manually. pgpool-II does not
1838 invalidate old query cache automatically when the data is updated.</p>
1840 <h2>Compatibility with pgpool</h2>
1842 <h1>References<a name="reference"></a></h1>
1843 <h2>PCP Command Reference</h2>
1845 <h3>PCP Command List</h3>
1847 <p>PCP commands are UNIX commands which manipulate pgpool-II via network.
1850 * pcp_node_count - retrieves the number of nodes
1851 * pcp_node_info - retrieves the node information
1852 * pcp_proc_count - retrieves the process list
1853 * pcp_proc_info - retrieves the process information
1854 * pcp_systemdb_info - retrieves the System DB information
1855 * pcp_detach_node - detaches a node from pgpool-II
1856 * pcp_attach_node - attaches a node to pgpool-II
1857 * pcp_stop_pgpool - stops pgpool-II
1862 <h2>Common Command-line Arguments</h2>
1864 <p>There are five arguments common to all of the PCP commands. They
1865 give information about pgpool-II and authentication. Extra
1866 arguments may be needed for some commands.
1869 e.g.) $ pcp_node_count 10 localhost 9898 postgres hogehoge
1871 First argument - timeout value in seconds. PCP disconnects if
1872 pgpool-II does not respond in so many seconds.
1873 Second argument - pgpool-II's hostname
1874 Third argument - pgpool-II's port number for PCP server
1875 Fourth argument - PCP username
1876 Fifth argument - PCP password
1879 <p>PCP usernames and passwords must use ones in the
1880 <code>pcp.conf</code> in <code>$prefix/etc</code>
1881 directory. <code>-F</code> option can be used when starting pgpool-II
1882 if <code>pcp.conf</code> is placed somewhere else. The password does
1883 not need to be in md5 format when passing it to the PCP commands.</p>
1886 <h2>PCP Commands</h2>
1888 <p>All PCP commands display the results to the standard output.</p>
1891 <h3>pcp_node_count</h3>
1895 pcp_node_count _timeout_ _host_ _port_ _userid_ _passwd_
1899 Displays the number of total nodes defined in <code>pgpool.conf</code>. It does
1900 not distinguish nodes status, ie attached/detached. ALL nodes are counted.
1904 <h3>pcp_node_info</h3>
1908 pcp_node_info _timeout_ _host_ _port_ _userid_ _passwd_ _nodeid_
1912 Displays the information on the given node ID. The output example is
1917 $ pcp_node_info 10 localhost 9898 postgres hogehoge 0
1918 host1 5432 1 1073741823.500000
1920 The result is in the following order:
1924 4. load balance weight
1926 Status is represented by a digit from [0 to 3].
1927 0 - This state is only used during the initialization. PCP will never display it.
1928 1 - Node is up. No connections yet.
1929 2 - Node is up. Connections are pooled.
1933 The load balance weight is displayed in normalized format.
1936 <p>Specifying an invalid node ID will result in an error with exit
1937 status 12, and BackendError will be displayed.</p>
1939 <h3>pcp_proc_count</h3>
1943 pcp_proc_count _timeout_ _host_ _port_ _userid_ _passwd_
1946 Displays the list of pgpool-II child process IDs. If there is more than
1947 one process, IDs will be delimitted by a white space.
1949 <h3>pcp_proc_info</h3>
1953 pcp_proc_info _timeout_ _host_ _port_ _userid_ _passwd_ _processid_
1956 Displays the information on the given pgpool-II child process ID. The
1957 output example is as follows:
1960 $ pcp_proc_info 10 localhost 9898 postgres hogehoge 3815
1961 postgres_db postgres 1150769932 1150767351 3 0 1
1963 The result is in the following order:
1964 1. connected database name
1965 2. connected username
1966 3. process start-up timestamp
1967 4. connection created timestamp
1968 5. protocol major version
1969 6. protocol minor version
1970 7. connection-reuse counter
1973 If there is no connection to the backends, nothing will be
1974 displayed. If there are multiple connections, one connection's
1975 information will be displayed on each line multiple
1976 times. Timestamps are displayed in EPOCH format.
1979 <p>Specifying an invalid node ID will result in an error with exit
1980 status 12, and BackendError will be displayed.</p>
1982 <h3>pcp_systemdb_info</h3>
1986 pcp_systemdb_info _timeout_ _host_ _port_ _userid_ _passwd_
1989 Displays the System DB information. The output example is as follows:
1992 $ pcp_systemdb_info 10 localhost 9898 postgres hogehoge
1993 localhost 5432 yamaguti '' pgpool_catalog pgpool 3
1994 yamaguti public accounts aid 4 aid bid abalance filler integer integer integer character(84) dist_def_accounts
1995 yamaguti public branches bid 3 bid bbalance filler integer integer character(84) dist_def_branches
1996 yamaguti public tellers bid 4 tid bid tbalance filler integer integer integer character(84) dist_def_tellers
1998 First, the System DB information will be displayed on the first
1999 line. The result is in the following order:
2003 4. password. '' for no password.
2006 7. number of partioning rules defined
2009 Second, partioning rules will be displayed on the following lines. If
2010 there are multiple definitions, one definition will be displayed on
2011 each line multiple times. The result is in the following order:
2014 1. targeted partitioning database name
2015 2. targeted partitioning schema name
2016 3. targeted partitioning table name
2017 4. partitioning key column name
2018 5. number of columns in the targeted table
2019 6. column names (displayed as many as said in 5.)
2020 7. column types (displayed as many as said in 5.)
2021 8. partitioning rule function name
2024 If the System DB is not defined (i.e. not in pgpool-II mode, and query
2025 cache is disabled), it results in error with exit status 12, and
2026 BackendError will be displayed.</p>
2029 <h3>pcp_detach_node</h3>
2032 pcp_detach_node _timeout_ _host_ _port_ _userid_ _passwd_ _nodeid_
2035 Detaches the given node from pgpool-II.
2039 <h3>pcp_attach_node</h3>
2043 pcp_attach_node _timeout_ _host_ _port_ _userid_ _passwd_ _nodeid_
2045 Attaches the given node to pgpool-II.
2049 <h3>pcp_stop_pgpool</h3>
2052 pcp_stop_pgpool _timeout_ _host_ _port_ _userid_ _passwd_ _mode_
2056 Terminate pgpool-II process with the given shutdown mode. The availabe
2057 modes are as follows:
2066 If pgpool-II process does not exist, it results in error with exit
2067 status 8, and ConnectionError will be displayed.
2070 * Currently, there is no difference between the fast and immediate
2071 mode. pgpool-II terminates all the processes wheter there are clients
2072 connected to the backends.</p>
2075 <h2>Exit Status</h2>
2077 <p>PCP command exits with status 0 when everything goes well. If any
2078 error had occured, it will exit with the following error status.
2081 UNKNOWNERR 1 Unknown Error (should not occur)
2083 NOMEMERR 3 Memory shortage
2084 READERR 4 Error while reading from the server
2085 WRITEERR 5 Error while writing to the server
2086 TIMEOUTERR 6 Timeout
2087 INVALERR 7 Argument(s) to the PCP command was invalid
2088 CONNERR 8 Server connection error
2089 NOCONNERR 9 No connection exists
2090 SOCKERR 10 Socket error
2091 HOSTERR 11 Hostname resolution error
2092 BACKENDERR 12 PCP process error on the server (specifying an invalid ID, etc.)
2093 AUTHERR 13 Authorization failure
2097 <h1>Inside information<a name="internal"></a></h1>
2099 On The pgpool-II version 2.0.x, Extensive modifications are added compared with the version 1.x
2100 Please note incompatibility with information on the version 1.x.
2103 <h2>Parallel execution engine</h2>
2105 The parallel execution engine is built into pgpool-II.
2106 This engine inquires same Query as each node, and indicates the engine that transmits the result to the front end in order of the response of the node.
2109 <h2>Query Rewriting</h2>
2111 It explains the Query rewriting that pgpool-II does in a parallel mode.
2114 In a parallel mode, the inquiry of the retrieval system (SELECT processing) that the client transmitted is following it divided roughly Two processing is done.
2121 I will sequentially explain these two processing.
2123 <h3>Analysis of Query</h3>
2124 <h4><p>Introduction</p></h4>
2126 The inquiry of the retrieval system that the client transmitted analyzes Query
2127 based on information registered in system DB after SQL parser is passed. It
2128 evaluates it to the analysis of Query by the transition of the execution status.
2129 The execution status is the one that the set of a certain data acquires where or
2130 it is treatable is judged here. For instance, because data is divided as for
2131 the entire data sets of tables registered in the pgpool_catalog.dist_def table,
2132 it is necessary to acquire it from all nodes. Oppositely, the entire data sets
2133 of tables registered in the pgpool_catalog.replicate_def table are enough if
2134 it does not acquire from all nodes but it acquires it from either of the
2135 nodes. The state that should be processed here by all nodes The state of
2136 P and the state that should be processed by one node It defines it as a
2137 state of L. As special ..another.. There is a state of S. This shows
2138 the state when processing it to all data acquired from all nodes. For
2139 instance, the sorting application. After data is acquired from all
2140 nodes, it is necessary to execute the sorting application to the data of
2141 the table registered in the pgpool_catalog.dist_def table.
2145 Retrieval system Query is analyzed in the following order of
2146 processing, and the execution status changes. In the process to which
2147 the execution status changes the following processing : as for the state
2148 of S It enters the state of S. Also whether it is processed with DB
2149 where is decided by the state of the final execution status of the last
2154 <li>Whether UNION, EXTRACT, and INTERCECT are used or not?
2155 <li>Execution status of FROM Clause
2156 <li>Change in execution status by TARGETLIST
2157 <li>Change in execution status accouding to WHERE Clause
2158 <li>Change in execution status according to GROUP BY Clause
2159 <li>Change in execution status according to HAVING Clause
2160 <li>Change in execution status according to ORDER BY Clause
2161 <li>It changes into the LIMIT OFFSET predicate in the execution status.
2162 <li>Acquisition of the final execution status of SELECT
2166 The relation between the final execution status of SELECT and the processed place is as follows.
2171 <tr><td>Execution status</td><td>Processed place</td></tr>
2172 <tr><td align=center>L</td><td>It inquires of either of node. </td></tr>
2173 <tr><td align=center>P</td><td>It returns it to the client through all the same node inquiries and a parallel execution engines.</td></tr>
2174 <tr><td align=center>S</td><td>After it processes it with system DB, it returns it to the client. </td></tr>
2179 Moreover, the above-mentioned rule adjusts to Sub-Query. In simple following Query, When p1-table is registered in pgpool_catalog.dist_def table of system DB, that is, when data is divided, the final execution status of Sub-Query : It becomes P, and call origin of Sub-Query as a result Execution status of SELECT also : It becomes P
2183 SELECT * FROM (SELECT * FROM P1-table) as P2-table;
2187 Next, it explains how the execution status changes concretely. Two first of all. It explains from the execution status of the From .
2190 <h4><p>Execution status of FROM Clause</p></h4>
2192 Retrieval system Query (SELECT) The set of data is defined according to the FROMCluase. ..the state of P and the state of L.. The state of S is taken. The execution status of the table : simply when the table specified for the FROM Clause is one It becomes the execution status of the entire data set composed of the FROM Cluase. The execution status is decided according to the JOIN method as follows when there is two or more tables or Sub-Query in the FROM Clause.
2197 <tr><td>JOIN type</td><td align = center colspan = 3> LEFT OUTER JOIN </td><td align = center colspan = 3> RIGHT OUTER JOIN </td><td align = center colspan = 3>FULL OUTER JOIN</td><td align = center colspan = 3>
\e$B!!!!!!
\e(Bothers
\e$B!!!!!!
\e(B</td></tr>
2198 <tr><td align = center>left
\e$B!@
\e(Bright</td><td> P </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td></tr>
2200 <tr><td align = center> P </td><td> S </td><td> P </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> P </td><td> S </td></tr>
2202 <tr><td align = center> L </td><td> S </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td><td> S </td><td> L </td><td> S </td><td> P </td><td> L </td><td> S </td></tr>
2204 <tr><td align = center> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td><td> S </td></tr>
2211 In the following examples, P1-table with the table in the state of P :
2212 L1-table and L2-table : It is assumed that the table in the state of L.
2214 P1-table (left) and L1-table (right) according to the above-mentioned table then JOIN : In addition ..entering the state of P.., With the state of P L2-table in the state of L joins and the execution status of the FROM Clause It enters the state of P.
2218 SELECT * FROM P1-table,L1-table,L2-table;
2221 <h4><p>Change in execution status by TARGETLIST and Where Clause</p></h4>
2223 In a basic Query, the same execution status as the FROM Clause is succeeded to. However, with TARGETLIST, te execution status of the WHERE Clause changes in the following cases.
2226 <li>When there is a subQuery
2227 <li>The FROM Clause When and, there are an aggregate function and DISTINCT in TARGETLIST for P
2228 <li>When the column that does not exist in the table (data set)
2229 defined by the FROM Clause is used
2232 The final execution status of the subQuery The execution status of TARGETLIST and the WHERE Clause enters the state of S for the state of P or S. In the following example, when the table used by the subQuery is P, the final execution status of the subQuery : It enters the state of P. Therefore, The execution status of the WHERE Clause : without depending on the execution status of L1-table It enters the state of S, and the execution place of this Query becomes system DB.
2235 SELECT * FROM L1-table where L1-table.column IN (SELECT * FROM P1-table);
2238 The FROM Clause It changes in the state of S to have to total it after data is acquired when and, there is an aggregate function in TARGETLIST for P. Moreover, optimization by the aggregate function is done under a specific condition.
2241 The column that does not exist in the table and the subQuery defined by the FROM Clause might be used for the WHERE Clause. This is generated in following correlation Sub-Query.
2244 SELECT * FROM L1-table FROM L1-table.col1 IN (SELECT * FROM P1-table WHERE P1-table.col = L1-table.col1);
2247 It is used for the above-mentioned Sub-Query External refer to L1-table for L1-table.col1. The execution status of the WHERE Clause of the Sub-Query : in this case It enters the state of S.
2249 <h4><p>Change in execution status by GROUP BY, HAVING, ORDER BY and LIMIT OFFSET</p></h4>
2252 The execution status of the WHERE Clause It changes in the state of S when there are GROUP BY, HAVING Clause, and ORDER BY Clause and LIMIT OFFSET predicate for P. The Query where the GROUP BY Clause does not exist succeeds to the execution status of the WHERE Clause. Moreover, the execution status of the GROUP BY Clause is succeeded to when there is no HAVING Clause. The ORDER BY Clause and the LIMIT OFFSET predicate are also similar.
2255 <h4><p>When UNION, EXTRACT, and INTERSECT are used</p></h4>
2257 The Query that UNION, EXTRAT, and INTERSECT use depends on the final execution status of a left SELECT sentence and right SELECT sentence. The final execution status of a left, right SELECT sentence both : It enters the state of L at the state of L. Moreover, the final execution status of a left, right SELECT sentence both : For the state of P and UNION ALL It enters the state of P. It enters the state of S for other combinations.
2259 <h4><p>Acquisition of the final execution status of SELECT</p></h4>
2261 Everything the execution status Everything ..the state of L.. for L It enters the state of P for P. Besides, it enters the state of S. The load is distributed when loadbalance_mode of pgpool.conf is true for L, and it inquires of MASTER besides. Moreover, the parallel processing is done for P with a parallel execution engine. The Query rewriting at S that is the following phase is done.
2264 <h3>Query rewriting</h3>
2266 The Query is rewritten by using the execution status acquired in an analyzing phase of the Query. As an example the state of P With P1-table the state of L It explains by the Query that uses L1-table.
2269 SELECT P1-table.col, L1-table.col FROM P1-table,L1-table where P1-table.col = L1-table.col order by P1-table.col;
2273 In this Query Because there is ORDER BY Clause It enters the state of S, and the FROM Clause, the WHERE Clause, and TARGETLIST : It enters the state of P. It is rewritten in such a Query as follows.
2277 SELECT P1-table.col, L1-table.col FROM
2278 dblink(select pool_parallel(SELECT P1-table.col, L1-table.col FROM P1-table,L1-table where P1-table.col = L1-table.col))
2279 order by P1-table.col;
2282 the dblink transmits the inquiry to pgpool-II here. Moreover, pool_parallel is the function which send the Query of arguments to the parallel execution engine. The above-mentioned is an image to the end and no actually executable Query.
2285 The FROM Clause, the WHERE Clause, and TARGETLIST all : like the above-mentioned example The parallel processing is done bringing the FROM Clause, the WHERE Clause, and TARGETLIST together for P.
2288 The following example is seen.
2292 SELECT L1-table.col FROM L1-table WHERE L1-table.col % 2 = 0 AND L1-table.col IN (SELECT P1-table FROM P1-table) ;
2296 In this example, the FROM Clause : The state of L and TARGETLIST also : The state of L and the WHERE Clause : Because it has the subQuery in the state of P It enters the state of S. As for this, rewriting is done as follows.
2299 SELECT L1-table.col FROM dblink(SELECT loadbalance(SELECT L1-table.col FROM L1-table WHERE L1-table.col % 2 = 0 AND TRUE))
2301 L1-table.col %2 = 0 AND
2304 SELECT P1-Table FROM
2305 dblink(select pool_parallel(SELECT P1-table FROM P1-table))
2309 Here, pool_loadbalance is a function that transmits Queries to either of node.
2312 <h3>Query rewriting by aggregate functions</h3>
2314 As for the totaled Query (aggregate function and GROUP BY), it calculates, and the decreasing performance also improves the load of system DB to each node by recounting it with system DB.
2317 First of all, rewriting the Query that pgpool-II actually does first is seen.
2320 The Query which have state P in FROM Clause and count(*) in TARGETLIST, The Rewriting Query is done as follows
2323 select count(*) from P1-table;
2328 sum(pool_c$1) as count
2330 dblink(select pool_parallel('select count(*) from P1-table'))
2331 AS pool_$1g (pool_c$1 bigint);
2334 The condition that the Query rewriting like the above-mentioned is done is the following.
2337 <li>The FROM Clause is in the state of P.
2338 <li>The column specified for the aggregate function (Only count, sum, min, max, and avg correspond) and GROUP BY is used for the target list.
2339 <li>The WHERE Clause is in the state of P.
2340 <li>Only the column defined by the aggregate function (Only count, sum, min, max, and avg correspond) used for the HAVING Clause and the FROM Clause and the column specified for GROUP BY are used.
2342 <h3>Notes of parallel mode</h3>
2344 When the Query is analyzed, the column name and the type are needed in a parallel mode. Therefore, when the expression and the function are used for TARGETLIST of the subQuery, it is necessary to give the alias and the type name in Cast. Please note processing as the text type when there are no Cast of the type in the expression and the function. As for count, when the Query rewriting by the case of the aggregate function and consolidating is done, the bigint type and sum become numeric types. It is calculated as a date type when the argument is a date type for min and max, and, besides, it is calculated as numeric. Avg is processed as sum/count.
2347 <h3>About the performance of a parallel mode</h3>
2348 <p>The final execution status of SELECT and a rough standard of the performance are as follows.
2352 <tr><td>Execution status</td><td>Performance</td></tr>
2353 <tr><td align = center>L</td><td>There is no performance deterioration with a single purpose node excluding the overhead of pgpool-II because it does not use a parallel Query. </td></tr>
2354 <tr><td align = center>P</td><td>The parallel processing is done with high-speed, especially the sequential scanning. Moreover, it becomes easy to get on cash because the size of the table becomes small by dividing data. </td></tr>
2355 <tr><td align = center>S</td><td>When the Query rewriting by aggregate functions is done, it is fast. </td></tr>
2362 <a href="tutorial-en.html">A tutorial for pgpool-II</a> is available.
2365 <div class="copyright">
2368 Copyright © 2003 – 2008 PgPool Global Development Group