<div dir="ltr"><span style="font-size:12.8px">Hi,</span><div style="font-size:12.8px"><br clear="all"><div>I have in issue with pgpool2 and cannot figure out why!!!! my setup is as follows:</div><div>singe pgpool to do LB for me</div><div>master slave postgress</div><div>The issue is as follows, when i send read queries via the pgpool everything is fine, load get distributed between and master and salve.</div><div>but when im doing a query that writes to the database the pgpool again uses both slave and master and it yeilds error on queries sent to slave.</div><div>I have installed 96 on centos.</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># CONNECTIONS</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</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 = 5432</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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # num_init_children * listen_backlog_multiplier.</div><div> # (change requires restart)</div><div>serialize_accept = off</div><div> # whether to serialize accept() call to avoid thundering herd problem</div><div> # (change requires restart)</div><div><br></div><div># - Backend Connection Settings -</div><div><br></div><div>backend_hostname0 = 'pgdb1'</div><div> # Host name or IP address to connect to for backend 0</div><div>backend_port0 = 5432</div><div> # Port number for backend 0</div><div>backend_weight0 = 1</div><div> # Weight for backend 0 (only in load balancing mode)</div><div>backend_data_directory0 = '/var/lib/pgsql/9.5/data'</div><div> # Data directory for backend 0</div><div>backend_flag0 = 'ALLOW_TO_FAILOVER'</div><div> # Controls various backend behavior</div><div> # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER</div><div>backend_hostname1 = 'pgdb2'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div>backend_data_directory1 = '/var/lib/pgsql/9.5/data'</div><div>backend_flag1 = 'ALLOW_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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># POOLS</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div><br></div><div># - Concurrent session and pool size -</div><div><br></div><div>num_init_children = 32</div><div> # Number of concurrent sessions allowed</div><div> # (change requires restart)</div><div>max_pool = 4</div><div> # Number of connection pool caches per connection</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># LOGS</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div><br></div><div># - Where to log -</div><div><br></div><div>log_destination = 'stderr'</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 = off</div><div> # Hostname will be shown in ps status</div><div> # and in logs if connections are logged</div><div>log_statement = off</div><div> # Log all statements</div><div>log_per_node_statement = off</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 = 0</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># FILE LOCATIONS</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div><br></div><div>pid_file_name = '/var/run/pgpool-II-95/pgpool.<wbr>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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># CONNECTION POOLING</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># REPLICATION MODE</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</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_mi<wbr>smatch = 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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># LOAD BALANCING MODE</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</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,setva<wbr>l'</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_l<wbr>ist = ''</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # comma separated list of pairs of database and node id.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # example: postgres:primary,mydb[0-4]:1,m<wbr>ydb[5-9]:2'</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # valid for streaming replicaton mode only.</div><div><br></div><div>app_name_redirect_preference_l<wbr>ist = ''</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # comma separated list of pairs of app name and node id.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # example: 'psql:primary,myapp[0-4]:1,mya<wbr>pp[5-9]:standby'</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # valid for streaming replicaton mode only.</div><div>allow_sql_comments = off</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # if on, ignore SQL comments when judging if load balance or</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # query cache is possible.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # If off, SQL comments effectively prevent the judgment</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # (pre 3.4 behavior).</div><div><br></div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># MASTER/SLAVE MODE</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</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 = 10</div><div> # Streaming replication check period</div><div> # Disabled (0) by default</div><div>sr_check_user = 'pgpool'</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 = 'pool'</div><div> # Password for streaming replication check user</div><div>sr_check_database = 'postgres'</div><div> # Database name for streaming replication check</div><div>delay_threshold = 10000000</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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # %r = new master port number</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # %R = new master database cluster path</div><div> # %% = '%' character</div><div><br></div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># HEALTH CHECK</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div><br></div><div>health_check_period = 10</div><div> # Health check period</div><div> # Disabled (0) by default</div><div>health_check_timeout = 20</div><div> # Health check timeout</div><div> # 0 means no timeout</div><div>health_check_user = 'pgpool'</div><div> # Health check user</div><div>health_check_password = 'pool'</div><div> # Password for health check user</div><div>health_check_database = ''</div><div> # Database name for health check. If '', tries 'postgres' frist, </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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Default is 10000 ms (10 second). Flaky network user may want to increase</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # the value. 0 means no timeout.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Note that this value is not only used for health check,</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # but also for ordinary conection to backend.</div><div><br></div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># FAILOVER AND FAILBACK</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div><br></div><div>failover_command = '/etc/pgpool-II-95/failover.sh %d %H'</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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # %r = new master port number</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # %r = new master port number</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </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 = 300</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># ONLINE RECOVERY</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div><br></div><div>recovery_user = 'pgpool'</div><div> # Online recovery user</div><div>recovery_password = 'pool'</div><div> # Online recovery password</div><div>recovery_1st_stage_command = ''</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># WATCHDOG</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div><br></div><div># - Enabling -</div><div><br></div><div>use_watchdog = off</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 = ''</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_priority = 1</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># priority of this watchdog in leader election</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># (change requires restart)</div><div><br></div><div>wd_authkey = ''</div><div> # Authentication key for watchdog communication</div><div> # (change requires restart)</div><div><br></div><div>wd_ipc_socket_dir = '/tmp'</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># Unix domain socket path for watchdog IPC socket</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># The Debian package defaults to</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># /var/run/postgresql</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># (change requires restart)</div><div><br></div><div><br></div><div># - Virtual IP control Setting -</div><div><br></div><div>delegate_IP = ''</div><div> # delegate IP address</div><div> # If this is empty, virtual IP never bring up. </div><div> # (change requires restart)</div><div>if_cmd_path = '/sbin'</div><div> # path to the directory where if_up/down_cmd exists </div><div> # (change requires restart)</div><div>if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'</div><div> # startup delegate IP command</div><div> # (change requires restart)</div><div>if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'</div><div> # shutdown delegate IP command</div><div> # (change requires restart)</div><div>arping_path = '/usr/sbin'</div><div> # arping command path</div><div> # (change requires restart)</div><div>arping_cmd = 'arping -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>wd_de_escalation_command = ''</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># Executes this command when master pgpool resigns from being master.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># (change requires restart)</div><div><br></div><div># - Lifecheck Setting -</div><div><br></div><div># -- common --</div><div><br></div><div>wd_monitoring_interfaces_list = '' # Comma separated list of interfaces names to monitor.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># if any interface from the list is active the watchdog will</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># consider the network is fine</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># 'any' to enable monitoring on all interfaces except loopback</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span># '' to disable monitoring</div><div><br></div><div>wd_lifecheck_method = 'heartbeat'</div><div> # Method of watchdog lifecheck ('heartbeat' or 'query' or 'external')</div><div> # (change requires restart)</div><div>wd_interval = 10</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>heartbeat_destination0 = 'host0_ip1'</div><div> # Host name or IP address of destination 0</div><div> # for sending heartbeat signal.</div><div> # (change requires restart)</div><div>heartbeat_destination_port0 = 9694 </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>heartbeat_device0 = ''</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 = 'nobody'</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>#other_pgpool_hostname0 = 'host0'</div><div> # Host name or IP address to connect to for other pgpool 0</div><div> # (change requires restart)</div><div>#other_pgpool_port0 = 5432</div><div> # Port number for othet pgpool 0</div><div> # (change requires restart)</div><div>#other_wd_port0 = 9000</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># OTHERS</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # "pool_search_relcache: cache replacement happend"</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # thus increases load of master.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # If you are absolutely sure that your system never uses temporary tables</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # and you want to save access to primary/master, you could turn this off.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Default is on.</div><div><br></div><div>check_unlogged_table = on</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>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div># IN MEMORY QUERY MEMORY CACHE</div><div>#-----------------------------<wbr>------------------------------<wbr>-------------------</div><div>memory_cache_enabled = off</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # If on, use the memory cache functionality, off by default</div><div>memqcache_method = 'shmem'</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Cache storage method. either 'shmem'(shared memory) or</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # 'memcached'. 'shmem' by default</div><div> # (change requires restart)</div><div>memqcache_memcached_host = 'localhost'</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Memcached host name or IP address. Mandatory if</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # memqcache_method = 'memcached'.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Defaults to localhost.</div><div> # (change requires restart)</div><div>memqcache_memcached_port = 11211</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Memcached port number. Mondatory if memqcache_method = 'memcached'.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Defaults to 11211.</div><div> # (change requires restart)</div><div>memqcache_total_size = 67108864</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Total memory size in bytes for storing memory cache.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Mandatory if memqcache_method = 'shmem'.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Defaults to 64MB.</div><div> # (change requires restart)</div><div>memqcache_max_num_cache = 1000000</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Total number of cache entries. Mandatory</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # if memqcache_method = 'shmem'.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Each cache entry consumes 48 bytes on shared memory.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Defaults to 1,000,000(45.8MB).</div><div> # (change requires restart)</div><div>memqcache_expire = 0</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Memory cache entry life time specified in seconds.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # 0 means infinite life time. 0 by default.</div><div> # (change requires restart)</div><div>memqcache_auto_cache_invalidat<wbr>ion = on</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # If on, invalidation of query cache is triggered by corresponding</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # DDL/DML/DCL(and memqcache_expire). If off, it is only triggered</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # by memqcache_expire. on by default.</div><div> # (change requires restart)</div><div>memqcache_maxcache = 409600</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Maximum SELECT result size in bytes.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </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="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> # Defaults to 1MB.</div><div> # (change requires restart)</div><div>memqcache_oiddir = '/var/log/pgpool/oiddir'</div><div><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </span> <span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-Apple-tab-span" style="white-space:pre-wrap"> </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><div><br></div><div><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1">---------+----------+------+--<wbr>------+-----------+---------+-<wbr>-----------</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1"> 0 | pgdb1 | 5432 | 2 | 0.500000 | primary | 10581</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1"> 1 | pgdb2 | 5432 | 2 | 0.500000 | standby | 2351</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1"><br></span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1">Role | Name | Upstream | Connection String</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1">----------+-------|----------|<wbr>------------------------------<wbr>-------</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1"> standby | pgdb2 | | host=pgdb2 user=repmgr dbname=repmgr</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1"></span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1">* master | pgdb1 | | host=pgdb1 user=repmgr dbname=repmgr</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1"><br></span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1">The errors I see on the web server:</span></p><p class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-p1"><span class="gmail-m_1313661913095189041gmail-m_-8072961440093362156gmail-s1"></span></p><table style="margin:0px;padding:0px;border:0px;font-size:13px;border-collapse:collapse;width:660px;color:rgb(36,39,41);font-family:arial,"helvetica neue",helvetica,sans-serif"><tbody style="margin:0px;padding:0px;border:0px"><tr id="gmail-comment-70462350" class="gmail-comment" style="margin:0px;padding:0px;border:0px"><td class="gmail-comment-text" style="padding:6px 6px 6px 0px;border-width:0px 0px 1px;border-top-style:initial;border-right-style:initial;border-bottom-style:solid;border-left-style:initial;border-top-color:initial;border-right-color:initial;border-bottom-color:rgb(239,240,241);border-left-color:initial;vertical-align:top;line-height:1.3"><div class="gmail-comment-body" style="margin:0px;padding:0px;border:0px"><span class="gmail-comment-copy" style="margin:0px;padding:0px;border:0px"><br class="gmail-Apple-interchange-newline">PreventCommandIfReadOnly </span></div><div class="gmail-comment-body" style="margin:0px;padding:0px;border:0px"><span class="gmail-comment-copy" style="margin:0px;padding:0px;border:0px">cannot execute SELECT in a read-only transaction</span></div></td></tr></tbody></table></div></div><div><br></div>
</div>