What is pgpool-II?

service · DEPRECATED

  ./configure

  make
  make install

  cd pgpool-II-x.x.x/sql/pgpool-regclass
  make
  make install
  psql -f pgpool-regclass.sql template1

! cd pgpool-II-x.x.x/sql/pgpool-walrecrunning
! make
! make install
! psql -f pgpool-walrecrunning.sql template1

  ./configure

  make
  make install

  cd pgpool-II-x.x.x/sql/pgpool-regclass
  make
  make install
  psql -f pgpool-regclass.sql template1

! cd pgpool-II-x.x.x/sql
! psql -f insert_lock.sql template1

  cp $prefix/etc/pgpool.conf.sample $prefix/etc/pgpool.conf

! 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)
! 	   
! 	local0.*    /var/log/pgpool.log
! 	
!       reset_query_list = 'ABORT; DISCARD ALL'
!       
! white_function_list = 'get_.*,select_.*'
! 
! black_function_list = 'nextval,setval,set_.*,update_.*,delete_.*,insert_.*'
! 
! white_function_list = ''
! black_function_list = 'nextval,setval,lastval,currval'
! 
!       INSERT INTO ...
!       
!       BEGIN;
!       LOCK TABLE ...
!       INSERT INTO ...
!       COMMIT;
!       
!       ! ERROR: current transaction is aborted, commands ignored until
!       end of transaction block
!       
  CREATE TABLE public.my_lock_table ();
  GRANT ALL ON public.my_lock_table TO PUBLIC;

!   /*REPLICATION*/ SELECT ...

  cp $prefix/etc/pgpool.conf.sample $prefix/etc/pgpool.conf

! 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)
! 
! local0.*    /var/log/pgpool.log
! 
! "pool_search_relcache: cache replacement happened"
! 
! reset_query_list = 'ABORT; DISCARD ALL'
! 
! white_function_list = 'get_.*,select_.*'
! 
! black_function_list = 'nextval,setval,set_.*,update_.*,delete_.*,insert_.*'
! 
! white_function_list = ''
! black_function_list = 'nextval,setval,lastval,currval'
! 
! INSERT INTO ...
! 
! BEGIN;
! LOCK TABLE ...
! INSERT INTO ...
! COMMIT;
! 
! ! ERROR: current transaction is aborted, commands ignored until
! end of transaction block
! 
  CREATE TABLE public.my_lock_table ();
  GRANT ALL ON public.my_lock_table TO PUBLIC;

! /*REPLICATION*/ SELECT ...

! =# 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
! 
! 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
! 
  $ 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.
  #
--- 1793,1927 ----
  Please restart pgpool-II if you change any of the above parameters.

! You can set white_function_list and
! black_function_list to control load
  balancing in master/slave mode.
! See white_function_list for more details.

! back to top
! 
! 
! 
! Streaming Replication V3.1 -
! 
! As stated above, pgpool-II can work together with Streaming Replication, which
! is available since PostgreSQL 9.0. To use it, enable 'master_slave' and
! set 'master_slave_sub_mode' to 'stream'.
! pgpool-II assumes that Streaming Replication is used with Hot Standby at present,
! which means that the standby database is open read-only.
! The following directives can be used with this mode:
! 
! 
! 
! 

! delay_threshold V3.0 -
!     
!     
!     Specifies the maximum tolerated replication delay of the standby against the
!     primary server in WAL bytes.
!     If the delay exceeds delay_threshold, pgpool-II does not send SELECT queries to
!     the standby server anymore. Everything is sent to the primary server
!     even if load balance mode is enabled, until the standby has caught-up.
!     If delay_threshold is 0 or sr checking is disabled, the delay
!     checking is not performed. This check is performed every 'sr_check_period'.
!     The default value for delay_threshold is 0.
!     
!     
!     You need to reload pgpool.conf if you change this directive.
!     
!     
! 
! sr_check_period V3.1 -
!     
!     
!     This parameter specifies the interval between the streaming replication
!     delay checks in seconds. Default is 0, which means the check is disabled.
!     
!     
!     You need to reload pgpool.conf if you change sr_check_period.
!     
!     
! 
! sr_check_user V3.1 -
!     
!     
!     The user name to perform streaming replication check. This user must
!     exist in all the PostgreSQL backends.
!     Otherwise, the check causes an error.
!     Note that sr_check_user and sr_check_password are used even sr_check_period is 0.
!     To identify the primary server, pgpool-II sends function call request to each backend.
!     sr_check_user and sr_check_password are used for this session.
!     
!     
!     You need to reload pgpool.conf if you change sr_check_user.
!     
!     
! 
! sr_check_password V3.1 -
!     
!     
!     The password of the user to perform streaming replication check.
!     If no password is required, specify empty string('').
!     
!     
!     You need to reload pgpool.conf if you change sr_check_password.
!     
!     
! 
! log_standby_delay V3.1 -
!     
!     
!     Specifies how to log the replication delay.
!     If 'none' is specified, no log is written.
!     If 'always', log the delay every time health checking is performed.
!     If 'if_over_threshold' is specified, the log is written when the delay
!     exceeds delay_threshold.
!     The default value for log_standby_delay is 'none'.
!     You need to reload pgpool.conf if you change this directive.
!     
!     
!     You could monitor the replication delay by using the "show pool_status"
!     command as well.
!     The column name is "standby_delay#"(where '#' should be replaced by DB node id).
!     
!     
! 

! 
! Failover with Streaming Replication

  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.

  Caution: If you plan to use multiple standby nodes, we recommend
! to set a delay_threshold to prevent any query directed to other standby
! nodes from retrieving older data.

  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.
+ 

  How to setup a failover configuration is as follows.

!     Put a failover script somewhere (for example /usr/local/pgsql/bin) and give it execute permission.
    $ 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.
  #
***************
*** 1510,1516 ****

  # Do nothing if standby goes down.
  if [ $failed_node = 1 ]; then
! 	exit 0;
  fi

  # Create the trigger file.
--- 1934,1940 ----

  # Do nothing if standby goes down.
  if [ $failed_node = 1 ]; then
!     exit 0;
  fi

  # Create the trigger file.
***************
*** 1520,1564 ****

  chmod 755 failover_stream.sh

! 
Set failover_commmand in pgpool.conf.
    failover_command = '/usr/local/src/pgsql/9.0-beta/bin/failover_stream.sh %d %H /tmp/trigger_file0'

! 
Set recovery.conf on the standby node.
! A sample recovery.conf 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.
    standby_mode = 'on'
  primary_conninfo = 'host=name of primary_host user=postgres'
  trigger_file = '/tmp/trigger_file0'

! 
Set postgresql.conf on the primary node.
! Below is just an example. You will need to tweak it for your environment.
    wal_level = hot_standby
  max_wal_senders = 1

! 
Set pg_hba.conf on the primary node.
! Below is just an example. You will need to tweak it for your environment.
  ! host	replication	postgres		192.168.0.10/32		trust

  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.

! Streaming Replication

  While using Streaming replication and Hot Standby, it is important to
  determine which query can be sent to the primary or the standby,
--- 1944,1993 ----

  chmod 755 failover_stream.sh

  failover_command = '/usr/local/src/pgsql/9.0-beta/bin/failover_stream.sh %d %H /tmp/trigger_file0'

  standby_mode = 'on'
  primary_conninfo = 'host=name of primary_host user=postgres'
  trigger_file = '/tmp/trigger_file0'

  wal_level = hot_standby
  max_wal_senders = 1

! host    replication    postgres        192.168.0.10/32        trust

  recovery_user = 'postgres'

  recovery_password = 't-ishii'

  recovery_1st_stage_command = 'basebackup.sh'

  recovery_2nd_stage_command = ''

--- 2072,2132 ----

  [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.]
! 
! 

  Lastly, queries that pgpool-II's parser thinks to be an error are sent to the
  primary node.

! Online recovery with Streaming Replication

! 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.

!     Set recovery_user. Usually it's "postgres".
    recovery_user = 'postgres'

!     
! 
!     Set recovery_password for
!     recovery_user to login database.
    recovery_password = 't-ishii'

+     

!     Set recovery_1st_stage_command.
!     The script for this stage should perform a base backup of the primary and
!     restore it on the standby node.
!     Place this script inside the primary database cluster directory and give it
!     execute permission.
!     Here is the sample script (basebackup.sh) 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.
    recovery_1st_stage_command = 'basebackup.sh'

!     
! 
!     Leave recovery_2nd_stage_command be empty.
    recovery_2nd_stage_command = ''

+     

!     Install required C and SQL functions to perform online recovery into each DB nodes.

  ***************
*** 1693,1807 ****
  # psql -f pgpool-recovery.sql template1

! After completing online recovery, pgpool-II will start PostgreSQL on
! the standby node.
! Install the script for this purpose on each DB nodes.
! Sample script 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.

! 

  That's it.
! Now you should be able to use pcp_recovery_node (as long as the standby
  node stops) or push "recovery" button of pgpoolAdmin to perform online recovery.

! Parallel Mode

! This mode activates parallel execution of queries. Tables can be split, and 
! data distributed to each node. Moreover, the replication and the load balancing
! features can be used at the same time. In parallel mode, replication_mode and 
! load_balance_mode are set to true in pgpool.conf, master_slave is set to false, 
! and parallel_mode is set to true. When you change this parameter, restart pgpool-II. 

! Configuring the System DB

  To use the parallel mode, the System DB must be configured
! properly. The System DB contains rules, stored in a table, to choose an 
  appropriate backend to send partitioned
  data to. The System DB does not need to be created on the same host as
  pgpool-II. The System DB's configuration is done in
  pgpool.conf.

