<div dir="ltr">Hi<div style>Sorry to trouble you, I'm new to pgpool, and met some problem for days</div><div style>I setup 2 SQL nodes and 1 pgpool manager in my VBox</div><div style>Didn't change much base on <pgpool.conf.sample-stream></div>
<div style>(2 nodes as backend_hostname0/1, and they trust connection from pgpool's machine in their pg_hba.conf)</div><div style>And I find my nodes' status are all "Attached. Run as Master", no one in slave mode.</div>
<div style>So when I run "creatdb -p 9999 test2", pgpool only create the DB on Node#0.</div><div style>I see these in logs, suspect this may case my problem.</div><div style><br></div><div style><table style="border-top-width:1px;border-top-style:solid;border-top-color:rgb(176,196,222);border-collapse:collapse;color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:x-small">
<tbody><tr class="" style="background-color:rgb(240,255,255)"><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:09:21</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
ERROR:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 2680:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
check_replication_time_lag: SELECT pg_last_xlog_replay_location() returns no data</td></tr></tbody></table><br></div><div style><table style="border-top-width:1px;border-top-style:solid;border-top-color:rgb(176,196,222);border-collapse:collapse;color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:x-small">
<tbody><tr class=""><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:51:22</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 1674:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
pool_ssl: SSL requested but SSL support is not available</td></tr></tbody></table></div><div style><br></div><div style><table style="border-top-width:1px;border-top-style:solid;border-top-color:rgb(176,196,222);border-collapse:collapse;color:rgb(0,0,0);font-family:Verdana,Arial,Helvetica,sans-serif;font-size:x-small">
<tbody><tr class="" style="background-color:rgb(240,255,255)"><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:52:30</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
statement2: CREATE DATABASE test2;</td></tr><tr class=""><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)"></td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)"></td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
</td></tr><tr class="" style="background-color:rgb(240,255,255)"><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:52:30</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
pool_set_query_in_progress: done</td></tr><tr class=""><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:52:30</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
send_to_where: 0 query: CREATE DATABASE test2;</td></tr><tr class="" style="background-color:rgb(240,255,255)"><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)"></td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)"></td></tr><tr class=""><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
2013-02-25 00:52:30</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">wait_for_query_response: waiting for backend 0 completing the query</td></tr><tr class="" style="background-color:rgb(240,255,255)">
<td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:52:31</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
detect_error: kind: C</td></tr><tr class=""><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:52:31</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
detect_error: kind: C</td></tr><tr class="" style="background-color:rgb(240,255,255)"><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:52:31</td>
<td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">detect_error: kind: C</td></tr><tr class=""><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
2013-02-25 00:52:31</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">detect_error: kind: C</td></tr><tr class="" style="background-color:rgb(240,255,255)"><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
2013-02-25 00:52:31</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">read_kind_from_one_backend: read kind from 0 th backend C</td></tr><tr class=""><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
2013-02-25 00:52:31</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">read_kind_from_backend: kind: C from 0 th backend</td></tr><tr class="" style="background-color:rgb(240,255,255)">
<td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">2013-02-25 00:52:31</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
DEBUG:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">pid 1675:</td><td style="padding:4px 8px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:rgb(176,196,222)">
read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2</td></tr></tbody></table></div><div style>any help will be great appreciate!</div><div style><br></div><div style>--------------------------------------------------</div>
<div style>OS: Ubuntu Server 12.04</div><div style>Pgpool Verstion: 3.2.3</div><div style>Pgpool Admin Version: 3.2.2</div><div style>---------------------------------------------------</div><div style><br></div><div style>
<br></div><div style>Below is my pgpool.conf</div><div style>----------------------------------------------------</div><div style><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 = '/home/apache/pgpool'</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_port = 9898</div><div> # Port number for pcp</div>
<div> # (change requires restart)</div><div>pcp_socket_dir = '/home/apache/pgpool'</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><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 = '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>print_timestamp = on</div><div> # Print timestamp on each line</div>
<div> # (change requires restart)</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><br></div><div>#------------------------------------------------------------------------------</div><div># FILE LOCATIONS</div>
<div>#------------------------------------------------------------------------------</div><div><br></div><div>pid_file_name = '/home/apache/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 or parallel 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><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 = 10</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 = 'mypass'</div>
<div> # Password for streaming replication check user</div><div>delay_threshold = 100</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><br></div><div>#------------------------------------------------------------------------------</div><div># PARALLEL MODE</div><div>#------------------------------------------------------------------------------</div>
<div><br></div><div>parallel_mode = off</div><div> # Activates parallel query mode</div><div> # (change requires restart)</div><div>pgpool2_hostname = ''</div>
<div> # Set pgpool2 hostname </div><div> # (change requires restart)</div><div><br></div><div># - System DB info -</div><div><br></div><div>#system_db_hostname = 'localhost'</div>
<div> # (change requires restart)</div><div>#system_db_port = 5432</div><div> # (change requires restart)</div><div>#system_db_dbname = 'pgpool'</div>
<div> # (change requires restart)</div><div>#system_db_schema = 'pgpool_catalog'</div><div> # (change requires restart)</div><div>#system_db_user = 'pgpool'</div>
<div> # (change requires restart)</div><div>#system_db_password = ''</div><div> # (change requires restart)</div><div><br></div><div><br></div><div>
#------------------------------------------------------------------------------</div><div># HEALTH CHECK</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>health_check_period = 1</div>
<div> # Health check period</div><div> # Disabled (0) by default</div><div>health_check_timeout = 5</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 = 'mypass'</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><br></div><div><br></div><div>#------------------------------------------------------------------------------</div>
<div># FAILOVER AND FAILBACK</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>failover_command = ''</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><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 = 'mypass'</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>#------------------------------------------------------------------------------</div><div># WATCHDOG</div>
<div>#------------------------------------------------------------------------------</div><div>use_watchdog = off</div><div> # Activates watchdog</div><div> # (change requires restart)</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>delegate_IP = ''</div><div> # delegate IP address</div><div> # (change requires restart)</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_interval = 10</div><div> # lifecheck interval (sec) > 0</div>
<div> # (change requires restart)</div><div>ping_path = '/bin'</div><div> # ping command path</div><div> # (change requires restart)</div>
<div>ifconfig_path = '/sbin'</div><div> # ifconfig command path</div><div> # (change requires restart)</div><div>if_up_cmd = 'ifconfig eth0: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 eth0:0 down'</div><div> # shutdown delegate IP command</div>
<div> # (change requires restart)</div><div><br></div><div>arping_path = '/usr/sbin # arping command path'</div><div> # (change requires restart)</div>
<div><br></div><div>arping_cmd = 'arping -U $_IP_$ -w 1'</div><div> # arping command</div><div> # (change requires restart)</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># 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>#other_pgpool_port0 = 5432</div><div> # Port number for othet pgpool 0</div><div>#other_wd_port0 = 9000</div><div> # Port number for othet watchdog 0</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><br></div><div>#------------------------------------------------------------------------------</div><div># ON 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><br></div><div>#backend</div><div>ssl_key = ''</div><div>ssl_cert = ''</div>
<div>ssl_ca_cert = ''</div><div>ssl_ca_cert_dir = ''</div><div>enable_query_cache = off</div><div>system_db_hostname = 'system_db_hostname'</div><div>system_db_port = 5432</div><div>system_db_dbname = 'pgpool'</div>
<div>system_db_schema = 'pgpool_catalog'</div><div>system_db_user = 'pgpool'</div><div>system_db_password = ''</div><div>#backend_socket_dir = '/tmp'</div><div>backend_socket_dir = '/tmp'</div>
<div>backend_hostname0 = '192.168.1.115'</div><div>backend_port0 = 5432</div><div>backend_weight0 = 1</div><div>backend_data_directory0 = '/var/lib/postgresql/9.1/main'</div><div>backend_flag0= 'ALLOW_TO_FAILOVER'</div>
<div>backend_hostname1 = '192.168.1.116'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div>backend_data_directory1 = '/var/lib/postgresql/9.1/main'</div><div>backend_flag1= 'ALLOW_TO_FAILOVER'</div>
<div><br></div></div></div>