[Pgpool-general] Help getting pgpool setup with hot standby.

Mon Oct 18 14:05:01 UTC 2010

Tatsuo,

Here is the updated log:

postgres at tstsrv:/usr/local/pgpool2/etc$ ../bin/pgpool -f ./pgpool.conf -F
./pcp.conf -n -d -D
2010-10-18 10:02:52 DEBUG: pid 8850: key: listen_addresses
2010-10-18 10:02:52 DEBUG: pid 8850: value: '*' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: port
2010-10-18 10:02:52 DEBUG: pid 8850: value: 9999 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: pcp_port
2010-10-18 10:02:52 DEBUG: pid 8850: value: 9898 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: socket_dir
2010-10-18 10:02:52 DEBUG: pid 8850: value: '/tmp' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: pcp_socket_dir
2010-10-18 10:02:52 DEBUG: pid 8850: value: '/tmp' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: backend_socket_dir
2010-10-18 10:02:52 DEBUG: pid 8850: value: '/tmp' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: pcp_timeout
2010-10-18 10:02:52 DEBUG: pid 8850: value: 10 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: num_init_children
2010-10-18 10:02:52 DEBUG: pid 8850: value: 32 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: max_pool
2010-10-18 10:02:52 DEBUG: pid 8850: value: 4 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: child_life_time
2010-10-18 10:02:52 DEBUG: pid 8850: value: 10 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: connection_life_time
2010-10-18 10:02:52 DEBUG: pid 8850: value: 30 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: child_max_connections
2010-10-18 10:02:52 DEBUG: pid 8850: value: 0 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: client_idle_limit
2010-10-18 10:02:52 DEBUG: pid 8850: value: 0 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: authentication_timeout
2010-10-18 10:02:52 DEBUG: pid 8850: value: 60 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: logdir
2010-10-18 10:02:52 DEBUG: pid 8850: value: '/tmp' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: pid_file_name
2010-10-18 10:02:52 DEBUG: pid 8850: value: '/var/run/pgpool/pgpool.pid'
kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: replication_mode
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: load_balance_mode
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: replication_stop_on_mismatch
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: replication_stop_on_mismatch: 0
2010-10-18 10:02:52 DEBUG: pid 8850: key:
failover_if_affected_tuples_mismatch
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: failover_if_affected_tuples_mismatch: 0
2010-10-18 10:02:52 DEBUG: pid 8850: key: replicate_select
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: replicate_select: 0
2010-10-18 10:02:52 DEBUG: pid 8850: key: reset_query_list
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'ABORT; DISCARD ALL' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: extract_string_tokens: token: ABORT
2010-10-18 10:02:52 DEBUG: pid 8850: extract_string_tokens: token:  DISCARD
ALL
2010-10-18 10:02:52 DEBUG: pid 8850: key: white_function_list
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: black_function_list
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'nextval,setval' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: extract_string_tokens: token: nextval
2010-10-18 10:02:52 DEBUG: pid 8850: extract_string_tokens: token: setval
2010-10-18 10:02:52 DEBUG: pid 8850: key: print_timestamp
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: master_slave_mode
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: master_slave_sub_mode
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'stream' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: delay_threshold
2010-10-18 10:02:52 DEBUG: pid 8850: value: 10 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: log_standby_delay
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'always' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: connection_cache
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: health_check_timeout
2010-10-18 10:02:52 DEBUG: pid 8850: value: 20 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: health_check_period
2010-10-18 10:02:52 DEBUG: pid 8850: value: 15 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: health_check_user
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'pgpool' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: failover_command
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: failback_command
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: fail_over_on_backend_error
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: insert_lock
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: ignore_leading_white_space
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: log_statement
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: log_per_node_statement
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: log_connections
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: log_hostname
2010-10-18 10:02:52 DEBUG: pid 8850: value: true kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: parallel_mode
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: enable_query_cache
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: pgpool2_hostname
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'tstsrv' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: system_db_hostname
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'localhost' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: system_db_port
2010-10-18 10:02:52 DEBUG: pid 8850: value: 6000 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: system_db_dbname
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'pgpool' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: system_db_schema
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'pgpool_catalog' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: system_db_user
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'pgpool' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: system_db_password
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'password' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: backend_hostname0
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: backend_port0
2010-10-18 10:02:52 DEBUG: pid 8850: value: 6000 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: pool_config: port slot number 0
2010-10-18 10:02:52 DEBUG: pid 8850: key: backend_hostname1
2010-10-18 10:02:52 DEBUG: pid 8850: value: '192.168.192.1' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: backend_port1
2010-10-18 10:02:52 DEBUG: pid 8850: value: 6000 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: pool_config: port slot number 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: enable_pool_hba
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: recovery_user
2010-10-18 10:02:52 DEBUG: pid 8850: value: 'nobody' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: recovery_password
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: recovery_1st_stage_command
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: recovery_2nd_stage_command
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: recovery_timeout
2010-10-18 10:02:52 DEBUG: pid 8850: value: 90 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: client_idle_limit_in_recovery
2010-10-18 10:02:52 DEBUG: pid 8850: value: 0 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: key: lobj_lock_table
2010-10-18 10:02:52 DEBUG: pid 8850: value: '' kind: 4
2010-10-18 10:02:52 DEBUG: pid 8850: key: ssl
2010-10-18 10:02:52 DEBUG: pid 8850: value: false kind: 1
2010-10-18 10:02:52 DEBUG: pid 8850: key: debug_level
2010-10-18 10:02:52 DEBUG: pid 8850: value: 100 kind: 2
2010-10-18 10:02:52 DEBUG: pid 8850: num_backends: 2 total_weight: 0.000000
2010-10-18 10:02:52 DEBUG: pid 8850: backend 0 weight: nan
2010-10-18 10:02:52 DEBUG: pid 8850: backend 1 weight: nan
2010-10-18 10:02:52 LOG:   pid 8850: Backend status file /tmp/pgpool_status
discarded
2010-10-18 10:02:52 DEBUG: pid 8851: I am 8851
2010-10-18 10:02:52 DEBUG: pid 8852: I am 8852
2010-10-18 10:02:52 DEBUG: pid 8853: I am 8853
2010-10-18 10:02:52 DEBUG: pid 8854: I am 8854
2010-10-18 10:02:52 DEBUG: pid 8855: I am 8855
2010-10-18 10:02:52 DEBUG: pid 8856: I am 8856
2010-10-18 10:02:52 DEBUG: pid 8857: I am 8857
2010-10-18 10:02:52 DEBUG: pid 8858: I am 8858
2010-10-18 10:02:52 DEBUG: pid 8859: I am 8859
2010-10-18 10:02:52 DEBUG: pid 8860: I am 8860
2010-10-18 10:02:52 DEBUG: pid 8861: I am 8861
2010-10-18 10:02:52 DEBUG: pid 8862: I am 8862
2010-10-18 10:02:52 DEBUG: pid 8863: I am 8863
2010-10-18 10:02:52 DEBUG: pid 8864: I am 8864
2010-10-18 10:02:52 DEBUG: pid 8865: I am 8865
2010-10-18 10:02:52 DEBUG: pid 8866: I am 8866
2010-10-18 10:02:52 DEBUG: pid 8867: I am 8867
2010-10-18 10:02:52 DEBUG: pid 8868: I am 8868
2010-10-18 10:02:52 DEBUG: pid 8869: I am 8869
2010-10-18 10:02:52 DEBUG: pid 8870: I am 8870
2010-10-18 10:02:52 DEBUG: pid 8871: I am 8871
2010-10-18 10:02:52 DEBUG: pid 8872: I am 8872
2010-10-18 10:02:52 DEBUG: pid 8873: I am 8873
2010-10-18 10:02:52 DEBUG: pid 8874: I am 8874
2010-10-18 10:02:52 DEBUG: pid 8875: I am 8875
2010-10-18 10:02:52 DEBUG: pid 8876: I am 8876
2010-10-18 10:02:52 DEBUG: pid 8877: I am 8877
2010-10-18 10:02:52 DEBUG: pid 8878: I am 8878
2010-10-18 10:02:52 DEBUG: pid 8879: I am 8879
2010-10-18 10:02:52 DEBUG: pid 8880: I am 8880
2010-10-18 10:02:52 DEBUG: pid 8881: I am 8881
2010-10-18 10:02:52 DEBUG: pid 8882: I am 8882
2010-10-18 10:02:52 LOG:   pid 8850: pgpool-II successfully started. version
3.0 (umiyameboshi)
2010-10-18 10:02:52 DEBUG: pid 8884: I am 8884
2010-10-18 10:02:52 DEBUG: pid 8884: pool_ssl: SSL requested but SSL support
is not available
2010-10-18 10:02:52 DEBUG: pid 8850: starting health checking
2010-10-18 10:02:52 DEBUG: pid 8850: health_check: 0 th DB node status: 1
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: auth kind: 0
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: parameter status data
received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: backend key data received
2010-10-18 10:02:52 DEBUG: pid 8884: s_do_auth: transaction state: I
2010-10-18 10:02:52 DEBUG: pid 8884: pool_ssl: SSL requested but SSL support
is not available
2010-10-18 10:02:52 ERROR: pid 8884: s_do_auth: expecting R got E
2010-10-18 10:02:52 ERROR: pid 8884: make_persistent_db_connection:
s_do_auth failed
2010-10-18 10:02:52 DEBUG: pid 8850: health_check: 1 th DB node status: 1
2010-10-18 10:02:52 DEBUG: pid 8884: do_query: kind: T
2010-10-18 10:02:52 DEBUG: pid 8884: num_fileds: 1
2010-10-18 10:02:52 DEBUG: pid 8884: do_query: kind: D
2010-10-18 10:02:52 DEBUG: pid 8884: do_query: kind: C
2010-10-18 10:02:52 DEBUG: pid 8884: do_query: kind: Z
2010-10-18 10:02:52 ERROR: pid 8884: check_replication_time_lag: DB node is
valid but no persistent connection
2010-10-18 10:03:07 DEBUG: pid 8850: starting health checking
2010-10-18 10:03:07 DEBUG: pid 8850: health_check: 0 th DB node status: 1
2010-10-18 10:03:07 DEBUG: pid 8884: pool_ssl: SSL requested but SSL support
is not available
2010-10-18 10:03:07 DEBUG: pid 8850: health_check: 1 th DB node status: 1
2010-10-18 10:03:07 ERROR: pid 8884: s_do_auth: expecting R got E
2010-10-18 10:03:07 ERROR: pid 8884: make_persistent_db_connection:
s_do_auth failed
2010-10-18 10:03:07 DEBUG: pid 8884: do_query: kind: T
2010-10-18 10:03:07 DEBUG: pid 8884: num_fileds: 1
2010-10-18 10:03:07 DEBUG: pid 8884: do_query: kind: D
2010-10-18 10:03:07 DEBUG: pid 8884: do_query: kind: C
2010-10-18 10:03:07 DEBUG: pid 8884: do_query: kind: Z
2010-10-18 10:03:07 ERROR: pid 8884: check_replication_time_lag: DB node is
valid but no persistent connection

On Fri, Oct 15, 2010 at 6:46 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > Tatsuo,
> >
> > Here is my log.  I started pgpool II and then connected in from my laptop
> > and issued a select count(*) from public.accounts.  Since this is a
> simple
> > select, so I'm understanding that it should have gone to the slave.
> >
> > Here is the log:
> >
> > postgres at tstsrv:/usr/local/pgpool2/etc$ ../bin/pgpool -f ./pgpool.conf
> -F
> > ./pcp.conf -n -d
> [snip]
>
> It seems pgpool-II remembers previous state when node 1 was down. Can
> you try to start pgpool with adding -D option?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20101018/1ed94271/attachment-0001.html>