!   system_db_hostname
!   
!       The hostname where the System DB is. Specifying the
!       empty string ('') means the System DB is on the same host as
!       pgpool-II, and will be accessed via a UNIX domain socket.
!   
! 
!   system_db_port
!   
!       The port number for the System DB
!   
! 
!   system_dbname
!   
!       The partitioning rules and other information will be defined
!       in the database specified here. Default value is:
!       'pgpool'.
!   
! 
!   system_db_schema
!   
!       The partitioning rules and other information will be defined
!       in the schema specified here. Default value is:
!       'pgpool_catalog'.
!   
! 
!   system_db_user
!   
!       The username to connect to the System DB.
!   
! 
!   system_db_password
!   
!       The password for the System DB. If no password is necessary,
!       set the empty string ('').
!   
! 
!   ssl_ca_cert
!   
!       
!       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 -CAfile option 
!       of the OpenSSL verify(1) command.
!       

!       
!       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 
!       ssl_ca_cert_dir.
!       
!   

!   ssl_ca_cert_dir
!   
!       
!       The path to a directory containing PEM format CA certficate
!       files, which can be used to verify the backend server
!       certificate.  This is analogous to the -CApath option 
!       of the OpenSSL verify(1) command.
!       

!       
!       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 
!       ssl_ca_cert.
!       
!   

! Initial Configuration of the System DB

  First, create the database and schema specified in the
  pgpool.conf file. A sample script can be found in
--- 2136,2291 ----
  # psql -f pgpool-recovery.sql template1

  CREATE TABLE pgpool_catalog.dist_def(
! dbname TEXT,                                              -- database name
! schema_name TEXT,                                         -- schema name
! table_name TEXT,                                          -- table name
! col_name TEXT NOT NULL CHECK (col_name = ANY (col_list)), -- partitioning key column name
! col_list TEXT[] NOT NULL,                                 -- names of table attributes
! type_list TEXT[] NOT NULL,                                -- types of table attributes
! dist_def_func TEXT NOT NULL,                              -- name of the partitioning rule function
! PRIMARY KEY (dbname,schema_name,table_name)
  );

  CREATE TABLE pgpool_catalog.dist_def(
!     dbname TEXT,                                              -- database name
!     schema_name TEXT,                                         -- schema name
!     table_name TEXT,                                          -- table name
!     col_name TEXT NOT NULL CHECK (col_name = ANY (col_list)), -- partitioning key column name
!     col_list TEXT[] NOT NULL,                                 -- names of table attributes
!     type_list TEXT[] NOT NULL,                                -- types of table attributes
!     dist_def_func TEXT NOT NULL,                              -- name of the partitioning rule function
!     PRIMARY KEY (dbname,schema_name,table_name)
  );

  CREATE TABLE pgpool_catalog.replicate_def(
! 	dbname TEXT,	    --database name
! 	schema_name TEXT,	--schema name 
! 	table_name TEXT,	--table name
! 	col_list TEXT[] NOT NULL,	-- names of table attributes
! 	type_list TEXT[] NOT NULL,	-- types of table attributes
! 	PRIMARY KEY (dbname,schema_name,table_name)
  );

  INSERT INTO pgpool_catalog.dist_def VALUES (
! 	'pgpool',
! 	'public',
! 	'accounts',
! 	'aid',
! 	ARRAY['aid','bid','abalance','filler'],
! 	ARRAY['integer','integer','integer','character(84)'],
! 	'pgpool_catalog.dist_def_accounts'
  );

  INSERT INTO pgpool_catalog.replicate_def VALUES (
! 	'pgpool',
! 	'public',
! 	'branches',
! 	ARRAY['bid','bbalance','filler'],
! 	ARRAY['integer','integer','character(84)']
  );

  CREATE TABLE pgpool_catalog.replicate_def(
!     dbname TEXT,                  -- database name
!     schema_name TEXT,             -- schema name
!     table_name TEXT,              --table name
!     col_list TEXT[] NOT NULL,     -- names of table attributes
!     type_list TEXT[] NOT NULL,    -- types of table attributes
!     PRIMARY KEY (dbname,schema_name,table_name)
  );

  INSERT INTO pgpool_catalog.dist_def VALUES (
!     'pgpool',
!     'public',
!     'accounts',
!     'aid',
!     ARRAY['aid','bid','abalance','filler'],
!     ARRAY['integer','integer','integer','character(84)'],
!     'pgpool_catalog.dist_def_accounts'
  );

  INSERT INTO pgpool_catalog.replicate_def VALUES (
!     'pgpool',
!     'public',
!     'branches',
!     ARRAY['bid','bbalance','filler'],
!     ARRAY['integer','integer','character(84)']
  );

  CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts (val ANYELEMENT) RETURNS INTEGER AS '
! SELECT CASE WHEN $1 >= 1 and $1 <= 30000 THEN 0
! WHEN $1 > 30000 and $1 <= 60000 THEN 1
  ELSE 2

!     local      DATABASE  USER  METHOD  [OPTION]
!     host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]

!     configure --with-pam

  enable_query_cache = true

  CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts (val ANYELEMENT) RETURNS INTEGER AS '
! SELECT CASE WHEN $1 >= 1 and $1 <= 30000 THEN 0
! WHEN $1 > 30000 and $1 <= 60000 THEN 1
  ELSE 2

! local      DATABASE  USER  METHOD  [OPTION]
! host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]

! configure --with-pam

  enable_query_cache = true

+ memory_cache_enabled = on
+ 
+ memqcache_method = 'shmem'
+ 
+ 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
+ 
+ 2012-05-01 15:42:09 LOG:   pid 20181: query result fetched from cache. statement: select * from t1;
+ 
+ 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
+ 
+ (memqcache_cache_hits) / (memqcache_no_cache_hits+memqcache_cache_hits) = 36717 / (36717 + 80471) = 31.3%
+ 
+ ./configure
+ 
+ make
+ make install
+ 
+ ./configure
+ 
+ make
+ make install
+ 
  pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file][-n][-D][-d]

  pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file][-n][-D][-d]

  pgpool [-f config_file][-F pcp_config_file] [-m {s[mart]|f[ast]|i[mmediate]}] stop

  pgpool [-f config_file][-F pcp_config_file] [-m {s[mart]|f[ast]|i[mmediate]}] stop

  benchs2=# show pool_status;
!              item              |              value              |                           description                            
! -------------------------------+---------------------------------+------------------------------------------------------------------
!  listen_addresses              | 127.0.0.1                       | host name(s) or IP address(es) to listen to
!  port                          | 9999                            | pgpool accepting port number
!  socket_dir                    | /tmp                            | pgpool socket directory
!  num_init_children             | 5                               | # of children initially pre-forked
!  child_life_time               | 300                             | if idle for this seconds, child exits

  benchs2=# show pool_nodes;
!   id  |  hostname   | port | status | lb_weight     
! ------+-------------+------+--------+-----------
!    0  | 127.0.0.1   | 5432 | 2      | 0.5
!    1  | 192.168.1.7 | 5432 | 3      | 0.5
  (2 lignes)

  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)

!   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)

  benchs2=# show pool_version;
!       pool_version      
  ------------------------
   3.0-dev (umiyameboshi)
  (1 line)

  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

  benchs2=# show pool_nodes;
!   id  |  hostname   | port | status | lb_weight |  role
! ------+-------------+------+--------+-----------+---------
!    0  | 127.0.0.1   | 5432 | 2      | 0.5       | primary
!    1  | 192.168.1.7 | 5432 | 3      | 0.5       | standby
  (2 lignes)

  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)

!   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)

  benchs2=# show pool_version;
!       pool_version
  ------------------------
   3.0-dev (umiyameboshi)
  (1 line)

+ 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
+ 
!   pgpool-II-x.x.x/sql/pgpool-recovery/

! pgpool-II-x.x.x/sql/pgpool-recovery/

!   % cd pgpool-II-x.x.x/sql/pgpool-recovery/
!   % make install

! % cd pgpool-II-x.x.x/sql/pgpool-recovery/
! % make install

!   % cd pgpool-II-x.x.x/sql/pgpool-recovery/
!   % psql -f pgpool-recovery.sql template1

!   #! /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

  restore_command = 'scp master:/data/archive_log/%f %p'

  #! /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
--- 3282,3370 ----

  ! % cd pgpool-II-x.x.x/sql/pgpool-recovery/
! % psql -f pgpool-recovery.sql template1

! Recovery script deployment 

  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.

! Online recovery by PITR

! 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.

+ 1st stage
+ 

  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.

  ! #! /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

! This script puts the master database in backup mode, generates the following recovery.conf:

    restore_command = 'scp master:/data/archive_log/%f %p'

+ 
+ 
  performs the backup, then puts the master database out of backup mode and copies
  the backup on the chosen target node.

+ 2nd stage
+ 

  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.
! 
! V3.1 -
! However it may return before 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
  Installing C functions section.

+ 

  Here is the sample script.

    #! /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
***************
*** 2423,2560 ****

  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. 
! 
  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.
- 

  We deploy these scripts into the $PGDATA directory.

  Finally, we edit pgpool.conf.

! 
! recovery_1st_stage_command = 'copy-base-backup'
! recovery_2nd_stage_command = 'pgpool_recovery_pitr'
! 

! 

! We have finished preparing online recovery by PITR.

! pgpool_remote_start

! This script starts up the remote host's postmaster process.
! pgpool-II executes it the following way.

