<div dir="ltr"><div>Thanks a lots for your time!!!!</div><div><br></div>Master pgpool.conf and pg_hba.conf<div><br></div><div><div># ----------------------------</div><div># pgPool-II configuration file</div><div># ----------------------------</div><div>#</div><div># This file consists of lines of the form:</div><div>#</div><div># name = value</div><div>#</div><div># Whitespace may be used. Comments are introduced with "#" anywhere on a line.</div><div># The complete list of parameter names and allowed values can be found in the</div><div># pgPool-II documentation.</div><div>#</div><div># This file is read on server startup and when the server receives a SIGHUP</div><div># signal. If you edit the file on a running system, you have to SIGHUP the</div><div># server for the changes to take effect, or use "pgpool reload". Some</div><div># parameters, which are marked below, require a server shutdown and restart to</div><div># take effect.</div><div>#</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># CONNECTIONS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - pgpool Connection Settings -</div><div><br></div><div>listen_addresses = '*'</div><div> # Host name or IP address to listen on:</div><div> # '*' for all, '' for no TCP/IP connections</div><div> # (change requires restart)</div><div>port = 9999</div><div> # Port number</div><div> # (change requires restart)</div><div>socket_dir = '/tmp'</div><div> # Unix domain socket path</div><div> # The Debian package defaults to</div><div> # /var/run/postgresql</div><div> # (change requires restart)</div><div><br></div><div><br></div><div># - pgpool Communication Manager Connection Settings -</div><div><br></div><div>pcp_listen_addresses = '*'</div><div> # Host name or IP address for pcp process to listen on:</div><div> # '*' for all, '' for no TCP/IP connections</div><div> # (change requires restart)</div><div>pcp_port = 9898</div><div> # Port number for pcp</div><div> # (change requires restart)</div><div>pcp_socket_dir = '/tmp'</div><div> # Unix domain socket path for pcp</div><div> # The Debian package defaults to</div><div> # /var/run/postgresql</div><div> # (change requires restart)</div><div>listen_backlog_multiplier = 2</div><div> # Set the backlog parameter of listen(2) to</div><div><span class="" style="white-space:pre"> </span> # num_init_children * listen_backlog_multiplier.</div><div> # (change requires restart)</div><div><br></div><div># - Backend Connection Settings -</div><div><br></div><div> # Host name or IP address to connect to for backend 0</div><div> # Port number for backend 0</div><div> # Weight for backend 0 (only in load balancing mode)</div><div> # Data directory for backend 0</div><div> # Controls various backend behavior</div><div> # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER</div><div><br></div><div># - Authentication -</div><div><br></div><div>enable_pool_hba = on</div><div> # Use pool_hba.conf for client authentication</div><div>pool_passwd = 'pool_passwd'</div><div> # File name of pool_passwd for md5 authentication.</div><div> # "" disables pool_passwd.</div><div> # (change requires restart)</div><div>authentication_timeout = 60</div><div> # Delay in seconds to complete client authentication</div><div> # 0 means no timeout.</div><div><br></div><div># - SSL Connections -</div><div><br></div><div>ssl = off</div><div> # Enable SSL support</div><div> # (change requires restart)</div><div>#ssl_key = './server.key'</div><div> # Path to the SSL private key file</div><div> # (change requires restart)</div><div>#ssl_cert = './server.cert'</div><div> # Path to the SSL public certificate file</div><div> # (change requires restart)</div><div>#ssl_ca_cert = ''</div><div> # Path to a single PEM format file</div><div> # containing CA root certificate(s)</div><div> # (change requires restart)</div><div>#ssl_ca_cert_dir = ''</div><div> # Directory containing CA root certificate(s)</div><div> # (change requires restart)</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># POOLS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - Pool size -</div><div><br></div><div>num_init_children = 32</div><div> # Number of pools</div><div> # (change requires restart)</div><div>max_pool = 4</div><div> # Number of connections per pool</div><div> # (change requires restart)</div><div><br></div><div># - Life time -</div><div><br></div><div>child_life_time = 300</div><div> # Pool exits after being idle for this many seconds</div><div>child_max_connections = 0</div><div> # Pool exits after receiving that many connections</div><div> # 0 means no exit</div><div>connection_life_time = 0</div><div> # Connection to backend closes after being idle for this many seconds</div><div> # 0 means no close</div><div>client_idle_limit = 0</div><div> # Client is disconnected after being idle for that many seconds</div><div> # (even inside an explicit transactions!)</div><div> # 0 means no disconnection</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># LOGS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - Where to log -</div><div><br></div><div>log_destination = 'syslog'</div><div> # Where to log</div><div> # Valid values are combinations of stderr,</div><div> # and syslog. Default to stderr.</div><div><br></div><div># - What to log -</div><div><br></div><div>log_line_prefix = '%t: pid %p: # printf-style string to output at beginning of each log line.'</div><div><br></div><div>log_connections = off</div><div> # Log connections</div><div>log_hostname = on</div><div> # Hostname will be shown in ps status</div><div> # and in logs if connections are logged</div><div>log_statement = on</div><div> # Log all statements</div><div>log_per_node_statement = on</div><div> # Log all statements</div><div> # with node and backend informations</div><div>log_standby_delay = 'if_over_threshold'</div><div> # Log standby delay</div><div> # Valid values are combinations of always,</div><div> # if_over_threshold, none</div><div><br></div><div># - Syslog specific -</div><div><br></div><div>syslog_facility = 'LOCAL0'</div><div> # Syslog local facility. Default to LOCAL0</div><div>syslog_ident = 'pgpool'</div><div> # Syslog program identification string</div><div> # Default to 'pgpool'</div><div><br></div><div># - Debug -</div><div><br></div><div>debug_level = 5</div><div> # Debug message verbosity level</div><div> # 0 means no message, 1 or more mean verbose</div><div><br></div><div>log_error_verbosity = 'DEFAULT'</div><div><br></div><div>#client_min_messages = notice # values in order of decreasing detail:</div><div> # debug5</div><div> # debug4</div><div> # debug3</div><div> # debug2</div><div> # debug1</div><div> # log</div><div> # notice</div><div> # warning</div><div> # error</div><div><br></div><div>#log_min_messages = warning # values in order of decreasing detail:</div><div> # debug5</div><div> # debug4</div><div> # debug3</div><div> # debug2</div><div> # debug1</div><div> # info</div><div> # notice</div><div> # warning</div><div> # error</div><div> # log</div><div> # fatal</div><div> # panic</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># FILE LOCATIONS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>pid_file_name = '/var/run/pgpool/pgpool.pid'</div><div> # PID file name</div><div> # (change requires restart)</div><div>logdir = '/tmp'</div><div> # Directory of pgPool status file</div><div> # (change requires restart)</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># CONNECTION POOLING</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>connection_cache = on</div><div> # Activate connection pools</div><div> # (change requires restart)</div><div><br></div><div> # Semicolon separated list of queries</div><div> # to be issued at the end of a session</div><div> # The default is for 8.3 and later</div><div>reset_query_list = 'ABORT; DISCARD ALL'</div><div> # The following one is for 8.2 and before</div><div>#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># REPLICATION MODE</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>replication_mode = off</div><div> # Activate replication mode</div><div> # (change requires restart)</div><div>replicate_select = off</div><div> # Replicate SELECT statements</div><div> # when in replication mode</div><div> # replicate_select is higher priority than</div><div> # load_balance_mode.</div><div><br></div><div>insert_lock = off</div><div> # Automatically locks a dummy row or a table</div><div> # with INSERT statements to keep SERIAL data</div><div> # consistency</div><div> # Without SERIAL, no lock will be issued</div><div>lobj_lock_table = ''</div><div> # When rewriting lo_creat command in</div><div> # replication mode, specify table name to</div><div> # lock</div><div><br></div><div># - Degenerate handling -</div><div><br></div><div>replication_stop_on_mismatch = off</div><div> # On disagreement with the packet kind</div><div> # sent from backend, degenerate the node</div><div> # which is most likely "minority"</div><div> # If off, just force to exit this session</div><div><br></div><div>failover_if_affected_tuples_mismatch = off</div><div> # On disagreement with the number of affected</div><div> # tuples in UPDATE/DELETE queries, then</div><div> # degenerate the node which is most likely</div><div> # "minority".</div><div> # If off, just abort the transaction to</div><div> # keep the consistency</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># LOAD BALANCING MODE</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>load_balance_mode = on</div><div> # Activate load balancing mode</div><div> # (change requires restart)</div><div>ignore_leading_white_space = on</div><div> # Ignore leading white spaces of each query</div><div>white_function_list = ''</div><div> # Comma separated list of function names</div><div> # that don't write to database</div><div> # Regexp are accepted</div><div>black_function_list = 'currval,lastval,nextval,setval'</div><div> # Comma separated list of function names</div><div> # that write to database</div><div> # Regexp are accepted</div><div><br></div><div>database_redirect_preference_list = ''</div><div><span class="" style="white-space:pre"> </span> # comma separated list of pairs of database and node id.</div><div><span class="" style="white-space:pre"> </span> # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'</div><div><span class="" style="white-space:pre"> </span> # valid for streaming replicaton mode only.</div><div><br></div><div>app_name_redirect_preference_list = ''</div><div><span class="" style="white-space:pre"> </span> # comma separated list of pairs of app name and node id.</div><div><span class="" style="white-space:pre"> </span> # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'</div><div><span class="" style="white-space:pre"> </span> # valid for streaming replicaton mode only.</div><div>allow_sql_comments = off</div><div><span class="" style="white-space:pre"> </span> # if on, ignore SQL comments when judging if load balance or</div><div><span class="" style="white-space:pre"> </span> # query cache is possible.</div><div><span class="" style="white-space:pre"> </span> # If off, SQL comments effectively prevent the judgment</div><div><span class="" style="white-space:pre"> </span> # (pre 3.4 behavior).</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># MASTER/SLAVE MODE</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>master_slave_mode = on</div><div> # Activate master/slave mode</div><div> # (change requires restart)</div><div>master_slave_sub_mode = 'stream'</div><div> # Master/slave sub mode</div><div> # Valid values are combinations slony or</div><div> # stream. Default is slony.</div><div> # (change requires restart)</div><div><br></div><div># - Streaming -</div><div><br></div><div>sr_check_period = 0</div><div> # Streaming replication check period</div><div> # Disabled (0) by default</div><div>sr_check_user = 'postgres'</div><div> # Streaming replication check user</div><div> # This is neccessary even if you disable streaming</div><div> # replication delay check by sr_check_period = 0</div><div>sr_check_password = 'xxx'</div><div> # Password for streaming replication check user</div><div>delay_threshold = 0</div><div> # Threshold before not dispatching query to standby node</div><div> # Unit is in bytes</div><div> # Disabled (0) by default</div><div><br></div><div># - Special commands -</div><div><br></div><div>follow_master_command = ''</div><div> # Executes this command after master failover</div><div> # Special values:</div><div> # %d = node id</div><div> # %h = host name</div><div> # %p = port number</div><div> # %D = database cluster path</div><div> # %m = new master node id</div><div> # %H = hostname of the new master node</div><div> # %M = old master node id</div><div> # %P = old primary node id</div><div><span class="" style="white-space:pre"> </span> # %r = new master port number</div><div><span class="" style="white-space:pre"> </span> # %R = new master database cluster path</div><div> # %% = '%' character</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># HEALTH CHECK</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>health_check_period = 5</div><div> # Health check period</div><div> # Disabled (0) by default</div><div>health_check_timeout = 0</div><div> # Health check timeout</div><div> # 0 means no timeout</div><div>health_check_user = 'postgres'</div><div> # Health check user</div><div>health_check_password = 'xxx'</div><div> # Password for health check user</div><div>health_check_max_retries = 0</div><div> # Maximum number of times to retry a failed health check before giving up.</div><div>health_check_retry_delay = 1</div><div> # Amount of time to wait (in seconds) between retries.</div><div>connect_timeout = 10000</div><div> # Timeout value in milliseconds before giving up to connect to backend.</div><div><span class="" style="white-space:pre"> </span> # Default is 10000 ms (10 second). Flaky network user may want to increase</div><div><span class="" style="white-space:pre"> </span> # the value. 0 means no timeout.</div><div><span class="" style="white-space:pre"> </span> # Note that this value is not only used for health check,</div><div><span class="" style="white-space:pre"> </span> # but also for ordinary conection to backend.</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># FAILOVER AND FAILBACK</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>failover_command = '/usr/local/etc/failover.sh %d %H /var/log/pgpool/trigger/trigger_file0'</div><div> # Executes this command at failover</div><div> # Special values:</div><div> # %d = node id</div><div> # %h = host name</div><div> # %p = port number</div><div> # %D = database cluster path</div><div> # %m = new master node id</div><div> # %H = hostname of the new master node</div><div> # %M = old master node id</div><div> # %P = old primary node id</div><div><span class="" style="white-space:pre"> </span> # %r = new master port number</div><div><span class="" style="white-space:pre"> </span> # %R = new master database cluster path</div><div> # %% = '%' character</div><div>failback_command = ''</div><div> # Executes this command at failback.</div><div> # Special values:</div><div> # %d = node id</div><div> # %h = host name</div><div> # %p = port number</div><div> # %D = database cluster path</div><div> # %m = new master node id</div><div> # %H = hostname of the new master node</div><div> # %M = old master node id</div><div> # %P = old primary node id</div><div><span class="" style="white-space:pre"> </span> # %r = new master port number</div><div><span class="" style="white-space:pre"> </span> # %R = new master database cluster path</div><div> # %% = '%' character</div><div><br></div><div>fail_over_on_backend_error = on</div><div> # Initiates failover when reading/writing to the</div><div> # backend communication socket fails</div><div> # If set to off, pgpool will report an</div><div> # error and disconnect the session.</div><div><br></div><div>search_primary_node_timeout = 10</div><div> # Timeout in seconds to search for the</div><div> # primary node when a failover occurs.</div><div> # 0 means no timeout, keep searching</div><div> # for a primary node forever.</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># ONLINE RECOVERY</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>recovery_user = 'postgres'</div><div> # Online recovery user</div><div>recovery_password = 'xxx'</div><div> # Online recovery password</div><div>recovery_1st_stage_command = 'recovery_1st_stage'</div><div> # Executes a command in first stage</div><div>recovery_2nd_stage_command = ''</div><div> # Executes a command in second stage</div><div>recovery_timeout = 90</div><div> # Timeout in seconds to wait for the</div><div> # recovering node's postmaster to start up</div><div> # 0 means no wait</div><div>client_idle_limit_in_recovery = 0</div><div> # Client is disconnected after being idle</div><div> # for that many seconds in the second stage</div><div> # of online recovery</div><div> # 0 means no disconnection</div><div> # -1 means immediate disconnection</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># WATCHDOG</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - Enabling -</div><div><br></div><div>use_watchdog = on</div><div> # Activates watchdog</div><div> # (change requires restart)</div><div><br></div><div># -Connection to up stream servers -</div><div><br></div><div>trusted_servers = ''</div><div> # trusted server list which are used</div><div> # to confirm network connection</div><div> # (hostA,hostB,hostC,...)</div><div> # (change requires restart)</div><div>ping_path = '/bin'</div><div> # ping command path</div><div> # (change requires restart)</div><div><br></div><div># - Watchdog communication Settings -</div><div><br></div><div>wd_hostname = 'SUYSevPrepDB01'</div><div> # Host name or IP address of this watchdog</div><div> # (change requires restart)</div><div>wd_port = 9000</div><div> # port number for watchdog service</div><div> # (change requires restart)</div><div>wd_authkey = ''</div><div> # Authentication key for watchdog communication</div><div> # (change requires restart)</div><div><br></div><div># - Virtual IP control Setting -</div><div><br></div><div>delegate_IP = '192.168.140.112'</div><div> # delegate IP address</div><div> # If this is empty, virtual IP never bring up. </div><div> # (change requires restart)</div><div>ifconfig_path = '/apachebin'</div><div> # ifconfig command path</div><div> # (change requires restart)</div><div>if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0'</div><div> # startup delegate IP command</div><div> # (change requires restart)</div><div>if_down_cmd = 'ifconfig eth1:0 down'</div><div> # shutdown delegate IP command</div><div> # (change requires restart)</div><div><br></div><div>arping_path = '/apachebin'</div><div> # (change requires restart)</div><div><br></div><div>arping_cmd = 'arping -I eth0 -U $_IP_$ -w 1'</div><div> # arping command</div><div> # (change requires restart)</div><div><br></div><div># - Behaivor on escalation Setting -</div><div><br></div><div>clear_memqcache_on_escalation = on</div><div> # Clear all the query cache on shared memory</div><div> # when standby pgpool escalate to active pgpool</div><div> # (= virtual IP holder).</div><div> # This should be off if client connects to pgpool</div><div> # not using virtual IP.</div><div> # (change requires restart)</div><div>wd_escalation_command = ''</div><div> # Executes this command at escalation on new active pgpool.</div><div> # (change requires restart)</div><div><br></div><div># - Lifecheck Setting - </div><div><br></div><div># -- common --</div><div><br></div><div>wd_lifecheck_method = 'heartbeat'</div><div> # Method of watchdog lifecheck ('heartbeat' or 'query')</div><div> # (change requires restart)</div><div>wd_interval = 3</div><div> # lifecheck interval (sec) > 0</div><div> # (change requires restart)</div><div><br></div><div># -- heartbeat mode --</div><div><br></div><div>wd_heartbeat_port = 9694</div><div> # Port number for receiving heartbeat signal</div><div> # (change requires restart)</div><div>wd_heartbeat_keepalive = 2</div><div> # Interval time of sending heartbeat signal (sec)</div><div> # (change requires restart)</div><div>wd_heartbeat_deadtime = 30</div><div> # Deadtime interval for heartbeat signal (sec)</div><div> # (change requires restart)</div><div> # Host name or IP address of destination 0</div><div> # for sending heartbeat signal.</div><div> # (change requires restart)</div><div> # Port number of destination 0 for sending</div><div> # heartbeat signal. Usually this is the</div><div> # same as wd_heartbeat_port.</div><div> # (change requires restart)</div><div> # Name of NIC device (such like 'eth0')</div><div> # used for sending/receiving heartbeat</div><div> # signal to/from destination 0.</div><div> # This works only when this is not empty</div><div> # and pgpool has root privilege.</div><div> # (change requires restart)</div><div><br></div><div>#heartbeat_destination1 = 'host0_ip2'</div><div>#heartbeat_destination_port1 = 9694</div><div>#heartbeat_device1 = ''</div><div><br></div><div># -- query mode --</div><div><br></div><div>wd_life_point = 3</div><div> # lifecheck retry times</div><div> # (change requires restart)</div><div>wd_lifecheck_query = 'SELECT 1'</div><div> # lifecheck query to pgpool from watchdog</div><div> # (change requires restart)</div><div>wd_lifecheck_dbname = 'template1'</div><div> # Database name connected for lifecheck</div><div> # (change requires restart)</div><div>wd_lifecheck_user = ''</div><div> # watchdog user monitoring pgpools in lifecheck</div><div> # (change requires restart)</div><div>wd_lifecheck_password = ''</div><div> # Password for watchdog user in lifecheck</div><div> # (change requires restart)</div><div><br></div><div># - Other pgpool Connection Settings -</div><div><br></div><div> # Host name or IP address to connect to for other pgpool 0</div><div> # (change requires restart)</div><div> # Port number for othet pgpool 0</div><div> # (change requires restart)</div><div> # Port number for othet watchdog 0</div><div> # (change requires restart)</div><div>#other_pgpool_hostname1 = 'host1'</div><div>#other_pgpool_port1 = 5432</div><div>#other_wd_port1 = 9000</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># OTHERS</div><div>#------------------------------------------------------------------------------</div><div>relcache_expire = 0</div><div> # Life time of relation cache in seconds.</div><div> # 0 means no cache expiration(the default).</div><div> # The relation cache is used for cache the</div><div> # query result against PostgreSQL system</div><div> # catalog to obtain various information</div><div> # including table structures or if it's a</div><div> # temporary table or not. The cache is</div><div> # maintained in a pgpool child local memory</div><div> # and being kept as long as it survives.</div><div> # If someone modify the table by using</div><div> # ALTER TABLE or some such, the relcache is</div><div> # not consistent anymore.</div><div> # For this purpose, cache_expiration</div><div> # controls the life time of the cache.</div><div>relcache_size = 256</div><div> # Number of relation cache</div><div> # entry. If you see frequently:</div><div><span class="" style="white-space:pre"> </span> # "pool_search_relcache: cache replacement happend"</div><div><span class="" style="white-space:pre"> </span> # in the pgpool log, you might want to increate this number.</div><div><br></div><div>check_temp_table = on</div><div> # If on, enable temporary table check in SELECT statements.</div><div> # This initiates queries against system catalog of primary/master</div><div><span class="" style="white-space:pre"> </span> # thus increases load of master.</div><div><span class="" style="white-space:pre"> </span> # If you are absolutely sure that your system never uses temporary tables</div><div><span class="" style="white-space:pre"> </span> # and you want to save access to primary/master, you could turn this off.</div><div><span class="" style="white-space:pre"> </span> # Default is on.</div><div><br></div><div>check_unlogged_table = off</div><div> # If on, enable unlogged table check in SELECT statements.</div><div> # This initiates queries against system catalog of primary/master</div><div> # thus increases load of master.</div><div> # If you are absolutely sure that your system never uses unlogged tables</div><div> # and you want to save access to primary/master, you could turn this off.</div><div> # Default is on.</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># IN MEMORY QUERY MEMORY CACHE</div><div>#------------------------------------------------------------------------------</div><div>memory_cache_enabled = off</div><div><span class="" style="white-space:pre"> </span> # If on, use the memory cache functionality, off by default</div><div>memqcache_method = 'shmem'</div><div><span class="" style="white-space:pre"> </span> # Cache storage method. either 'shmem'(shared memory) or</div><div><span class="" style="white-space:pre"> </span> # 'memcached'. 'shmem' by default</div><div> # (change requires restart)</div><div>memqcache_memcached_host = 'localhost'</div><div><span class="" style="white-space:pre"> </span> # Memcached host name or IP address. Mandatory if</div><div><span class="" style="white-space:pre"> </span> # memqcache_method = 'memcached'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to localhost.</div><div> # (change requires restart)</div><div>memqcache_memcached_port = 11211</div><div><span class="" style="white-space:pre"> </span> # Memcached port number. Mondatory if memqcache_method = 'memcached'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 11211.</div><div> # (change requires restart)</div><div>memqcache_total_size = 67108864</div><div><span class="" style="white-space:pre"> </span> # Total memory size in bytes for storing memory cache.</div><div><span class="" style="white-space:pre"> </span> # Mandatory if memqcache_method = 'shmem'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 64MB.</div><div> # (change requires restart)</div><div>memqcache_max_num_cache = 1000000</div><div><span class="" style="white-space:pre"> </span> # Total number of cache entries. Mandatory</div><div><span class="" style="white-space:pre"> </span> # if memqcache_method = 'shmem'.</div><div><span class="" style="white-space:pre"> </span> # Each cache entry consumes 48 bytes on shared memory.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 1,000,000(45.8MB).</div><div> # (change requires restart)</div><div>memqcache_expire = 0</div><div><span class="" style="white-space:pre"> </span> # Memory cache entry life time specified in seconds.</div><div><span class="" style="white-space:pre"> </span> # 0 means infinite life time. 0 by default.</div><div> # (change requires restart)</div><div>memqcache_auto_cache_invalidation = on</div><div><span class="" style="white-space:pre"> </span> # If on, invalidation of query cache is triggered by corresponding</div><div><span class="" style="white-space:pre"> </span> # DDL/DML/DCL(and memqcache_expire). If off, it is only triggered</div><div><span class="" style="white-space:pre"> </span> # by memqcache_expire. on by default.</div><div> # (change requires restart)</div><div>memqcache_maxcache = 409600</div><div><span class="" style="white-space:pre"> </span> # Maximum SELECT result size in bytes.</div><div><span class="" style="white-space:pre"> </span> # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.</div><div> # (change requires restart)</div><div>memqcache_cache_block_size = 1048576</div><div><span class="" style="white-space:pre"> </span> # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 1MB.</div><div> # (change requires restart)</div><div>memqcache_oiddir = '/var/log/pgpool/oiddir'</div><div><span class="" style="white-space:pre"> </span> <span class="" style="white-space:pre"> </span> # Temporary work directory to record table oids</div><div> # (change requires restart)</div><div>white_memqcache_table_list = ''</div><div> # Comma separated list of table names to memcache</div><div> # that don't write to database</div><div> # Regexp are accepted</div><div>black_memqcache_table_list = ''</div><div> # Comma separated list of table names not to memcache</div><div> # that don't write to database</div><div> # Regexp are accepted</div><div>ssl_key = ''</div><div>ssl_cert = ''</div><div>ssl_ca_cert = ''</div><div>ssl_ca_cert_dir = ''</div><div>log_error_verbosity = 'DEFAULT'</div><div>client_min_messages = 'debug2'</div><div>log_min_messages = 'debug2'</div><div>backend_hostname0 = 'SUYSevPrepDB01'</div><div>backend_port0 = 5432</div><div>backend_weight0 = 1</div><div>backend_data_directory0 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag0= 'ALLOW_TO_FAILOVER'</div><div>backend_hostname1 = 'SUYSevPrepDB02'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div>backend_data_directory1 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag1= 'ALLOW_TO_FAILOVER'</div><div>other_pgpool_hostname0 = 'SUYSevPrepDB02'</div><div>other_pgpool_port0 = 9999</div><div>other_wd_port0 = 9000</div><div>heartbeat_destination0 = 'SUYSevPrepDB02'</div><div>heartbeat_destination_port0 = 9694</div><div>heartbeat_device0 = ''</div></div><div><br></div><div><div># use another authentication method.</div><div><br></div><div><br></div><div># TYPE DATABASE USER ADDRESS METHOD</div><div><br></div><div># "local" is for Unix domain socket connections only</div><div>local all all md5</div><div># IPv4 local connections:</div><div>host all all <a href="http://127.0.0.1/32">127.0.0.1/32</a> md5</div><div>host all all <a href="http://0.0.0.0/0">0.0.0.0/0</a> md5</div><div># IPv6 local connections:</div><div># Allow replication connections from localhost, by a user with the</div><div># replication privilege.</div><div>local replication postgres trust</div><div>host replication replication <a href="http://0.0.0.0/0">0.0.0.0/0</a> trust</div><div>#host replication postgres ::1/128 trust</div><div>"9.4/data/pg_hba.conf" 93L, 4422C </div></div><div><br></div><div><br></div><div>Slave pgpool.conf and pg_hba.conf</div><div><br></div><div><br></div><div><div># ----------------------------</div><div># pgPool-II configuration file</div><div># ----------------------------</div><div>#</div><div># This file consists of lines of the form:</div><div>#</div><div># name = value</div><div>#</div><div># Whitespace may be used. Comments are introduced with "#" anywhere on a line.</div><div># The complete list of parameter names and allowed values can be found in the</div><div># pgPool-II documentation.</div><div>#</div><div># This file is read on server startup and when the server receives a SIGHUP</div><div># signal. If you edit the file on a running system, you have to SIGHUP the</div><div># server for the changes to take effect, or use "pgpool reload". Some</div><div># parameters, which are marked below, require a server shutdown and restart to</div><div># take effect.</div><div>#</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># CONNECTIONS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - pgpool Connection Settings -</div><div><br></div><div>listen_addresses = '*'</div><div> # Host name or IP address to listen on:</div><div> # '*' for all, '' for no TCP/IP connections</div><div> # (change requires restart)</div><div>port = 9999</div><div> # Port number</div><div> # (change requires restart)</div><div>socket_dir = '/tmp'</div><div> # Unix domain socket path</div><div> # The Debian package defaults to</div><div> # /var/run/postgresql</div><div> # (change requires restart)</div><div><br></div><div><br></div><div># - pgpool Communication Manager Connection Settings -</div><div><br></div><div>pcp_listen_addresses = '*'</div><div> # Host name or IP address for pcp process to listen on:</div><div> # '*' for all, '' for no TCP/IP connections</div><div> # (change requires restart)</div><div>pcp_port = 9898</div><div> # Port number for pcp</div><div> # (change requires restart)</div><div>pcp_socket_dir = '/tmp'</div><div> # Unix domain socket path for pcp</div><div> # The Debian package defaults to</div><div> # /var/run/postgresql</div><div> # (change requires restart)</div><div>listen_backlog_multiplier = 2</div><div> # Set the backlog parameter of listen(2) to</div><div><span class="" style="white-space:pre"> </span> # num_init_children * listen_backlog_multiplier.</div><div> # (change requires restart)</div><div><br></div><div># - Backend Connection Settings -</div><div><br></div><div> # Host name or IP address to connect to for backend 0</div><div> # Port number for backend 0</div><div> # Weight for backend 0 (only in load balancing mode)</div><div> # Data directory for backend 0</div><div> # Controls various backend behavior</div><div> # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER</div><div><br></div><div># - Authentication -</div><div><br></div><div>enable_pool_hba = on</div><div> # Use pool_hba.conf for client authentication</div><div>pool_passwd = 'pool_passwd'</div><div> # File name of pool_passwd for md5 authentication.</div><div> # "" disables pool_passwd.</div><div> # (change requires restart)</div><div>authentication_timeout = 60</div><div> # Delay in seconds to complete client authentication</div><div> # 0 means no timeout.</div><div><br></div><div># - SSL Connections -</div><div><br></div><div>ssl = off</div><div> # Enable SSL support</div><div> # (change requires restart)</div><div>#ssl_key = './server.key'</div><div> # Path to the SSL private key file</div><div> # (change requires restart)</div><div>#ssl_cert = './server.cert'</div><div> # Path to the SSL public certificate file</div><div> # (change requires restart)</div><div>#ssl_ca_cert = ''</div><div> # Path to a single PEM format file</div><div> # containing CA root certificate(s)</div><div> # (change requires restart)</div><div>#ssl_ca_cert_dir = ''</div><div> # Directory containing CA root certificate(s)</div><div> # (change requires restart)</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># POOLS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - Pool size -</div><div><br></div><div>num_init_children = 32</div><div> # Number of pools</div><div> # (change requires restart)</div><div>max_pool = 4</div><div> # Number of connections per pool</div><div> # (change requires restart)</div><div><br></div><div># - Life time -</div><div><br></div><div>child_life_time = 300</div><div> # Pool exits after being idle for this many seconds</div><div>child_max_connections = 0</div><div> # Pool exits after receiving that many connections</div><div> # 0 means no exit</div><div>connection_life_time = 0</div><div> # Connection to backend closes after being idle for this many seconds</div><div> # 0 means no close</div><div>client_idle_limit = 0</div><div> # Client is disconnected after being idle for that many seconds</div><div> # (even inside an explicit transactions!)</div><div> # 0 means no disconnection</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># LOGS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - Where to log -</div><div><br></div><div>log_destination = 'syslog'</div><div> # Where to log</div><div> # Valid values are combinations of stderr,</div><div> # and syslog. Default to stderr.</div><div><br></div><div># - What to log -</div><div><br></div><div>log_line_prefix = '%t: pid %p: # printf-style string to output at beginning of each log line.'</div><div><br></div><div>log_connections = off</div><div> # Log connections</div><div>log_hostname = on</div><div> # Hostname will be shown in ps status</div><div> # and in logs if connections are logged</div><div>log_statement = on</div><div> # Log all statements</div><div>log_per_node_statement = on</div><div> # Log all statements</div><div> # with node and backend informations</div><div>log_standby_delay = 'if_over_threshold'</div><div> # Log standby delay</div><div> # Valid values are combinations of always,</div><div> # if_over_threshold, none</div><div><br></div><div># - Syslog specific -</div><div><br></div><div>syslog_facility = 'LOCAL0'</div><div> # Syslog local facility. Default to LOCAL0</div><div>syslog_ident = 'pgpool'</div><div> # Syslog program identification string</div><div> # Default to 'pgpool'</div><div><br></div><div># - Debug -</div><div><br></div><div>debug_level = 2</div><div> # Debug message verbosity level</div><div> # 0 means no message, 1 or more mean verbose</div><div><br></div><div>#log_error_verbosity = default # terse, default, or verbose messages</div><div><br></div><div>#client_min_messages = notice # values in order of decreasing detail:</div><div> # debug5</div><div> # debug4</div><div> # debug3</div><div> # debug2</div><div> # debug1</div><div> # log</div><div> # notice</div><div> # warning</div><div> # error</div><div><br></div><div>#log_min_messages = warning # values in order of decreasing detail:</div><div> # debug5</div><div> # debug4</div><div> # debug3</div><div> # debug2</div><div> # debug1</div><div> # info</div><div> # notice</div><div> # warning</div><div> # error</div><div> # log</div><div> # fatal</div><div> # panic</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># FILE LOCATIONS</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>pid_file_name = '/var/run/pgpool/pgpool.pid'</div><div> # PID file name</div><div> # (change requires restart)</div><div>logdir = '/tmp'</div><div> # Directory of pgPool status file</div><div> # (change requires restart)</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># CONNECTION POOLING</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>connection_cache = on</div><div> # Activate connection pools</div><div> # (change requires restart)</div><div><br></div><div> # Semicolon separated list of queries</div><div> # to be issued at the end of a session</div><div> # The default is for 8.3 and later</div><div>reset_query_list = 'ABORT; DISCARD ALL'</div><div> # The following one is for 8.2 and before</div><div>#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># REPLICATION MODE</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>replication_mode = off</div><div> # Activate replication mode</div><div> # (change requires restart)</div><div>replicate_select = off</div><div> # Replicate SELECT statements</div><div> # when in replication mode</div><div> # replicate_select is higher priority than</div><div> # load_balance_mode.</div><div><br></div><div>insert_lock = off</div><div> # Automatically locks a dummy row or a table</div><div> # with INSERT statements to keep SERIAL data</div><div> # consistency</div><div> # Without SERIAL, no lock will be issued</div><div>lobj_lock_table = ''</div><div> # When rewriting lo_creat command in</div><div> # replication mode, specify table name to</div><div> # lock</div><div><br></div><div># - Degenerate handling -</div><div><br></div><div>replication_stop_on_mismatch = off</div><div> # On disagreement with the packet kind</div><div> # sent from backend, degenerate the node</div><div> # which is most likely "minority"</div><div> # If off, just force to exit this session</div><div><br></div><div>failover_if_affected_tuples_mismatch = off</div><div> # On disagreement with the number of affected</div><div> # tuples in UPDATE/DELETE queries, then</div><div> # degenerate the node which is most likely</div><div> # "minority".</div><div> # If off, just abort the transaction to</div><div> # keep the consistency</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># LOAD BALANCING MODE</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>load_balance_mode = on</div><div> # Activate load balancing mode</div><div> # (change requires restart)</div><div>ignore_leading_white_space = on</div><div> # Ignore leading white spaces of each query</div><div>white_function_list = ''</div><div> # Comma separated list of function names</div><div> # that don't write to database</div><div> # Regexp are accepted</div><div>black_function_list = 'currval,lastval,nextval,setval'</div><div> # Comma separated list of function names</div><div> # that write to database</div><div> # Regexp are accepted</div><div><br></div><div>database_redirect_preference_list = ''</div><div><span class="" style="white-space:pre"> </span> # comma separated list of pairs of database and node id.</div><div><span class="" style="white-space:pre"> </span> # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'</div><div><span class="" style="white-space:pre"> </span> # valid for streaming replicaton mode only.</div><div><br></div><div>app_name_redirect_preference_list = ''</div><div><span class="" style="white-space:pre"> </span> # comma separated list of pairs of app name and node id.</div><div><span class="" style="white-space:pre"> </span> # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'</div><div><span class="" style="white-space:pre"> </span> # valid for streaming replicaton mode only.</div><div>allow_sql_comments = off</div><div><span class="" style="white-space:pre"> </span> # if on, ignore SQL comments when judging if load balance or</div><div><span class="" style="white-space:pre"> </span> # query cache is possible.</div><div><span class="" style="white-space:pre"> </span> # If off, SQL comments effectively prevent the judgment</div><div><span class="" style="white-space:pre"> </span> # (pre 3.4 behavior).</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># MASTER/SLAVE MODE</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>master_slave_mode = on</div><div> # Activate master/slave mode</div><div> # (change requires restart)</div><div>master_slave_sub_mode = 'stream'</div><div> # Master/slave sub mode</div><div> # Valid values are combinations slony or</div><div> # stream. Default is slony.</div><div> # (change requires restart)</div><div><br></div><div># - Streaming -</div><div><br></div><div>sr_check_period = 0</div><div> # Streaming replication check period</div><div> # Disabled (0) by default</div><div>sr_check_user = 'postgres'</div><div> # Streaming replication check user</div><div> # This is neccessary even if you disable streaming</div><div> # replication delay check by sr_check_period = 0</div><div>sr_check_password = 'xxx'</div><div> # Password for streaming replication check user</div><div>delay_threshold = 0</div><div> # Threshold before not dispatching query to standby node</div><div> # Unit is in bytes</div><div> # Disabled (0) by default</div><div><br></div><div># - Special commands -</div><div><br></div><div>follow_master_command = ''</div><div> # Executes this command after master failover</div><div> # Special values:</div><div> # %d = node id</div><div> # %h = host name</div><div> # %p = port number</div><div> # %D = database cluster path</div><div> # %m = new master node id</div><div> # %H = hostname of the new master node</div><div> # %M = old master node id</div><div> # %P = old primary node id</div><div><span class="" style="white-space:pre"> </span> # %r = new master port number</div><div><span class="" style="white-space:pre"> </span> # %R = new master database cluster path</div><div> # %% = '%' character</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># HEALTH CHECK</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>health_check_period = 5</div><div> # Health check period</div><div> # Disabled (0) by default</div><div>health_check_timeout = 0</div><div> # Health check timeout</div><div> # 0 means no timeout</div><div>health_check_user = 'postgres'</div><div> # Health check user</div><div>health_check_password = 'xxx'</div><div> # Password for health check user</div><div>health_check_max_retries = 0</div><div> # Maximum number of times to retry a failed health check before giving up.</div><div>health_check_retry_delay = 1</div><div> # Amount of time to wait (in seconds) between retries.</div><div>connect_timeout = 10000</div><div> # Timeout value in milliseconds before giving up to connect to backend.</div><div><span class="" style="white-space:pre"> </span> # Default is 10000 ms (10 second). Flaky network user may want to increase</div><div><span class="" style="white-space:pre"> </span> # the value. 0 means no timeout.</div><div><span class="" style="white-space:pre"> </span> # Note that this value is not only used for health check,</div><div><span class="" style="white-space:pre"> </span> # but also for ordinary conection to backend.</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># FAILOVER AND FAILBACK</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>failover_command = '/usr/local/etc/failover.sh %d %H /var/log/pgpool/trigger/trigger_file0'</div><div> # Executes this command at failover</div><div> # Special values:</div><div> # %d = node id</div><div> # %h = host name</div><div> # %p = port number</div><div> # %D = database cluster path</div><div> # %m = new master node id</div><div> # %H = hostname of the new master node</div><div> # %M = old master node id</div><div> # %P = old primary node id</div><div><span class="" style="white-space:pre"> </span> # %r = new master port number</div><div><span class="" style="white-space:pre"> </span> # %R = new master database cluster path</div><div> # %% = '%' character</div><div>failback_command = ''</div><div> # Executes this command at failback.</div><div> # Special values:</div><div> # %d = node id</div><div> # %h = host name</div><div> # %p = port number</div><div> # %D = database cluster path</div><div> # %m = new master node id</div><div> # %H = hostname of the new master node</div><div> # %M = old master node id</div><div> # %P = old primary node id</div><div><span class="" style="white-space:pre"> </span> # %r = new master port number</div><div><span class="" style="white-space:pre"> </span> # %R = new master database cluster path</div><div> # %% = '%' character</div><div><br></div><div>fail_over_on_backend_error = on</div><div> # Initiates failover when reading/writing to the</div><div> # backend communication socket fails</div><div> # If set to off, pgpool will report an</div><div> # error and disconnect the session.</div><div><br></div><div>search_primary_node_timeout = 10</div><div> # Timeout in seconds to search for the</div><div> # primary node when a failover occurs.</div><div> # 0 means no timeout, keep searching</div><div> # for a primary node forever.</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># ONLINE RECOVERY</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>recovery_user = 'postgres'</div><div> # Online recovery user</div><div>recovery_password = 'xxx'</div><div> # Online recovery password</div><div>recovery_1st_stage_command = 'recovery_1st_stage'</div><div> # Executes a command in first stage</div><div>recovery_2nd_stage_command = ''</div><div> # Executes a command in second stage</div><div>recovery_timeout = 90</div><div> # Timeout in seconds to wait for the</div><div> # recovering node's postmaster to start up</div><div> # 0 means no wait</div><div>client_idle_limit_in_recovery = 0</div><div> # Client is disconnected after being idle</div><div> # for that many seconds in the second stage</div><div> # of online recovery</div><div> # 0 means no disconnection</div><div> # -1 means immediate disconnection</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># WATCHDOG</div><div>#------------------------------------------------------------------------------</div><div><br></div><div># - Enabling -</div><div><br></div><div>use_watchdog = on</div><div> # Activates watchdog</div><div> # (change requires restart)</div><div><br></div><div># -Connection to up stream servers -</div><div><br></div><div>trusted_servers = ''</div><div> # trusted server list which are used</div><div> # to confirm network connection</div><div> # (hostA,hostB,hostC,...)</div><div> # (change requires restart)</div><div>ping_path = '/bin'</div><div> # ping command path</div><div> # (change requires restart)</div><div><br></div><div># - Watchdog communication Settings -</div><div><br></div><div>wd_hostname = 'SUYSevPrepDB02'</div><div> # Host name or IP address of this watchdog</div><div> # (change requires restart)</div><div>wd_port = 9000</div><div> # port number for watchdog service</div><div> # (change requires restart)</div><div>wd_authkey = ''</div><div> # Authentication key for watchdog communication</div><div> # (change requires restart)</div><div><br></div><div># - Virtual IP control Setting -</div><div><br></div><div>delegate_IP = '192.168.140.112'</div><div> # delegate IP address</div><div> # If this is empty, virtual IP never bring up. </div><div> # (change requires restart)</div><div>ifconfig_path = '/apachebin'</div><div> # ifconfig command path</div><div> # (change requires restart)</div><div>if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0'</div><div> # startup delegate IP command</div><div> # (change requires restart)</div><div>if_down_cmd = 'ifconfig eth1:0 down'</div><div> # shutdown delegate IP command</div><div> # (change requires restart)</div><div><br></div><div>arping_path = '/apachebin'</div><div> # (change requires restart)</div><div><br></div><div>arping_cmd = 'arping -I eth0 -U $_IP_$ -w 1'</div><div> # arping command</div><div> # (change requires restart)</div><div><br></div><div># - Behaivor on escalation Setting -</div><div><br></div><div>clear_memqcache_on_escalation = on</div><div> # Clear all the query cache on shared memory</div><div> # when standby pgpool escalate to active pgpool</div><div> # (= virtual IP holder).</div><div> # This should be off if client connects to pgpool</div><div> # not using virtual IP.</div><div> # (change requires restart)</div><div>wd_escalation_command = ''</div><div> # Executes this command at escalation on new active pgpool.</div><div> # (change requires restart)</div><div><br></div><div># - Lifecheck Setting - </div><div><br></div><div># -- common --</div><div><br></div><div>wd_lifecheck_method = 'heartbeat'</div><div> # Method of watchdog lifecheck ('heartbeat' or 'query')</div><div> # (change requires restart)</div><div>wd_interval = 3</div><div> # lifecheck interval (sec) > 0</div><div> # (change requires restart)</div><div><br></div><div># -- heartbeat mode --</div><div><br></div><div>wd_heartbeat_port = 9694</div><div> # Port number for receiving heartbeat signal</div><div> # (change requires restart)</div><div>wd_heartbeat_keepalive = 2</div><div> # Interval time of sending heartbeat signal (sec)</div><div> # (change requires restart)</div><div>wd_heartbeat_deadtime = 30</div><div> # Deadtime interval for heartbeat signal (sec)</div><div> # (change requires restart)</div><div> # Host name or IP address of destination 0</div><div> # for sending heartbeat signal.</div><div> # (change requires restart)</div><div> # Port number of destination 0 for sending</div><div> # heartbeat signal. Usually this is the</div><div> # same as wd_heartbeat_port.</div><div> # (change requires restart)</div><div> # Name of NIC device (such like 'eth0')</div><div> # used for sending/receiving heartbeat</div><div> # signal to/from destination 0.</div><div> # This works only when this is not empty</div><div> # and pgpool has root privilege.</div><div> # (change requires restart)</div><div><br></div><div>#heartbeat_destination1 = 'host0_ip2'</div><div>#heartbeat_destination_port1 = 9694</div><div>#heartbeat_device1 = ''</div><div><br></div><div># -- query mode --</div><div><br></div><div>wd_life_point = 3</div><div> # lifecheck retry times</div><div> # (change requires restart)</div><div>wd_lifecheck_query = 'SELECT 1'</div><div> # lifecheck query to pgpool from watchdog</div><div> # (change requires restart)</div><div>wd_lifecheck_dbname = 'template1'</div><div> # Database name connected for lifecheck</div><div> # (change requires restart)</div><div>wd_lifecheck_user = ''</div><div> # watchdog user monitoring pgpools in lifecheck</div><div> # (change requires restart)</div><div>wd_lifecheck_password = ''</div><div> # Password for watchdog user in lifecheck</div><div> # (change requires restart)</div><div><br></div><div># - Other pgpool Connection Settings -</div><div><br></div><div> # Host name or IP address to connect to for other pgpool 0</div><div> # (change requires restart)</div><div> # Port number for othet pgpool 0</div><div> # (change requires restart)</div><div> # Port number for othet watchdog 0</div><div> # (change requires restart)</div><div>#other_pgpool_hostname1 = 'host1'</div><div>#other_pgpool_port1 = 5432</div><div>#other_wd_port1 = 9000</div><div><br></div><div><br></div><div>#------------------------------------------------------------------------------</div><div># OTHERS</div><div>#------------------------------------------------------------------------------</div><div>relcache_expire = 0</div><div> # Life time of relation cache in seconds.</div><div> # 0 means no cache expiration(the default).</div><div> # The relation cache is used for cache the</div><div> # query result against PostgreSQL system</div><div> # catalog to obtain various information</div><div> # including table structures or if it's a</div><div> # temporary table or not. The cache is</div><div> # maintained in a pgpool child local memory</div><div> # and being kept as long as it survives.</div><div> # If someone modify the table by using</div><div> # ALTER TABLE or some such, the relcache is</div><div> # not consistent anymore.</div><div> # For this purpose, cache_expiration</div><div> # controls the life time of the cache.</div><div>relcache_size = 256</div><div> # Number of relation cache</div><div> # entry. If you see frequently:</div><div><span class="" style="white-space:pre"> </span> # "pool_search_relcache: cache replacement happend"</div><div><span class="" style="white-space:pre"> </span> # in the pgpool log, you might want to increate this number.</div><div><br></div><div>check_temp_table = on</div><div> # If on, enable temporary table check in SELECT statements.</div><div> # This initiates queries against system catalog of primary/master</div><div><span class="" style="white-space:pre"> </span> # thus increases load of master.</div><div><span class="" style="white-space:pre"> </span> # If you are absolutely sure that your system never uses temporary tables</div><div><span class="" style="white-space:pre"> </span> # and you want to save access to primary/master, you could turn this off.</div><div><span class="" style="white-space:pre"> </span> # Default is on.</div><div><br></div><div>check_unlogged_table = off</div><div> # If on, enable unlogged table check in SELECT statements.</div><div> # This initiates queries against system catalog of primary/master</div><div> # thus increases load of master.</div><div> # If you are absolutely sure that your system never uses unlogged tables</div><div> # and you want to save access to primary/master, you could turn this off.</div><div> # Default is on.</div><div><br></div><div>#------------------------------------------------------------------------------</div><div># IN MEMORY QUERY MEMORY CACHE</div><div>#------------------------------------------------------------------------------</div><div>memory_cache_enabled = off</div><div><span class="" style="white-space:pre"> </span> # If on, use the memory cache functionality, off by default</div><div>memqcache_method = 'shmem'</div><div><span class="" style="white-space:pre"> </span> # Cache storage method. either 'shmem'(shared memory) or</div><div><span class="" style="white-space:pre"> </span> # 'memcached'. 'shmem' by default</div><div> # (change requires restart)</div><div>memqcache_memcached_host = 'localhost'</div><div><span class="" style="white-space:pre"> </span> # Memcached host name or IP address. Mandatory if</div><div><span class="" style="white-space:pre"> </span> # memqcache_method = 'memcached'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to localhost.</div><div> # (change requires restart)</div><div>memqcache_memcached_port = 11211</div><div><span class="" style="white-space:pre"> </span> # Memcached port number. Mondatory if memqcache_method = 'memcached'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 11211.</div><div> # (change requires restart)</div><div>memqcache_total_size = 67108864</div><div><span class="" style="white-space:pre"> </span> # Total memory size in bytes for storing memory cache.</div><div><span class="" style="white-space:pre"> </span> # Mandatory if memqcache_method = 'shmem'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 64MB.</div><div> # (change requires restart)</div><div>memqcache_max_num_cache = 1000000</div><div><span class="" style="white-space:pre"> </span> # Total number of cache entries. Mandatory</div><div><span class="" style="white-space:pre"> </span> # if memqcache_method = 'shmem'.</div><div><span class="" style="white-space:pre"> </span> # Each cache entry consumes 48 bytes on shared memory.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 1,000,000(45.8MB).</div><div> # (change requires restart)</div><div>memqcache_expire = 0</div><div><span class="" style="white-space:pre"> </span> # Memory cache entry life time specified in seconds.</div><div><span class="" style="white-space:pre"> </span> # 0 means infinite life time. 0 by default.</div><div> # (change requires restart)</div><div>memqcache_auto_cache_invalidation = on</div><div><span class="" style="white-space:pre"> </span> # If on, invalidation of query cache is triggered by corresponding</div><div><span class="" style="white-space:pre"> </span> # DDL/DML/DCL(and memqcache_expire). If off, it is only triggered</div><div><span class="" style="white-space:pre"> </span> # by memqcache_expire. on by default.</div><div> # (change requires restart)</div><div>memqcache_maxcache = 409600</div><div><span class="" style="white-space:pre"> </span> # Maximum SELECT result size in bytes.</div><div><span class="" style="white-space:pre"> </span> # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.</div><div> # (change requires restart)</div><div>memqcache_cache_block_size = 1048576</div><div><span class="" style="white-space:pre"> </span> # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.</div><div><span class="" style="white-space:pre"> </span> # Defaults to 1MB.</div><div> # (change requires restart)</div><div>memqcache_oiddir = '/var/log/pgpool/oiddir'</div><div><span class="" style="white-space:pre"> </span> <span class="" style="white-space:pre"> </span> # Temporary work directory to record table oids</div><div> # (change requires restart)</div><div>white_memqcache_table_list = ''</div><div> # Comma separated list of table names to memcache</div><div> # that don't write to database</div><div> # Regexp are accepted</div><div>black_memqcache_table_list = ''</div><div> # Comma separated list of table names not to memcache</div><div> # that don't write to database</div><div> # Regexp are accepted</div><div>ssl_key = ''</div><div>ssl_cert = ''</div><div>ssl_ca_cert = ''</div><div>ssl_ca_cert_dir = ''</div><div>log_error_verbosity = 'DEFAULT'</div><div>client_min_messages = 'notice'</div><div>log_min_messages = 'warning'</div><div>backend_hostname0 = 'SUYSevPrepDB01'</div><div>backend_port0 = 5432</div><div>backend_weight0 = 1</div><div>backend_data_directory0 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag0= 'ALLOW_TO_FAILOVER'</div><div>backend_hostname1 = 'SUYSevPrepDB02'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div>backend_data_directory1 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag1= 'ALLOW_TO_FAILOVER'</div><div>other_pgpool_hostname0 = 'SUYSevPrepDB01'</div><div>other_pgpool_port0 = 9999</div><div>other_wd_port0 = 9000</div><div>heartbeat_destination0 = 'SUYSevPrepDB01'</div><div>heartbeat_destination_port0 = 9694</div><div>heartbeat_device0 = ''</div></div><div><br></div><div><div># TYPE DATABASE USER ADDRESS METHOD</div><div><br></div><div># "local" is for Unix domain socket connections only</div><div>local all all md5</div><div># IPv4 local connections:</div><div>host all all <a href="http://127.0.0.1/32">127.0.0.1/32</a> md5</div><div>host all all <a href="http://0.0.0.0/0">0.0.0.0/0</a> md5</div><div># IPv6 local connections:</div><div># Allow replication connections from localhost, by a user with the</div><div># replication privilege.</div><div>local replication postgres trust</div><div>host replication replication <a href="http://0.0.0.0/0">0.0.0.0/0</a> trust</div><div>#host replication postgres ::1/128 trust</div></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2016-04-07 19:31 GMT-03:00 Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">>>> *DETALLE: message length (12) in slot 1 does not match with slot 0(8)*<br>
<br>
Probably a configuration mismatch in PostgreSQL server #0 and<br>
PostgreSQL server #1. From reading the message above, server #0 asks<br>
trust authentication (no password), while server #1 requires certain<br>
authentication (possibly md5). Showing full pgpool.conf and<br>
pg_hba.conf from both PostgreSQL servers will reveal this.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
<span class=""><br>
> Lucas is the same error!!!!<br>
><br>
><br>
> Apr 7 16:14:24 localhost pgpool[14839]: [40-1] 2016-04-07 16:14:24: pid<br>
> 14839: # printf-style string to output at beginning of each log line.ERROR:<br>
> unable to read message length<br>
> Apr 7 16:14:24 localhost pgpool[14839]: [40-2] 2016-04-07 16:14:24: pid<br>
> 14839: # printf-style string to output at beginning of each log<br>
> line.DETAIL: message length (12) in slot 1 does not match with slot 0<br>
><br>
> 2016-04-07 15:52 GMT-03:00 Lucas Luengas <<a href="mailto:lucasluengas@gmail.com">lucasluengas@gmail.com</a>>:<br>
><br>
>> Hello<br>
>> You can check pgpool log and postgresql log when you run that psql command.<br>
>> Regards.<br>
>> On Apr 6, 2016 8:30 PM, "Daniel Gallo" <<a href="mailto:danielito.gallo@gmail.com">danielito.gallo@gmail.com</a>> wrote:<br>
>><br>
>>> Dear I'm trying to run the following and I get the following error and I<br>
>>> can not figure out what the problem is , I could help ?<br>
>>><br>
>>><br>
>>> Any information that need be notified me and sent them<br>
>>><br>
>>><br>
>>><br>
>>> Thanks a lots!!!!!<br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
>>> -bash-4.2$ psql -h localhost -U postgres -p 5432<br>
>>><br>
>>> psql (9.4.4)<br>
>>><br>
>>> Digite «help» para obtener ayuda.<br>
>>><br>
>>><br>
>>><br>
>>> postgres=#<br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
</span>>>> *-bash-4.2$ psql -h localhost -U postgres -p 9999*<br>
>>><br>
>>> *psql: ERROR: unable to read message length*<br>
>>><br>
>>> *DETALLE: message length (12) in slot 1 does not match with slot 0(8)*<br>
>>><br>
>>><br>
>>><br>
>>> Any idea?<br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
>>> *pg_hba.conf*<br>
<span class="">>>><br>
>>><br>
>>><br>
>>> # TYPE DATABASE USER ADDRESS METHOD<br>
>>><br>
>>><br>
>>><br>
>>> # "local" is for Unix domain socket connections only<br>
>>><br>
>>> local all all md5<br>
>>><br>
>>> # IPv4 local connections:<br>
>>><br>
>>> host all all <a href="http://127.0.0.1/32" rel="noreferrer" target="_blank">127.0.0.1/32</a> md5<br>
>>><br>
>>> host all all <a href="http://0.0.0.0/0" rel="noreferrer" target="_blank">0.0.0.0/0</a> md5<br>
>>><br>
>>> # IPv6 local connections:<br>
>>><br>
>>> # Allow replication connections from localhost, by a user with the<br>
>>><br>
>>> # replication privilege.<br>
>>><br>
>>> local replication postgres trust<br>
>>><br>
>>> host replication replication <a href="http://0.0.0.0/0" rel="noreferrer" target="_blank">0.0.0.0/0</a> trust<br>
>>><br>
>>> #host replication postgres ::1/128 trust<br>
>>><br>
>>><br>
>>><br>
>>> -rwxrw-r--. 1 apache apache 33283 abr 1 15:49 pgpool.conf<br>
>>><br>
>>><br>
>>><br>
</span>>>> *pgpool.conf*<br>
<div class="HOEnZb"><div class="h5">>>><br>
>>><br>
>>><br>
>>> # - Authentication -<br>
>>><br>
>>><br>
>>><br>
>>> enable_pool_hba = on<br>
>>><br>
>>> # Use pool_hba.conf for client<br>
>>> authentication<br>
>>><br>
>>> pool_passwd = 'pool_passwd'<br>
>>><br>
>>> # File name of pool_passwd for md5<br>
>>> authentication.<br>
>>><br>
>>> # "" disables pool_passwd.<br>
>>><br>
>>> # (change requires restart)<br>
>>><br>
>>> authentication_timeout = 60<br>
>>><br>
>>> # Delay in seconds to complete client<br>
>>> authentication<br>
>>><br>
>>> # 0 means no timeout.<br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
>>> -rw-r--r--. 1 apache root 42 feb 29 18:28 pool_passwd<br>
>>><br>
>>><br>
>>><br>
>>> postgres:5c0435eee0f211f150e980e459151xxx<br>
>>><br>
>>><br>
>>><br>
>>><br>
>>><br>
>>> -rwxrw-r--. 1 root root 3295 mar 3 18:19 pool_hba.conf<br>
>>><br>
>>><br>
>>><br>
>>> # TYPE DATABASE USER CIDR-ADDRESS METHOD<br>
>>><br>
>>><br>
>>><br>
>>> # "local" is for Unix domain socket connections only<br>
>>><br>
>>> local all all md5<br>
>>><br>
>>> # IPv4 local connections:<br>
>>><br>
>>> host all all <a href="http://127.0.0.1/32" rel="noreferrer" target="_blank">127.0.0.1/32</a> md5<br>
>>><br>
>>> host all all <a href="http://0.0.0.0/0" rel="noreferrer" target="_blank">0.0.0.0/0</a> md5<br>
>>><br>
>>> #host all all ::1/128 trust<br>
>>><br>
>>> _______________________________________________<br>
>>> pgpool-general mailing list<br>
>>> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
>>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>>><br>
>>><br>
</div></div></blockquote></div><br></div>