postgresql - PgPool2 load ballance -
i have 2 pg servers, 1 master, other 1 slave - normal stream replica works great. need put pgpool before them, make load balancer on both machines - select queries, because of high traffic.
but configure pgpool, can not force use more 1 node, first .. don't know second one. when made attempt iptables , blocked first server, pgpool didn't recover that. tried play weight, allow_to_failover, change guest backend0 backend1, switch on / off master / slave mode, nothing. anyway looks connected 1 (first) server , nothing else.
attaching config pgpool.
# ---------------------------- # pgpool-ii configuration file # ---------------------------- # # file consists of lines of form: # # name = value # # whitespace may used. comments introduced "#" anywhere on line. # complete list of parameter names , allowed values can found in # pgpool-ii documentation. # # file read on server startup , when server receives sighup # signal. if edit file on running system, have sighup # server changes take effect, or use "pgpool reload". # parameters, marked below, require server shutdown , restart # take effect. # #------------------------------------------------------------------------------ # connections #------------------------------------------------------------------------------ # - pgpool connection settings - listen_addresses = '*' # host name or ip address listen on: # '*' all, '' no tcp/ip connections # (change requires restart) port = 5433 # port number # (change requires restart) socket_dir = '/var/run/postgresql' # unix domain socket path # debian package defaults # /var/run/postgresql # (change requires restart) listen_backlog_multiplier = 2 # set backlog parameter of listen(2) # num_init_children * listen_backlog_multiplier. # (change requires restart) serialize_accept = off # whether serialize accept() call avoid thundering herd problem # (change requires restart) # - pgpool communication manager connection settings - pcp_listen_addresses = '*' # host name or ip address pcp process listen on: # '*' all, '' no tcp/ip connections # (change requires restart) pcp_port = 9898 # port number pcp # (change requires restart) pcp_socket_dir = '/var/run/postgresql' # unix domain socket path pcp # debian package defaults # /var/run/postgresql # (change requires restart) # - backend connection settings - backend_hostname0 = 'madmax' # host name or ip address connect backend 0 backend_port0 = 5432 # port number backend 0 backend_weight0 = 1 # weight backend 0 (only in load balancing mode) backend_data_directory0 = '/srv/postgresql/9.5/main' # data directory backend 0 backend_flag0 = 'disallow_to_failover' # controls various backend behavior # allow_to_failover or disallow_to_failover backend_hostname1 = 'vengerberg' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/srv/postgresql/9.5/main' backend_flag1 = 'disallow_to_failover' # - authentication - enable_pool_hba = on # use pool_hba.conf client authentication pool_passwd = 'pool_passwd' # file name of pool_passwd md5 authentication. # "" disables pool_passwd. # (change requires restart) authentication_timeout = 60 # delay in seconds complete client authentication # 0 means no timeout. # - ssl connections - ssl = off # enable ssl support # (change requires restart) #ssl_key = './server.key' # path ssl private key file # (change requires restart) #ssl_cert = './server.cert' # path ssl public certificate file # (change requires restart) #ssl_ca_cert = '' # path single pem format file # containing ca root certificate(s) # (change requires restart) #ssl_ca_cert_dir = '' # directory containing ca root certificate(s) # (change requires restart) #------------------------------------------------------------------------------ # pools #------------------------------------------------------------------------------ # - concurrent session , pool size - num_init_children = 32 # number of concurrent sessions allowed # (change requires restart) max_pool = 4 # number of connection pool caches per connection # (change requires restart) # - life time - child_life_time = 300 # pool exits after being idle many seconds child_max_connections = 0 # pool exits after receiving many connections # 0 means no exit connection_life_time = 0 # connection backend closes after being idle many seconds # 0 means no close client_idle_limit = 0 # client disconnected after being idle many seconds # (even inside explicit transactions!) # 0 means no disconnection #------------------------------------------------------------------------------ # logs #------------------------------------------------------------------------------ # - log - log_destination = 'stderr' # log # valid values combinations of stderr, # , syslog. default stderr. # - log - log_line_prefix = '%t: pid %p: ' # printf-style string output @ beginning of each log line. log_connections = off # log connections log_hostname = off # hostname shown in ps status # , in logs if connections logged log_statement = off # log statements log_per_node_statement = on # log statements # node , backend informations log_standby_delay = 'none' # log standby delay # valid values combinations of always, # if_over_threshold, none # - syslog specific - syslog_facility = 'local0' # syslog local facility. default local0 syslog_ident = 'pgpool' # syslog program identification string # default 'pgpool' # - debug - debug_level = 0 # debug message verbosity level # 0 means no message, 1 or more mean verbose #log_error_verbosity = default # terse, default, or verbose messages #client_min_messages = notice # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error #log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic #------------------------------------------------------------------------------ # file locations #------------------------------------------------------------------------------ pid_file_name = '/var/run/postgresql/pgpool.pid' # pid file name # (change requires restart) logdir = '/var/log/postgresql' # directory of pgpool status file # (change requires restart) #------------------------------------------------------------------------------ # connection pooling #------------------------------------------------------------------------------ connection_cache = off # activate connection pools # (change requires restart) # semicolon separated list of queries # issued @ end of session # default 8.3 , later reset_query_list = 'abort; discard all' # following 1 8.2 , before #reset_query_list = 'abort; reset all; set session authorization default' #------------------------------------------------------------------------------ # replication mode #------------------------------------------------------------------------------ replication_mode = off # activate replication mode # (change requires restart) replicate_select = off # replicate select statements # when in replication mode # replicate_select higher priority # load_balance_mode. insert_lock = on # automatically locks dummy row or table # insert statements keep serial data # consistency # without serial, no lock issued lobj_lock_table = '' # when rewriting lo_creat command in # replication mode, specify table name # lock # - degenerate handling - replication_stop_on_mismatch = off # on disagreement packet kind # sent backend, degenerate node # "minority" # if off, force exit session failover_if_affected_tuples_mismatch = off # on disagreement number of affected # tuples in update/delete queries, # degenerate node # "minority". # if off, abort transaction # keep consistency #------------------------------------------------------------------------------ # load balancing mode #------------------------------------------------------------------------------ load_balance_mode = on # activate load balancing mode # (change requires restart) ignore_leading_white_space = on # ignore leading white spaces of each query white_function_list = '' # comma separated list of function names # don't write database # regexp accepted black_function_list = 'nextval,setval,nextval,setval' # comma separated list of function names # write database # regexp accepted database_redirect_preference_list = '' # comma separated list of pairs of database , node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' # valid streaming replicaton mode only. app_name_redirect_preference_list = '' # comma separated list of pairs of app name , node id. # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby' # valid streaming replicaton mode only. allow_sql_comments = off # if on, ignore sql comments when judging if load balance or # query cache possible. # if off, sql comments prevent judgment # (pre 3.4 behavior). #------------------------------------------------------------------------------ # master/slave mode #------------------------------------------------------------------------------ master_slave_mode = off # activate master/slave mode # (change requires restart) master_slave_sub_mode = 'stream' # master/slave sub mode # valid values combinations slony or # stream. default slony. # (change requires restart) # - streaming - sr_check_period = 0 # streaming replication check period # disabled (0) default sr_check_user = 'pgpool' # streaming replication check user # necessary if disable # streaming replication delay check # sr_check_period = 0 sr_check_password = '' # password streaming replication check user sr_check_database = 'postgres' # database name streaming replication check delay_threshold = 0 # threshold before not dispatching query standby node # unit in bytes # disabled (0) default # - special commands - follow_master_command = '' # executes command after master failover # special values: # %d = node id # %h = host name # %p = port number # %d = database cluster path # %m = new master node id # %h = hostname of new master node # %m = old master node id # %p = old primary node id # %r = new master port number # %r = new master database cluster path # %% = '%' character #------------------------------------------------------------------------------ # health check #------------------------------------------------------------------------------ health_check_period = 0 # health check period # disabled (0) default health_check_timeout = 20 # health check timeout # 0 means no timeout health_check_user = 'nobody' # health check user health_check_password = '' # password health check user health_check_database = '' # database name health check. if '', tries 'postgres' frist, 'template1' health_check_max_retries = 0 # maximum number of times retry failed health check before giving up. health_check_retry_delay = 1 # amount of time wait (in seconds) between retries. connect_timeout = 10000 # timeout value in milliseconds before giving connect backend. # default 10000 ms (10 second). flaky network user may want increase # value. 0 means no timeout. # note value not used health check, # ordinary conection backend. #------------------------------------------------------------------------------ # failover , failback #------------------------------------------------------------------------------ failover_command = '' # executes command @ failover # special values: # %d = node id # %h = host name # %p = port number # %d = database cluster path # %m = new master node id # %h = hostname of new master node # %m = old master node id # %p = old primary node id # %r = new master port number # %r = new master database cluster path # %% = '%' character failback_command = '' # executes command @ failback. # special values: # %d = node id # %h = host name # %p = port number # %d = database cluster path # %m = new master node id # %h = hostname of new master node # %m = old master node id # %p = old primary node id # %r = new master port number # %r = new master database cluster path # %% = '%' character fail_over_on_backend_error = on # initiates failover when reading/writing # backend communication socket fails # if set off, pgpool report # error , disconnect session. search_primary_node_timeout = 300 # timeout in seconds search # primary node when failover occurs. # 0 means no timeout, keep searching # primary node forever. #------------------------------------------------------------------------------ # online recovery #------------------------------------------------------------------------------ recovery_user = 'nobody' # online recovery user recovery_password = '' # online recovery password recovery_1st_stage_command = '' # executes command in first stage recovery_2nd_stage_command = '' # executes command in second stage recovery_timeout = 90 # timeout in seconds wait # recovering node's postmaster start # 0 means no wait client_idle_limit_in_recovery = 0 # client disconnected after being idle # many seconds in second stage # of online recovery # 0 means no disconnection # -1 means immediate disconnection .... rest default ....
from http://www.pgpool.net/docs/pgpool-ii-3.5.4/doc/pgpool-en.html:
for query load balanced, following requirements must met: postgresql version 7.4 or later, either in replication mode or master slave mode ...
there's more, in config both replication mode , master slave disabled. description, easiest enable master-slave mode.
also ought enable connection cache (one of main points of pgpool) setting "connection_cache = on".
Comments
Post a Comment