! !   % pgpool_remote_start remote_host remote_datadir
!   remote_host:    Hostname of a recovery target.
!   remote_datadir: Database cluster path of a recovery target.
! 
! 

! 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.

! 
! 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.
! 

! ! #! /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 &
! 

! Online recovery with rsync.
! 
! PostgreSQL 7.4 does not have PITR. 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.
! 

! !   % 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.
! 

! 
! This script copies physical files with rsync over ssh. So you need to
! be able to connect over ssh without a password.
! 

! 
! Note about rsync:
! 

!  -c (or --checksum) option is required to enable reliable file tranmitting

!  
-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.
! 
!  
rsync 3.0.5 has great speed performance
! 	  improvements (50% faster according to a report from pgpool-general
! 	  mailing list.
! 

! 

! 
! If you use pgpool_recovery, add the following lines into pgpool.conf.

! 
! recovery_1st_stage_command = 'pgpool_recovery'
! recovery_2nd_stage_command = 'pgpool_recovery'
! 
! 

! How to perform online recovery
! 
! In order to do online recovery, use the pcp_recovery_node command or
! pgpoolAdmin.
! 

! 
! Note that you need to pass a large number to the first argument of
! pcp_recovery_node. It is the timeout parameter in seconds. If you use 
! pgpoolAdmin, set "_PGPOOL2_PCP_TIMEOUT " parameter to a large number in
! pgmgt.conf.php.
! 

  Troubleshooting

  This section describes problems and their workarounds while you are using
  pgpool-II.

- 
- 

! Health check failed
! 

! 
! Pgpool-II's health checking feature detects DB nodes failure.
! 
! 

  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
--- 3379,4026 ----

  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.
! 
! 
  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.

  We deploy these scripts into the $PGDATA directory.

  Finally, we edit pgpool.conf.
+ 
+ 
+ + recovery_1st_stage_command = 'copy-base-backup'
+ recovery_2nd_stage_command = 'pgpool_recovery_pitr'
+ 
+ 
+ 
+ We have finished preparing online recovery by PITR.
+ 
+ 
+ pgpool_remote_start
+ 
+ This script starts up the remote host's postmaster process.
+ pgpool-II executes it the following way.
+ 
+ 
+ + % pgpool_remote_start remote_host remote_datadir
+ remote_host:    Hostname of a recovery target.
+ remote_datadir: Database cluster path of a recovery target.
+ 
+ 
+ 
+ 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.
+ 
+ 
+ 
+ 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.
+ 
+ 
+ + #! /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 &
+ 
+ 
+ Online recovery with rsync.
+ 
+ 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.
+ 
+ 
+ + % 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.
+ 
+ 
+ 
+ This script copies physical files with rsync over ssh. So you need to
+ be able to connect over ssh without a password.
+ 
+ 
+ 
+ Note about rsync:
+ 

+     -c (or --checksum) option is required to enable reliable file tranmitting
+     
+     -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.
+     
+     rsync 3.0.5 has great speed performance
+         improvements (50% faster according to a report from pgpool-general mailing list.
+     
+ 
+ 
+ 
+ 
+ 
+ If you use pgpool_recovery, add the following lines into pgpool.conf.
+ 
+ 
+ + recovery_1st_stage_command = 'pgpool_recovery'
+ recovery_2nd_stage_command = 'pgpool_recovery'
+ 
+ 
+ How to perform online recovery
+ 
+ In order to do online recovery, use the pcp_recovery_node command or
+ pgpoolAdmin.
+ 
+ 
+ 
+ Note that you need to pass a large number to the first argument of
+ pcp_recovery_node. It is the timeout parameter in seconds. If you use
+ pgpoolAdmin, set "_PGPOOL2_PCP_TIMEOUT " parameter to a large number in
+ pgmgt.conf.php.
+ 
+ 
+ PostgreSQL version up using online recovery
+ replication mode case
+ 
+ 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).
+ 
+ 
+ 
+ 

+     
+     Prepare online recovery.
+     
+ 
+     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.
+         
+ + 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)
+ 
+     
+ 
+     
+     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.
+     
+     
+ 
+     
+     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.
+     
+     
+ 
+     
+     Change installation directory of old PostgreSQL.
+     Installting directory of PostgreSQL is supposed to be /usr/local/pgsql in following description.
+     
+ + $ mv /usr/local/pgsql /usr/local/pgsql-old
+ 
+     
+ 
+     
+     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.
+     
+ + $ ln -s /usr/local/pgsql-new /usr/local/pgsql
+ 
+     
+ 
+     
+     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.
+     
+ + $ ln -s /usr/local/pgsql-old/data /usr/local/pgsql/data
+ 
+     
+ 
+     
+     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.
+     
+     
+ 
+     
+     Do online recovery. You are done with one node version up.
+     To execute online recovery, you can use pcp_recovery_node or pgpoolAdmin.
+     
+     
+ 
+     
+     Repeat steps above on each node. In the very last master node should be updated.
+     You are done.
+     
+     
+ 
+ 
+ If you are using streaming replication
+ 
+ You can update standby PostgreSQL server without stopping pgpool-II.
+ 
+ 
+ 
+ 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.
+ 
+ 
+ 
+ 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:
+ 
+ 
+     Stop pgpool-II
+     Stop primary PostgreSQL
+     Update primary PostgreSQL
+     Start primary PostgreSQL
+     Start pgpool-II
+ 
+ 
+ back to top
+ 
+ 
+ 
+ Backup
+ 
+ To back up backend PostgreSQL servers and system DB, you can 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 PostgreSQL,
+ rather than via pgpool-II to avoid errors caused by load_balance_mode and
+ replicate_select.
+ 
+ 
+ replication mode and master/slave mode
+ 
+ If pgpool-II is operated in replication mode or master/slave mode, take a backup on one DB nodes in the cluster.
+ 
+ 
+ 
+ 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.
+ 
+ 
+ 
+ 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.
+ 
+ 
+ Parallel mode
+ 
+ If you are using parallel mode and need to take a consistent backup, you need to stop pgpool-II.
+ 
+ 
+ 
+ To use logical backup, stop applications and pgpool-II then perform pg_dump or pg_dumpall on all nodes.
+ After finishing backup, start pgpool-II then start applications.
+ 
+ 
+ 
+ To use PITR, please make sure that system times are identical on all nodes.
+ Prepare archive logging and take base backup.
+ After finishing backup, stop and restart applications or pgpool-II.
+ Record the time of stop and start.
+ This temporary stop will make consistent state among all over cluster.
+ If you need to restore form the base backup and archive log,
+ set recovery_target_time of recovery.conf in the middle of the start/stop time.
+ 
+ 
+ Backing up of system DB
+ 
+ You need to backup system DB if pgpool-II is operated in parallel query
+ mode or if you are using query cache.
+ Backup database specified by system_db_dbname in pgpool.conf.
+ 
+ 
+ back to top
+ 
+ 
+ 
+ Deploying pgpool-II
+ 
+ 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.
+ 
+ 
+ 
+ 

+ Dedicated server
+     
+     
+     Pgpool-II is running on a dedicated server.
+     It's simple and pgpool-II is not affected by other server softwares.
+     Obvious cons is you need to by more hardware.
+     Also pgpool-II can be a single point of failure with this configuration
+     (you can avoid this by using pgpool-HA described somewhere below).
+     
+     
+ 
+ Deploying on a web server or application server
+     
+     
+     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).
+     There are several things you should care about in this configuration:
+     
+     
+         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.
+         
+         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.
+         
+     
+     
+ 
+ Running pgpool-II on DB server
+     
+     
+     Running pgpool-II on the server as PostgreSQL is running on.
+     You can avoid the single point of failure problem of pgpool-II with configuration.
+     And obviously you do need to buy additional dedicated server.
+     Problem with this configuration is, application need to aware that which DB server they should connect to.
+     To solve the problem you can use virtual IP with pgpool-HA.
+     
+     
+ 
+ 
+ About pgpool-HA
+ 
+ Pgpool-HA is a high availability software for pgpool-II using hearbeat.
+ Pgpool-HA is a sub project of pgpool project as well as pgpool-II.
+ Pgpool-HA can be available from pgpool development site as an open source software.
+ 
+ 
+ back to top
+ 
+ 
+ 
+ Watchdog V3.2 -
+ 
+ 
+ "Watchdog" is a sub process of pgpool-II aiming for adding high availability feature to it.
+ Features added by watchdog include:
+ 
+ 
+ 
+ Life checking of pgpool service
+     
+     
+     Watchdog monitors responses of pgpool service rather than process.
+     It sends queries to PostgreSQL via pgpool which is being monitored
+     by watchdog and watchdog checks the response.
+     
+     
+     Also watchdog monitors connections to up stream servers
+     (application servers etc.) from the pgpool.
+     The connection between the up stream servers and the pgpool is monitored as service of pgpool.
+     
+     
+ 
+ Mutual monitoring of watchdog processes
+     
+     
+     Watchdog processes exchange information on the monitored servers
+     to keep the information up to date,
+     and to allow watchdog processes to mutually monitor each other. 
+     
+     
+ 
+ Changing active/standby state in case of certain faults detected
+     
+     
+     When a fault is detected in the pgpool service, watchdog notifies the other watchdogs of it.
+     Watchdogs decide the new active pgpool if previous active pgpool is broken
+     by voting and change active/standby state.
+     
+     
+ 
+ Automatic virtual IP address assigning synchronous to server switching
+     
+     
+     When a standby pgpool 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 to work using the same IP address even when servers is switched over.
+     
+     

