[Pgpool-general] Registering a partitioning rule with PgPool-II
Alessio Grasso
grasso at iclos.com
Mon May 3 17:03:54 UTC 2010
Hi all,
I installed pgpool-II ver. 2.3.2.2 on debian lenny but I have some problems to set up partitioning function on my system DB. I created the db bench_replication in 2 nodes. In pgpool db I modified the dist_def default function (sample/ dir) to:
CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts(anyelement)
RETURNS integer AS
$BODY$
SELECT CASE WHEN $1 > 0 AND $1 <= 50000 THEN 0
WHEN $1 > 50000 AND $1 <= 100000 THEN 1
ELSE 2
END;
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100;
ALTER FUNCTION pgpool_catalog.dist_def_accounts(anyelement) OWNER TO pgpool;
but when I launch the command 'psql -d bench_replication -p 9999 -c "UPDATE accounts SET abalance=101010 WHERE aid = 50003" -U postgres;'
the pgpool replicate the information to node 0 and node 1, while I would like to distribute the information only to node 1.
What's wrong?
The pgpool log shows the following:
2010-05-03 19:00:09 DEBUG: pid 26879: key: listen_addresses
2010-05-03 19:00:09 DEBUG: pid 26879: value: '*' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: port
2010-05-03 19:00:09 DEBUG: pid 26879: value: 9999 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: pcp_port
2010-05-03 19:00:09 DEBUG: pid 26879: value: 9898 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: socket_dir
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/run/postgresql' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: pcp_socket_dir
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/run/postgresql' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_socket_dir
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/run/postgresql' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: pcp_timeout
2010-05-03 19:00:09 DEBUG: pid 26879: value: 15 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: num_init_children
2010-05-03 19:00:09 DEBUG: pid 26879: value: 32 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: max_pool
2010-05-03 19:00:09 DEBUG: pid 26879: value: 4 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: child_life_time
2010-05-03 19:00:09 DEBUG: pid 26879: value: 300 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: connection_life_time
2010-05-03 19:00:09 DEBUG: pid 26879: value: 0 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: child_max_connections
2010-05-03 19:00:09 DEBUG: pid 26879: value: 0 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: client_idle_limit
2010-05-03 19:00:09 DEBUG: pid 26879: value: 0 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: authentication_timeout
2010-05-03 19:00:09 DEBUG: pid 26879: value: 60 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: logdir
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/run/postgresql' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: pid_file_name
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/run/pgpool/pgpool.pid' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: replication_mode
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: load_balance_mode
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: replication_stop_on_mismatch
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: replication_stop_on_mismatch: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: replicate_select
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: replicate_select: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: reset_query_list
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'ABORT; DISCARD ALL' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: extract_string_tokens: token: ABORT
2010-05-03 19:00:09 DEBUG: pid 26879: extract_string_tokens: token: DISCARD ALL
2010-05-03 19:00:09 DEBUG: pid 26879: key: print_timestamp
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: master_slave_mode
2010-05-03 19:00:09 DEBUG: pid 26879: value: false kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: connection_cache
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: health_check_timeout
2010-05-03 19:00:09 DEBUG: pid 26879: value: 20 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: health_check_period
2010-05-03 19:00:09 DEBUG: pid 26879: value: 300 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: health_check_user
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'postgres' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: failover_command
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/lib/postgresql/8.3/main/pgpool-failover %d %h %p %D %m %M' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: failback_command
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/lib/postgresql/8.3/main/pgpool-failback %d %h %p %D %m %M' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: fail_over_on_backend_error
2010-05-03 19:00:09 DEBUG: pid 26879: value: false kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: insert_lock
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: ignore_leading_white_space
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: log_statement
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: log_per_node_statement
2010-05-03 19:00:09 DEBUG: pid 26879: value: false kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: log_connections
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: log_hostname
2010-05-03 19:00:09 DEBUG: pid 26879: value: true kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: parallel_mode
2010-05-03 19:00:09 DEBUG: pid 26879: value: false kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: enable_query_cache
2010-05-03 19:00:09 DEBUG: pid 26879: value: false kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: pgpool2_hostname
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'localhost' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: system_db_hostname
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'localhost' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: system_db_port
2010-05-03 19:00:09 DEBUG: pid 26879: value: 5432 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: system_db_dbname
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'pgpool' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: system_db_schema
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'pgpool_catalog' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: system_db_user
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'pgpool' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: system_db_password
2010-05-03 19:00:09 DEBUG: pid 26879: value: '' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: enable_pool_hba
2010-05-03 19:00:09 DEBUG: pid 26879: value: false kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: recovery_user
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'pgpool2' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: recovery_password
2010-05-03 19:00:09 DEBUG: pid 26879: value: '' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: recovery_1st_stage_command
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'base-backup' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: recovery_2nd_stage_command
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'pgpool-recovery-pitr' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: recovery_timeout
2010-05-03 19:00:09 DEBUG: pid 26879: value: 60 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: client_idle_limit_in_recovery
2010-05-03 19:00:09 DEBUG: pid 26879: value: 0 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: lobj_lock_table
2010-05-03 19:00:09 DEBUG: pid 26879: value: '' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: ssl
2010-05-03 19:00:09 DEBUG: pid 26879: value: false kind: 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: replication_timeout
2010-05-03 19:00:09 DEBUG: pid 26879: value: 10000 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_hostname0
2010-05-03 19:00:09 DEBUG: pid 26879: value: 'localhost' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_port0
2010-05-03 19:00:09 DEBUG: pid 26879: value: 5432 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: pool_config: port slot number 0
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_weight0
2010-05-03 19:00:09 DEBUG: pid 26879: value: 1 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: pool_config: weight slot number 0 weight: 1.000000
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_data_directory0
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/lib/postgresql/8.3/main' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_hostname1
2010-05-03 19:00:09 DEBUG: pid 26879: value: '192.168.253.30' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_port1
2010-05-03 19:00:09 DEBUG: pid 26879: value: 5432 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: pool_config: port slot number 1
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_weight1
2010-05-03 19:00:09 DEBUG: pid 26879: value: 1 kind: 2
2010-05-03 19:00:09 DEBUG: pid 26879: pool_config: weight slot number 1 weight: 1.000000
2010-05-03 19:00:09 DEBUG: pid 26879: key: backend_data_directory1
2010-05-03 19:00:09 DEBUG: pid 26879: value: '/var/lib/postgresql/8.3/main' kind: 4
2010-05-03 19:00:09 DEBUG: pid 26879: num_backends: 2 num_backends: 2 total_weight: 2.000000
2010-05-03 19:00:09 DEBUG: pid 26879: backend 0 weight: 1073741823.500000
2010-05-03 19:00:09 DEBUG: pid 26879: backend 1 weight: 1073741823.500000
2010-05-03 19:00:09 DEBUG: pid 26880: I am 26880
2010-05-03 19:00:09 DEBUG: pid 26881: I am 26881
2010-05-03 19:00:09 DEBUG: pid 26882: I am 26882
2010-05-03 19:00:09 DEBUG: pid 26883: I am 26883
2010-05-03 19:00:09 DEBUG: pid 26884: I am 26884
2010-05-03 19:00:09 DEBUG: pid 26885: I am 26885
2010-05-03 19:00:09 DEBUG: pid 26886: I am 26886
2010-05-03 19:00:09 DEBUG: pid 26887: I am 26887
2010-05-03 19:00:09 DEBUG: pid 26888: I am 26888
2010-05-03 19:00:09 DEBUG: pid 26889: I am 26889
2010-05-03 19:00:09 DEBUG: pid 26890: I am 26890
2010-05-03 19:00:09 DEBUG: pid 26891: I am 26891
2010-05-03 19:00:09 DEBUG: pid 26892: I am 26892
2010-05-03 19:00:09 DEBUG: pid 26893: I am 26893
2010-05-03 19:00:09 DEBUG: pid 26894: I am 26894
2010-05-03 19:00:09 DEBUG: pid 26895: I am 26895
2010-05-03 19:00:09 DEBUG: pid 26896: I am 26896
2010-05-03 19:00:09 DEBUG: pid 26897: I am 26897
2010-05-03 19:00:09 DEBUG: pid 26898: I am 26898
2010-05-03 19:00:09 DEBUG: pid 26899: I am 26899
2010-05-03 19:00:09 DEBUG: pid 26900: I am 26900
2010-05-03 19:00:09 DEBUG: pid 26901: I am 26901
2010-05-03 19:00:09 DEBUG: pid 26902: I am 26902
2010-05-03 19:00:09 DEBUG: pid 26903: I am 26903
2010-05-03 19:00:09 DEBUG: pid 26904: I am 26904
2010-05-03 19:00:09 DEBUG: pid 26905: I am 26905
2010-05-03 19:00:09 DEBUG: pid 26906: I am 26906
2010-05-03 19:00:09 DEBUG: pid 26907: I am 26907
2010-05-03 19:00:09 DEBUG: pid 26908: I am 26908
2010-05-03 19:00:09 DEBUG: pid 26909: I am 26909
2010-05-03 19:00:09 DEBUG: pid 26910: I am 26910
2010-05-03 19:00:09 DEBUG: pid 26911: I am 26911
2010-05-03 19:00:09 LOG: pid 26879: pgpool-II successfully started. version 2.3.2.2 (tomiteboshi)
2010-05-03 19:00:09 DEBUG: pid 26912: I am PCP 26912
2010-05-03 19:00:09 DEBUG: pid 26879: starting health checking
2010-05-03 19:00:09 DEBUG: pid 26879: health_check: 0 th DB node status: 1
2010-05-03 19:00:09 DEBUG: pid 26879: health_check: 1 th DB node status: 1
2010-05-03 19:00:28 DEBUG: pid 26910: I am 26910 accept fd 5
2010-05-03 19:00:28 LOG: pid 26910: connection received: host=[local]
2010-05-03 19:00:28 DEBUG: pid 26910: Protocol Major: 3 Minor: 0 database: bench_replication user: postgres
2010-05-03 19:00:28 DEBUG: pid 26910: new_connection: connecting 0 backend
2010-05-03 19:00:28 DEBUG: pid 26910: new_connection: connecting 1 backend
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 0 length: 8
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 1 length: 8
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: client_encoding value: UTF8
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: client_encoding value: UTF8
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 23
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 23
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: DateStyle value: ISO, DMY
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: DateStyle value: ISO, DMY
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: integer_datetimes value: on
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: integer_datetimes value: on
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 20
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 20
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: is_superuser value: on
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: is_superuser value: on
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: server_encoding value: UTF8
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: server_encoding value: UTF8
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 25
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: server_version value: 8.3.9
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: server_version value: 8.3.7
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 35
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 35
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: session_authorization value: postgres
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: session_authorization value: postgres
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 36
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 36
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: standard_conforming_strings value: off
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: standard_conforming_strings value: off
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 0 length: 23
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length2: master slot: 1 length: 23
2010-05-03 19:00:28 DEBUG: pid 26910: 0 th backend: name: TimeZone value: localtime
2010-05-03 19:00:28 DEBUG: pid 26910: 1 th backend: name: TimeZone value: localtime
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 0 length: 12
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 1 length: 12
2010-05-03 19:00:28 DEBUG: pid 26910: pool_send_auth_ok: send pid 2541 to frontend
2010-05-03 19:00:28 DEBUG: pid 26910: select_load_balancing_node: selected backend id is 1
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: pool_process_query: kind from backend: Z
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 0 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 1 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: message length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: transaction state: I
2010-05-03 19:00:28 DEBUG: pid 26910: read kind from frontend Q(51)
2010-05-03 19:00:28 LOG: pid 26910: statement: UPDATE accounts SET abalance=101010 WHERE aid = 50003
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: Query: BEGIN
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 0 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: Z
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: transaction state: T
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: Query: BEGIN
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 1 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: Z
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: transaction state: T
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 0 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 1 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 1 th backend C NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: pool_process_query: kind from backend: C
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: pool_process_query: kind from backend: Z
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 0 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 1 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: message length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: transaction state: T
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: Query: COMMIT
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 1 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: Z
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: Query: COMMIT
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 0 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: C
2010-05-03 19:00:28 DEBUG: pid 26910: do_command: kind: Z
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 0 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 1 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: message length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: transaction state: I
2010-05-03 19:00:28 DEBUG: pid 26910: read kind from frontend X(58)
2010-05-03 19:00:28 LOG: pid 26910: statement: DISCARD ALL
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 0 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: S
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: S
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: S
2010-05-03 19:00:28 DEBUG: pid 26910: detect_error: kind: S
2010-05-03 19:00:28 DEBUG: pid 26910: wait_for_query_response: waiting for backend 1 completing the query
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: parameter name: is_superuser value: on
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: parameter name: session_authorization value: postgres
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: parameter name: is_superuser value: on
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: parameter name: session_authorization value: postgres
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 1 th backend C NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: pool_process_query: kind from backend: C
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
2010-05-03 19:00:28 DEBUG: pid 26910: pool_process_query: kind from backend: Z
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 0 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: pool_read_message_length: slot: 1 length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: message length: 5
2010-05-03 19:00:28 DEBUG: pid 26910: ReadyForQuery: transaction state: I
2010-05-03 19:00:28 DEBUG: pid 26910: pool_connection_pool_timer: set close time 1272906028
My pgpool.conf is the follow:
#
# pgpool-II configuration file sample
# $Header: /cvsroot/pgpool/pgpool-II/pgpool.conf.sample,v 1.32 2010/01/31 02:22:24 t-ishii Exp $
# Host name or IP address to listen on: '*' for all, '' for no TCP/IP
# connections
listen_addresses = '*'
# Port number for pgpool
port = 9999
# Port number for pgpool communication manager
pcp_port = 9898
# Unix domain socket path. (The Debian package defaults to
# /var/run/postgresql.)
# socket_dir = '/tmp'
socket_dir = '/var/run/postgresql'
# Unix domain socket path for pgpool communication manager.
# (Debian package defaults to /var/run/postgresql)
pcp_socket_dir = '/var/run/postgresql'
# Unix domain socket path for the backend. Debian package defaults to /var/run/postgresql!
backend_socket_dir = '/var/run/postgresql'
# pgpool communication manager timeout. 0 means no timeout, but strongly not recommended!
pcp_timeout = 10
# number of pre-forked child process
num_init_children = 32
# Number of connection pools allowed for a child process
max_pool = 4
# If idle for this many seconds, child exits. 0 means no timeout.
child_life_time = 300
# If idle for this many seconds, connection to PostgreSQL closes.
# 0 means no timeout.
connection_life_time = 0
# If child_max_connections connections were received, child exits.
# 0 means no exit.
child_max_connections = 0
# If client_idle_limit is n (n > 0), the client is forced to be
# disconnected whenever after n seconds idle (even inside an explicit
# transactions!)
# 0 means no disconnect.
client_idle_limit = 0
# Maximum time in seconds to complete client authentication.
# 0 means no timeout.
authentication_timeout = 60
# Logging directory
# logdir = '/tmp'
logdir = '/var/run/postgresql'
# pid file name
pid_file_name = '/var/run/pgpool/pgpool.pid'
# Replication mode
replication_mode = true
# Load balancing mode, i.e., all SELECTs are load balanced.
# This is ignored if replication_mode is false.
load_balance_mode = false
# if there's a data mismatch between master and secondary
# start degeneration to stop replication mode
replication_stop_on_mismatch = true
# If true, replicate SELECT statement when load balancing is disabled.
# If false, it is only sent to the master node.
replicate_select = true
# Semicolon separated list of queries to be issued at the end of a
# session
reset_query_list = 'ABORT; DISCARD ALL'
# for 8.2 or older this should be as follows.
#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
# If true print timestamp on each log line.
print_timestamp = true
# If true, operate in master/slave mode.
master_slave_mode = false
# If true, cache connection pool.
connection_cache = true
# Health check timeout. 0 means no timeout.
health_check_timeout = 20
# Health check period. 0 means no health check.
health_check_period = 300
# Health check user
health_check_user = 'postgres'
# Execute command by failover.
# special values: %d = node id
# %h = host name
# %p = port number
# %D = database cluster path
# %m = new master node id
# %M = old master node id
# %% = '%' character
#
failover_command = '/var/lib/postgresql/8.3/main/pgpool-failover %d %h %p %D %m %M'
# Execute command by failback.
# special values: %d = node id
# %h = host name
# %p = port number
# %D = database cluster path
# %m = new master node id
# %M = old master node id
# %% = '%' character
#
failback_command = '/var/lib/postgresql/8.3/main/pgpool-failback %d %h %p %D %m %M'
# failback_command = ''
# If true, trigger fail over when writing to the backend communication
# socket fails. This is the same behavior of pgpool-II 2.2.x or
# earlier. If set to false, pgpool will report an error and disconnect
# the session.
fail_over_on_backend_error = true
# If true, automatically locks a table with INSERT statements to keep
# SERIAL data consistency. If the data does not have SERIAL data
# type, no lock will be issued. An /*INSERT LOCK*/ comment has the
# same effect. A /NO INSERT LOCK*/ comment disables the effect.
insert_lock = true
# If true, ignore leading white spaces of each query while pgpool judges
# whether the query is a SELECT so that it can be load balanced. This
# is useful for certain APIs such as DBI/DBD which is known to adding an
# extra leading white space.
ignore_leading_white_space = true
# If true, print all statements to the log. Like the log_statement option
# to PostgreSQL, this allows for observing queries without engaging in full
# debugging.
log_statement = false
# If true, print all statements to the log. Similar to log_statement except
# that prints DB node id and backend process id info.
log_per_node_statement = false
# If true, incoming connections will be printed to the log.
log_connections = true
# If true, hostname will be shown in ps status. Also shown in
# connection log if log_connections = true.
# Be warned that this feature will add overhead to look up hostname.
log_hostname = true
# if non 0, run in parallel query mode
parallel_mode = false
# if non 0, use query cache
enable_query_cache = false
#set pgpool2 hostname
pgpool2_hostname = 'localhost'
# system DB info
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
# backend_hostname, backend_port, backend_weight
# here are examples
# backend_data_directory1 = '/data1'
# backend_hostname2 = '192.168.253.30'
# backend_port2 = 5432
# backend_weight2 = 1
# backend_data_directory1 = '/data1'
# - HBA -
# If true, use pool_hba.conf for client authentication. In pgpool-II
# 1.1, the default value is false. The default value will be true in
# 1.2.
enable_pool_hba = false
# - online recovery -
# online recovery user
recovery_user = 'pgpool2'
# online recovery password
recovery_password = ''
# execute a command in first stage.
recovery_1st_stage_command = 'base-backup'
# execute a command in second stage.
recovery_2nd_stage_command = 'pgpool-recovery-pitr'
# maximum time in seconds to wait for the recovering node's postmaster
# start-up. 0 means no wait.
# this is also used as a timer waiting for clients disconnected before
# starting 2nd stage
recovery_timeout = 90
# If client_idle_limit_in_recovery is n (n > 0), the client is forced
# to be disconnected whenever after n seconds idle (even inside an
# explicit transactions!) 0 means no disconnect. This parameter only
# takes effect in recovery 2nd stage.
client_idle_limit_in_recovery = 0
# Specify table name to lock. This is used when rewriting lo_creat
# command in replication mode. The table must exist and has writable
# permission to public. If the table name is '', no rewriting occurs.
lobj_lock_table = ''
# If true, enable SSL support for both frontend and backend connections.
# note that you must also set ssl_key and ssl_cert for SSL to work in
# the frontend connections.
ssl = false
# path to the SSL private key file
#ssl_key = './server.key'
# path to the SSL public certificate file
#ssl_cert = './server.cert'
# If either ssl_ca_cert or ssl_ca_cert_dir is set, then certificate
# verification will be performed to establish the authenticity of the
# certificate. If neither is set to a nonempty string then no such
# verification takes place. ssl_ca_cert should be a path to a single
# PEM format file containing CA root certificate(s), whereas ssl_ca_cert_dir
# should be a directory containing such files. These are analagous to the
# -CAfile and -CApath options to openssl verify(1), respectively.
#ssl_ca_cert = ''
#ssl_ca_cert_dir = ''
replication_timeout = 5000
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/8.3/main'
backend_hostname1 = '192.168.253.30'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/8.3/main'
Thanks in advance,
Alessio Grasso
More information about the Pgpool-general
mailing list