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
psql -f insert_lock.sql template1

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

username:[password encrypted in md5]

pg_md5 -p
password: <your password>

./pg_md5 foo
acbd18db4cc2f85cedef654fccc4a4d8

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.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.

failed_node=$1
new_master=$2
trigger_file=$3

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

# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file

exit 0;

chmod 755 failover_stream.sh

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 = ''

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

psql -f $prefix/share/system_db.sql pgpool

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 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 TABLE pgpool_catalog.query_cache (
  hash TEXT,
  query TEXT,
  value bytea,
  dbname TEXT,
  create_time TIMESTAMP WITH TIME ZONE,
  PRIMARY KEY(hash, dbname)
);

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 [-f config_file][-F pcp_config_file] [-m {s[mart]|f[ast]|i[mmediate]}] stop

pgpool [-c][-f config_file][-a hba_file][-F pcp_config_file] reload

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/

% 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
  if [ "$i" != "" ];then
    psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i
  fi
done

psql -p $port -c "SELECT pgpool_switch_xlog('$archdir')" template1

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

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

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

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

recovery_1st_stage_command = 'pgpool_recovery'
recovery_2nd_stage_command = 'pgpool_recovery'

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)

$ mv /usr/local/pgsql /usr/local/pgsql-old

$ ln -s /usr/local/pgsql-new /usr/local/pgsql

$ ln -s /usr/local/pgsql-old/data /usr/local/pgsql/data

2010-07-23 16:42:57 ERROR: pid 20031: health check failed. 1 th host foo at port 5432 is down
2010-07-23 16:42:57 LOG:   pid 20031: set 1 th backend down status
2010-07-23 16:42:57 LOG:   pid 20031: starting degeneration. shutdown host foo(5432)
2010-07-23 16:42:58 LOG:   pid 20031: failover_handler: set new master node: 0
2010-07-23 16:42:58 LOG:   pid 20031: failover done. shutdown host foo(5432)

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

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]

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

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

CREATE TABLE rel1(
  d1 date DEFAULT CURRENT_DATE + 1
)

CREATE TABLE rel1(
  d1 date DEFAULT CURRENT_DATE
)

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

CREATE TABLE rel1(
  c1 int,
  c2 timestamp default now()
)

INSERT INTO rel1(c1) VALUES(1)

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

INSERT INTO rel1(c1) SELECT 1

INSERT INTO t(x) VALUES (DEFAULT);

INSERT INTO t(x) VALUES (func());

UPDATE branches set bid = 100 where bid = (select max(bid) from beances);

SELECT * FROM  branches where bid = (select max(bid) from beances) FOR UPDATE;

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 a, b where a.i = b.i

=# 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;
A) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;
B) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
A) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
B) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;

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

schema.object

set search_path = xxx

e.g.)  $ pcp_node_count 10 localhost 9898 postgres hogehoge

First argument    - timeout value in seconds. PCP disconnects if
                    pgpool-II does not respond in this many seconds.
Second argument   - pgpool-II's hostname
Third argument    - PCP port number
Fourth argument   - PCP username
Fifth argument    - PCP password

Format:
pcp_node_count  _timeout_  _host_  _port_  _userid_  _passwd_

Format:
pcp_node_info  _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_

$ pcp_node_info 10 localhost 9898 postgres hogehoge 0
host1 5432 1 1073741823.500000

The result is in the following order:
1. hostname
2. port number
3. status
4. load balance weight

Status is represented by a digit from [0 to 3].
0 - This state is only used during the initialization. PCP will never display it.
1 - Node is up. No connections yet.
2 - Node is up. Connections are pooled.
3 - Node is down.

$ pcp_node_info --verbose 10 localhost 9898 postgres hogehoge 0
Hostname: host1
Port    : 5432
Status  : 1
Weight  : 0.5

Format:
pcp_proc_count  _timeout_  _host_  _port_  _userid_  _passwd_

Format:
pcp_proc_info  _timeout_  _host_  _port_  _userid_  _passwd_  _processid_

$ pcp_proc_info 10 localhost 9898 postgres hogehoge 3815
postgres_db postgres 1150769932 1150767351 3 0 1 1467 1
postgres_db postgres 1150769932 1150767351 3 0 1 1468 1

The result is in the following order:
1. connected database name
2. connected username
3. process start-up timestamp
4. connection created timestamp
5. protocol major version
6. protocol minor version
7. connection-reuse counter
8. PostgreSQL backend process id
9. 1 if frontend conncted 0 if not