! Automatic registration of a server as standby in recovery
!     
!     
!     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 standby.
!     
!     
! 

! Server composition

! Figure below describes how pgpool-II and watchdog process is configured.

+ 

! Starting/stopping watchdog

! Watchdog process is started/stopped automatically by pgpool-II,
! therefore there is no dedicated command to start/stop it.
! However, pgpool-II must to be started with root privilege
! because watchdog process needs to control the virtual IP interface.

! Configuring watchdog (pgpool.conf)

! Watchdog configuration parameters is described in pgpool.conf.
! There is sample configuration in WATCHDOG section in pgpool.conf.sample file.
! All following options are required to be specified in watchdog process.

! Basic configuration

! Enabling
! 
! use_watchdog V3.2 -
!     
!     
!     If on, activates watchdog. Default is off.
!     
!     
! 

! Connection to up stream servers

! 
! trusted_servers V3.2 -
!     
!     
!     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".
!     
!     

! ping_path V3.2 -
!     
!     
!     This parameter specifies a path of ping command for monitoring connection
!     to the upper servers.
!     Set the only path such as "/bin".
!     
!     
! 

! Life checking pgpool-II

! 
! wd_interval V3.2 -
!     
!     
!     This parameter specifies the interval between life checks of pgpool-II
!     in second.
!     (A number greater than or equal to 1)
!     
!     

! wd_life_point V3.2 -
!     
!     
!     The times to retry a failed life check of pgpool-II.
!     (A number greater than or equal to 1)
!     
!     

! wd_lifecheck_query V3.2 -
!     
!     
!     Actual query to check pgpool-II.
!     Default is "SELECT 1"
!     
!     
! 

! Virtual IP address

! 
! delegate_IP V3.2 -
!     
!     
!     Specifies the virtual IP address (VIP) of pgpool-II that is connected from
!     client servers (application servers etc.).
!     When a pgpool is switched from standby to active, the pgpool takes over this VIP.
!     
!     

! ifconfig_path V3.2 -
!     
!     
!     This parameter specifies a path of a command to switch the IP address.
!     Set the only path such as "/sbin".
!     
!     

! if_up_cmd V3.2 -
!     
!     
!     This parameter specifies a command to bring up the virtual IP.
!     Set the command and parameters such as
!     "ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0".
!     $_IP_$ is replaced by the IP address specified in delegate_IP.
!     
!     
! 
! if_down_cmd V3.2 -
!     
!     
!     This parameter specifies a command to bring down the virtual IP.
!     Set the command and parameters such as
!     "ifconfig eth0:0 down".
!     
!     
! 
! arping_path V3.2 -
!     
!     
!     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".
!     
!     
! 
! arping_cmd V3.2 -
!     
!     
!     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 delegate_IP.
!     
!     
! 
! 
! Server itself to be monitored
! 
! wd_hostname V3.2 -
!     
!     
!     Specifies the hostname or IP address for mutual monitoring of watchdog processes.
!     
!     
! 
! wd_port V3.2 -
!     
!     
!     Specifies the port number for mutual monitoring of watchdog processes.
!     
!     
! 
! 
! Servers to monitor
! 
! other_pgpool_hostname0
!     V3.2 -
!     
!     
!     Specifies the hostname 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.
!     
!     
! 
! other_pgpool_port0
!     V3.2 -
!     
!     
!     Specifies the port number for pgpool 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.
!     
!     
! 
! other_wd_port0 V3.2 -
!     
!     
!     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.
!     
!     
! 
! 
! back to top
! 
! 

  Troubleshooting

  This section describes problems and their workarounds while you are using
  pgpool-II.

! 
! 
! Health check failed
!     
!     
!     Pgpool-II's health checking feature detects DB nodes failure.
!     
! 
    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
***************
*** 2562,2661 ****
  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)

- 
- 
- 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.
- 

! 
Failed to read kind from frontend
! 

! 

  2010-07-26 18:43:24 LOG:   pid 24161: ProcessFrontendResponse: failed to read kind from frontend. frontend abnormally exited

! 
! 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.
! 

! 
Kind mismatch errors
! 

! 
! It is possible that you get this error when pgpool-II operates in
! replication mode.
! 
    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]

! 
! 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.
! 

! 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.
! 
! 
! Check the databases and resync them using online recovery if you find
! that they are out of sync.
! 

! 
! 
Pgpool detected difference of the number of inserted, updated or deleted tuples
! 

! 
! In replication mode, pgpool-II detects a different number of
! INSERT/UPDATE/DELETE rows on affected nodes.
! 
! 

  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

! 
! 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.
! 
! 
! Stop the DB node which is suspected of having wrong data and do an online
! recovery.
! 

  Restrictions

! 
Functionalities of PostgreSQL

  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.

--- 4028,4129 ----
  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)

!     
!     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.
!     
!     
! 
! Failed to read kind from frontend
!     
    2010-07-26 18:43:24 LOG:   pid 24161: ProcessFrontendResponse: failed to read kind from frontend. frontend abnormally exited

!     
!     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.
!     
!     

! Kind mismatch errors
!     
!     
!     It is possible that you get this error when pgpool-II operates in replication mode.
!     
    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]

!     
!     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.
!     
!     
!     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.
!     
!     
!     Check the databases and re-sync them using online recovery if you find
!     that they are out of sync.
!     
!     

! Pgpool detected difference of the number of inserted, updated or deleted tuples
!     

!     
!     In replication mode, pgpool-II detects a different number of
!     INSERT/UPDATE/DELETE rows on affected nodes.
!     
    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

!     
!     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.
!     
!     
!     Stop the DB node which is suspected of having wrong data and do an online recovery.
!     
!     
! 
! 
! back to top
! 

+ 

  Restrictions

! 
Functionality of PostgreSQL

  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.
! 

***************
*** 2663,2735 ****

!   In the replication mode or master/slave mode, trust, clear text
!       password, and pam methods are supported. md5 in also supported since pgpool-II 3.0.
! 	   
! 	   md5 is supported by using "pool_passwd". Here are the steps to enable
! 	   md5 authentication:
! 	   
! 		Login as the database's operating system user and type 
! 		   "pg_md5 --md5auth "
! 			 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.
! 		 The format of pool_passwd is "username:encrypted_passwd".
! 		You also need to add an appropriate md5 entry to pool_hba.conf.
! 			 See Setting up pool_hba.conf for client
! 			 authentication (HBA) for more details.
! 		Please note that the user name and password must be
! 			 identical to those registered in PostgreSQL.
! 	   
! 
! 	   
!   In all the other modes, trust, clear text password, crypt, md5,
!       and pam methods are supported.
!   pgpool-II does not support pg_hba.conf-like access controls. If
!       TCP/IP connections are enabled, pgpool-II accepts all the
!       connections from any host. If needed, use iptables and such to
!       control access from other hosts. (PostgreSQL server accepting
!       pgpool-II connections can use pg_hba.conf, of course).
! 	   
! 

  Large objects

! 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 lobj_lock_table directive in pgpool.conf.
  Large object replication using backend function lo_import is not supported, however.

! Temporary tables in master/slave mode

  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.

! 

  Sample SELECT which causes a problem:
  SELECT 't1'::regclass::oid;

! 

  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.

! Functions, etc. In Replication Mode

! 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.
  For SERIAL, enabling insert_lock will help replicating data.
--- 4131,4200 ----

!     In the replication mode or master/slave mode, trust, clear text password,
!         and pam methods are supported. md5 in also supported since pgpool-II 3.0.
!         md5 is supported by using "pool_passwd". Here are the steps to enable md5 authentication:
!         
!             Login as the database's operating system user and type
!                 "pg_md5 --md5auth --username= "
!                  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.
!             The format of pool_passwd is "username:encrypted_passwd".
!             You also need to add an appropriate md5 entry to pool_hba.conf.
!                 See Setting up pool_hba.conf for client authentication (HBA)
!                 for more details.
!             Please note that the user name and password must be
!                  identical to those registered in PostgreSQL.
!             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).
!        
!     
!     In all the other modes, trust, clear text password, crypt, md5,
!         and pam methods are supported.
!     pgpool-II does not support pg_hba.conf-like access controls. If
!         TCP/IP connections are enabled, pgpool-II accepts all the
!         connections from any host. If needed, use iptables and such to
!         control access from other hosts. (PostgreSQL server accepting
!         pgpool-II connections can use pg_hba.conf, of course).
!     

  Large objects

! 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 lobj_lock_table directive in pgpool.conf.
  Large object replication using backend function lo_import is not supported, however.

! Temporary tables in master/slave mode

  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.

! 
    Sample SELECT which causes a problem:
  SELECT 't1'::regclass::oid;

! 

  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.

! Functions, etc. In Replication Mode

! 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.
  For SERIAL, enabling insert_lock will help replicating data.
***************
*** 2739,2793 ****
  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:

! The calculation of temporal data in table default value is not accurate in 
! some cases. For example, the following table definition:
    CREATE TABLE rel1(
    d1 date DEFAULT CURRENT_DATE + 1
  )

! is treated the same as:
    CREATE TABLE rel1(
    d1 date DEFAULT CURRENT_DATE
  )

! 
! Please note that if the column type is not a temporal one, rewriting is
! not performed. Such example:

  foo bigint default (date_part('epoch'::text,('now'::text)::timestamp(3) with time zone) * (1000)::double precision)

! 
! 
Suppose we have the following table:
    CREATE TABLE rel1(
    c1 int,
    c2 timestamp default now()
  )

! We can replicate
  ! INSERT INTO rel1(c1) VALUES(1) 

