[Pgpool-general] [pgpool problem] about streaming replication and load balancing.

Fri Nov 18 04:26:13 UTC 2011

thank you for help.
this is it.

------------------------------
--------------------------------------------------------------------------------
$ pgpool -d -n
2011-11-18 13:23:05 DEBUG: pid 16892: key: listen_addresses
2011-11-18 13:23:05 DEBUG: pid 16892: value: '*' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: port
2011-11-18 13:23:05 DEBUG: pid 16892: value: 9999 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: socket_dir
2011-11-18 13:23:05 DEBUG: pid 16892: value: '/tmp' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: pcp_port
2011-11-18 13:23:05 DEBUG: pid 16892: value: 9898 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: pcp_socket_dir
2011-11-18 13:23:05 DEBUG: pid 16892: value: '/tmp' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_hostname0
2011-11-18 13:23:05 DEBUG: pid 16892: value: '192.168.3.160' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_port0
2011-11-18 13:23:05 DEBUG: pid 16892: value: 5432 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: port slot number 0
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_weight0
2011-11-18 13:23:05 DEBUG: pid 16892: value: 1 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: weight slot number 0
weight: 1.000000
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_data_directory0
2011-11-18 13:23:05 DEBUG: pid 16892: value: '/DbCenter_master/data' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_flag0
2011-11-18 13:23:05 DEBUG: pid 16892: value: 'ALLOW_TO_FAILOVER' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: extract_string_tokens: token:
ALLOW_TO_FAILOVER
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: allow_to_failover on
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: slot number 0 flag: 0000
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_hostname1
2011-11-18 13:23:05 DEBUG: pid 16892: value: '192.168.3.141' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_port1
2011-11-18 13:23:05 DEBUG: pid 16892: value: 5432 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: port slot number 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_weight1
2011-11-18 13:23:05 DEBUG: pid 16892: value: 1000 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: weight slot number 1
weight: 1000.000000
2011-11-18 13:23:05 DEBUG: pid 16892: key: backend_flag1
2011-11-18 13:23:05 DEBUG: pid 16892: value: 'ALLOW_TO_FAILOVER' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: extract_string_tokens: token:
ALLOW_TO_FAILOVER
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: allow_to_failover on
2011-11-18 13:23:05 DEBUG: pid 16892: pool_config: slot number 1 flag: 0000
2011-11-18 13:23:05 DEBUG: pid 16892: key: enable_pool_hba
2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: authentication_timeout
2011-11-18 13:23:05 DEBUG: pid 16892: value: 60 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: ssl
2011-11-18 13:23:05 DEBUG: pid 16892: value: off kind: 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: num_init_children
2011-11-18 13:23:05 DEBUG: pid 16892: value: 32 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: max_pool
2011-11-18 13:23:05 DEBUG: pid 16892: value: 4 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: child_life_time
2011-11-18 13:23:05 DEBUG: pid 16892: value: 300 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: child_max_connections
2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: connection_life_time
2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: client_idle_limit
2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
2011-11-18 13:23:05 DEBUG: pid 16892: key: log_destination
2011-11-18 13:23:05 DEBUG: pid 16892: value: 'syslog' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: print_timestamp
2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: log_connections
2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: log_hostname
2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: log_statement
2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: log_per_node_statement
2011-11-18 13:23:05 DEBUG: pid 16892: value: on kind: 1
2011-11-18 13:23:05 DEBUG: pid 16892: key: log_standby_delay
2011-11-18 13:23:05 DEBUG: pid 16892: value: 'if_over_threshold' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: syslog_facility
2011-11-18 13:23:05 DEBUG: pid 16892: value: 'LOCAL0' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: syslog_ident
2011-11-18 13:23:05 DEBUG: pid 16892: value: 'pgpool' kind: 4
2011-11-18 13:23:05 DEBUG: pid 16892: key: debug_level
2011-11-18 13:23:05 DEBUG: pid 16892: value: 0 kind: 2
pid file found but it seems bogus. Trying to start pgpool anyway...

--------------------------------------------------------------------------------------------------------------
what do you have something idea about it?

良かったら、irc://irc.freenode.net/pgpool で、相談させて頂きたいですが・・・

2011/11/16 Tatsuo Ishii <ishii at sraoss.co.jp>