$ pcp_proc_info --verbose 10 localhost 9898 postgres hogehoge 3815
Database     : postgres_db
Username     : postgres
Start time   : 1150769932
Creation time: 1150767351
Major        : 3
Minor        : 0
Counter      : 1
PID          : 1467
Connected    : 1
Database     : postgres_db
Username     : postgres
Start time   : 1150769932
Creation time: 1150767351
Major        : 3
Minor        : 0
Counter      : 1
PID          : 1468
Connected    : 1

Format:
pcp_pool_status _timeout_  _host_  _port_  _userid_  _passwd_

$ 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

Format:
pcp_systemdb_info  _timeout_  _host_  _port_  _userid_  _passwd_

$ pcp_systemdb_info 10 localhost 9898 postgres hogehoge
localhost 5432 yamaguti '' pgpool_catalog pgpool 3
yamaguti public accounts aid 4 aid bid abalance filler integer integer integer character(84) dist_def_accounts
yamaguti public branches bid 3 bid bbalance filler integer integer character(84) dist_def_branches
yamaguti public tellers bid 4 tid bid tbalance filler integer integer integer character(84) dist_def_tellers

First, the System DB information will be displayed on the first
line. The result is in the following order:
1. hostname
2. port number
3. username
4. password. '' for no password.
5. schema name
6. database name
7. number of partioning rules defined

1. targeted partitioning database name
2. targeted partitioning schema name
3. targeted partitioning table name
4. partitioning key column name
5. number of columns in the targeted table
6. column names (displayed as many as said in 5.)
7. column types (displayed as many as said in 5.)
8. partitioning rule function name

Format:
pcp_detach_node  [-g] _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_

Format:
pcp_attach_node  _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_

Attaches the given node to pgpool-II.

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

Format:
pcp_stop_pgpool  _timeout_  _host_  _port_  _userid_  _passwd_  _mode_

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

書式：
pcp_recovery_node  _timeout_  _host_  _port_  _userid_  _passwd_  _nodeid_

Attaches the given backend node with recovery.

SELECT * FROM (SELECT * FROM P1-table) as P2-table;

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

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

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

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

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;

SELECT L1-table.col FROM L1-table WHERE L1-table.col % 2 = 0 AND L1-table.col IN (SELECT P1-table FROM P1-table) ;

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

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

`--prefix=path`	pgpool-II binaries and docs will be installed in this directory. Default value is `/usr/local`
`--with-pgsql=path`	The top directory where PostgreSQL's client libraries are installed. Default value is provided by `pg_config`
`--with-openssl`	pgpool-II binaries will be built with OpenSSL support. OpenSSL support is disabled by default. V2.3 -
`--enable-sequence-lock`	Use insert_lock compatible with pgpool-II 3.0 series(until 3.0.4). pgpool-II locks against a row in the sequence table. PostgreSQL 8.2 or later which was released after June 2011 cannot use this lock method. V3.1 -
`--enable-table-lock`	Use insert_lock compatible with pgpool-II 2.2 and 2.3 series. pgpool-II locks against the insert target table. This lock method is deprecated because it causes a lock conflict with VACUUM. V3.1 -
`--with-memcached=path`	pgpool-II binaries will use memcached for on memory query cache. You have to install libmemcached. V3.2 -

Function/Mode	Raw Mode (*3)	Replication Mode	Master/Slave Mode	Parallel Query Mode
Connection Pool	X	O	O	O
Replication	X	O	X	(*1)
Load Balance	X	O	O	(*1)
Failover	O	O	O	X
Online recovery	X	0	(*2)	X
Parallel Query	X	X	X	O
Required # of Servers	1 or higher	2 or higher	2 or higher	2 or higher
System DB required?	no	no	no	yes

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

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.
%r	New master port number.
%R	New master database cluster directory.
%%	'%' character

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.

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

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

-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'`

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?

Supported Platforms

pgpool-II Installation

Configuring pgpool-II

Configuring pcp.conf

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

Specific errors in replication mode

Master/Slave Mode

Streaming Replication V3.1 -

Failover with Streaming Replication

Streaming Replication

Online recovery with Streaming Replication

Parallel Mode

Configuring the System DB

Initial Configuration of the System DB

Registering a Partitioning Rule

Registering a Replication Rule

Example for partitioning the pgbench tables

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

SHOW commands

Overview

pool_status

pool_nodes V3.0 -

pool_processes V3.0 -

pool_pools V3.0 -

pool_version V3.0 -

pool_cache V3.0 -

Online Recovery

Overview

Configuration for online recovery

Installing C language functions

Recovery script deployment

Online recovery by PITR

1st stage

2nd stage

pgpool_remote_start

Online recovery with rsync.

How to perform online recovery

PostgreSQL version up using online recovery

replication mode case

If you are using streaming replication

Backup

replication mode and master/slave mode

Parallel mode

Backing up of system DB

Configuring `pcp.conf`

Configuring `pgpool.conf`