! since this turn into
    INSERT INTO rel1(c1, c2) VALUES(1, '2009-01-01 23:59:59.123456+09')

! However,
    INSERT INTO rel1(c1) SELECT 1

! cannot to be transformed, thus cannot be properly replicated in the current implementation.
! Values will still be inserted, with no transformation at all.

! 
  Tables created by CREATE TEMP TABLE 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.

--- 4204,4272 ----
  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:
+ 
+ 

!     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:
    CREATE TABLE rel1(
    d1 date DEFAULT CURRENT_DATE + 1
  )

!     is treated the same as:
    CREATE TABLE rel1(
    d1 date DEFAULT CURRENT_DATE
  )

!     
!     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.
!     
!     
!     Please note that if the column type is not a temporal one, rewriting is
!     not performed. Such example:
!     
    foo bigint default (date_part('epoch'::text,('now'::text)::timestamp(3) with time zone) * (1000)::double precision)

!     
! 
!     Suppose we have the following table:
    CREATE TABLE rel1(
    c1 int,
    c2 timestamp default now()
  )

! 
!     We can replicate
  ! INSERT INTO rel1(c1) VALUES(1)

! 
!     since this turn into
    INSERT INTO rel1(c1, c2) VALUES(1, '2009-01-01 23:59:59.123456+09')

! 
!     However,
    INSERT INTO rel1(c1) SELECT 1

!     cannot to be transformed, thus cannot be properly replicated in the current implementation.
!     Values will still be inserted, with no transformation at all.
!     

! 
  Tables created by CREATE TEMP TABLE 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.

***************
*** 2798,2812 ****
  temporary tables by issuing DROP TABLE, or use CREATE TEMP
  TABLE ... ON COMMIT DROP inside the transaction block.

! Queries

  Here are the queries which cannot be processed by pgpool-II

! INSERT (for parallel mode)

  You cannot use DEFAULT with the
  partitioning key column. For example, if the column x in the table t
  was the partitioning key column,

  INSERT INTO t(x) VALUES (DEFAULT);
--- 4277,4292 ----
  temporary tables by issuing DROP TABLE, or use CREATE TEMP
  TABLE ... ON COMMIT DROP inside the transaction block.

! Queries

  Here are the queries which cannot be processed by pgpool-II

! INSERT (for parallel mode)

  You cannot use DEFAULT with the
  partitioning key column. For example, if the column x in the table t
  was the partitioning key column,
+ 

    INSERT INTO t(x) VALUES (DEFAULT);
***************
*** 2824,2830 ****
  are also not supported.

! UPDATE (for parallel mode)

  Data consistency between the backends may be lost if the
  partitioning key column values are updated. pgpool-II does not
--- 4304,4310 ----
  are also not supported.

! UPDATE (for parallel mode)

  Data consistency between the backends may be lost if the
  partitioning key column values are updated. pgpool-II does not
***************
*** 2840,2846 ****

! SELECT ... FOR UPDATE (for parallel mode)

  If a function is called in the WHERE clause, that
  query might not be executed correctly. For example:
--- 4320,4326 ----

! SELECT ... FOR UPDATE (for parallel mode)

  If a function is called in the WHERE clause, that
  query might not be executed correctly. For example:
***************
*** 2849,2866 ****

  BEGIN;
  INSERT INTO t(a) VALUES (1);
! SELECT * FROM t ORDER BY a; <-- INSERT above is not visible from this SELECT statement
  END;

!   =# SELECT * FROM t1 JOIN t2 USING(id);
!    id |  t  |   t
!   ----+-----+-------
!     1 | 1st | first
!   (1 row)
! 
-   =# SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
-    id |  t  | id |   t
-   ----+-----+----+-------
-     1 | 1st |  1 | first
-   (1 row)
- 
  (tellers table is partitioned using the following rule)
!   tid <= 10  --> node 0
!   tid >= 10  --> node 1

  A) BEGIN;
  B) BEGIN;
--- 4383,4442 ----
  supported. The simple query protocol must be used. This means you can't use
  prepared statements.

! Natural Join (for parallel mode)

  The Natural Join is not supported.
  "ON join condition" or "USING (join_column)" must be used.

! USING CLAUSE (for parallel mode)

  The USING CLAUSE is converted to an ON CLAUSE by the query rewrite process.
  Therefore, when "*" is used at target list, the joined column(s) appear twice.

  Here is an example:

! ! =# SELECT * FROM t1 JOIN t2 USING(id);
!  id |  t  |   t
! ----+-----+-------
!   1 | 1st | first
! (1 row)
! 

  In the rewrite process "USING" is translated into "ON". So the
  effective result is as follows:

+ + =# SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
+  id |  t  | id |   t
+ ----+-----+----+-------
+   1 | 1st |  1 | first
+ (1 row)
+ 
+ 

  Notice that column "t" is duplicated.

! Multi-byte Characters (for all modes)

  pgpool-II does not translate between different multi-byte
  characters. The encoding for the client, backend and System DB must be
  the same.

! Multi-statement Query (for all modes)

  pgpool-II cannot process multi-statement queries.

! Deadlocks (for parallel mode)

  Deadlocks across the backends cannot be detected. For example:

    (tellers table is partitioned using the following rule)
!   tid <= 10  --> node 0
!   tid >= 10  --> node 1

  A) BEGIN;
  B) BEGIN;
***************
*** 2977,2983 ****
  pool_read_kind: kind does not match between master(84) slot[1] (69)

! Schemas (for parallel mode)

  Objects in a schema other than public must be fully qualified like:

--- 4458,4464 ----
  pool_read_kind: kind does not match between master(84) slot[1] (69)

! * pcp_node_count        - retrieves the number of nodes
! * pcp_node_info         - retrieves the node information
! * pcp_proc_count        - retrieves the process list
! * pcp_proc_info         - retrieves the process information
! * pcp_systemdb_info     - retrieves the System DB information
! * pcp_detach_node       - detaches a node from pgpool-II
! * pcp_attach_node       - attaches a node to pgpool-II
! * pcp_stop_pgpool       - stops pgpool-II
! 
  Format:
--- 4571,4577 ----
  All PCP commands display the results to the standard output.

! pcp_node_count

    Format:
***************
*** 3083,3094 ****

! Displays the total number of nodes defined in pgpool.conf. It does 
  not distinguish between nodes status, ie attached/detached. ALL nodes are counted.

! pcp_node_info

    Format:
--- 4579,4590 ----

! Displays the total number of nodes defined in pgpool.conf. It does
  not distinguish between nodes status, ie attached/detached. ALL nodes are counted.

! pcp_node_info

    Format:
***************
*** 3134,3140 ****
  Specifying an invalid node ID will result in an error with exit
  status 12, and BackendError will be displayed.

! pcp_proc_count

  Format:
--- 4630,4636 ----
  Specifying an invalid node ID will result in an error with exit
  status 12, and BackendError will be displayed.

! pcp_proc_count

  Format:
***************
*** 3144,3150 ****
  Displays the list of pgpool-II children process IDs. If there is more than
  one process, IDs will be delimited by a white space.

! pcp_proc_info

  Format:
--- 4640,4647 ----
  Displays the list of pgpool-II children process IDs. If there is more than
  one process, IDs will be delimited by a white space.

! 
! pcp_proc_info

  Format:
***************
*** 3206,3212 ****
  Specifying an invalid node ID will result in an error with exit
  status 12, and BackendError will be displayed.

! pcp_systemdb_info

  Format:
--- 4703,4736 ----
  Specifying an invalid node ID will result in an error with exit
  status 12, and BackendError will be displayed.

! pcp_pool_status V3.1 -
! ! Format:
! pcp_pool_status _timeout_  _host_  _port_  _userid_  _passwd_
! 
! 
! Displays parameters in pgpool.conf. The output example is as follows:
! 
! ! $ pcp_pool_status 10 localhost 9898 postgres hogehoge
! 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
! 
! 
! pcp_systemdb_info

  Format:
***************
*** 3253,3259 ****
  BackendError will be displayed.

! pcp_detach_node
    Format:
  pcp_detach_node  [-g] _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_
--- 4777,4783 ----
  BackendError will be displayed.

! pcp_detach_node
    Format:
  pcp_detach_node  [-g] _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_
***************
*** 3265,3271 ****

! pcp_attach_node

  Format:
--- 4789,4795 ----

! pcp_attach_node

  Format:
***************
*** 3275,3281 ****

! pcp_stop_pgpool
    Format:
  pcp_stop_pgpool  _timeout_  _host_  _port_  _userid_  _passwd_  _mode_
--- 4799,4817 ----

! pcp_promote_node V3.1 -
! 
! 
! Format:
! pcp_promote_node  [-g] _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_
! 
! Promotes the given node as new master to pgpool-II. In master/slave streaming replication only.
! If -g is given, wait until all clients are disconnected (unless
! client_idle_limit_in_recovery is -1 or recovery_timeout is expired).
! 
! 
! 
! pcp_stop_pgpool
    Format:
  pcp_stop_pgpool  _timeout_  _host_  _port_  _userid_  _passwd_  _mode_
***************
*** 3287,3295 ****

  ! s	- smart mode
! f	- fast mode
! i	- immediate mode

  If the pgpool-II process does not exist, it results in error with exit
--- 4823,4831 ----

  ! s    - smart mode
! f    - fast mode
! i    - immediate mode

  If the pgpool-II process does not exist, it results in error with exit
***************
*** 3300,3344 ****
  mode. pgpool-II terminates all the processes whether there are clients
  connected to the backends or not.

! Exit Status

  PCP commands exits with status 0 when everything goes well. If any
  error had occurred, it will exit with the following error status.

