[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: [ADMIN] Pgpool failing to connect to standby postgres server
From: HEMPLEMAN Matthew <matthew.hempleman () alstom ! com>
Date: 2016-03-08 0:03:36
Message-ID: 6a7655c079424b0ea527ecf851c6b95a () DB4PR48MB0124 ! 041d ! mgd ! msft ! net
[Download RAW message or body]
Hi All,
I'm working on a postgresql HA setup and running into an issue with pgpool. I'm \
using built in postgres streaming replication and pgpool to monitor the cluster and \
initiate the failover process. Pgpool has no trouble connecting to the master \
server; however, it will not connect to the standby. This is the pgpool log on boot \
when it finds the master first:
2016-03-07 16:40:31: pid 13221: LOG: reading status file: 1 th backend is set to \
down status 2016-03-07 16:40:31: pid 13221: LOG: Setting up socket for 0.0.0.0:19999
2016-03-07 16:40:31: pid 13221: LOG: Setting up socket for :::19999
2016-03-07 16:40:31: pid 13221: LOG: pgpool-II successfully started. version 3.4.3 \
(tataraboshi) 2016-03-07 16:40:31: pid 13221: LOG: find_primary_node: checking \
backend no 0
2016-03-07 16:40:31: pid 13221: LOG: find_primary_node: primary node id is 0
And after shutting down the master:
2016-03-07 16:44:08: pid 13334: LOG: failed to connect to PostgreSQL server on \
"192.168.2.114:5432", getsockopt() detected error "Operation now in progress" \
2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connection \
2016-03-07 16:44:08: pid 13334: DETAIL: connection to host:"192.168.2.114:5432" \
failed 2016-03-07 16:44:08: pid 13334: LOG: failed to connect to PostgreSQL server \
on "192.168.2.114:5432", getsockopt() detected error "Operation now in progress" \
2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connection \
2016-03-07 16:44:08: pid 13334: DETAIL: connection to host:"192.168.2.114:5432" \
failed 2016-03-07 16:44:08: pid 13334: LOG: setting backend node 0 status to NODE \
DOWN 2016-03-07 16:44:08: pid 13334: LOG: starting degeneration. shutdown host \
192.168.2.114(5432) 2016-03-07 16:44:08: pid 13334: LOG: failover: no valid backends \
node found 2016-03-07 16:44:08: pid 13334: LOG: Restart all children
2016-03-07 16:44:08: pid 13334: LOG: execute command: /var/lib/pgsql/9.4/failover.sh \
0 0 "" "" 2016-03-07 16:44:08: pid 13335: LOG: child process received shutdown \
request signal 3 2016-03-07 16:44:08: pid 13336: LOG: child process received \
shutdown request signal 3 2016-03-07 16:44:08: pid 13337: LOG: child process \
received shutdown request signal 3 + FALLING_NODE=0
+ OLDPRIMARY_NODE=0
+ NEW_PRIMARY=
+ PGDATA=
+ '[' 0 = 0 ']'
+ '[' 0 -eq 0 ']'
+ su postgres -c 'ssh -T postgres@ touch /var/lib/pgsql/9.4/data/failover.trigger'
And then it hangs on the script due to the missing parameters. This happens no \
matter which server is the master. Also, sometimes on boot pgpool seems to look for \
the standby first and this is the log I get:
2016-03-07 16:43:56: pid 13334: LOG: reading status file: 1 th backend is set to \
down status 2016-03-07 16:43:56: pid 13334: LOG: Setting up socket for 0.0.0.0:19999
2016-03-07 16:43:56: pid 13334: LOG: Setting up socket for :::19999
2016-03-07 16:43:56: pid 13334: LOG: pgpool-II successfully started. version 3.4.3 \
(tataraboshi) 2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: checking \
backend no 0
2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: primary node id is 0
2016-03-07 16:44:08: pid 13334: LOG: failed to connect to PostgreSQL server on \
"192.168.2.114:5432", getsockopt() detected error "Operation now in progress" \
2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connection \
2016-03-07 16:44:08: pid 13334: DETAIL: connection to host:"192.168.2.114:5432" \
failed 2016-03-07 16:44:08: pid 13334: LOG: failed to connect to PostgreSQL server \
on "192.168.2.114:5432", getsockopt() detected error "Operation now in progress" \
2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connection \
2016-03-07 16:44:08: pid 13334: DETAIL: connection to host:"192.168.2.114:5432" \
failed 2016-03-07 16:44:08: pid 13334: LOG: setting backend node 0 status to NODE \
DOWN 2016-03-07 16:44:08: pid 13334: LOG: starting degeneration. shutdown host \
192.168.2.114(5432) 2016-03-07 16:44:08: pid 13334: LOG: failover: no valid backends \
node found 2016-03-07 16:44:08: pid 13334: LOG: Restart all children
2016-03-07 16:44:08: pid 13334: LOG: execute command: /var/lib/pgsql/9.4/failover.sh \
0 0 "" "" 2016-03-07 16:44:08: pid 13335: LOG: child process received shutdown \
request signal 3 2016-03-07 16:44:08: pid 13336: LOG: child process received \
shutdown request signal 3 2016-03-07 16:44:08: pid 13337: LOG: child process \
received shutdown request signal 3 + FALLING_NODE=0
+ OLDPRIMARY_NODE=0
+ NEW_PRIMARY=
+ PGDATA=
+ '[' 0 = 0 ']'
+ '[' 0 -eq 0 ']'
+ su postgres -c 'ssh -T postgres@ touch /var/lib/pgsql/9.4/data/failover.trigger'
^C
Session terminated, killing shell... ...killed.
+ exit 0
2016-03-07 16:44:34: pid 13334: LOG: find_primary_node_repeatedly: waiting for \
finding a primary node 2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: \
checking backend no 0
2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: checking backend no 1
2016-03-07 16:44:34: pid 13334: LOG: received fast shutdown request
2016-03-07 16:44:34: pid 13334: LOG: shutdown request. closing listen socket
[root@URTTDFDB01 ~]# pgpool -n
2016-03-07 16:47:19: pid 13490: LOG: reading status file: 0 th backend is set to \
down status 2016-03-07 16:47:19: pid 13490: LOG: reading status file: 1 th backend \
is set to down status 2016-03-07 16:47:19: pid 13490: LOG: Setting up socket for \
0.0.0.0:19999 2016-03-07 16:47:19: pid 13490: LOG: Setting up socket for :::19999
2016-03-07 16:47:19: pid 13490: LOG: pgpool-II successfully started. version 3.4.3 \
(tataraboshi) 2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: checking \
backend no 0
2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: primary node id is 0
2016-03-07 16:43:56: pid 13334: LOG: reading status file: 1 th backend is set to \
down status 2016-03-07 16:43:56: pid 13334: LOG: Setting up socket for 0.0.0.0:19999
2016-03-07 16:43:56: pid 13334: LOG: Setting up socket for :::19999
2016-03-07 16:43:56: pid 13334: LOG: pgpool-II successfully started. version 3.4.3 \
(tataraboshi) 2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: checking \
backend no 0
2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: primary node id is 0
2016-03-07 16:44:08: pid 13334: LOG: failed to connect to PostgreSQL server on \
"192.168.2.114:5432", getsockopt() detected error "Operation now in progress" \
2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connection \
2016-03-07 16:44:08: pid 13334: DETAIL: connection to host:"192.168.2.114:5432" \
failed 2016-03-07 16:44:08: pid 13334: LOG: failed to connect to PostgreSQL server \
on "192.168.2.114:5432", getsockopt() detected error "Operation now in progress" \
2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connection \
2016-03-07 16:44:08: pid 13334: DETAIL: connection to host:"192.168.2.114:5432" \
failed 2016-03-07 16:44:08: pid 13334: LOG: setting backend node 0 status to NODE \
DOWN 2016-03-07 16:44:08: pid 13334: LOG: starting degeneration. shutdown host \
192.168.2.114(5432) 2016-03-07 16:44:08: pid 13334: LOG: failover: no valid backends \
node found 2016-03-07 16:44:08: pid 13334: LOG: Restart all children
2016-03-07 16:44:08: pid 13334: LOG: execute command: /var/lib/pgsql/9.4/failover.sh \
0 0 "" "" + FALLING_NODE=0
+ OLDPRIMARY_NODE=0
+ NEW_PRIMARY=
+ PGDATA=
+ '[' 0 = 0 ']'
+ '[' 0 -eq 0 ']'
+ su postgres -c 'ssh -T postgres@ touch /var/lib/pgsql/9.4/data/failover.trigger'
^C
Session terminated, killing shell... ...killed.
+ exit 0
2016-03-07 16:44:34: pid 13334: LOG: find_primary_node_repeatedly: waiting for \
finding a primary node 2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: \
checking backend no 0
2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: checking backend no 1
2016-03-07 16:44:34: pid 13334: LOG: received fast shutdown request
2016-03-07 16:44:34: pid 13334: LOG: shutdown request. closing listen socket
[root@URTTDFDB01 ~]# pgpool -n
2016-03-07 16:47:19: pid 13490: LOG: reading status file: 0 th backend is set to \
down status 2016-03-07 16:47:19: pid 13490: LOG: reading status file: 1 th backend \
is set to down status 2016-03-07 16:47:19: pid 13490: LOG: Setting up socket for \
0.0.0.0:19999 2016-03-07 16:47:19: pid 13490: LOG: Setting up socket for :::19999
2016-03-07 16:47:19: pid 13490: LOG: pgpool-II successfully started. version 3.4.3 \
(tataraboshi) 2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: checking \
backend no 0
2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: primary node id is 0
2016-03-07 16:47:19: pid 13490: ERROR: failed to authenticate
2016-03-07 16:47:19: pid 13490: DETAIL: invalid authentication message response \
type, Expecting 'R' and received 'E' 2016-03-07 16:47:19: pid 13490: ERROR: failed \
to authenticate 2016-03-07 16:47:19: pid 13490: DETAIL: invalid authentication \
message response type, Expecting 'R' and received 'E' 2016-03-07 16:47:19: pid 13490: \
LOG: setting backend node 1 status to NODE DOWN 2016-03-07 16:47:19: pid 13490: LOG: \
starting degeneration. shutdown host 192.168.2.104(5432) 2016-03-07 16:47:19: pid \
13490: LOG: Restart all children 2016-03-07 16:47:19: pid 13490: LOG: execute \
command: /var/lib/pgsql/9.4/failover.sh 1 0 192.168.2.114 /var/lib/pgsql/9.4/data/ \
2016-03-07 16:47:19: pid 13491: LOG: child process received shutdown request signal \
3 2016-03-07 16:47:19: pid 13492: LOG: child process received shutdown request \
signal 3 2016-03-07 16:47:19: pid 13493: LOG: child process received shutdown \
request signal 3 + FALLING_NODE=1
+ OLDPRIMARY_NODE=0
+ NEW_PRIMARY=192.168.2.114
+ PGDATA=/var/lib/pgsql/9.4/data/
+ '[' 1 = 0 ']'
+ exit 0
2016-03-07 16:47:19: pid 13490: LOG: failover: set new primary node: 0
2016-03-07 16:47:19: pid 13490: LOG: failover: set new master node: 0
failover done. shutdown host 192.168.2.104(5432)2016-03-07 16:47:19: pid 13490: LOG: \
failover done. shutdown host 192.168.2.104(5432) 2016-03-07 16:47:19: pid 13495: LOG: \
worker process received restart request 2016-03-07 16:47:20: pid 13494: LOG: restart \
request received in pcp child process 2016-03-07 16:47:20: pid 13490: LOG: PCP child \
13494 exits with status 256 in failover() 2016-03-07 16:47:20: pid 13490: LOG: fork \
a new PCP child pid 13502 in failover() 2016-03-07 16:47:20: pid 13490: LOG: child \
process with pid: 13491 exits with status 0 2016-03-07 16:47:20: pid 13490: LOG: \
child process with pid: 13491 exited with success and will not be restarted \
2016-03-07 16:47:20: pid 13490: LOG: child process with pid: 13492 exits with status \
0 2016-03-07 16:47:20: pid 13490: LOG: child process with pid: 13492 exited with \
success and will not be restarted 2016-03-07 16:47:20: pid 13490: LOG: child process \
with pid: 13493 exits with status 0 2016-03-07 16:47:20: pid 13490: LOG: child \
process with pid: 13493 exited with success and will not be restarted 2016-03-07 \
16:47:20: pid 13490: LOG: worker child process with pid: 13495 exits with status 256 \
2016-03-07 16:47:20: pid 13490: LOG: fork a new worker child process with pid: 13503 \
2016-03-07 16:47:19: pid 13490: LOG: setting backend node 1 status to NODE DOWN \
2016-03-07 16:47:19: pid 13490: LOG: starting degeneration. shutdown host \
192.168.2.104(5432) 2016-03-07 16:47:19: pid 13490: LOG: Restart all children
2016-03-07 16:47:19: pid 13490: LOG: execute command: /var/lib/pgsql/9.4/failover.sh \
1 0 192.168.2.114 /var/lib/pgsql/9.4/data/ 2016-03-07 16:47:19: pid 13491: LOG: \
child process received shutdown request signal 3 2016-03-07 16:47:19: pid 13492: LOG: \
child process received shutdown request signal 3 2016-03-07 16:47:19: pid 13493: LOG: \
child process received shutdown request signal 3 + FALLING_NODE=1
+ OLDPRIMARY_NODE=0
+ NEW_PRIMARY=192.168.2.114
+ PGDATA=/var/lib/pgsql/9.4/data/
+ '[' 1 = 0 ']'
+ exit 0
2016-03-07 16:47:19: pid 13490: LOG: failover: set new primary node: 0
2016-03-07 16:47:19: pid 13490: LOG: failover: set new master node: 0
failover done. shutdown host 192.168.2.104(5432)2016-03-07 16:47:19: pid 13490: LOG: \
failover done. shutdown host 192.168.2.104(5432)
So there is some kind of authentication issue but only for the standby server (and \
it's not dependant on the physical server.. if I switch master and standby it still \
happens on new standby)? Any ideas on what might be going on here? Thanks.
Pgsql: 9.45
Pgpool: 3.4.3
Here are the relevant parts of my pgpool.conf config:
# - pgpool Connection Settings -
listen_addresses = '*'
# Host name or IP address to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
port = 19999
# Port number
# (change requires restart)
socket_dir = '/tmp'
# Unix domain socket path
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
# - Backend Connection Settings -
backend_hostname0 = '192.168.2.114'
# 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.4/data/'
# Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = '192.168.2.104'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.4/data/'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = off
# Use pool_hba.conf for client authentication
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.
#------------------------------------------------------------------------------
# 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 = 'currval,lastval,nextval,setval'
# Comma separated list of function names
# that write to database
# Regexp are accepted
database_redirect_preference_list = ''
\
# comma separated list of pairs of database and node id. # example: \
postgres:primary,mydb[0-4]:1,mydb[5-9]:2' # valid for streaming replicaton mode \
only.
app_name_redirect_preference_list = ''
\
# comma separated list of pairs of app name and node id. # example: \
'psql:primary,myapp[0-4]:1,myapp[5-9]:standby' # valid for streaming replicaton mode \
only. allow_sql_comments = off
\
# if on, ignore SQL comments when judging if load balance or
\
# query cache is possible. # If off, SQL comments effectively prevent the judgment
\
# (pre 3.4 behavior).
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
# Activate master/slave mode
# (change requires restart)
master_slave_sub_mode = 'stream'
# 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 = 'postgres'
# Streaming replication check user
# This is neccessary even if you disable streaming
# replication delay check by sr_check_period = 0
sr_check_password = ''
# Password for streaming replication check user
delay_threshold = 10000000
# 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
#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------
health_check_period = 2
# Health check period
# Disabled (0) by default
health_check_timeout = 10
# Health check timeout
# 0 means no timeout
health_check_user = 'postgres'
# Health check user
health_check_password = 'P0stgres'
# Password for health check user
health_check_max_retries = 0
# 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. connect_timeout = 10000
# Timeout value in milliseconds before giving up \
to connect to backend. # Default is 10000 ms (10 second). Flaky network user may \
want to increase
\
# the value. 0 means no timeout. # Note that this value is not only used for health \
check, # but also for ordinary conection to backend.
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/var/lib/pgsql/9.4/failover.sh %d %P %H %R'
# 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 = 10
# Timeout in seconds to search for the
# primary node when a failover occurs.
# 0 means no timeout, keep searching
# for a primary node forever.
#------------------------------------------------------------------------------
# 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 eth1:0 inet $_IP_$ netmask 255.255.255.0'
# startup delegate IP command
# (change requires restart)
if_down_cmd = 'ifconfig eth1:0 down'
# shutdown delegate IP command
# (change requires restart)
arping_path = '/usr/sbin' # arping command path
# (change requires restart)
arping_cmd = 'arping -U -I eth1 $_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
________________________________
CONFIDENTIALITY : This e-mail and any attachments are confidential and may be \
privileged. If you are not a named recipient, please notify the sender immediately \
and do not disclose the contents to another person, use it for any purpose or store \
or copy the information in any medium.
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’m working on a postgresql HA setup and running into an \
issue with pgpool. I’m using built in postgres streaming replication and \
pgpool to monitor the cluster and initiate the failover process. Pgpool has no \
trouble connecting to the master server; however, it will not connect to the \
standby. This is the pgpool log on boot when it finds the master \
first:<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:40:31: pid 13221: LOG: reading status file: \
1 th backend is set to down status<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:40:31: pid 13221: LOG: Setting up socket for 0.0.0.0:19999<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:40:31: pid 13221: LOG: Setting up socket for \
:::19999<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:40:31: pid 13221: \
LOG: pgpool-II successfully started. version 3.4.3 (tataraboshi)<o:p></o:p></p> \
<p class="MsoNormal">2016-03-07 16:40:31: pid 13221: LOG: find_primary_node: \
checking backend no 0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:40:31: pid 13221: LOG: find_primary_node: \
primary node id is 0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b>And after shutting down the master:<o:p></o:p></b></p>
<p class="MsoNormal"><b><o:p> </o:p></b></p>
<p class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: failed to connect to \
PostgreSQL server on "192.168.2.114:5432", getsockopt() detected error \
"Operation now in progress"<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:44:08: pid 13334: ERROR: failed to make persistent db \
connection<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
DETAIL: connection to host:"192.168.2.114:5432" failed<o:p></o:p></p> \
<p class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: failed to connect to \
PostgreSQL server on "192.168.2.114:5432", getsockopt() detected error \
"Operation now in progress"<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:44:08: pid 13334: ERROR: failed to make persistent db \
connection<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
DETAIL: connection to host:"192.168.2.114:5432" failed<o:p></o:p></p> \
<p class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: setting backend node \
0 status to NODE DOWN<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: starting degeneration. shutdown host \
192.168.2.114(5432)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: failover: no valid backends node found<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: Restart all \
children<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
LOG: execute command: /var/lib/pgsql/9.4/failover.sh 0 0 "" \
""<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13335: \
LOG: child process received shutdown request signal 3<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13336: LOG: child process received \
shutdown request signal 3<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: \
pid 13337: LOG: child process received shutdown request signal 3<o:p></o:p></p> \
<p class="MsoNormal">+ FALLING_NODE=0<o:p></o:p></p> <p class="MsoNormal">+ \
OLDPRIMARY_NODE=0<o:p></o:p></p> <p class="MsoNormal">+ \
NEW_PRIMARY=<o:p></o:p></p> <p class="MsoNormal">+ PGDATA=<o:p></o:p></p>
<p class="MsoNormal">+ '[' 0 = 0 ']'<o:p></o:p></p>
<p class="MsoNormal">+ '[' 0 -eq 0 ']'<o:p></o:p></p>
<p class="MsoNormal">+ su postgres -c 'ssh -T postgres@ touch \
/var/lib/pgsql/9.4/data/failover.trigger'<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><b>And then it hangs on \
the script due to the missing parameters. This happens no matter which server \
is the master. Also, sometimes on boot pgpool seems to look for the standby \
first and this is the log I get:<o:p></o:p></b></p> <p \
class="MsoNormal"><b><o:p> </o:p></b></p> <p \
class="MsoNormal"><b><o:p> </o:p></b></p> <p class="MsoNormal">2016-03-07 \
16:43:56: pid 13334: LOG: reading status file: 1 th backend is set to down \
status<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:43:56: pid 13334: LOG: \
Setting up socket for 0.0.0.0:19999<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:43:56: pid 13334: LOG: Setting up socket for :::19999<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:43:56: pid 13334: LOG: pgpool-II successfully \
started. version 3.4.3 (tataraboshi)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:43:56: pid 13334: LOG: find_primary_node: checking backend no \
0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: \
primary node id is 0<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: failed to connect to PostgreSQL server on \
"192.168.2.114:5432", getsockopt() detected error "Operation now in \
progress"<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
ERROR: failed to make persistent db connection<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13334: DETAIL: connection to \
host:"192.168.2.114:5432" failed<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: failed to connect to \
PostgreSQL server on "192.168.2.114:5432", getsockopt() detected error \
"Operation now in progress"<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:44:08: pid 13334: ERROR: failed to make persistent db \
connection<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
DETAIL: connection to host:"192.168.2.114:5432" failed<o:p></o:p></p> \
<p class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: setting backend node \
0 status to NODE DOWN<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: starting degeneration. shutdown host \
192.168.2.114(5432)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: failover: no valid backends node found<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: Restart all \
children<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
LOG: execute command: /var/lib/pgsql/9.4/failover.sh 0 0 "" \
""<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13335: \
LOG: child process received shutdown request signal 3<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13336: LOG: child process received \
shutdown request signal 3<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: \
pid 13337: LOG: child process received shutdown request signal 3<o:p></o:p></p> \
<p class="MsoNormal">+ FALLING_NODE=0<o:p></o:p></p> <p class="MsoNormal">+ \
OLDPRIMARY_NODE=0<o:p></o:p></p> <p class="MsoNormal">+ \
NEW_PRIMARY=<o:p></o:p></p> <p class="MsoNormal">+ PGDATA=<o:p></o:p></p>
<p class="MsoNormal">+ '[' 0 = 0 ']'<o:p></o:p></p>
<p class="MsoNormal">+ '[' 0 -eq 0 ']'<o:p></o:p></p>
<p class="MsoNormal">+ su postgres -c 'ssh -T postgres@ touch \
/var/lib/pgsql/9.4/data/failover.trigger'<o:p></o:p></p> <p \
class="MsoNormal">^C<o:p></o:p></p> <p class="MsoNormal">Session terminated, killing \
shell... ...killed.<o:p></o:p></p> <p class="MsoNormal">+ exit 0<o:p></o:p></p>
<p class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: \
find_primary_node_repeatedly: waiting for finding a primary node<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: \
checking backend no 0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: \
checking backend no 1<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: received fast \
shutdown request<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:34: pid 13334: \
LOG: shutdown request. closing listen socket<o:p></o:p></p> <p \
class="MsoNormal">[root@URTTDFDB01 ~]# pgpool -n<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: reading status file: 0 \
th backend is set to down status<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:19: pid 13490: LOG: reading status file: 1 th backend is set to down \
status<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: \
Setting up socket for 0.0.0.0:19999<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:19: pid 13490: LOG: Setting up socket for :::19999<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: pgpool-II successfully \
started. version 3.4.3 (tataraboshi)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:19: pid 13490: LOG: find_primary_node: checking backend no \
0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: \
primary node id is 0<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:43:56: pid \
13334: LOG: reading status file: 1 th backend is set to down \
status<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:43:56: pid 13334: LOG: \
Setting up socket for 0.0.0.0:19999<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:43:56: pid 13334: LOG: Setting up socket for :::19999<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:43:56: pid 13334: LOG: pgpool-II successfully \
started. version 3.4.3 (tataraboshi)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:43:56: pid 13334: LOG: find_primary_node: checking backend no \
0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: \
primary node id is 0<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: failed to connect to PostgreSQL server on \
"192.168.2.114:5432", getsockopt() detected error "Operation now in \
progress"<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
ERROR: failed to make persistent db connection<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13334: DETAIL: connection to \
host:"192.168.2.114:5432" failed<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: failed to connect to \
PostgreSQL server on "192.168.2.114:5432", getsockopt() detected error \
"Operation now in progress"<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:44:08: pid 13334: ERROR: failed to make persistent db \
connection<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
DETAIL: connection to host:"192.168.2.114:5432" failed<o:p></o:p></p> \
<p class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: setting backend node \
0 status to NODE DOWN<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: starting degeneration. shutdown host \
192.168.2.114(5432)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid \
13334: LOG: failover: no valid backends node found<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:08: pid 13334: LOG: Restart all \
children<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:08: pid 13334: \
LOG: execute command: /var/lib/pgsql/9.4/failover.sh 0 0 "" \
""<o:p></o:p></p> <p class="MsoNormal">+ FALLING_NODE=0<o:p></o:p></p>
<p class="MsoNormal">+ OLDPRIMARY_NODE=0<o:p></o:p></p>
<p class="MsoNormal">+ NEW_PRIMARY=<o:p></o:p></p>
<p class="MsoNormal">+ PGDATA=<o:p></o:p></p>
<p class="MsoNormal">+ '[' 0 = 0 ']'<o:p></o:p></p>
<p class="MsoNormal">+ '[' 0 -eq 0 ']'<o:p></o:p></p>
<p class="MsoNormal">+ su postgres -c 'ssh -T postgres@ touch \
/var/lib/pgsql/9.4/data/failover.trigger'<o:p></o:p></p> <p \
class="MsoNormal">^C<o:p></o:p></p> <p class="MsoNormal">Session terminated, killing \
shell... ...killed.<o:p></o:p></p> <p class="MsoNormal">+ exit 0<o:p></o:p></p>
<p class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: \
find_primary_node_repeatedly: waiting for finding a primary node<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: \
checking backend no 0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: \
checking backend no 1<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:44:34: pid 13334: LOG: received fast \
shutdown request<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:44:34: pid 13334: \
LOG: shutdown request. closing listen socket<o:p></o:p></p> <p \
class="MsoNormal">[root@URTTDFDB01 ~]# pgpool -n<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: reading status file: 0 \
th backend is set to down status<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:19: pid 13490: LOG: reading status file: 1 th backend is set to down \
status<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: \
Setting up socket for 0.0.0.0:19999<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:19: pid 13490: LOG: Setting up socket for :::19999<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: pgpool-II successfully \
started. version 3.4.3 (tataraboshi)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:19: pid 13490: LOG: find_primary_node: checking backend no \
0<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: \
primary node id is 0<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid \
13490: ERROR: failed to authenticate<o:p></o:p></p> <p \
class="MsoNormal"><b><span style="color:red">2016-03-07 16:47:19: pid 13490: \
DETAIL: invalid authentication message response type, Expecting 'R' and \
received 'E'<o:p></o:p></span></b></p> <p class="MsoNormal"><b><span \
style="color:red">2016-03-07 16:47:19: pid 13490: ERROR: failed to \
authenticate<o:p></o:p></span></b></p> <p class="MsoNormal"><b><span \
style="color:red">2016-03-07 16:47:19: pid 13490: DETAIL: invalid \
authentication message response type, Expecting 'R' and received \
'E'<o:p></o:p></span></b></p> <p class="MsoNormal">2016-03-07 16:47:19: pid 13490: \
LOG: setting backend node 1 status to NODE DOWN<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: starting degeneration. \
shutdown host 192.168.2.104(5432)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:19: pid 13490: LOG: Restart all children<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: execute command: \
/var/lib/pgsql/9.4/failover.sh 1 0 192.168.2.114 \
/var/lib/pgsql/9.4/data/<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid \
13491: LOG: child process received shutdown request signal 3<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13492: LOG: child process received \
shutdown request signal 3<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: \
pid 13493: LOG: child process received shutdown request signal 3<o:p></o:p></p> \
<p class="MsoNormal">+ FALLING_NODE=1<o:p></o:p></p> <p class="MsoNormal">+ \
OLDPRIMARY_NODE=0<o:p></o:p></p> <p class="MsoNormal">+ \
NEW_PRIMARY=192.168.2.114<o:p></o:p></p> <p class="MsoNormal">+ \
PGDATA=/var/lib/pgsql/9.4/data/<o:p></o:p></p> <p class="MsoNormal">+ '[' 1 = 0 \
']'<o:p></o:p></p> <p class="MsoNormal">+ exit 0<o:p></o:p></p>
<p class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: failover: set new \
primary node: 0<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid 13490: \
LOG: failover: set new master node: 0<o:p></o:p></p> <p \
class="MsoNormal">failover done. shutdown host 192.168.2.104(5432)2016-03-07 \
16:47:19: pid 13490: LOG: failover done. shutdown host \
192.168.2.104(5432)<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid \
13495: LOG: worker process received restart request<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:20: pid 13494: LOG: restart request received \
in pcp child process<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:20: pid \
13490: LOG: PCP child 13494 exits with status 256 in failover()<o:p></o:p></p> \
<p class="MsoNormal">2016-03-07 16:47:20: pid 13490: LOG: fork a new PCP child \
pid 13502 in failover()<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:20: pid \
13490: LOG: child process with pid: 13491 exits with status 0<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:20: pid 13490: LOG: child process with pid: \
13491 exited with success and will not be restarted<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:20: pid 13490: LOG: child process with pid: \
13492 exits with status 0<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:20: \
pid 13490: LOG: child process with pid: 13492 exited with success and will not \
be restarted<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:20: pid 13490: \
LOG: child process with pid: 13493 exits with status 0<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:20: pid 13490: LOG: child process with pid: \
13493 exited with success and will not be restarted<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:20: pid 13490: LOG: worker child process \
with pid: 13495 exits with status 256<o:p></o:p></p> <p class="MsoNormal">2016-03-07 \
16:47:20: pid 13490: LOG: fork a new worker child process with pid: 13503 \
2016-03-07 16:47:19: pid 13490: LOG: setting backend node 1 status to NODE \
DOWN<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: \
starting degeneration. shutdown host 192.168.2.104(5432)<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: Restart all \
children<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid 13490: \
LOG: execute command: /var/lib/pgsql/9.4/failover.sh 1 0 192.168.2.114 \
/var/lib/pgsql/9.4/data/<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid \
13491: LOG: child process received shutdown request signal 3<o:p></o:p></p> <p \
class="MsoNormal">2016-03-07 16:47:19: pid 13492: LOG: child process received \
shutdown request signal 3<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: \
pid 13493: LOG: child process received shutdown request signal 3<o:p></o:p></p> \
<p class="MsoNormal">+ FALLING_NODE=1<o:p></o:p></p> <p class="MsoNormal">+ \
OLDPRIMARY_NODE=0<o:p></o:p></p> <p class="MsoNormal">+ \
NEW_PRIMARY=192.168.2.114<o:p></o:p></p> <p class="MsoNormal">+ \
PGDATA=/var/lib/pgsql/9.4/data/<o:p></o:p></p> <p class="MsoNormal">+ '[' 1 = 0 \
']'<o:p></o:p></p> <p class="MsoNormal">+ exit 0<o:p></o:p></p>
<p class="MsoNormal">2016-03-07 16:47:19: pid 13490: LOG: failover: set new \
primary node: 0<o:p></o:p></p> <p class="MsoNormal">2016-03-07 16:47:19: pid 13490: \
LOG: failover: set new master node: 0<o:p></o:p></p> <p \
class="MsoNormal">failover done. shutdown host 192.168.2.104(5432)2016-03-07 \
16:47:19: pid 13490: LOG: failover done. shutdown host \
192.168.2.104(5432)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">So there is some kind of authentication issue but only for the \
standby server (and it’s not dependant on the physical server.. if I switch \
master and standby it still happens on new standby)? Any ideas on what might be \
going on here? Thanks.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Pgsql: 9.45<o:p></o:p></p>
<p class="MsoNormal">Pgpool: 3.4.3<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Here are the relevant parts of my pgpool.conf \
config:<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - pgpool Connection Settings -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">listen_addresses = '*'<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Host name or IP address to listen on:<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# '*' for all, '' for no TCP/IP connections<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">port = \
19999<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Port number<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">socket_dir = \
'/tmp'<o:p></o:p></p> <p class="MsoNormal"> \
&n \
bsp; # \
Unix domain socket path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# The Debian package defaults to<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# /var/run/postgresql<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Backend Connection Settings -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:red">backend_hostname0 = \
'192.168.2.114'<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:red"> &n \
bsp; \
# Host name or IP address to connect to for backend 0<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:red">backend_port0 = 5432<o:p></o:p></span></p> \
<p class="MsoNormal"><span \
style="color:red"> &n \
bsp; \
# Port number for backend 0<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:red">backend_weight0 = 1<o:p></o:p></span></p> <p \
class="MsoNormal"><span \
style="color:red"> &n \
bsp; \
# Weight for backend 0 (only in load balancing mode)<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:red">backend_data_directory0 = \
'/var/lib/pgsql/9.4/data/'<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:red"> &n \
bsp; \
# Data directory for backend 0<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:red">backend_flag0 = 'ALLOW_TO_FAILOVER'<o:p></o:p></span></p> <p \
class="MsoNormal"><span \
style="color:red"> \
# \
Controls various backend behavior<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:red"> &n \
bsp; \
# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:red">backend_hostname1 = \
'192.168.2.104'<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:red">backend_port1 = 5432<o:p></o:p></span></p> <p \
class="MsoNormal"><span style="color:red">backend_weight1 = 1<o:p></o:p></span></p> \
<p class="MsoNormal"><span style="color:red">backend_data_directory1 = \
'/var/lib/pgsql/9.4/data/'<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:red">backend_flag1 = 'ALLOW_TO_FAILOVER'<o:p></o:p></span></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"># - Authentication \
-<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">enable_pool_hba = off<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Use pool_hba.conf for client authentication<o:p></o:p></p> <p \
class="MsoNormal">pool_passwd = ''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# File name of pool_passwd for md5 authentication.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# "" disables pool_passwd.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p \
class="MsoNormal">authentication_timeout = 60<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Delay in seconds to complete client authentication<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# 0 means no timeout.<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"># LOAD BALANCING MODE<o:p></o:p></p>
<p class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">load_balance_mode = off<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Activate load balancing mode<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p \
class="MsoNormal">ignore_leading_white_space = on<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Ignore leading white spaces of each query<o:p></o:p></p> <p \
class="MsoNormal">white_function_list = ''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Comma separated list of function names<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# that don't write to database<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Regexp are accepted<o:p></o:p></p> <p class="MsoNormal">black_function_list = \
'currval,lastval,nextval,setval'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Comma separated list of function names<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# that write to database<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Regexp are accepted<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">database_redirect_preference_list = ''<o:p></o:p></p>
<p class="MsoNormal">   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; \
# comma separated list of pairs of database and node id.<o:p></o:p></p> \
<p class="MsoNormal">   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; \
# example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# valid for streaming replicaton mode only.<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p \
class="MsoNormal">app_name_redirect_preference_list = ''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# comma separated list of pairs of app name and node id.<o:p></o:p></p> \
<p class="MsoNormal">   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; \
# example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'<o:p></o:p></p> \
<p class="MsoNormal">   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; \
# valid for streaming replicaton mode only.<o:p></o:p></p> <p \
class="MsoNormal">allow_sql_comments = off<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# if on, ignore SQL comments when judging if load balance \
or<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# query cache is possible.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# If off, SQL comments effectively prevent the judgment<o:p></o:p></p> \
<p class="MsoNormal">   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; \
# (pre 3.4 behavior).<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p \
class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"># MASTER/SLAVE MODE<o:p></o:p></p>
<p class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">master_slave_mode = on<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Activate master/slave mode<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">master_slave_sub_mode \
= 'stream'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Master/slave sub mode<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Valid values are combinations slony or<o:p></o:p></p> <p class="MsoNormal"> \
&n \
bsp; # \
stream. Default is slony.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Streaming -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">sr_check_period = 0<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Streaming replication check period<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Disabled (0) by default<o:p></o:p></p> <p class="MsoNormal">sr_check_user = \
'postgres'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Streaming replication check user<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# This is neccessary even if you disable streaming<o:p></o:p></p> <p \
class="MsoNormal"> \
# \
replication delay check by sr_check_period = 0<o:p></o:p></p> <p \
class="MsoNormal">sr_check_password = ''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Password for streaming replication check user<o:p></o:p></p> <p \
class="MsoNormal">delay_threshold = 10000000<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Threshold before not dispatching query to standby node<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Unit is in bytes<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Disabled (0) by default<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Special commands -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">follow_master_command = ''<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Executes this command after master failover<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Special values:<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %d = node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %h = host name<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %p = port number<o:p></o:p></p> <p \
class="MsoNormal"> \
&n \
bsp; # \
%D = database cluster path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %m = new master node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %H = hostname of the new master node<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %M = old master node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %P = old primary node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# %r = new master port number<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# %R = new master database cluster path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %% = '%' character<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p \
class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"># HEALTH CHECK<o:p></o:p></p>
<p class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">health_check_period = 2<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Health check period<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Disabled (0) by default<o:p></o:p></p> <p class="MsoNormal">health_check_timeout = \
10<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Health check timeout<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# 0 means no timeout<o:p></o:p></p> <p class="MsoNormal">health_check_user = \
'postgres'<o:p></o:p></p> <p \
class="MsoNormal"> \
# Health \
check user<o:p></o:p></p> <p class="MsoNormal">health_check_password = \
'P0stgres'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Password for health check user<o:p></o:p></p> <p \
class="MsoNormal">health_check_max_retries = 0<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Maximum number of times to retry a failed health check before giving \
up.<o:p></o:p></p> <p class="MsoNormal">health_check_retry_delay = 1<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Amount of time to wait (in seconds) between retries.<o:p></o:p></p> <p \
class="MsoNormal">connect_timeout = 10000<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Timeout value in milliseconds before giving up to connect to \
backend.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# Default is 10000 ms (10 second). Flaky network user may want to \
increase<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# the value. 0 means no timeout.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# Note that this value is not only used for health check,<o:p></o:p></p> \
<p class="MsoNormal">   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; \
# but also for ordinary conection to backend.<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p \
class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"># FAILOVER AND FAILBACK<o:p></o:p></p>
<p class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">failover_command = '/var/lib/pgsql/9.4/failover.sh %d %P %H \
%R'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Executes this command at failover<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Special values:<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %d = node id<o:p></o:p></p> <p \
class="MsoNormal"> \
# \
%h = host name<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %p = port number<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %D = database cluster path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %m = new master node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %H = hostname of the new master node<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %M = old master node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %P = old primary node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# %r = new master port number<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# %R = new master database cluster path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %% = '%' character<o:p></o:p></p> <p class="MsoNormal">failback_command \
= ''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Executes this command at failback.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Special values:<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %d = node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %h = host name<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %p = port number<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %D = database cluster path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %m = new master node id<o:p></o:p></p> <p \
class="MsoNormal"> \
&n \
bsp; # \
%H = hostname of the new master node<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %M = old master node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %P = old primary node id<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# %r = new master port number<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
# %R = new master database cluster path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# %% = '%' character<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p \
class="MsoNormal">fail_over_on_backend_error = on<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Initiates failover when reading/writing to the<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# backend communication socket fails<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# If set to off, pgpool will report an<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# error and disconnect the session.<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p \
class="MsoNormal">search_primary_node_timeout = 10<o:p></o:p></p> <p \
class="MsoNormal"> \
&n \
bsp; # \
Timeout in seconds to search for the<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# primary node when a failover occurs.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# 0 means no timeout, keep searching<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# for a primary node forever.<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"># WATCHDOG<o:p></o:p></p>
<p class="MsoNormal">#------------------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Enabling -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">use_watchdog = off<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Activates watchdog<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># -Connection to up stream servers -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">trusted_servers = ''<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# trusted server list which are used<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# to confirm network connection<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (hostA,hostB,hostC,...)<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">ping_path = \
'/bin'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# ping command path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Watchdog communication Settings -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">wd_hostname = ''<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Host name or IP address of this watchdog<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_port = \
9000<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# port number for watchdog service<o:p></o:p></p> <p \
class="MsoNormal"> \
# \
(change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_authkey = \
''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Authentication key for watchdog communication<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Virtual IP control Setting -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">delegate_IP = ''<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# delegate IP address<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# If this is empty, virtual IP never bring up. <o:p></o:p></p>
<p class="MsoNormal">   \
; # \
(change requires restart)<o:p></o:p></p> <p class="MsoNormal">ifconfig_path = \
'/sbin'<o:p></o:p></p> <p \
class="MsoNormal"> \
# \
ifconfig command path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">if_up_cmd = 'ifconfig \
eth1:0 inet $_IP_$ netmask 255.255.255.0'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# startup delegate IP command<o:p></o:p></p> <p \
class="MsoNormal"> \
# \
(change requires restart)<o:p></o:p></p> <p class="MsoNormal">if_down_cmd = 'ifconfig \
eth1:0 down'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# shutdown delegate IP command<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">arping_path = \
'/usr/sbin' # arping \
command path<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">arping_cmd = 'arping -U -I eth1 $_IP_$ -w 1'<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# arping command<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"># - Behaivor on \
escalation Setting -<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">clear_memqcache_on_escalation = on<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Clear all the query cache on shared memory<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# when standby pgpool escalate to active pgpool<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (= virtual IP holder).<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# This should be off if client connects to pgpool<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# not using virtual IP.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_escalation_command \
= ''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Executes this command at escalation on new active pgpool.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Lifecheck Setting - <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># -- common --<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">wd_lifecheck_method = 'heartbeat'<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Method of watchdog lifecheck ('heartbeat' or 'query')<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_interval = \
10<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# lifecheck interval (sec) > 0<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># -- heartbeat mode --<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">wd_heartbeat_port = 9694<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Port number for receiving heartbeat signal<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p \
class="MsoNormal">wd_heartbeat_keepalive = 2<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Interval time of sending heartbeat signal (sec)<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_heartbeat_deadtime \
= 30<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Deadtime interval for heartbeat signal (sec)<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p \
class="MsoNormal">heartbeat_destination0 = 'host0_ip1'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Host name or IP address of destination 0<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# for sending heartbeat signal.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p \
class="MsoNormal">heartbeat_destination_port0 = 9694 <o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; # \
Port number of destination 0 for sending<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# heartbeat signal. Usually this is the<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# same as wd_heartbeat_port.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">heartbeat_device0 = \
''<o:p></o:p></p> <p \
class="MsoNormal"> \
# \
Name of NIC device (such like 'eth0')<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# used for sending/receiving heartbeat<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# signal to/from destination 0.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# This works only when this is not empty<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# and pgpool has root privilege.<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">#heartbeat_destination1 = 'host0_ip2'<o:p></o:p></p>
<p class="MsoNormal">#heartbeat_destination_port1 = 9694<o:p></o:p></p>
<p class="MsoNormal">#heartbeat_device1 = ''<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># -- query mode --<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">wd_life_point = 3<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# lifecheck retry times<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_lifecheck_query = \
'SELECT 1'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# lifecheck query to pgpool from watchdog<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_lifecheck_dbname = \
'template1'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Database name connected for lifecheck<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_lifecheck_user = \
'nobody'<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# watchdog user monitoring pgpools in lifecheck<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">wd_lifecheck_password \
= ''<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Password for watchdog user in lifecheck<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># - Other pgpool Connection Settings -<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">#other_pgpool_hostname0 = 'host0'<o:p></o:p></p>
<p class="MsoNormal">   \
; \
# Host name or IP address to connect to for other pgpool 0<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">#other_pgpool_port0 = \
5432<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Port number for othet pgpool 0<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p class="MsoNormal">#other_wd_port0 = \
9000<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# Port number for othet watchdog 0<o:p></o:p></p> <p \
class="MsoNormal"> &n \
bsp; \
# (change requires restart)<o:p></o:p></p> <p \
class="MsoNormal">#other_pgpool_hostname1 = 'host1'<o:p></o:p></p> <p \
class="MsoNormal">#other_pgpool_port1 = 5432<o:p></o:p></p> <p \
class="MsoNormal">#other_wd_port1 = 9000<o:p></o:p></p> <p \
class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<hr>
<font face="Arial" color="Black" size="2">CONFIDENTIALITY : This e-mail and any \
attachments are confidential and may be privileged. If you are not a named recipient, \
please notify the sender immediately and do not disclose the contents to another \
person, use it for any purpose or store or copy the information in any medium.<br>
</font>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic