Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ad5AB-0004Rc-S9 for pgsql-admin@arkaria.postgresql.org; Tue, 08 Mar 2016 00:06:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ad5AB-0007Nn-EJ for pgsql-admin@arkaria.postgresql.org; Tue, 08 Mar 2016 00:06:11 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ad57q-0005CN-Pc for pgsql-admin@postgresql.org; Tue, 08 Mar 2016 00:03:47 +0000 Received: from mcegsmtp3.alstom.com ([159.245.16.223]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ad57k-0008D2-TT for pgsql-admin@postgresql.org; Tue, 08 Mar 2016 00:03:45 +0000 Received: from 041-smtp-out.Alstom.com (unknown [141.251.120.81]) by MCEGSMTP3.alstom.com with smtp (TLS: TLSv1/SSLv3,256bits,ECDHE-RSA-AES256-SHA384) id 1998_83ba_f76a8d51_9380_4469_bcbf_c708966f2914; Tue, 08 Mar 2016 01:03:37 +0100 Received: from DB4PR48MB0124.041d.mgd.msft.net (141.251.120.84) by DB4PR48MB0121.041d.mgd.msft.net (141.251.120.81) with Microsoft SMTP Server (TLS) id 15.1.427.16; Tue, 8 Mar 2016 00:03:36 +0000 Received: from DB4PR48MB0124.041d.mgd.msft.net ([141.251.120.84]) by DB4PR48MB0124.041d.mgd.msft.net ([141.251.120.84]) with mapi id 15.01.0427.019; Tue, 8 Mar 2016 00:03:36 +0000 From: HEMPLEMAN Matthew To: "pgsql-admin@postgresql.org" Subject: Pgpool failing to connect to standby postgres server Thread-Topic: Pgpool failing to connect to standby postgres server Thread-Index: AdF4zfUB1wgibEqHTH28a6LHsY73fw== Date: Tue, 8 Mar 2016 00:03:36 +0000 Message-ID: <6a7655c079424b0ea527ecf851c6b95a@DB4PR48MB0124.041d.mgd.msft.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [141.251.121.68] x-ms-office365-filtering-correlation-id: e69c8ed6-066c-4739-0e2a-08d346e518ed Content-Type: multipart/alternative; boundary="_000_6a7655c079424b0ea527ecf851c6b95aDB4PR48MB0124041dmgdmsf_" MIME-Version: 1.0 X-Pg-Spam-Score: -6.9 (------) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_6a7655c079424b0ea527ecf851c6b95aDB4PR48MB0124041dmgdmsf_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 t= he cluster and initiate the failover process. Pgpool has no trouble connec= ting to the master server; however, it will not connect to the standby. Th= is 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. versi= on 3.4.3 (tataraboshi) 2016-03-07 16:40:31: pid 13221: LOG: find_primary_node: checking backend n= o 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 serve= r on "192.168.2.114:5432", getsockopt() detected error "Operation now in pr= ogress" 2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connec= tion 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 serve= r on "192.168.2.114:5432", getsockopt() detected error "Operation now in pr= ogress" 2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connec= tion 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 foun= d 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/f= ailover.sh 0 0 "" "" 2016-03-07 16:44:08: pid 13335: LOG: child process received shutdown reque= st signal 3 2016-03-07 16:44:08: pid 13336: LOG: child process received shutdown reque= st signal 3 2016-03-07 16:44:08: pid 13337: LOG: child process received shutdown reque= st signal 3 + FALLING_NODE=3D0 + OLDPRIMARY_NODE=3D0 + NEW_PRIMARY=3D + PGDATA=3D + '[' 0 =3D 0 ']' + '[' 0 -eq 0 ']' + su postgres -c 'ssh -T postgres@ touch /var/lib/pgsql/9.4/data/failover.t= rigger' And then it hangs on the script due to the missing parameters. This happen= s no matter which server is the master. Also, sometimes on boot pgpool see= ms 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. versi= on 3.4.3 (tataraboshi) 2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: checking backend n= o 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 serve= r on "192.168.2.114:5432", getsockopt() detected error "Operation now in pr= ogress" 2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connec= tion 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 serve= r on "192.168.2.114:5432", getsockopt() detected error "Operation now in pr= ogress" 2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connec= tion 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 foun= d 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/f= ailover.sh 0 0 "" "" 2016-03-07 16:44:08: pid 13335: LOG: child process received shutdown reque= st signal 3 2016-03-07 16:44:08: pid 13336: LOG: child process received shutdown reque= st signal 3 2016-03-07 16:44:08: pid 13337: LOG: child process received shutdown reque= st signal 3 + FALLING_NODE=3D0 + OLDPRIMARY_NODE=3D0 + NEW_PRIMARY=3D + PGDATA=3D + '[' 0 =3D 0 ']' + '[' 0 -eq 0 ']' + su postgres -c 'ssh -T postgres@ touch /var/lib/pgsql/9.4/data/failover.t= rigger' ^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 n= o 0 2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: checking backend n= o 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 sock= et [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. versi= on 3.4.3 (tataraboshi) 2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: checking backend n= o 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. versi= on 3.4.3 (tataraboshi) 2016-03-07 16:43:56: pid 13334: LOG: find_primary_node: checking backend n= o 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 serve= r on "192.168.2.114:5432", getsockopt() detected error "Operation now in pr= ogress" 2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connec= tion 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 serve= r on "192.168.2.114:5432", getsockopt() detected error "Operation now in pr= ogress" 2016-03-07 16:44:08: pid 13334: ERROR: failed to make persistent db connec= tion 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 foun= d 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/f= ailover.sh 0 0 "" "" + FALLING_NODE=3D0 + OLDPRIMARY_NODE=3D0 + NEW_PRIMARY=3D + PGDATA=3D + '[' 0 =3D 0 ']' + '[' 0 -eq 0 ']' + su postgres -c 'ssh -T postgres@ touch /var/lib/pgsql/9.4/data/failover.t= rigger' ^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 n= o 0 2016-03-07 16:44:34: pid 13334: LOG: find_primary_node: checking backend n= o 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 sock= et [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. versi= on 3.4.3 (tataraboshi) 2016-03-07 16:47:19: pid 13490: LOG: find_primary_node: checking backend n= o 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 res= ponse 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 res= ponse 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/f= ailover.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 reque= st signal 3 2016-03-07 16:47:19: pid 13492: LOG: child process received shutdown reque= st signal 3 2016-03-07 16:47:19: pid 13493: LOG: child process received shutdown reque= st signal 3 + FALLING_NODE=3D1 + OLDPRIMARY_NODE=3D0 + NEW_PRIMARY=3D192.168.2.114 + PGDATA=3D/var/lib/pgsql/9.4/data/ + '[' 1 =3D 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 13= 490: LOG: failover done. shutdown host 192.168.2.104(5432) 2016-03-07 16:47:19: pid 13495: LOG: worker process received restart reque= st 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 fai= lover() 2016-03-07 16:47:20: pid 13490: LOG: child process with pid: 13491 exits w= ith 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 w= ith 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 w= ith 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 sta= tus 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/f= ailover.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 reque= st signal 3 2016-03-07 16:47:19: pid 13492: LOG: child process received shutdown reque= st signal 3 2016-03-07 16:47:19: pid 13493: LOG: child process received shutdown reque= st signal 3 + FALLING_NODE=3D1 + OLDPRIMARY_NODE=3D0 + NEW_PRIMARY=3D192.168.2.114 + PGDATA=3D/var/lib/pgsql/9.4/data/ + '[' 1 =3D 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 13= 490: LOG: failover done. shutdown host 192.168.2.104(5432) So there is some kind of authentication issue but only for the standby serv= er (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 =3D '*' # Host name or IP address to listen on: # '*' for all, '' for no TCP/IP connecti= ons # (change requires restart) port =3D 19999 # Port number # (change requires restart) socket_dir =3D '/tmp' # Unix domain socket path # The Debian package defaults to # /var/run/postgresql # (change requires restart) # - Backend Connection Settings - backend_hostname0 =3D '192.168.2.114' # Host name or IP address to connect to = for backend 0 backend_port0 =3D 5432 # Port number for backend 0 backend_weight0 =3D 1 # Weight for backend 0 (only in load bal= ancing mode) backend_data_directory0 =3D '/var/lib/pgsql/9.4/data/' # Data directory for backend 0 backend_flag0 =3D 'ALLOW_TO_FAILOVER' # Controls various backend behavior # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILO= VER backend_hostname1 =3D '192.168.2.104' backend_port1 =3D 5432 backend_weight1 =3D 1 backend_data_directory1 =3D '/var/lib/pgsql/9.4/data/' backend_flag1 =3D 'ALLOW_TO_FAILOVER' # - Authentication - enable_pool_hba =3D off # Use pool_hba.conf for client authentic= ation pool_passwd =3D '' # File name of pool_passwd for md5 authe= ntication. # "" disables pool_passwd. # (change requires restart) authentication_timeout =3D 60 # Delay in seconds to complete client au= thentication # 0 means no timeout. #--------------------------------------------------------------------------= ---- # LOAD BALANCING MODE #--------------------------------------------------------------------------= ---- load_balance_mode =3D off # Activate load balancing mode # (change requires restart) ignore_leading_white_space =3D on # Ignore leading white spaces of each qu= ery white_function_list =3D '' # Comma separated list of function names # that don't write to database # Regexp are accepted black_function_list =3D 'currval,lastval,nextval,setval' # Comma separated list of function names # that write to database # Regexp are accepted database_redirect_preference_list =3D '' = # comma separated l= ist of pairs of database and node id. = # example: postgres= :primary,mydb[0-4]:1,mydb[5-9]:2' = # valid for streami= ng replicaton mode only. app_name_redirect_preference_list =3D '' = # comma separated l= ist of pairs of app name and node id. = # example: 'psql:pr= imary,myapp[0-4]:1,myapp[5-9]:standby' = # valid for streami= ng replicaton mode only. allow_sql_comments =3D off = # if on, ignore SQL= comments when judging if load balance or = # query cache is po= ssible. = # If off, SQL comme= nts effectively prevent the judgment = # (pre 3.4 behavior= ). #--------------------------------------------------------------------------= ---- # MASTER/SLAVE MODE #--------------------------------------------------------------------------= ---- master_slave_mode =3D on # Activate master/slave mode # (change requires restart) master_slave_sub_mode =3D 'stream' # Master/slave sub mode # Valid values are combinations slony or # stream. Default is slony. # (change requires restart) # - Streaming - sr_check_period =3D 0 # Streaming replication check period # Disabled (0) by default sr_check_user =3D 'postgres' # Streaming replication check user # This is neccessary even if you disable= streaming # replication delay check by sr_check_pe= riod =3D 0 sr_check_password =3D '' # Password for streaming replication che= ck user delay_threshold =3D 10000000 # Threshold before not dispatching query= to standby node # Unit is in bytes # Disabled (0) by default # - Special commands - follow_master_command =3D '' # Executes this command after master fai= lover # Special values: # %d =3D node id # %h =3D host name # %p =3D port number # %D =3D database cluster path # %m =3D new master node id # %H =3D hostname of the new master no= de # %M =3D old master node id # %P =3D old primary node id = # %r =3D new mast= er port number = # %R =3D new mast= er database cluster path # %% =3D '%' character #--------------------------------------------------------------------------= ---- # HEALTH CHECK #--------------------------------------------------------------------------= ---- health_check_period =3D 2 # Health check period # Disabled (0) by default health_check_timeout =3D 10 # Health check timeout # 0 means no timeout health_check_user =3D 'postgres' # Health check user health_check_password =3D 'P0stgres' # Password for health check user health_check_max_retries =3D 0 # Maximum number of times to retry a fai= led health check before giving up. health_check_retry_delay =3D 1 # Amount of time to wait (in seconds) be= tween retries. connect_timeout =3D 10000 # Timeout value in milliseconds before g= iving up to connect to backend. = # Default is 10000 = ms (10 second). Flaky network user may want to increase = # the value. 0 mean= s no timeout. = # Note that this va= lue is not only used for health check, = # but also for ordi= nary conection to backend. #--------------------------------------------------------------------------= ---- # FAILOVER AND FAILBACK #--------------------------------------------------------------------------= ---- failover_command =3D '/var/lib/pgsql/9.4/failover.sh %d %P %H %R' # Executes this command at failover # Special values: # %d =3D node id # %h =3D host name # %p =3D port number # %D =3D database cluster path # %m =3D new master node id # %H =3D hostname of the new master no= de # %M =3D old master node id # %P =3D old primary node id = # %r =3D new mast= er port number = # %R =3D new mast= er database cluster path # %% =3D '%' character failback_command =3D '' # Executes this command at failback. # Special values: # %d =3D node id # %h =3D host name # %p =3D port number # %D =3D database cluster path # %m =3D new master node id # %H =3D hostname of the new master no= de # %M =3D old master node id # %P =3D old primary node id = # %r =3D new mast= er port number = # %R =3D new mast= er database cluster path # %% =3D '%' character fail_over_on_backend_error =3D on # Initiates failover when reading/writin= g to the # backend communication socket fails # If set to off, pgpool will report an # error and disconnect the session. search_primary_node_timeout =3D 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 =3D off # Activates watchdog # (change requires restart) # -Connection to up stream servers - trusted_servers =3D '' # trusted server list which are used # to confirm network connection # (hostA,hostB,hostC,...) # (change requires restart) ping_path =3D '/bin' # ping command path # (change requires restart) # - Watchdog communication Settings - wd_hostname =3D '' # Host name or IP address of this watch= dog # (change requires restart) wd_port =3D 9000 # port number for watchdog service # (change requires restart) wd_authkey =3D '' # Authentication key for watchdog commu= nication # (change requires restart) # - Virtual IP control Setting - delegate_IP =3D '' # delegate IP address # If this is empty, virtual IP never br= ing up. # (change requires restart) ifconfig_path =3D '/sbin' # ifconfig command path # (change requires restart) if_up_cmd =3D 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0' # startup delegate IP command # (change requires restart) if_down_cmd =3D 'ifconfig eth1:0 down' # shutdown delegate IP command # (change requires restart) arping_path =3D '/usr/sbin' # arping command path # (change requires restart) arping_cmd =3D 'arping -U -I eth1 $_IP_$ -w 1' # arping command # (change requires restart) # - Behaivor on escalation Setting - clear_memqcache_on_escalation =3D on # Clear all the query cache on shared m= emory # when standby pgpool escalate to activ= e pgpool # (=3D virtual IP holder). # This should be off if client connects= to pgpool # not using virtual IP. # (change requires restart) wd_escalation_command =3D '' # Executes this command at escalation o= n new active pgpool. # (change requires restart) # - Lifecheck Setting - # -- common -- wd_lifecheck_method =3D 'heartbeat' # Method of watchdog lifecheck ('heartb= eat' or 'query') # (change requires restart) wd_interval =3D 10 # lifecheck interval (sec) > 0 # (change requires restart) # -- heartbeat mode -- wd_heartbeat_port =3D 9694 # Port number for receiving heartbeat s= ignal # (change requires restart) wd_heartbeat_keepalive =3D 2 # Interval time of sending heartbeat si= gnal (sec) # (change requires restart) wd_heartbeat_deadtime =3D 30 # Deadtime interval for heartbeat signa= l (sec) # (change requires restart) heartbeat_destination0 =3D 'host0_ip1' # Host name or IP address of destinatio= n 0 # for sending heartbeat signal. # (change requires restart) heartbeat_destination_port0 =3D 9694 # Port number of destination 0 for send= ing # heartbeat signal. Usually this is the # same as wd_heartbeat_port. # (change requires restart) heartbeat_device0 =3D '' # Name of NIC device (such like 'eth0') # used for sending/receiving heartbeat # signal to/from destination 0. # This works only when this is not empt= y # and pgpool has root privilege. # (change requires restart) #heartbeat_destination1 =3D 'host0_ip2' #heartbeat_destination_port1 =3D 9694 #heartbeat_device1 =3D '' # -- query mode -- wd_life_point =3D 3 # lifecheck retry times # (change requires restart) wd_lifecheck_query =3D 'SELECT 1' # lifecheck query to pgpool from watchd= og # (change requires restart) wd_lifecheck_dbname =3D 'template1' # Database name connected for lifecheck # (change requires restart) wd_lifecheck_user =3D 'nobody' # watchdog user monitoring pgpools in l= ifecheck # (change requires restart) wd_lifecheck_password =3D '' # Password for watchdog user in lifeche= ck # (change requires restart) # - Other pgpool Connection Settings - #other_pgpool_hostname0 =3D 'host0' # Host name or IP address to connect to= for other pgpool 0 # (change requires restart) #other_pgpool_port0 =3D 5432 # Port number for othet pgpool 0 # (change requires restart) #other_wd_port0 =3D 9000 # Port number for othet watchdog 0 # (change requires restart) #other_pgpool_hostname1 =3D 'host1' #other_pgpool_port1 =3D 5432 #other_wd_port1 =3D 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 i= mmediately and do not disclose the contents to another person, use it for a= ny purpose or store or copy the information in any medium. --_000_6a7655c079424b0ea527ecf851c6b95aDB4PR48MB0124041dmgdmsf_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

 

Hi All,

 

I’m working on a postgresql HA setup and runni= ng into an issue with pgpool.  I’m using built in postgres strea= ming replication and pgpool to monitor the cluster and initiate the failove= r 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 s= tatus file: 1 th backend is set to down status

2016-03-07 16:40:31: pid 13221: LOG:  Setting u= p socket for 0.0.0.0:19999

2016-03-07 16:40:31: pid 13221: LOG:  Setting u= p 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_prim= ary_node: checking backend no 0

 

2016-03-07 16:40:31: pid 13221: LOG:  find_prim= ary_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:  connec= tion 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:  connec= tion to host:"192.168.2.114:5432" failed

2016-03-07 16:44:08: pid 13334: LOG:  setting b= ackend 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 a= ll children

2016-03-07 16:44:08: pid 13334: LOG:  execute c= ommand: /var/lib/pgsql/9.4/failover.sh 0 0 "" ""

2016-03-07 16:44:08: pid 13335: LOG:  child pro= cess received shutdown request signal 3

2016-03-07 16:44:08: pid 13336: LOG:  child pro= cess received shutdown request signal 3

2016-03-07 16:44:08: pid 13337: LOG:  child pro= cess received shutdown request signal 3

+ FALLING_NODE=3D0

+ OLDPRIMARY_NODE=3D0

+ NEW_PRIMARY=3D

+ PGDATA=3D

+ '[' 0 =3D 0 ']'

+ '[' 0 -eq 0 ']'

+ su postgres -c 'ssh -T postgres@ touch /var/li= b/pgsql/9.4/data/failover.trigger'

 

And then it hangs on the script due to the missin= g parameters.  This happens no matter which server is the master. = ; Also, sometimes on boot pgpool seems to look for the standby first and th= is is the log I get:

 

 

2016-03-07 16:43:56: pid 13334: LOG:  reading s= tatus file: 1 th backend is set to down status

2016-03-07 16:43:56: pid 13334: LOG:  Setting u= p socket for 0.0.0.0:19999

2016-03-07 16:43:56: pid 13334: LOG:  Setting u= p 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_prim= ary_node: checking backend no 0

 

2016-03-07 16:43:56: pid 13334: LOG:  find_prim= ary_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:  connec= tion 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:  connec= tion to host:"192.168.2.114:5432" failed

2016-03-07 16:44:08: pid 13334: LOG:  setting b= ackend 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 a= ll children

2016-03-07 16:44:08: pid 13334: LOG:  execute c= ommand: /var/lib/pgsql/9.4/failover.sh 0 0 "" ""

2016-03-07 16:44:08: pid 13335: LOG:  child pro= cess received shutdown request signal 3

2016-03-07 16:44:08: pid 13336: LOG:  child pro= cess received shutdown request signal 3

2016-03-07 16:44:08: pid 13337: LOG:  child pro= cess received shutdown request signal 3

+ FALLING_NODE=3D0

+ OLDPRIMARY_NODE=3D0

+ NEW_PRIMARY=3D

+ PGDATA=3D

+ '[' 0 =3D 0 ']'

+ '[' 0 -eq 0 ']'

+ su postgres -c 'ssh -T postgres@ touch /var/li= b/pgsql/9.4/data/failover.trigger'

^C

Session terminated, killing shell... ...killed.=

+ exit 0

2016-03-07 16:44:34: pid 13334: LOG:  find_prim= ary_node_repeatedly: waiting for finding a primary node

2016-03-07 16:44:34: pid 13334: LOG:  find_prim= ary_node: checking backend no 0

 

2016-03-07 16:44:34: pid 13334: LOG:  find_prim= ary_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 s= tatus file: 0 th backend is set to down status

2016-03-07 16:47:19: pid 13490: LOG:  reading s= tatus file: 1 th backend is set to down status

2016-03-07 16:47:19: pid 13490: LOG:  Setting u= p socket for 0.0.0.0:19999

2016-03-07 16:47:19: pid 13490: LOG:  Setting u= p 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_prim= ary_node: checking backend no 0

 

2016-03-07 16:47:19: pid 13490: LOG:  find_prim= ary_node: primary node id is 0

2016-03-07 16:43:56: pid 13334: LOG:  reading s= tatus file: 1 th backend is set to down status

2016-03-07 16:43:56: pid 13334: LOG:  Setting u= p socket for 0.0.0.0:19999

2016-03-07 16:43:56: pid 13334: LOG:  Setting u= p 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_prim= ary_node: checking backend no 0

 

2016-03-07 16:43:56: pid 13334: LOG:  find_prim= ary_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:  connec= tion 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:  connec= tion to host:"192.168.2.114:5432" failed

2016-03-07 16:44:08: pid 13334: LOG:  setting b= ackend 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 a= ll children

2016-03-07 16:44:08: pid 13334: LOG:  execute c= ommand: /var/lib/pgsql/9.4/failover.sh 0 0 "" ""

+ FALLING_NODE=3D0

+ OLDPRIMARY_NODE=3D0

+ NEW_PRIMARY=3D

+ PGDATA=3D

+ '[' 0 =3D 0 ']'

+ '[' 0 -eq 0 ']'

+ su postgres -c 'ssh -T postgres@ touch /var/li= b/pgsql/9.4/data/failover.trigger'

^C

Session terminated, killing shell... ...killed.=

+ exit 0

2016-03-07 16:44:34: pid 13334: LOG:  find_prim= ary_node_repeatedly: waiting for finding a primary node

2016-03-07 16:44:34: pid 13334: LOG:  find_prim= ary_node: checking backend no 0

 

2016-03-07 16:44:34: pid 13334: LOG:  find_prim= ary_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 s= tatus file: 0 th backend is set to down status

2016-03-07 16:47:19: pid 13490: LOG:  reading s= tatus file: 1 th backend is set to down status

2016-03-07 16:47:19: pid 13490: LOG:  Setting u= p socket for 0.0.0.0:19999

2016-03-07 16:47:19: pid 13490: LOG:  Setting u= p 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_prim= ary_node: checking backend no 0

 

2016-03-07 16:47:19: pid 13490: LOG:  find_prim= ary_node: primary node id is 0

2016-03-07 16:47:19: pid 13490: ERROR:  failed = to authenticate

2016-03-07 16:47:19: pi= d 13490: DETAIL:  invalid authentication message response type, Expect= ing 'R' and received 'E'

2016-03-07 16:47:19: pi= d 13490: ERROR:  failed to authenticate

2016-03-07 16:47:19: pi= d 13490: DETAIL:  invalid authentication message response type, Expect= ing 'R' and received 'E'

2016-03-07 16:47:19: pid 13490: LOG:  setting b= ackend 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 a= ll children

2016-03-07 16:47:19: pid 13490: LOG:  execute c= ommand: /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 pro= cess received shutdown request signal 3

2016-03-07 16:47:19: pid 13492: LOG:  child pro= cess received shutdown request signal 3

2016-03-07 16:47:19: pid 13493: LOG:  child pro= cess received shutdown request signal 3

+ FALLING_NODE=3D1

+ OLDPRIMARY_NODE=3D0

+ NEW_PRIMARY=3D192.168.2.114

+ PGDATA=3D/var/lib/pgsql/9.4/data/

+ '[' 1 =3D 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 pr= ocess received restart request

2016-03-07 16:47:20: pid 13494: LOG:  restart r= equest 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 ne= w PCP child pid 13502 in failover()

2016-03-07 16:47:20: pid 13490: LOG:  child pro= cess with pid: 13491 exits with status 0

2016-03-07 16:47:20: pid 13490: LOG:  child pro= cess with pid: 13491 exited with success and will not be restarted

2016-03-07 16:47:20: pid 13490: LOG:  child pro= cess with pid: 13492 exits with status 0

2016-03-07 16:47:20: pid 13490: LOG:  child pro= cess with pid: 13492 exited with success and will not be restarted

2016-03-07 16:47:20: pid 13490: LOG:  child pro= cess with pid: 13493 exits with status 0

2016-03-07 16:47:20: pid 13490: LOG:  child pro= cess with pid: 13493 exited with success and will not be restarted

2016-03-07 16:47:20: pid 13490: LOG:  worker ch= ild process with pid: 13495 exits with status 256

2016-03-07 16:47:20: pid 13490: LOG:  fork a ne= w 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 a= ll children

2016-03-07 16:47:19: pid 13490: LOG:  execute c= ommand: /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 pro= cess received shutdown request signal 3

2016-03-07 16:47:19: pid 13492: LOG:  child pro= cess received shutdown request signal 3

2016-03-07 16:47:19: pid 13493: LOG:  child pro= cess received shutdown request signal 3

+ FALLING_NODE=3D1

+ OLDPRIMARY_NODE=3D0

+ NEW_PRIMARY=3D192.168.2.114

+ PGDATA=3D/var/lib/pgsql/9.4/data/

+ '[' 1 =3D 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 on= ly for the standby server (and it’s not dependant on the physical ser= ver.. 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 =3D '*'

        &nbs= p;            &= nbsp;           &nbs= p; # Host name or IP address to listen on:

        &nbs= p;            &= nbsp;            &nb= sp;# '*' for all, '' for no TCP/IP connections

        &nbs= p;            &= nbsp;           &nbs= p; # (change requires restart)

port =3D 19999

        &nbs= p;            &= nbsp;           &nbs= p; # Port number

        &nbs= p;            &= nbsp;           &nbs= p; # (change requires restart)

socket_dir =3D '/tmp'

         &nb= sp;            =             &nb= sp;# Unix domain socket path

        &nbs= p;            &= nbsp;           &nbs= p; # The Debian package defaults to

        &nbs= p;            &= nbsp;           &nbs= p; # /var/run/postgresql

        &nbs= p;            &= nbsp;           &nbs= p; # (change requires restart)

 

# - Backend Connection Settings -

 

backend_hostname0 =3D '192= .168.2.114'

    &n= bsp;            = ;            &n= bsp;     # Host name or IP address to connect to for ba= ckend 0

backend_port0 =3D 5432

    &n= bsp;            = ;            &n= bsp;     # Port number for backend 0<= /p>

backend_weight0 =3D 1=

    &n= bsp;            = ;            &n= bsp;     # Weight for backend 0 (only in load balancing= mode)

backend_data_directory0 = =3D '/var/lib/pgsql/9.4/data/'

    &n= bsp;            = ;            &n= bsp;     # Data directory for backend 0

backend_flag0 =3D 'ALLOW_T= O_FAILOVER'

    &n= bsp;            &nbs= p;            &= nbsp;    # Controls various backend behavior=

    &n= bsp;            = ;            &n= bsp;     # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER

backend_hostname1 =3D '192= .168.2.104'

backend_port1 =3D 5432

backend_weight1 =3D 1=

backend_data_directory1 = =3D '/var/lib/pgsql/9.4/data/'

backend_flag1 =3D 'ALLOW_T= O_FAILOVER'

 

# - Authentication -

 

enable_pool_hba =3D off

        &nbs= p;            &= nbsp;           &nbs= p; # Use pool_hba.conf for client authentication

pool_passwd =3D ''

        &nbs= p;            &= nbsp;           &nbs= p; # File name of pool_passwd for md5 authentication.

        &nbs= p;            &= nbsp;           &nbs= p; # "" disables pool_passwd.

        &nbs= p;            &= nbsp;           &nbs= p; # (change requires restart)

authentication_timeout =3D 60

        &nbs= p;            &= nbsp;           &nbs= p; # Delay in seconds to complete client authentication

        &nbs= p;            &= nbsp;           &nbs= p; # 0 means no timeout.

 

 

#---------------------------------------------------= ---------------------------

# LOAD BALANCING MODE

#---------------------------------------------------= ---------------------------

 

load_balance_mode =3D off

        &nbs= p;            &= nbsp;           &nbs= p; # Activate load balancing mode

        &nbs= p;            &= nbsp;           &nbs= p; # (change requires restart)

ignore_leading_white_space =3D on

        &nbs= p;            &= nbsp;           &nbs= p; # Ignore leading white spaces of each query

white_function_list =3D ''

        &nbs= p;            &= nbsp;           &nbs= p; # Comma separated list of function names

        &nbs= p;            &= nbsp;           &nbs= p; # that don't write to database

        &nbs= p;            &= nbsp;           &nbs= p; # Regexp are accepted

black_function_list =3D 'currval,lastval,nextval,set= val'

        &nbs= p;            &= nbsp;           &nbs= p; # Comma separated list of function names

        &nbs= p;            &= nbsp;           &nbs= p; # that write to database

        &nbs= p;            &= nbsp;           &nbs= p; # Regexp are accepted

 

database_redirect_preference_list =3D ''<= /p>

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # comma separated list of = pairs of database and node id.

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # example: postgres:primar= y,mydb[0-4]:1,mydb[5-9]:2'

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # valid for streaming repl= icaton mode only.

 

app_name_redirect_preference_list =3D ''<= /p>

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # comma separated list of = pairs of app name and node id.

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # example: 'psql:primary,m= yapp[0-4]:1,myapp[5-9]:standby'

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # valid for streaming repl= icaton mode only.

allow_sql_comments =3D off

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # if on, ignore SQL commen= ts when judging if load balance or

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # query cache is possible.=

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # If off, SQL comments eff= ectively prevent the judgment

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # (pre 3.4 behavior).=

 

#---------------------------------------------------= ---------------------------

# MASTER/SLAVE MODE

#---------------------------------------------------= ---------------------------

 

master_slave_mode =3D on

        &nbs= p;            &= nbsp;           &nbs= p; # Activate master/slave mode

        &nbs= p;            &= nbsp;           &nbs= p; # (change requires restart)

master_slave_sub_mode =3D 'stream'

        &nbs= p;            &= nbsp;           &nbs= p; # Master/slave sub mode

        &nbs= p;            &= nbsp;           &nbs= p; # Valid values are combinations slony or

         &nb= sp;            =             &nb= sp;# stream. Default is slony.

        &nbs= p;            &= nbsp;           &nbs= p; # (change requires restart)

 

# - Streaming -

 

sr_check_period =3D 0

        &nbs= p;            &= nbsp;           &nbs= p; # Streaming replication check period

        &nbs= p;            &= nbsp;           &nbs= p; # Disabled (0) by default

sr_check_user =3D 'postgres'

        &nbs= p;            &= nbsp;           &nbs= p; # Streaming replication check user

        &nbs= p;            &= nbsp;           &nbs= p; # This is neccessary even if you disable streaming

        &nbs= p;             =             &nb= sp;# replication delay check by sr_check_period =3D 0

sr_check_password =3D ''

        &nbs= p;            &= nbsp;           &nbs= p; # Password for streaming replication check user

delay_threshold =3D 10000000

        &nbs= p;            &= nbsp;           &nbs= p; # Threshold before not dispatching query to standby node

        &nbs= p;            &= nbsp;           &nbs= p; # Unit is in bytes

        &nbs= p;            &= nbsp;           &nbs= p; # Disabled (0) by default

 

# - Special commands -

 

follow_master_command =3D ''

        &nbs= p;            &= nbsp;           &nbs= p; # Executes this command after master failover

        &nbs= p;            &= nbsp;           &nbs= p; # Special values:

        &nbs= p;            &= nbsp;           &nbs= p; #   %d =3D node id

        &nbs= p;            &= nbsp;           &nbs= p; #   %h =3D host name

        &nbs= p;            &= nbsp;           &nbs= p; #   %p =3D port number

         &nb= sp;            =             &nb= sp;#   %D =3D database cluster path

        &nbs= p;            &= nbsp;           &nbs= p; #   %m =3D new master node id

        &nbs= p;            &= nbsp;           &nbs= p; #   %H =3D hostname of the new master node

        &nbs= p;            &= nbsp;           &nbs= p; #   %M =3D old master node id

        &nbs= p;            &= nbsp;           &nbs= p; #   %P =3D old primary node id

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         #   %r =3D new m= aster port number

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         #   %R =3D new m= aster database cluster path

        &nbs= p;            &= nbsp;           &nbs= p; #   %% =3D '%' character

 

#---------------------------------------------------= ---------------------------

# HEALTH CHECK

#---------------------------------------------------= ---------------------------

 

health_check_period =3D 2

        &nbs= p;            &= nbsp;           &nbs= p; # Health check period

        &nbs= p;            &= nbsp;           &nbs= p; # Disabled (0) by default

health_check_timeout =3D 10

        &nbs= p;            &= nbsp;           &nbs= p; # Health check timeout

        &nbs= p;            &= nbsp;           &nbs= p; # 0 means no timeout

health_check_user =3D 'postgres'

        &nbs= p;            &= nbsp;            &nb= sp;# Health check user

health_check_password =3D 'P0stgres'

        &nbs= p;            &= nbsp;           &nbs= p; # Password for health check user

health_check_max_retries =3D 0

        &nbs= p;            &= nbsp;           &nbs= p; # Maximum number of times to retry a failed health check before giving u= p.

health_check_retry_delay =3D 1

        &nbs= p;            &= nbsp;           &nbs= p; # Amount of time to wait (in seconds) between retries.

connect_timeout =3D 10000

        &nbs= p;            &= nbsp;           &nbs= p; # Timeout value in milliseconds before giving up to connect to backend.<= o:p>

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # Default is 10000 ms (10 = second). Flaky network user may want to increase

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # the value. 0 means no ti= meout.

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # Note that this value is = not only used for health check,

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         # but also for ordinary co= nection to backend.

 

#---------------------------------------------------= ---------------------------

# FAILOVER AND FAILBACK

#---------------------------------------------------= ---------------------------

 

failover_command =3D '/var/lib/pgsql/9.4/failover.sh= %d %P %H %R'

        &nbs= p;            &= nbsp;           &nbs= p; # Executes this command at failover

        &nbs= p;            &= nbsp;           &nbs= p; # Special values:

        &nbs= p;            &= nbsp;           &nbs= p; #   %d =3D node id

        &nbs= p;             =             &nb= sp;#   %h =3D host name

        &nbs= p;            &= nbsp;           &nbs= p; #   %p =3D port number

        &nbs= p;            &= nbsp;           &nbs= p; #   %D =3D database cluster path

        &nbs= p;            &= nbsp;           &nbs= p; #   %m =3D new master node id

        &nbs= p;            &= nbsp;            &nb= sp;#   %H =3D hostname of the new master node

        &nbs= p;            &= nbsp;           &nbs= p; #   %M =3D old master node id

        &nbs= p;            &= nbsp;           &nbs= p; #   %P =3D old primary node id

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         #   %r =3D new m= aster port number

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         #   %R =3D new m= aster database cluster path

        &nbs= p;            &= nbsp;           &nbs= p; #   %% =3D '%' character

failback_command =3D ''

        &nbs= p;            &= nbsp;           &nbs= p; # Executes this command at failback.

        &nbs= p;            &= nbsp;           &nbs= p; # Special values:

        &nbs= p;            &= nbsp;           &nbs= p; #   %d =3D node id

        &nbs= p;            &= nbsp;           &nbs= p; #   %h =3D host name

        &nbs= p;            &= nbsp;           &nbs= p; #   %p =3D port number

        &nbs= p;            &= nbsp;           &nbs= p; #   %D =3D database cluster path

        &nbs= p;            &= nbsp;           &nbs= p; #   %m =3D new master node id

         &nb= sp;            =             &nb= sp;#   %H =3D hostname of the new master node

        &nbs= p;            &= nbsp;           &nbs= p; #   %M =3D old master node id

        &nbs= p;            &= nbsp;           &nbs= p; #   %P =3D old primary node id

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         #   %r =3D new m= aster port number

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         #   %R =3D new m= aster database cluster path

        &nbs= p;            &= nbsp;           &nbs= p; #   %% =3D '%' character

 

fail_over_on_backend_error =3D on

        &nbs= p;            &= nbsp;           &nbs= p; # Initiates failover when reading/writing to the

        &nbs= p;            &= nbsp;           &nbs= p; # backend communication socket fails

        &nbs= p;            &= nbsp;           &nbs= p; # If set to off, pgpool will report an

        &nbs= p;            &= nbsp;           &nbs= p; # error and disconnect the session.

 

search_primary_node_timeout =3D 10

         &nb= sp;            =             &nb= sp;# Timeout in seconds to search for the

        &nbs= p;            &= nbsp;           &nbs= p; # primary node when a failover occurs.

        &nbs= p;            &= nbsp;           &nbs= p; # 0 means no timeout, keep searching

        &nbs= p;            &= nbsp;           &nbs= p; # for a primary node forever.

 

 

#---------------------------------------------------= ---------------------------

# WATCHDOG

#---------------------------------------------------= ---------------------------

 

# - Enabling -

 

use_watchdog =3D off

        &nbs= p;            &= nbsp;           &nbs= p;  # Activates watchdog

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

# -Connection to up stream servers -

 

trusted_servers =3D ''

        &nbs= p;            &= nbsp;           &nbs= p;  # trusted server list which are used

        &nbs= p;            &= nbsp;           &nbs= p;  # to confirm network connection

        &nbs= p;            &= nbsp;           &nbs= p;  # (hostA,hostB,hostC,...)

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

ping_path =3D '/bin'

        &nbs= p;            &= nbsp;           &nbs= p;  # ping command path

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

# - Watchdog communication Settings -

 

wd_hostname =3D ''

        &nbs= p;            &= nbsp;           &nbs= p;  # Host name or IP address of this watchdog

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_port =3D 9000

        &nbs= p;            &= nbsp;           &nbs= p;  # port number for watchdog service

        &nbs= p;             =             &nb= sp; # (change requires restart)

wd_authkey =3D ''

        &nbs= p;            &= nbsp;           &nbs= p;  # Authentication key for watchdog communication

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

# - Virtual IP control Setting -

 

delegate_IP =3D ''

        &nbs= p;            &= nbsp;           &nbs= p;  # delegate IP address

        &nbs= p;            &= nbsp;           &nbs= p;  # If this is empty, virtual IP never bring up.

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

ifconfig_path =3D '/sbin'

        &nbs= p;             =             &nb= sp; # ifconfig command path

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

if_up_cmd =3D 'ifconfig eth1:0 inet $_IP_$ netmask 2= 55.255.255.0'

        &nbs= p;            &= nbsp;           &nbs= p;  # startup delegate IP command

        &nbs= p;             =             &nb= sp; # (change requires restart)

if_down_cmd =3D 'ifconfig eth1:0 down'

        &nbs= p;            &= nbsp;           &nbs= p;  # shutdown delegate IP command

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

arping_path =3D '/usr/sbin'    &= nbsp;      # arping command path

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

arping_cmd =3D 'arping -U -I eth1 $_IP_$ -w 1'<= /o:p>

        &nbs= p;            &= nbsp;           &nbs= p;  # arping command

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

# - Behaivor on escalation Setting -

 

clear_memqcache_on_escalation =3D on

        &nbs= p;            &= nbsp;           &nbs= p;  # Clear all the query cache on shared memory

        &nbs= p;            &= nbsp;           &nbs= p;  # when standby pgpool escalate to active pgpool

        &nbs= p;            &= nbsp;           &nbs= p;  # (=3D virtual IP holder).

        &nbs= p;            &= nbsp;           &nbs= p;  # This should be off if client connects to pgpool

        &nbs= p;            &= nbsp;           &nbs= p;  # not using virtual IP.

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_escalation_command =3D ''

        &nbs= p;            &= nbsp;           &nbs= p;  # Executes this command at escalation on new active pgpool.

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

# - Lifecheck Setting -

 

# -- common --

 

wd_lifecheck_method =3D 'heartbeat'

        &nbs= p;            &= nbsp;           &nbs= p;  # Method of watchdog lifecheck ('heartbeat' or 'query')=

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_interval =3D 10

        &nbs= p;            &= nbsp;           &nbs= p;  # lifecheck interval (sec) > 0

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

# -- heartbeat mode --

 

wd_heartbeat_port =3D 9694

        &nbs= p;            &= nbsp;           &nbs= p;  # Port number for receiving heartbeat signal

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_heartbeat_keepalive =3D 2

        &nbs= p;            &= nbsp;           &nbs= p;  # Interval time of sending heartbeat signal (sec)

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_heartbeat_deadtime =3D 30

        &nbs= p;            &= nbsp;           &nbs= p;  # Deadtime interval for heartbeat signal (sec)

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

heartbeat_destination0 =3D 'host0_ip1'

        &nbs= p;            &= nbsp;           &nbs= p;  # Host name or IP address of destination 0

        &nbs= p;            &= nbsp;           &nbs= p;  # for sending heartbeat signal.

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

heartbeat_destination_port0 =3D 9694

        &nbs= p;            &= nbsp;           &nbs= p;  # Port number of destination 0 for sending

        &nbs= p;            &= nbsp;           &nbs= p;  # heartbeat signal. Usually this is the

        &nbs= p;            &= nbsp;           &nbs= p;  # same as wd_heartbeat_port.

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

heartbeat_device0 =3D ''

        &nbs= p;             =             &nb= sp; # Name of NIC device (such like 'eth0')

        &nbs= p;            &= nbsp;           &nbs= p;  # used for sending/receiving heartbeat

        &nbs= p;            &= nbsp;           &nbs= p;  # signal to/from destination 0.

        &nbs= p;            &= nbsp;           &nbs= p;  # This works only when this is not empty

        &nbs= p;            &= nbsp;           &nbs= p;  # and pgpool has root privilege.

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

#heartbeat_destination1 =3D 'host0_ip2'

#heartbeat_destination_port1 =3D 9694

#heartbeat_device1 =3D ''

 

# -- query mode --

 

wd_life_point =3D 3

        &nbs= p;            &= nbsp;           &nbs= p;  # lifecheck retry times

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_lifecheck_query =3D 'SELECT 1'

        &nbs= p;            &= nbsp;           &nbs= p;  # lifecheck query to pgpool from watchdog

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_lifecheck_dbname =3D 'template1'

        &nbs= p;            &= nbsp;           &nbs= p;  # Database name connected for lifecheck

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_lifecheck_user =3D 'nobody'

        &nbs= p;            &= nbsp;           &nbs= p;  # watchdog user monitoring pgpools in lifecheck

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

wd_lifecheck_password =3D ''

        &nbs= p;            &= nbsp;           &nbs= p;  # Password for watchdog user in lifecheck

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

 

# - Other pgpool Connection Settings -

 

#other_pgpool_hostname0 =3D 'host0'

        &nbs= p;            &= nbsp;           &nbs= p;  # Host name or IP address to connect to for other pgpool 0

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

#other_pgpool_port0 =3D 5432

        &nbs= p;            &= nbsp;           &nbs= p;  # Port number for othet pgpool 0

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

#other_wd_port0 =3D 9000

        &nbs= p;            &= nbsp;           &nbs= p;  # Port number for othet watchdog 0

        &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)

#other_pgpool_hostname1 =3D 'host1'

#other_pgpool_port1 =3D 5432

#other_wd_port1 =3D 9000

 

 

 



CONFIDENTIALITY : This e-ma= il and any attachments are confidential and may be privileged. If you are n= ot a named recipient, please notify the sender immediately and do not discl= ose the contents to another person, use it for any purpose or store or copy the information in any medium.
--_000_6a7655c079424b0ea527ecf851c6b95aDB4PR48MB0124041dmgdmsf_--