! 
! UNKNOWNERR      1      Unknown Error (should not occur)
! EOFERR          2      EOF Error
! NOMEMERR        3      Memory shortage
! READERR         4      Error while reading from the server
! WRITEERR        5      Error while writing to the server
! TIMEOUTERR      6      Timeout
! INVALERR        7      Argument(s) to the PCP command was invalid
! CONNERR         8      Server connection error
! NOCONNERR       9      No connection exists
! SOCKERR         10     Socket error
! HOSTERR         11     Hostname resolution error
! BACKENDERR      12     PCP process error on the server (specifying an invalid ID, etc.)
! AUTHERR         13     Authorization failure
! 
! 

  Inside information

! pgpool-II version 2.0.x brings extensive modifications, compared with the version 1.x 
  Please note that what follows doesn't apply to version 1.x.

! Parallel execution engine

! The parallel execution engine is built into pgpool-II. 
  This engine performs the same Query on each node, and drives the engine that
  transmits the result to the front end, depending on the nodes' answers.

! Query Rewriting

! This explains the Query rewriting that pgpool-II does in parallel mode. 

  In parallel mode, a query transmitted by the client goes through two stages of
--- 4836,4904 ----
  mode. pgpool-II terminates all the processes whether there are clients
  connected to the backends or not.

+ pcp_recovery_node
+ 
+ + 書式：
+ pcp_recovery_node  _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_
+ 
+ Attaches the given backend node with recovery.
+ 

! Exit Status

  PCP commands exits with status 0 when everything goes well. If any
  error had occurred, it will exit with the following error status.

! 
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! 
!     
! UNKNOWNERR 1 Unknown Error (should not occur)
EOFERR 2 EOF Error
NOMEMERR 3 Memory shortage
READERR 4 Error while reading from the server
WRITEERR 5 Error while writing to the server
TIMEOUTERR 6 Timeout
INVALERR 7 Argument(s) to the PCP command was invalid
CONNERR 8 Server connection error
NOCONNERR 9 No connection exists
SOCKERR 10 Socket error
HOSTERR 11 Hostname resolution error
BACKENDERR 12 PCP process error on the server (specifying an invalid ID, etc.)
AUTHERR 13 Authorization failure
! 
! 
back to top
! 
! 

  Inside information

! pgpool-II version 2.0.x brings extensive modifications, compared with the version 1.x
  Please note that what follows doesn't apply to version 1.x.

! Parallel execution engine

! The parallel execution engine is built into pgpool-II.
  This engine performs the same Query on each node, and drives the engine that
  transmits the result to the front end, depending on the nodes' answers.

! Query Rewriting

! This explains the Query rewriting that pgpool-II does in parallel mode.

  In parallel mode, a query transmitted by the client goes through two stages of
***************
*** 3351,3370 ****

  What follows explains these two processing steps:

! Analysis of Query
! Introduction

