[pgpool-general: 4103] Re: issue with single node failover

Tatsuo Ishii ishii at postgresql.org
Sat Oct 10 08:10:51 JST 2015


Usama,

Here's how I tested the patch.

$ pgpool_setup -m r -n 1

$ ./startall

t-ishii at localhost: psql -p 11000 test
psql (9.4.4)
Type "help" for help.

test=# 

$ pg_ctl -D data0 -m f stop

$ psql -p 11000 test
psql: ERROR:  pgpool is not accepting any new connections
DETAIL:  all backend nodes are down, pgpool requires atleast one valid node
HINT:  repair the backend nodes and restart pgpool

$ pg_ctl -D data0 start

$ psql -p 11000 test
psql: ERROR:  pgpool is not accepting any new connections
DETAIL:  all backend nodes are down, pgpool requires atleast one valid node
HINT:  repair the backend nodes and restart pgpool

The last psql is expected to start normaly if the bug is fixed.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Unfortunately, your patch does not fix the problem.
> 
> Also your patch does not address the typo problem.
>>> Also "atleast" seems a typo.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> Hi
>> 
>> I have found the problem and attached is the patch for the fix.
>> I will check-in the patch after executing the regression test. Meanwhile,
>> you can also test it
>> 
>> Thanks
>> Best regards
>> Muhammad Usama
>> 
>> 
>> On Wed, Oct 7, 2015 at 4:17 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 
>>> I have confirmed the problem is in 3.4.0 or later, not in 3.3.x or
>>> before.
>>>
>>> Usama,
>>>
>>> Can you please take care of this? It seems the problem was introduced
>>> in 3.4.0.
>>>
>>> protocol/child.c:
>>>
>>>                 /* In non parallel mode single valid backend is all we need
>>>                  * to continue
>>>                  */
>>>                 if(valid_backends == 0)
>>>                 {
>>>                         fatal_error = true;
>>>                         error_msg = "pgpool is not accepting any new
>>> connections";
>>>                         error_detail = "all backend nodes are down, pgpool
>>> requires atleast one valid node";
>>>                         error_hint = "repair the backend nodes and restart
>>> pgpool";
>>>                 }
>>>
>>> Also "atleast" seems a typo.
>>>
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese:http://www.sraoss.co.jp
>>>
>>> > I have pgpool 3.4.3 (from the pgdb93 repo) setup in raw mode pointing to
>>> a
>>> > single backend node on a separate server.  I would like set pgpool to
>>> > failover to the single node and attempt to reconnect indefinitely.  For
>>> > some reason I can't get the config right or it may be a bug?  I have it
>>> > working in a different environment using pgpool 3.2 from the epel repo
>>> >
>>> > Any help would be greatly appreciated.
>>> >
>>> >
>>> > # Log
>>> >
>>> > 2015-10-06 09:45:49: pid 13371: DETAIL:  postmaster on DB node 0 was
>>> > shutdown by administrative command
>>> > 2015-10-06 09:45:49: pid 13371: LOG:  received degenerate backend request
>>> > for node_id: 0 from pid [13371]
>>> > 2015-10-06 09:45:49: pid 13361: LOG:  starting degeneration. shutdown
>>> host
>>> > 172.16.0.59(5432)
>>> > 2015-10-06 09:45:49: pid 13361: LOG:  failover: no valid backends node
>>> found
>>> > 2015-10-06 09:45:49: pid 13361: LOG:  Restart all children
>>> > 2015-10-06 09:45:49: pid 13361: LOG:  failover: set new primary node: -1
>>> > 2015-10-06 09:45:49: pid 13374: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13370: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13376: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13367: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13363: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13373: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13372: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13365: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13364: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13377: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13366: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13375: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13368: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13369: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13371: LOG:  child process received shutdown
>>> > request signal 3
>>> > 2015-10-06 09:45:49: pid 13379: LOG:  worker process received restart
>>> > request
>>> > failover done. shutdown host 172.16.0.59(5432)2015-10-06 09:45:49: pid
>>> > 13361: LOG:  failover done. shutdown host 172.16.0.59(5432)
>>> > 2015-10-06 09:45:50: pid 13378: LOG:  restart request received in pcp
>>> child
>>> > process
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  PCP child 13378 exits with status
>>> 256
>>> > in failover()
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  fork a new PCP child pid 13399 in
>>> > failover()
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13363 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13363
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13364 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13364
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13365 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13365
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13366 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13366
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13367 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13367
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13368 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13368
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13369 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13369
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13370 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13370
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13371 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13371
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13372 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13372
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13373 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13373
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13374 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13374
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13375 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13375
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13376 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13376
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13377 exits
>>> > with status 0
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  child process with pid: 13377
>>> exited
>>> > with success and will not be restarted
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  worker child process with pid:
>>> 13379
>>> > exits with status 256
>>> > 2015-10-06 09:45:50: pid 13361: LOG:  fork a new worker child process
>>> with
>>> > pid: 13400
>>> > 2015-10-06 09:46:02: pid 13392: FATAL:  pgpool is not accepting any new
>>> > connections
>>> > 2015-10-06 09:46:02: pid 13392: DETAIL:  all backend nodes are down,
>>> pgpool
>>> > requires atleast one valid node
>>> > 2015-10-06 09:46:02: pid 13392: HINT:  repair the backend nodes and
>>> restart
>>> > pgpool
>>> > 2015-10-06 09:46:02: pid 13361: LOG:  child process with pid: 13392 exits
>>> > with status 256
>>> > 2015-10-06 09:46:02: pid 13361: LOG:  fork a new child process with pid:
>>> > 13401
>>> > 2015-10-06 09:46:27: pid 13401: FATAL:  pgpool is not accepting any new
>>> > connections
>>> > 2015-10-06 09:46:27: pid 13401: DETAIL:  all backend nodes are down,
>>> pgpool
>>> > requires atleast one valid node
>>> > 2015-10-06 09:46:27: pid 13401: HINT:  repair the backend nodes and
>>> restart
>>> > pgpool
>>> > 2015-10-06 09:46:27: pid 13361: LOG:  child process with pid: 13401 exits
>>> > with status 256
>>> > 2015-10-06 09:46:27: pid 13361: LOG:  fork a new child process with pid:
>>> > 13410
>>> > 2015-10-06 09:46:34: pid 13390: FATAL:  pgpool is not accepting any new
>>> > connections
>>> > 2015-10-06 09:46:34: pid 13390: DETAIL:  all backend nodes are down,
>>> pgpool
>>> > requires atleast one valid node
>>> > 2015-10-06 09:46:34: pid 13390: HINT:  repair the backend nodes and
>>> restart
>>> > pgpool
>>> > 2015-10-06 09:46:34: pid 13361: LOG:  child process with pid: 13390 exits
>>> > with status 256
>>> > 2015-10-06 09:46:34: pid 13361: LOG:  fork a new child process with pid:
>>> > 13412
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > # Config File
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # CONNECTIONS
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > # - pgpool Connection Settings -
>>> >
>>> > listen_addresses = '*'
>>> >                                    # Host name or IP address to listen
>>> on:
>>> >                                    # '*' for all, '' for no TCP/IP
>>> > connections
>>> >                                    # (change requires restart)
>>> > port = 9999
>>> >                                    # Port number
>>> >                                    # (change requires restart)
>>> > socket_dir = '/var/run/pgpool-II-93'
>>> >                                    # Unix domain socket path
>>> >                                    # (change requires restart)
>>> >
>>> >
>>> > # - pgpool Communication Manager Connection Settings -
>>> >
>>> > pcp_port = 9898
>>> >                                    # Port number for pcp
>>> >                                    # (change requires restart)
>>> > pcp_socket_dir = '/var/run/pgpool-II-93'
>>> >                                    # Unix domain socket path for pcp
>>> >                                    # (change requires restart)
>>> >
>>> > # - Backend Connection Settings -
>>> >
>>> > backend_hostname0 = '172.16.0.59'
>>> >                                    # Host name or IP address to connect
>>> to
>>> > for backend 0
>>> > backend_port0 = 5432
>>> >                                    # Port number for backend 0
>>> > # backend_weight0 = 1
>>> >                                    # Weight for backend 0 (only in load
>>> > balancing mode)
>>> > # backend_data_directory0 = '/var/lib/pgsql/9.3/data'
>>> >                                    # Data directory for backend 0
>>> > backend_flag0 = 'ALLOW_TO_FAILOVER'
>>> >                                    # Controls various backend behavior
>>> >                                    # ALLOW_TO_FAILOVER or
>>> > DISALLOW_TO_FAILOVER
>>> > #backend_hostname1 = 'host2'
>>> > #backend_port1 = 5433
>>> > #backend_weight1 = 1
>>> > #backend_data_directory1 = '/data1'
>>> > #backend_flag1 = 'ALLOW_TO_FAILOVER'
>>> >
>>> > # - Authentication -
>>> >
>>> > enable_pool_hba = off
>>> >                                    # Use pool_hba.conf for client
>>> > authentication
>>> > pool_passwd = 'pool_passwd'
>>> >                                    # File name of pool_passwd for md5
>>> > authentication.
>>> >                                    # "" disables pool_passwd.
>>> >                                    # (change requires restart)
>>> > authentication_timeout = 60
>>> >                                    # Delay in seconds to complete client
>>> > authentication
>>> >                                    # 0 means no timeout.
>>> >
>>> > # - SSL Connections -
>>> >
>>> > ssl = off
>>> >                                    # Enable SSL support
>>> >                                    # (change requires restart)
>>> > #ssl_key = './server.key'
>>> >                                    # Path to the SSL private key file
>>> >                                    # (change requires restart)
>>> > #ssl_cert = './server.cert'
>>> >                                    # Path to the SSL public certificate
>>> file
>>> >                                    # (change requires restart)
>>> > #ssl_ca_cert = ''
>>> >                                    # Path to a single PEM format file
>>> >                                    # containing CA root certificate(s)
>>> >                                    # (change requires restart)
>>> > #ssl_ca_cert_dir = ''
>>> >                                    # Directory containing CA root
>>> > certificate(s)
>>> >                                    # (change requires restart)
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # POOLS
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > # - Pool size -
>>> >
>>> > num_init_children = 15
>>> >                                    # Number of pools
>>> >                                   # (change requires restart)
>>> > max_pool = 1
>>> >                                    # Number of connections per pool
>>> >                                    # (change requires restart)
>>> >
>>> > # - Life time -
>>> >
>>> > child_life_time = 120 # was 300
>>> >                                    # Pool exits after being idle for this
>>> > many seconds
>>> > child_max_connections = 0
>>> >                                    # Pool exits after receiving that many
>>> > connections
>>> >                                    # 0 means no exit
>>> > connection_life_time = 0 # was 0
>>> >                                    # Connection to backend closes after
>>> > being idle for this many seconds
>>> >                                    # 0 means no close
>>> > client_idle_limit = 0 # was 0
>>> >                                    # Client is disconnected after being
>>> > idle for that many seconds
>>> >                                    # (even inside an explicit
>>> transactions!)
>>> >                                    # 0 means no disconnection
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # LOGS
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > # - Where to log -
>>> >
>>> > log_destination = 'stderr'
>>> >                                    # Where to log
>>> >                                    # Valid values are combinations of
>>> > stderr,
>>> >                                    # and syslog. Default to stderr.
>>> >
>>> > # - What to log -
>>> >
>>> > print_timestamp = on
>>> >                                    # Print timestamp on each line
>>> >                                    # (change requires restart)
>>> >
>>> > log_connections = on
>>> >                                    # Log connections
>>> > log_hostname = off
>>> >                                    # Hostname will be shown in ps status
>>> >                                    # and in logs if connections are
>>> logged
>>> > log_statement = off
>>> >                                    # Log all statements
>>> > log_per_node_statement = off
>>> >                                    # Log all statements
>>> >                                    # with node and backend informations
>>> > log_standby_delay = 'none'
>>> >                                    # Log standby delay
>>> >                                    # Valid values are combinations of
>>> > always,
>>> >                                    # if_over_threshold, none
>>> >
>>> > # - Syslog specific -
>>> >
>>> > syslog_facility = 'LOCAL0'
>>> >                                    # Syslog local facility. Default to
>>> > LOCAL0
>>> > syslog_ident = 'pgpool'
>>> >                                    # Syslog program identification string
>>> >                                    # Default to 'pgpool'
>>> >
>>> > # - Debug -
>>> >
>>> > debug_level = 2
>>> >                                    # Debug message verbosity level
>>> >                                    # 0 means no message, 1 or more mean
>>> > verbose
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # FILE LOCATIONS
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > pid_file_name = '/var/run/pgpool-II-93/pgpool.pid'
>>> >                                    # PID file name
>>> >                                    # (change requires restart)
>>> > logdir = '/var/log/pgpool-II'
>>> >                                    # Directory of pgPool status file
>>> >                                    # (change requires restart)
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # CONNECTION POOLING
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > #connection_cache = off
>>> > connection_cache = on
>>> >                                    # Activate connection pools
>>> >                                    # (change requires restart)
>>> >
>>> >                                    # Semicolon separated list of queries
>>> >                                    # to be issued at the end of a session
>>> >                                    # The default is for 8.3 and later
>>> > reset_query_list = 'ABORT; DISCARD ALL'
>>> >                                    # The following one is for 8.2 and
>>> before
>>> > #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # REPLICATION MODE
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > replication_mode = off
>>> >                                    # Activate replication mode
>>> >                                    # (change requires restart)
>>> > replicate_select = off
>>> >                                    # Replicate SELECT statements
>>> >                                    # when in replication or parallel mode
>>> >                                    # replicate_select is higher priority
>>> > than
>>> >                                    # load_balance_mode.
>>> >
>>> > insert_lock = on
>>> >                                    # Automatically locks a dummy row or a
>>> > table
>>> >                                    # with INSERT statements to keep
>>> SERIAL
>>> > data
>>> >                                    # consistency
>>> >                                    # Without SERIAL, no lock will be
>>> issued
>>> > lobj_lock_table = ''
>>> >                                    # When rewriting lo_creat command in
>>> >                                    # replication mode, specify table
>>> name to
>>> >                                    # lock
>>> >
>>> > # - Degenerate handling -
>>> >
>>> > replication_stop_on_mismatch = off
>>> >                                    # On disagreement with the packet kind
>>> >                                    # sent from backend, degenerate the
>>> node
>>> >                                    # which is most likely "minority"
>>> >                                    # If off, just force to exit this
>>> session
>>> >
>>> > failover_if_affected_tuples_mismatch = off
>>> >                                    # On disagreement with the number of
>>> > affected
>>> >                                    # tuples in UPDATE/DELETE queries,
>>> then
>>> >                                    # degenerate the node which is most
>>> > likely
>>> >                                    # "minority".
>>> >                                    # If off, just abort the transaction
>>> to
>>> >                                    # keep the consistency
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # LOAD BALANCING MODE
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > load_balance_mode = off
>>> >                                    # Activate load balancing mode
>>> >                                    # (change requires restart)
>>> > ignore_leading_white_space = on
>>> >                                    # Ignore leading white spaces of each
>>> > query
>>> > white_function_list = ''
>>> >                                    # Comma separated list of function
>>> names
>>> >                                    # that don't write to database
>>> >                                    # Regexp are accepted
>>> > black_function_list = 'nextval,setval'
>>> >                                    # Comma separated list of function
>>> names
>>> >                                    # that write to database
>>> >                                    # Regexp are accepted
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # MASTER/SLAVE MODE
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > master_slave_mode = off
>>> >                                    # Activate master/slave mode
>>> >                                    # (change requires restart)
>>> > master_slave_sub_mode = 'slony'
>>> >                                    # Master/slave sub mode
>>> >                                    # Valid values are combinations slony
>>> or
>>> >                                    # stream. Default is slony.
>>> >                                    # (change requires restart)
>>> >
>>> > # - Streaming -
>>> >
>>> > sr_check_period = 0
>>> >                                    # Streaming replication check period
>>> >                                    # Disabled (0) by default
>>> > sr_check_user = 'webserver'
>>> >                                    # Streaming replication check user
>>> >                                    # This is necessary even if you
>>> disable
>>> >                                    # streaming replication delay check
>>> with
>>> >                                    # sr_check_period = 0
>>> > sr_check_password = ''
>>> >                                    # Password for streaming replication
>>> > check user
>>> > delay_threshold = 0
>>> >                                    # Threshold before not dispatching
>>> query
>>> > to standby node
>>> >                                    # Unit is in bytes
>>> >                                    # Disabled (0) by default
>>> >
>>> > # - Special commands -
>>> >
>>> > follow_master_command = ''
>>> >                                    # Executes this command after master
>>> > failover
>>> >                                    # Special values:
>>> >                                    #   %d = node id
>>> >                                    #   %h = host name
>>> >                                    #   %p = port number
>>> >                                    #   %D = database cluster path
>>> >                                    #   %m = new master node id
>>> >                                    #   %H = hostname of the new master
>>> node
>>> >                                    #   %M = old master node id
>>> >                                    #   %P = old primary node id
>>> >                                    #   %r = new master port number
>>> >                                    #   %R = new master database cluster
>>> path
>>> >                                    #   %% = '%' character
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # PARALLEL MODE
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > parallel_mode = off
>>> >                                    # Activates parallel query mode
>>> >                                    # (change requires restart)
>>> > pgpool2_hostname = ''
>>> >                                    # Set pgpool2 hostname
>>> >                                    # (change requires restart)
>>> >
>>> > # - System DB info -
>>> >
>>> > system_db_hostname  = 'localhost'
>>> >                                    # (change requires restart)
>>> > system_db_port = 5432
>>> >                                    # (change requires restart)
>>> > system_db_dbname = 'pgpool'
>>> >                                    # (change requires restart)
>>> > system_db_schema = 'pgpool_catalog'
>>> >                                    # (change requires restart)
>>> > system_db_user = 'pgpool'
>>> >                                    # (change requires restart)
>>> > system_db_password = ''
>>> >                                    # (change requires restart)
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # HEALTH CHECK
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > health_check_period = 10
>>> >                                    # Health check period
>>> >                                    # Disabled (0) by default
>>> > health_check_timeout = 10
>>> >                                    # Health check timeout
>>> >                                    # 0 means no timeout
>>> > health_check_user = 'webserver'
>>> >                                    # Health check user
>>> > health_check_password = ''
>>> >                                    # Password for health check user
>>> > health_check_max_retries = 30
>>> >                                    # Maximum number of times to retry a
>>> > failed health check before giving up.
>>> > health_check_retry_delay = 1
>>> >                                    # Amount of time to wait (in seconds)
>>> > between retries.
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # FAILOVER AND FAILBACK
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > failover_command = ''
>>> >                                    # Executes this command at failover
>>> >                                    # Special values:
>>> >                                    #   %d = node id
>>> >                                    #   %h = host name
>>> >                                    #   %p = port number
>>> >                                    #   %D = database cluster path
>>> >                                    #   %m = new master node id
>>> >                                    #   %H = hostname of the new master
>>> node
>>> >                                    #   %M = old master node id
>>> >                                    #   %P = old primary node id
>>> >                                    #   %r = new master port number
>>> >                                    #   %R = new master database cluster
>>> path
>>> >                                    #   %% = '%' character
>>> > failback_command = ''
>>> >                                    # Executes this command at failback.
>>> >                                    # Special values:
>>> >                                    #   %d = node id
>>> >                                    #   %h = host name
>>> >                                    #   %p = port number
>>> >                                    #   %D = database cluster path
>>> >                                    #   %m = new master node id
>>> >                                    #   %H = hostname of the new master
>>> node
>>> >                                    #   %M = old master node id
>>> >                                    #   %P = old primary node id
>>> >                                    #   %r = new master port number
>>> >                                    #   %R = new master database cluster
>>> path
>>> >                                    #   %% = '%' character
>>> >
>>> >
>>> >
>>> >
>>> > fail_over_on_backend_error = on
>>> >                                    # Initiates failover when
>>> > reading/writing to the
>>> >                                    # backend communication socket fails
>>> >                                    # If set to off, pgpool will report an
>>> >                                    # error and disconnect the session.
>>> >
>>> > search_primary_node_timeout = 0
>>> >                                    # Timeout in seconds to search for the
>>> >                                    # primary node when a failover occurs.
>>> >                                    # 0 means no timeout, keep searching
>>> >                                    # for a primary node forever.
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # ONLINE RECOVERY
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > recovery_user = 'webserver'
>>> >                                    # Online recovery user
>>> > recovery_password = ''
>>> >                                    # Online recovery password
>>> > recovery_1st_stage_command = ''
>>> >                                    # Executes a command in first stage
>>> > recovery_2nd_stage_command = ''
>>> >                                    # Executes a command in second stage
>>> > recovery_timeout = 90
>>> >                                    # Timeout in seconds to wait for the
>>> >                                    # recovering node's postmaster to
>>> start
>>> > up
>>> >                                    # 0 means no wait
>>> > client_idle_limit_in_recovery = 0
>>> >                                    # Client is disconnected after being
>>> idle
>>> >                                    # for that many seconds in the second
>>> > stage
>>> >                                    # of online recovery
>>> >                                    # 0 means no disconnection
>>> >                                    # -1 means immediate disconnection
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # WATCHDOG
>>> >
>>> #------------------------------------------------------------------------------
>>> >
>>> > # - Enabling -
>>> >
>>> > use_watchdog = off
>>> >                                     # Activates watchdog
>>> >                                     # (change requires restart)
>>> >
>>> > # -Connection to up stream servers -
>>> >
>>> > trusted_servers = ''
>>> >                                     # trusted server list which are used
>>> >                                     # to confirm network connection
>>> >                                     # (hostA,hostB,hostC,...)
>>> >                                     # (change requires restart)
>>> > ping_path = '/bin'
>>> >                                     # ping command path
>>> >                                     # (change requires restart)
>>> >
>>> > # - Watchdog communication Settings -
>>> >
>>> > wd_hostname = ''
>>> >                                     # Host name or IP address of this
>>> > watchdog
>>> >                                     # (change requires restart)
>>> > wd_port = 9000
>>> >                                     # port number for watchdog service
>>> >                                     # (change requires restart)
>>> > wd_authkey = ''
>>> >                                     # Authentication key for watchdog
>>> > communication
>>> >                                     # (change requires restart)
>>> >
>>> > # - Virtual IP control Setting -
>>> >
>>> > delegate_IP = ''
>>> >                                     # delegate IP address
>>> >                                     # If this is empty, virtual IP never
>>> > bring up.
>>> >                                     # (change requires restart)
>>> > ifconfig_path = '/sbin'
>>> >                                     # ifconfig command path
>>> >                                     # (change requires restart)
>>> > if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
>>> >                                     # startup delegate IP command
>>> >                                     # (change requires restart)
>>> > if_down_cmd = 'ifconfig eth0:0 down'
>>> >                                     # shutdown delegate IP command
>>> >                                     # (change requires restart)
>>> >
>>> > arping_path = '/usr/sbin'           # arping command path
>>> >                                     # (change requires restart)
>>> >
>>> > arping_cmd = 'arping -U $_IP_$ -w 1'
>>> >                                     # arping command
>>> >                                     # (change requires restart)
>>> >
>>> > # - Behaivor on escalation Setting -
>>> >
>>> > clear_memqcache_on_escalation = on
>>> >                                     # Clear all the query cache on shared
>>> > memory
>>> >                                     # when standby pgpool escalate to
>>> > active pgpool
>>> >                                     # (= virtual IP holder).
>>> >                                     # This should be off if client
>>> connects
>>> > to pgpool
>>> >                                     # not using virtual IP.
>>> >                                     # (change requires restart)
>>> > wd_escalation_command = ''
>>> >                                     # Executes this command at escalation
>>> > on new active pgpool.
>>> >                                     # (change requires restart)
>>> >
>>> > # - Lifecheck Setting -
>>> >
>>> > # -- common --
>>> >
>>> > wd_lifecheck_method = 'heartbeat'
>>> >                                     # Method of watchdog lifecheck
>>> > ('heartbeat' or 'query')
>>> >                                     # (change requires restart)
>>> > wd_interval = 10
>>> >                                     # lifecheck interval (sec) > 0
>>> >                                     # (change requires restart)
>>> >
>>> > # -- heartbeat mode --
>>> >
>>> > wd_heartbeat_port = 9694
>>> >                                     # Port number for receiving heartbeat
>>> > signal
>>> >                                     # (change requires restart)
>>> > wd_heartbeat_keepalive = 2
>>> >                                     # Interval time of sending heartbeat
>>> > signal (sec)
>>> >                                     # (change requires restart)
>>> > wd_heartbeat_deadtime = 30
>>> >                                     # Deadtime interval for heartbeat
>>> > signal (sec)
>>> >                                     # (change requires restart)
>>> > heartbeat_destination0 = 'host0_ip1'
>>> >                                     # Host name or IP address of
>>> > destination 0
>>> >                                     # for sending heartbeat signal.
>>> >                                     # (change requires restart)
>>> > heartbeat_destination_port0 = 9694
>>> >                                     # Port number of destination 0 for
>>> > sending
>>> >                                     # heartbeat signal. Usually this is
>>> the
>>> >                                     # same as wd_heartbeat_port.
>>> >                                     # (change requires restart)
>>> > heartbeat_device0 = ''
>>> >                                     # Name of NIC device (such like
>>> 'eth0')
>>> >                                     # used for sending/receiving
>>> heartbeat
>>> >                                     # signal to/from destination 0.
>>> >                                     # This works only when this is not
>>> empty
>>> >                                     # and pgpool has root privilege.
>>> >                                     # (change requires restart)
>>> >
>>> > #heartbeat_destination1 = 'host0_ip2'
>>> > #heartbeat_destination_port1 = 9694
>>> > #heartbeat_device1 = ''
>>> >
>>> > # -- query mode --
>>> >
>>> > wd_life_point = 3
>>> >                                     # lifecheck retry times
>>> >                                     # (change requires restart)
>>> > wd_lifecheck_query = 'SELECT 1'
>>> >                                     # lifecheck query to pgpool from
>>> > watchdog
>>> >                                     # (change requires restart)
>>> > wd_lifecheck_dbname = 'template1'
>>> >                                     # Database name connected for
>>> lifecheck
>>> >                                     # (change requires restart)
>>> > wd_lifecheck_user = 'nobody'
>>> >                                     # watchdog user monitoring pgpools in
>>> > lifecheck
>>> >                                     # (change requires restart)
>>> > wd_lifecheck_password = ''
>>> >                                     # Password for watchdog user in
>>> > lifecheck
>>> >                                     # (change requires restart)
>>> >
>>> > # - Other pgpool Connection Settings -
>>> >
>>> > #other_pgpool_hostname0 = 'host0'
>>> >                                     # Host name or IP address to connect
>>> to
>>> > for other pgpool 0
>>> >                                     # (change requires restart)
>>> > #other_pgpool_port0 = 5432
>>> >                                     # Port number for othet pgpool 0
>>> >                                     # (change requires restart)
>>> > #other_wd_port0 = 9000
>>> >                                     # Port number for othet watchdog 0
>>> >                                     # (change requires restart)
>>> > #other_pgpool_hostname1 = 'host1'
>>> > #other_pgpool_port1 = 5432
>>> > #other_wd_port1 = 9000
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # OTHERS
>>> >
>>> #------------------------------------------------------------------------------
>>> > relcache_expire = 0
>>> >                                    # Life time of relation cache in
>>> seconds.
>>> >                                    # 0 means no cache expiration(the
>>> > default).
>>> >                                    # The relation cache is used for cache
>>> > the
>>> >                                    # query result against PostgreSQL
>>> system
>>> >                                    # catalog to obtain various
>>> information
>>> >                                    # including table structures or if
>>> it's a
>>> >                                    # temporary table or not. The cache is
>>> >                                    # maintained in a pgpool child local
>>> > memory
>>> >                                    # and being kept as long as it
>>> survives.
>>> >                                    # If someone modify the table by using
>>> >                                    # ALTER TABLE or some such, the
>>> relcache
>>> > is
>>> >                                    # not consistent anymore.
>>> >                                    # For this purpose, cache_expiration
>>> >                                    # controls the life time of the cache.
>>> >
>>> > relcache_size = 256
>>> >                                    # Number of relation cache
>>> >                                    # entry. If you see frequently:
>>> >                                    # "pool_search_relcache: cache
>>> > replacement happend"
>>> >                                    # in the pgpool log, you might want to
>>> > increate this number.
>>> >
>>> > check_temp_table = on
>>> >                                    # If on, enable temporary table check
>>> in
>>> > SELECT statements.
>>> >                                    # This initiates queries against
>>> system
>>> > catalog of primary/master
>>> >                                    # thus increases load of master.
>>> >                                    # If you are absolutely sure that your
>>> > system never uses temporary tables
>>> >                                    # and you want to save access to
>>> > primary/master, you could turn this off.
>>> >                                    # Default is on.
>>> >
>>> >
>>> >
>>> #------------------------------------------------------------------------------
>>> > # ON MEMORY QUERY MEMORY CACHE
>>> >
>>> #------------------------------------------------------------------------------
>>> > memory_cache_enabled = off
>>> >                                    # If on, use the memory cache
>>> > functionality, off by default
>>> > memqcache_method = 'shmem'
>>> >                                    # Cache storage method. either
>>> > 'shmem'(shared memory) or
>>> >                                    # 'memcached'. 'shmem' by default
>>> >                                    # (change requires restart)
>>> > memqcache_memcached_host = 'localhost'
>>> >                                    # Memcached host name or IP address.
>>> > Mandatory if
>>> >                                    # memqcache_method = 'memcached'.
>>> >                                    # Defaults to localhost.
>>> >                                    # (change requires restart)
>>> > memqcache_memcached_port = 11211
>>> >                                    # Memcached port number. Mondatory if
>>> > memqcache_method = 'memcached'.
>>> >                                    # Defaults to 11211.
>>> >                                    # (change requires restart)
>>> > memqcache_total_size = 67108864
>>> >                                    # Total memory size in bytes for
>>> storing
>>> > memory cache.
>>> >                                    # Mandatory if memqcache_method =
>>> > 'shmem'.
>>> >                                    # Defaults to 64MB.
>>> >                                    # (change requires restart)
>>> > memqcache_max_num_cache = 1000000
>>> >                                    # Total number of cache entries.
>>> > Mandatory
>>> >                                    # if memqcache_method = 'shmem'.
>>> >                                    # Each cache entry consumes 48 bytes
>>> on
>>> > shared memory.
>>> >                                    # Defaults to 1,000,000(45.8MB).
>>> >                                    # (change requires restart)
>>> > memqcache_expire = 0
>>> >                                    # Memory cache entry life time
>>> specified
>>> > in seconds.
>>> >                                    # 0 means infinite life time. 0 by
>>> > default.
>>> >                                    # (change requires restart)
>>> > memqcache_auto_cache_invalidation = on
>>> >                                    # If on, invalidation of query cache
>>> is
>>> > triggered by corresponding
>>> >                                    # DDL/DML/DCL(and memqcache_expire).
>>> If
>>> > off, it is only triggered
>>> >                                    # by memqcache_expire.  on by default.
>>> >                                    # (change requires restart)
>>> > memqcache_maxcache = 409600
>>> >                                    # Maximum SELECT result size in bytes.
>>> >                                    # Must be smaller than
>>> > memqcache_cache_block_size. Defaults to 400KB.
>>> >                                    # (change requires restart)
>>> > memqcache_cache_block_size = 1048576
>>> >                                    # Cache block size in bytes. Mandatory
>>> > if memqcache_method = 'shmem'.
>>> >                                    # Defaults to 1MB.
>>> >                                    # (change requires restart)
>>> > memqcache_oiddir = '/var/log/pgpool/oiddir'
>>> >                                       # Temporary work directory to
>>> record
>>> > table oids
>>> >                                    # (change requires restart)
>>> > white_memqcache_table_list = ''
>>> >                                    # Comma separated list of table names
>>> to
>>> > memcache
>>> >                                    # that don't write to database
>>> >                                    # Regexp are accepted
>>> > black_memqcache_table_list = ''
>>> >                                    # Comma separated list of table names
>>> > not to memcache
>>> >                                    # that don't write to database
>>> >                                    # Regexp are accepted
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > --
>>> > [image: FBS Logo]
>>> >
>>> > *Travis Kirstine*, Web Development Supervisor
>>> > *t *905‑477‑3600* x *301 | *m *647‑534‑4798 |
>>> > tkirstine at firstbasesolution.com
>>> >
>>> > *First Base Solutions Inc.*
>>> > 140 Renfrew Drive, Suite 100 | Markham | Ontario | L3R 6B3
>>> > *t* 905‑477‑3600 | *f* 905‑477‑0892 | www.firstbasesolutions.com
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list