> Can you show me debug out? I mean run pgpool with "-d -n" option.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
> > yes, i am sure.
> >
> > I tried it)
> >
> > I tried a test how to change backend_hostname0 and backend_hostname1.
> > (like as
> > ..
> > backend_hostname0='192.168.3.141'
> > backend_weight0 = 1     # but not changed 'backend_weight0'
> > backend_hostname1='192.168.3.160'
> > backend_weight1 = 1000    # not changed 'backend_weight1' too.
> > ...
> > )
> >
> > then pgpool has always connected backend_hostname0(192.168.3.141).
> >
> > i think that this mean is 'can not reading pgpool load balancing' or 'can
> > not reading PG server after backend_hostname0'.
> >
> > what do you have a idea about something please?
> >
> >
> > 2011/11/11 Martín Marqués <martin.marques at gmail.com>
> >
> >> Are you sure that pgpool can connect to the second PG server?
> >>
> >> 2011/11/10 イジュヨン <elijah9 at gmail.com>:
> >> > hello, developers and anybody using pgpool.
> >> >
> >> > i have a something problem about 'streaming replication' and 'load
> >> > balancing'.
> >> > i don't know what mail address for to ask like this problem. then i
> send
> >> > this asking mail to you.
> >> >
> >> > (if i mistake this doing, please teach me that i can ask mailling
> list.)
> >> >
> >> > well, my question is this)
> >> >
> >> > <server spac>
> >> >  - my 1st postgresql server(os:ubuntu10.4, ip:192.168.3.160,
> postgresql
> >> > version 9.1) run normal operations.
> >> >
> >> >    (i checked that requested query success by use log)
> >> >
> >> >  - my 2nd postgresql server(os:ubuntu10.4, ip:192.168.3.141,
> postgresql
> >> > version 9.1) run normal operations too.
> >> >    (i checked it too.)
> >> >
> >> >  - my pgpool server(ip:192.168.3.160 - with 1st postgresql server,
> pgpool
> >> > version 3.1 -
> >> >
> >>
> http://ftp2.jp.postgresql.org/pub/postgresql/projects/pgFoundry/pgpool/pgpool-II-3.1.tar.gz
> >> )
> >> > run normal operations too.
> >> >
> >> >    (i checked that requested query success by use log too.)
> >> >
> >> >
> >> > <pgpool conf information>
> >> > # cat pgpool.conf
> >> > listen_addresses = '*'
> >> > port = 9999
> >> > socket_dir = '/tmp'
> >> > pcp_port = 9898
> >> >
> >> > pcp_socket_dir = '/tmp'
> >> > backend_hostname0 = '192.168.3.160'
> >> > backend_port0 = 5432
> >> > backend_weight0 = 1
> >> > backend_data_directory0 = '/DbCenter_master/data'
> >> > backend_flag0 = 'ALLOW_TO_FAILOVER'
> >> >
> >> > backend_hostname1 = '192.168.3.141'
> >> > backend_port1 = 5432
> >> > backend_weight1 = 1000
> >> > backend_flag1 = 'ALLOW_TO_FAILOVER'
> >> > enable_pool_hba = on
> >> > authentication_timeout = 60
> >> > ssl = off
> >> > num_init_children = 32
> >> >
> >> > max_pool = 4
> >> > child_life_time = 300
> >> > child_max_connections = 0
> >> > connection_life_time = 0
> >> > client_idle_limit = 0
> >> > log_destination = 'syslog'
> >> > print_timestamp = on
> >> > log_connections = on
> >> > log_hostname = on
> >> >
> >> > log_statement = on
> >> > log_per_node_statement = on
> >> > log_standby_delay = 'if_over_threshold'
> >> > syslog_facility = 'LOCAL0'
> >> > syslog_ident = 'pgpool'
> >> > debug_level = 0
> >> > pid_file_name = '/var/run/pgpool/pgpool.pid'
> >> >
> >> > logdir = '/tmp'
> >> > connection_cache = off
> >> > reset_query_list = 'ABORT; DISCARD ALL'
> >> > replication_mode = off
> >> > replicate_select = off
> >> > insert_lock = off
> >> > lobj_lock_table = ''
> >> > replication_stop_on_mismatch = off
> >> >
> >> > failover_if_affected_tuples_mismatch = off
> >> > load_balance_mode = on
> >> > ignore_leading_white_space = on
> >> > white_function_list = ''
> >> > black_function_list = 'currval,lastval,nextval,setval'
> >> > master_slave_mode = on
> >> >
> >> > master_slave_sub_mode = 'stream'
> >> > sr_check_period = 10
> >> > sr_check_user = 'repl_user'
> >> > sr_check_password = 'password'
> >> > delay_threshold = 10000000
> >> > follow_master_command = 'date > /DataCenter_nas/abc'
> >> >
> >> > parallel_mode = off
> >> > enable_query_cache = off
> >> > pgpool2_hostname = ''
> >> > system_db_hostname  = 'localhost'
> >> > system_db_port = 5432
> >> > system_db_dbname = 'pgpool'
> >> > system_db_schema = 'pgpool_catalog'
> >> >
> >> > system_db_user = 'pgpool'
> >> > system_db_password = ''
> >> > health_check_period = 0
> >> > health_check_timeout = 20
> >> > health_check_user = 'nobody'
> >> > health_check_password = ''
> >> > failover_command = 'date > /DataCenter_nas/abc2'
> >> >
> >> > failback_command = 'date > /DataCenter_nas/abc3'
> >> > fail_over_on_backend_error = on
> >> > recovery_user = 'nobody'
> >> > recovery_password = ''
> >> > recovery_1st_stage_command = ''
> >> > recovery_2nd_stage_command = ''
> >> >
> >> > recovery_timeout = 90
> >> > client_idle_limit_in_recovery = 0
> >> > relcache_expire = 0
> >> > #-------------- the end of pgpool conf---------------
> >> >
> >> >
> >> > <i tried it>
> >> > i send query request to pgpool, and 1st postgresql catch it always.
> >> >
> >> > it is a wrong working
> >> >  why i did set pgpools to 1:1000 about backend_weight0 and
> >> backend_weight1
> >> > values.(look my pgpool.conf file)
> >> > i think that this case's good working is almost catch 2nd postgresql
> >> server.
> >> >
> >> > i don't know this working reason(about wrong pgpool's load balancing
> and
> >> > after backend_hostname1 setting values).
> >> >
> >> > do you have to teach me about this problem or something idea?
> >> > I hope your help. please help me.
> >> >
> >> >
> >> > i waiting your mail.
> >> > thanks.
> >> >
> >> >
> >> > _______________________________________________
> >> > Pgpool-general mailing list
> >> > Pgpool-general at pgfoundry.org
> >> > http://pgfoundry.org/mailman/listinfo/pgpool-general
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> Martín Marqués
> >> select 'martin.marques' || '@' || 'gmail.com'
> >> DBA, Programador, Administrador
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20111118/9ced8591/attachment.html>