! The retrieval query submitted by the client goes through the SQL parser. It is then analyzed
! using information stored in the system DB. Execution status of each part
! of this query is updated using this information. This execution status stores
! where this node can be treated. For instance, if a table's data is distributed
! on several nodes (as declared in the catalog's pgpool_catalog.dist_def table),
  it has to be retrieved from all nodes. On the other hand, data from a table
  registered in pgpool_catalog.replicate_def is replicated, and can therefore
! be retrieved from any node. These states are 'P' when data has to be processed by
! all nodes, 'L' when it should be processed by one node. The 'S' status is a special
! case: it means that there is another step to be performed on the data after
! retrieving it from all nodes. For example, sorting data coming from a table 
  registered in the pgpool_catalog.dist_def table.

--- 4911,4937 ----

  What follows explains these two processing steps:

! 
! Analysis of Query
! Introduction
! 
! The retrieval query submitted by the client goes through the SQL parser.
! It is then analyzed using information stored in the system DB. Execution status of each part
! of this query is updated using this information.
! 

! This execution status stores where this node can be treated.
! For instance, if a table's data is distributed on several nodes
! (as declared in the catalog's pgpool_catalog.dist_def table),
  it has to be retrieved from all nodes. On the other hand, data from a table
  registered in pgpool_catalog.replicate_def is replicated, and can therefore
! be retrieved from any node.
! 
! 
! These states are 'P' when data has to be processed by all nodes,
! 'L' when it should be processed by one node.
! The 'S' status is a special case: it means that there is another step to be performed
! on the data after retrieving it from all nodes. For example, sorting data coming from a table
  registered in the pgpool_catalog.dist_def table.

***************
*** 3375,3411 ****

!   Are UNION, EXTRACT, and INTERSECT used or not?
!   
What is the Execution status of FROM clause ?
!   
Change the execution status by TARGETLIST
!   
Change in execution status according to WHERE clause
!   
Change in execution status according to GROUP BY clause
!   
Change in execution status according to HAVING clause
!   
Change in execution status according to ORDER BY clause
!   
Changes into the LIMIT OFFSET predicate in the execution status. 
!   
Acquisition of the final execution status of SELECT

! The relation between the final execution status of SELECT and the processing 
! place is as follows. 

! 		
! 		
! 		
! 		Execution status Processed place
L Query issued on either node. 
P Returns data to the client by running the
! 		same query on all nodes and using the parallel execution engine.
S After processing using the system DB, data
! 		is returned to the client. 

! The above-mentioned rule also applies to Sub-Query. In the simple following 
! Query, if p1-table is registered in pgpool_catalog.dist_def table of the system 
! DB (p1-table is distributed), the final execution status of the subquery 
  becomes P, and as a result, the parent of the subquery, SELECT, also becomes P.

--- 4942,4978 ----

!   Are UNION, EXTRACT, and INTERSECT used or not?
!   What is the Execution status of FROM clause ?
!   Change the execution status by TARGETLIST
!   Change in execution status according to WHERE clause
!   Change in execution status according to GROUP BY clause
!   Change in execution status according to HAVING clause
!   Change in execution status according to ORDER BY clause
!   Changes into the LIMIT OFFSET predicate in the execution status.
!   Acquisition of the final execution status of SELECT

! The relation between the final execution status of SELECT and the processing
! place is as follows.

! 
! 
! 
! Execution status Processed place
L Query issued on either node. 
P Returns data to the client by running the
! same query on all nodes and using the parallel execution engine.
S After processing using the system DB, data
! is returned to the client. 

! The above-mentioned rule also applies to Sub-Query. In the simple following
! Query, if p1-table is registered in pgpool_catalog.dist_def table of the system
! DB (p1-table is distributed), the final execution status of the subquery
  becomes P, and as a result, the parent of the subquery, SELECT, also becomes P.

***************
*** 3418,3526 ****
  Let's start with an example, to explain the FROM status.

! Execution status of FROM clause

! This is a retrieval Query (SELECT). The data set and its status (P,L and S) is 
  defined according to the FROM clause

  The execution status of the table is as follows: when there is only one table in
  the from clause, the execution status of the entire dataset is this table's
  execution status. When there are two or more tables or sub-queries in the FROM
  clause, the execution is decided according to the join method and the execution
! statuses, as show in the following table. 

! 
! 		

! 

! 

! 

- JOIN type  LEFT OUTER JOIN  RIGHT OUTER JOIN FULL OUTER JOIN Other
left/right  P  L  S  P  L  S  P  L  S  P  L  S 
 P  S  P  S  S  S  S  S  S  S  S  P  S 
 L  S  L  S  P  L  S  S  L  S  P  L  S 
 S  S  S  S  S  S  S  S  S  S  S  S  S 

  In the following examples, P1-table is in the P status.
  L1-table and L2-table are in the  L status.

  SELECT * FROM P1-table,L1-table,L2-table;

! 
  P1-table (left) and L1-table (right) are joined, and according to the table they
  get the P status. With this P status, they are joined with the L2-table, in the L
  status, which is now in the P status too.

! Changes in execution status because of TARGETLIST and WHERE clause

  In a basic Query, the execution status is the one from the FROM clause.
  However, if there is a TARGETLIST, the execution status of the WHERE clause can
  change in the following cases.

! 	When there is a subquery
! 	
When there is an aggregate function or DISTINCT in TARGETLIST marked as 'P'
! 	
When a column that does not exist in a table (data set) 
! 	    in the FROM clause is used

! In these cases, the final execution status of the subquery, the execution status of 
! TARGETLIST and the WHERE clause get the S status if the initial status was
! P or S.

! In the following example, when the table used by the subquery is P, the final 
! execution status of the subquery gets the P status. Therefore, The execution 
! status of the WHERE clause gets the S status, whatever the execution status of 
! L1 is, and this query is run in the system DB. 

! 	SELECT * FROM L1-table where L1-table.column IN (SELECT * FROM P1-table);

! The FROM clause changes to the S status when there is an aggregate function in 
  a 'P' TARGETLIST, in order to perform the aggregate after all data has been
  acquired.
! Some optimization on the aggregate function is done under specific conditions. 

  A column that does not exist in a table can be used in a query. For instance, in
  the following correlated subquery:

  ! 	SELECT * FROM L1-table WHERE L1-table.col1 IN (SELECT * FROM P1-table WHERE P1-table.col = L1-table.col1);

! This subquery refers to L1-table.col1, from the L1-table. The execution status 
! of the WHERE clause of the subquery is 'S'. 

! Change in execution status because of GROUP BY, HAVING, ORDER BY and LIMIT/OFFSET

! The execution status of the WHERE clause is changed to 'S' when there is any GROUP 
  BY, HAVING, or ORDER BY clause, or LIMIT/OFFSET predicate and status is 'P'. A
  query with no GROUP BY clause gets the execution status of the WHERE clause.
! In the same way, the execution status of the GROUP BY clause is 
  used when there is no HAVING clause. The ORDER BY clause and the LIMIT/OFFSET
  predicate are also similar in this behavior.

! When UNION, EXTRACT, and INTERSECT are used

! UNION, EXTRACT, and INTERSECT queries' status depends on the final execution 
  status of both the left and right SELECT statements. If both statements are L, the
  combined statement is L. If both statements are P, and the query is a UNION ALL
! the combined statement is P. For any other combination, the resulting status is
! S.

! Acquisition of the final execution status of SELECT

  If everything in the SELECT has a status of L, then the final execution status
  is L. The same rule applies for P. For any other combination, the final status is S.
--- 4985,5099 ----
  Let's start with an example, to explain the FROM status.

! Execution status of FROM clause

! This is a retrieval Query (SELECT). The data set and its status (P,L and S) is
  defined according to the FROM clause

  The execution status of the table is as follows: when there is only one table in
  the from clause, the execution status of the entire dataset is this table's
  execution status. When there are two or more tables or sub-queries in the FROM
  clause, the execution is decided according to the join method and the execution
! statuses, as show in the following table.

! 
! 
! 
! 
! 
! 

! 

! 

! JOIN type  LEFT OUTER JOIN  RIGHT OUTER JOIN FULL OUTER JOIN Other
left/right  P  L  S  P  L  S  P  L  S  P  L  S 
 P  S  P  S  S  S  S  S  S  S  S  P  S 
 L  S  L  S  P  L  S  S  L  S  P  L  S 
 S  S  S  S  S  S  S  S  S  S  S  S  S 

  In the following examples, P1-table is in the P status.
  L1-table and L2-table are in the  L status.
+ 

    SELECT * FROM P1-table,L1-table,L2-table;

! 
  P1-table (left) and L1-table (right) are joined, and according to the table they
  get the P status. With this P status, they are joined with the L2-table, in the L
  status, which is now in the P status too.

! Changes in execution status because of TARGETLIST and WHERE clause

  In a basic Query, the execution status is the one from the FROM clause.
  However, if there is a TARGETLIST, the execution status of the WHERE clause can
  change in the following cases.

!     When there is a subquery
!     When there is an aggregate function or DISTINCT in TARGETLIST marked as 'P'
!     When a column that does not exist in a table (data set)
!         in the FROM clause is used

! In these cases, the final execution status of the subquery, the execution status of TARGETLIST
! and the WHERE clause get the S status if the initial status was P or S.
! 

! In the following example, when the table used by the subquery is P,
! the final execution status of the subquery gets the P status.
! Therefore, The execution status of the WHERE clause gets the S status,
! whatever the execution status of L1 is, and this query is run in the system DB.
! 
  ! SELECT * FROM L1-table where L1-table.column IN (SELECT * FROM P1-table);

! The FROM clause changes to the S status when there is an aggregate function in
  a 'P' TARGETLIST, in order to perform the aggregate after all data has been
  acquired.
! Some optimization on the aggregate function is done under specific conditions.

  A column that does not exist in a table can be used in a query. For instance, in
  the following correlated subquery:

  ! SELECT * FROM L1-table WHERE L1-table.col1 IN (SELECT * FROM P1-table WHERE P1-table.col = L1-table.col1);

! This subquery refers to L1-table.col1, from the L1-table. The execution status
! of the WHERE clause of the subquery is 'S'.

! 
! Change in execution status because of GROUP BY, HAVING, ORDER BY and LIMIT/OFFSET

! The execution status of the WHERE clause is changed to 'S' when there is any GROUP
  BY, HAVING, or ORDER BY clause, or LIMIT/OFFSET predicate and status is 'P'. A
  query with no GROUP BY clause gets the execution status of the WHERE clause.
! In the same way, the execution status of the GROUP BY clause is
  used when there is no HAVING clause. The ORDER BY clause and the LIMIT/OFFSET
  predicate are also similar in this behavior.

! When UNION, EXTRACT, and INTERSECT are used

! UNION, EXTRACT, and INTERSECT queries' status depends on the final execution
  status of both the left and right SELECT statements. If both statements are L, the
  combined statement is L. If both statements are P, and the query is a UNION ALL
! the combined statement is P. For any other combination, the resulting status is S.

! 
! Acquisition of the final execution status of SELECT

  If everything in the SELECT has a status of L, then the final execution status
  is L. The same rule applies for P. For any other combination, the final status is S.
***************
*** 3529,3545 ****
  the parallel execution engine. For S, the query rewriting presented below is done.

! Query rewriting

  The Query is rewritten by using the execution status acquired while analyzing
  the query.
  Here is an example. The P1-table has the P status, the L1-table has the L status.
- 

  ! SELECT P1-table.col, L1-table.col 
! FROM P1-table,L1-table 
! where P1-table.col = L1-table.col 
  order by P1-table.col;

--- 5102,5117 ----
  the parallel execution engine. For S, the query rewriting presented below is done.

! Query rewriting

  The Query is rewritten by using the execution status acquired while analyzing
  the query.
  Here is an example. The P1-table has the P status, the L1-table has the L status.

  ! SELECT P1-table.col, L1-table.col
! FROM P1-table,L1-table
! where P1-table.col = L1-table.col
  order by P1-table.col;

***************
*** 3551,3558 ****

    SELECT P1-table.col, L1-table.col FROM
! 	dblink(select pool_parallel(SELECT P1-table.col, L1-table.col FROM P1-table,L1-table where P1-table.col = L1-table.col)) 
!   	order by P1-table.col;

  dblink transmits the query to pgpool-II here.
--- 5123,5130 ----

  SELECT P1-table.col, L1-table.col FROM
!     dblink(select pool_parallel(SELECT P1-table.col, L1-table.col FROM P1-table,L1-table where P1-table.col = L1-table.col))
!       order by P1-table.col;

  dblink transmits the query to pgpool-II here.
***************
*** 3572,3602 ****

! In this example, the FROM and WHERE clause and the TARGETLIST are in the L 
  status. Because the subquery is in the P status, the query itself is in the S status.
  The rewriting is, as a consequence, performed as follows.

  ! 	SELECT L1-table.col 
! 	  FROM dblink(SELECT loadbalance(SELECT L1-table.col 
! 	              FROM L1-table 
! 	              WHERE L1-table.col % 2 = 0 
! 	                AND TRUE))
! 		WHERE
! 			L1-table.col %2 = 0 AND 
! 		  L1-table.col IN 
! 		  (
! 		  	SELECT P1-Table FROM 
! 		  	dblink(select pool_parallel(SELECT P1-table FROM P1-table))
! 		  ) ;

! pool_loadbalance is a function responsible for dispatching queries to either node. 

  Query rewriting for aggregate functions

! For grouping queries (aggregate functions and GROUP BY), rewriting tries to 
  reduce the load on the system DB by performing a first aggregate on each node.

--- 5144,5174 ----

! In this example, the FROM and WHERE clause and the TARGETLIST are in the L
  status. Because the subquery is in the P status, the query itself is in the S status.
  The rewriting is, as a consequence, performed as follows.

  ! SELECT L1-table.col
!   FROM dblink(SELECT loadbalance(SELECT L1-table.col
!               FROM L1-table
!               WHERE L1-table.col % 2 = 0
!                 AND TRUE))
!     WHERE
!         L1-table.col %2 = 0 AND
!       L1-table.col IN
!       (
!           SELECT P1-Table FROM
!           dblink(select pool_parallel(SELECT P1-table FROM P1-table))
!       ) ;

! pool_loadbalance is a function responsible for dispatching queries to either node.

  Query rewriting for aggregate functions

! For grouping queries (aggregate functions and GROUP BY), rewriting tries to
  reduce the load on the system DB by performing a first aggregate on each node.

***************
*** 3609,3635 ****

    select count(*) from P1-table;

! 	-> rewrite

      SELECT
          sum(pool_c$1) as count
      FROM
          dblink(select pool_parallel('select count(*) from  P1-table'))
! 					AS pool_$1g (pool_c$1 bigint);

  Query rewriting like above is done in these conditions.

! 		The FROM clause is in the P status. 
! 		
The column specified in the aggregate function (only count, sum, min,
! 		    max, and avg) and GROUP BY is used in the target list. 
! 		
The WHERE clause is in the P status. 
! 		
Only the columns defined by the aggregate function (only count, sum,
! 				min, max, and avg), used in the HAVING clause and the FROM clause, and 
! 				the column specified for GROUP BY are used. 

! Notes on the parallel mode

  The column names and types are needed when a query is analyzed in parallel mode.
  Therefore, when an expression or a function is used in the TARGETLIST of
--- 5181,5208 ----

    select count(*) from P1-table;

!     -> rewrite

      SELECT
          sum(pool_c$1) as count
      FROM
          dblink(select pool_parallel('select count(*) from  P1-table'))
!                     AS pool_$1g (pool_c$1 bigint);

  Query rewriting like above is done in these conditions.

!     The FROM clause is in the P status.
!     The column specified in the aggregate function (only count, sum, min,
!         max, and avg) and GROUP BY is used in the target list.
!     The WHERE clause is in the P status.
!     Only the columns defined by the aggregate function (only count, sum,
!         min, max, and avg), used in the HAVING clause and the FROM clause, and
!         the column specified for GROUP BY are used.

! 
! Notes on the parallel mode

  The column names and types are needed when a query is analyzed in parallel mode.
  Therefore, when an expression or a function is used in the TARGETLIST of
***************
*** 3639,3672 ****
  numeric. For min()/max(), if the argument is a date type, returned datatype is date,
  else, it is assumed numeric. avg() is processed as sum()/count() (sum divided by count).

! 
About the performance of a parallel mode
  Here is a rough estimate of the query performance versus execution status:

! 

! 		
! 
! Execution status Performance
L There is no performance deterioration with a
! single node, excluding the overhead of pgpool-II, because there is no parallel
! querying done at all.
P Parallel processing is fast, especially the
! sequential scans. It's easy to get speedups because the scan of a big table
! becomes the parallel scan of much smaller ones by being distributed on several
! nodes.
! 
S When aggregate functions can be rewritten in a
! parallel fashion, they are fast.

! 

  Tutorial

  A tutorial for pgpool-II is available.

! 

! Copyright © 2003 – 2010 pgpool Global Development Group

--- 5212,5254 ----
  numeric. For min()/max(), if the argument is a date type, returned datatype is date,
  else, it is assumed numeric. avg() is processed as sum()/count() (sum divided by count).

! About the performance of a parallel mode
  Here is a rough estimate of the query performance versus execution status:

! 

! 
! 
! 
! 
! 
! 
! Execution status Performance
L There is no performance deterioration with a single node,
!     excluding the overhead of pgpool-II, because there is no parallel querying done at all.
! 
P Parallel processing is fast, especially the sequential scans.
!     It's easy to get speedups because the scan of a big table becomes the parallel scan
!     of much smaller ones by being distributed on several nodes.
! 
S When aggregate functions can be rewritten in a parallel fashion, they are fast.

! 
! back to top
! 
! 

  Tutorial

  A tutorial for pgpool-II is available.

! back to top
! 
! 
! 
! 

! Copyright © 2003 – 2012 pgpool Global Development Group

Special character	Description
%d	Backend ID of a detached node.
%h	Hostname of a detached node.
%p	Port number of a detached node.
%D	Database cluster directory of a detached node.
%M	Old master node ID.
%m	New master node ID.
%H	Hostname of the new master node.
%P	Old primary node ID.
%%	'%' character

Special character	Description
%d	Backend ID of an attached node.
%h	Hostname of an attached node.
%p	Port number of an attached node.
%D	Database cluster path of an attached node.
%M	Old master node
%m	New master node
%H	Hostname of the new master node.
%P	Old primary node ID.
%%	'%' character

SELECT is inside a transaction block	Y	Y	Y	N	N	N	Y	N
replicate_select is true	Y	Y	N	N	Y	Y	N	N
load_balance_mode is true	Y	N	N	N	Y	N	Y	Y
results(R:replication, M: send only to master, L: load balance)	R	R	M	M	R	R	M	L

Mode	sample file
replication mode	pgpool.conf.sample-replication
master/slave mode(Slony-I)	pgpool.conf.sample-master-slave
master/slave mode(Streaming replication)	pgpool.conf.sample-stream

Special character	Description
%d	Backend ID of a detached node.
%h	Hostname of a detached node.
%p	Port number of a detached node.
%D	Database cluster directory of a detached node.
%M	Old master node ID.
%m	New master node ID.
%H	Hostname of the new master node.
%P	Old primary node ID.
%r	New master port number.
%R	New master database cluster directory.
%%	'%' character

PostgreSQL version	reset_query_list value
7.1 or before	ABORT
7.2 to 8.2	ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT
8.3 or later	ABORT; DISCARD ALL

ALLOW_TO_FAILOVER	Allow to failover or detaching backend. This is the default. ! You cannot specify with DISALLOW_TO_FAILOVER at a same time. !
DISALLOW_TO_FAILOVER	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. !

-c --clear-cache	deletes query cache
-f config_file --config-file config-file	specifies pgpool.conf
-a hba_file --hba-file hba_file	specifies pool_hba.conf
-F pcp_config_file --pcp-password-file	specifies pcp.conf
-n --no-daemon	no daemon mode (terminal is not detached)
-D --discard-status	Discard pgpool_status file and do not restore previous status
-d --debug	debug mode

-c	--clear-cache	deletes query cache
-f config_file	--config-file config-file	specifies pgpool.conf
-a hba_file	--hba-file hba_file	specifies pool_hba.conf
-F pcp_config_file	--pcp-password-file	specifies pcp.conf
-n	--no-daemon	no daemon mode (terminal is not detached)
-D	--discard-status	Discard pgpool_status file and do not restore previous status ! V3.0 -
-C	--clear-oidmaps	Discard oid maps in memqcache_oiddir for on memory query cache ! (only when memqcache_method is 'memcached', ! if shmem, discard whenever pgpool starts). ! V3.2 -
-d	--debug	debug mode

`-m s[mart]` `--mode s[mart]`	waits for clients to disconnect, and shutdown (default)
`-m f[ast]` `--mode f[ast]`	does not wait for clients; shutdown immediately
`-m i[mmediate]` `--mode i[mmediate]`	the same as `'-m f'`

`-m s[mart]`	`--mode s[mart]`	waits for clients to disconnect, and shutdown (default)
`-m f[ast]`	`--mode f[ast]`	does not wait for clients; shutdown immediately
`-m i[mmediate]`	`--mode i[mmediate]`	the same as `'-m f'`

pcp_node_count	retrieves the number of nodes
pcp_node_info	retrieves the node information
pcp_proc_count	retrieves the process list
pcp_proc_info	retrieves the process information
pcp_pool_status V3.1 -	retrieves parameters in pgpool.conf
pcp_systemdb_info	retrieves the System DB information
pcp_detach_node	detaches a node from pgpool-II
pcp_attach_node	attaches a node to pgpool-II
pcp_promote_node V3.1 -	promote a new master node to pgpool-II
pcp_stop_pgpool	stops pgpool-II

UNKNOWNERR	1	Unknown Error (should not occur)
EOFERR	2	EOF Error
NOMEMERR	3	Memory shortage
READERR	4	Error while reading from the server
WRITEERR	5	Error while writing to the server
TIMEOUTERR	6	Timeout
INVALERR	7	Argument(s) to the PCP command was invalid
CONNERR	8	Server connection error
NOCONNERR	9	No connection exists
SOCKERR	10	Socket error
HOSTERR	11	Hostname resolution error
BACKENDERR	12	PCP process error on the server (specifying an invalid ID, etc.)
AUTHERR	13	Authorization failure

Execution status	Processed place
L	Query issued on either node.
P	Returns data to the client by running the ! same query on all nodes and using the parallel execution engine.
S	After processing using the system DB, data ! is returned to the client.

JOIN type	LEFT OUTER JOIN			RIGHT OUTER JOIN			FULL OUTER JOIN			Other
left/right	P	L	S	P	L	S	P	L	S	P	L	S
P	S	P	S	S	S	S	S	S	S	S	P	S
L	S	L	S	P	L	S	S	L	S	P	L	S
S	S	S	S	S	S	S	S	S	S	S	S	S

Execution status	Performance
L	There is no performance deterioration with a ! single node, excluding the overhead of pgpool-II, because there is no parallel ! querying done at all.
P	Parallel processing is fast, especially the ! sequential scans. It's easy to get speedups because the scan of a big table ! becomes the parallel scan of much smaller ones by being distributed on several ! nodes. !
S	When aggregate functions can be rewritten in a ! parallel fashion, they are fast.

What is pgpool-II?

What is pgpool-II?

Supported Platforms

pgpool-II Installation

pgpool-II Installation

Configuring pgpool-II

Configuring pgpool-II

Configuring pcp.conf

Configuring pcp.conf

Configuring pgpool.conf

Common settings

Generating SSL certificates

Failover in the raw Mode

Connection Pool Mode

Failover in the Connection Pool Mode

Replication Mode

condition for load balancing

Configuring pgpool.conf

Connections

Pools

Logs

File locations

Connection pooling

Health check

Failover and failback

Load balancing mode

Backends

SSL

Other

Generating SSL certificates

Failover in the raw Mode

Connection Pool Mode

Failover in the Connection Pool Mode

Replication Mode

condition for load balancing

Failover in Replication Mode

Master/Slave Mode

Failover in Replication Mode

Specific errors in replication mode

Master/Slave Mode

Streaming Replication

Failover with Streaming Replication

Streaming Replication V3.1 -

Failover with Streaming Replication

Streaming Replication

Streaming Replication

Online recovery with Streaming Replication

Online recovery with Streaming Replication

Parallel Mode

Configuring the System DB

Initial Configuration of the System DB

Parallel Mode

Configuring the System DB

Initial Configuration of the System DB

Registering a Partitioning Rule

Registering a Replication Rule

Registering a Partitioning Rule

Registering a Replication Rule

Example for partitioning the pgbench tables

Example for partitioning the pgbench tables

Setting up pool_hba.conf for client authentication (HBA)

Setting Query cache method

Setting up pool_hba.conf for client authentication (HBA)

Setting Query cache method - V3.1 (DEPRECATED)

On memory query Cache V3.2 -

Restrictions

Enabling on memory query cache

Choosing cache storage

When on memory query cache is prohibited

When cache is not used

Configuring

Monitoring caches

Configuring to use shared memory

Configuring to use memcached

memcached Installation

libmemcached Installation

Starting/Stopping pgpool-II

Start pgpool-II

Stop pgpool-II

Reloading pgpool-II configuration files

Configuring `pcp.conf`

Configuring `pcp.conf`

Configuring `pgpool.conf`

Configuring `pgpool.conf`