<html><head></head><body><div class="ydpcf377089yahoo-style-wrap" style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"><div><div>Dimitri, <br></div><div><br></div><div>it should really work like you think, when a standby fails then pgool detaches it from the pool and the status should be down (can be seen with show pool_nodes). So my explanation was not clear: what I mean is that when a standby fails the script failover.sh is executed but in the script there is nothing to do. pgpool itself will take care to detach the failing node from the pool.<br></div><div><br></div><div>Here is an extract from my pgpool log fil when I stopped the standby database called pg02. I have the following parameters:</div><div><br></div><div>health_check_period=5<br></div><div>health_check_max_retries=3</div><div>health_check_retry_delay=1</div><div>failover_on_backend_error='off'</div><div><br></div><div>As you can see the health check retry 3 times on DB node 1 (the standby node), then it executes the failover command. In my failover script I check if the falling node is the primary, if it is not then I do nothing. After that show pool_nodes says the node pg02 is down <br></div><br><div>It is not working for you, there must be a misconfiguration</div><div><br></div><div>Pierre<br></div><div><br></div><div><br></div><div><br></div><div><span>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:42: pid 188: LOG: failed to connect to PostgreSQL server on "pg02:5432", getsockopt() detected error "Connection refused"<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:42: pid 188: LOCATION: pool_connection_pool.c:680<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:42: pid 188: ERROR: failed to make persistent db connection<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:42: pid 188: DETAIL: connection to host:"pg02:5432" failed<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:42: pid 188: LOCATION: child.c:1328<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:42: pid 188: LOG: health check retrying on DB node: 1 (round:1)<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:42: pid 188: LOCATION: health_check.c:298<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:43: pid 188: LOG: failed to connect to PostgreSQL server on "pg02:5432", getsockopt() detected error "Connection refused"<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:43: pid 188: LOCATION: pool_connection_pool.c:680<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:43: pid 188: ERROR: failed to make persistent db connection<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:43: pid 188: DETAIL: connection to host:"pg02:5432" failed<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:43: pid 188: LOCATION: child.c:1328<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:43: pid 188: LOG: health check retrying on DB node: 1 (round:2)<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:43: pid 188: LOCATION: health_check.c:298<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:44: pid 188: LOG: failed to connect to PostgreSQL server on "pg02:5432", getsockopt() detected error "Connection refused"<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:44: pid 188: LOCATION: pool_connection_pool.c:680<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:44: pid 188: ERROR: failed to make persistent db connection<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:44: pid 188: DETAIL: connection to host:"pg02:5432" failed<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:44: pid 188: LOCATION: child.c:1328<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:44: pid 188: LOG: health check retrying on DB node: 1 (round:3)<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:44: pid 188: LOCATION: health_check.c:298<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: LOG: failed to connect to PostgreSQL server on "pg02:5432", getsockopt() detected error "Connection refused"<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: LOCATION: pool_connection_pool.c:680<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: ERROR: failed to make persistent db connection<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: DETAIL: connection to host:"pg02:5432" failed<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: LOCATION: child.c:1328<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: LOG: health check failed on node 1 (timeout:0)<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: LOCATION: health_check.c:201<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: LOG: received degenerate backend request for node_id: 1 from pid [188]<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 188: LOCATION: pgpool_main.c:1125<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOG: Pgpool-II parent process has received failover request<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOCATION: pgpool_main.c:1588<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOG: starting degeneration. shutdown host pg02(5432)<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOCATION: pgpool_main.c:1867<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOG: Do not restart children because we are switching over node id 1 host: pg02 port: 5432 and we are in streaming replication mode<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOCATION: pgpool_main.c:1974<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOG: execute command: /scripts/failover.sh 1 pg02 5432 /data 0 pg01 0 0 5432 /data<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOCATION: pgpool_main.c:3064<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 1 pg02 5432 /data 0 pg01 0 0 5432 /data<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | Wed Mar 27 14:44:45 UTC 2019<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | FALLING_NODE: 1<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | FALLING_HOST: pg02<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | FALLING_PORT_NUMBER: 5432<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | FALLING_CLUSTER_PATH: /data<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | NEW_MASTER_ID: 0<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | NEW_HOST: pg01<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | OLD_MASTER_ID: 0<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | OLD_PRIMARY_ID: 0<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | NEW_PORT: 5432<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | NEW_CLUSTER_PATH: 10<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | + '[' 1 = 0 ']'<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | + echo old primary id is 0 and falling node is 1<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | old primary id is 0 and falling node is 1<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | + exit 0<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOG: failover: set new primary node: 0<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOCATION: pgpool_main.c:2187<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOG: failover: set new master node: 0<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOCATION: pgpool_main.c:2194<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | failover done. shutdown host pg02(5432)2019-03-27 14:44:45: pid 1: LOG: failover done. shutdown host pg02(5432)<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 1: LOCATION: pgpool_main.c:2317<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 186: LOG: worker process received restart request<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:45: pid 186: LOCATION: pool_worker_child.c:153<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 185: LOG: restart request received in pcp child process<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 185: LOCATION: pcp_child.c:155<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOG: PCP child 185 exits with status 0 in failover()<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOCATION: pgpool_main.c:2359<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOG: fork a new PCP child pid 235 in failover()<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOCATION: pgpool_main.c:2363<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOG: worker child process with pid: 186 exits with status 256<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOCATION: pgpool_main.c:2620<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOG: fork a new worker child process with pid: 236<br>pgcluster_pgpool.1.yg0sbf4dywfe@romero | 2019-03-27 14:44:46: pid 1: LOCATION: pgpool_main.c:2729<br><br></span><br></div><div><br></div><div class="ydpcf377089signature">Pierre</div></div>
<div><br></div><div><br></div>
</div><div id="ydp4edc4a40yahoo_quoted_4207757703" class="ydp4edc4a40yahoo_quoted">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;">
<div>
On Wednesday, March 27, 2019, 3:26:30 PM GMT+1, Dmitry Medvedev <dm.dm.medvedev@gmail.com> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div id="ydp4edc4a40yiv0572098488"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Thanks a lot, very usefull and detailed manual. My config (at least at this moment) is simplier - I have only one instance of pgpool and 1 master and 1 standby.</div><div dir="ltr"><br clear="none"></div><div dir="ltr">You wrote "pgpool execute the failover_command in case there is a failure of the master or a failure of one of the standby (at least when health checks are used). In the failover script you will usually wants to check (based on the arguments) what to do: if the failing host is the current primary then you would promote the standby otherwise the script does nothing".</div><div dir="ltr"><br clear="none"></div><div>Imagine, that stand-by node is failed. If pgpool does nothing -> in this case we have failed redundancy -> pgpool thinks that we have stand-by node in reserve -> however stand-by node is down and there is no reserve.</div><div><br clear="none"></div><div>So, maybe, the best way will be use such tools as Zabbix for monitoring and use PgPool only for 2 things:</div><div>1) connection pooling</div><div>2) switching to standby on master failure</div></div></div></div></div></div></div>
</div>
</div></body></html>