<html><head></head><body><div class="ydp2a756be9yahoo-style-wrap" style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"><div><div>Tatsuo, the link to the github page is not correct, did you mean this one ?</div><div><br></div><div><a href="https://github.com/pgpool/pgpool2/blob/cad8a512d63dab7659c7288356f9cb5ffc331b92/doc/src/sgml/failover.sgml" rel="nofollow" target="_blank">https://github.com/pgpool/pgpool2/blob/cad8a512d63dab7659c7288356f9cb5ffc331b92/doc/src/sgml/failover.sgml</a><br></div><div><br></div><div>I will have a look. </div><div><br></div><div>In the follow_master description it is mentioned that it might be useful to check that the node is running (with pg_ctl) but I believe it might also be useful to check that the node is in recovery, by looking at recovery.conf or by executing function pg_is_in_recovery(), reason it to avoid issue with a degenerated master. </div><div><br></div><div>Maybe there is another issue ? Suppose I have 3 nodes: A, B and C and suppose A is in status down (detached from pgpool) and the other two are up. B is primary and C is standby. When B is stopped, then follow_master is executed on A and on C. But I think it should not be executed on node A because node A was detached from pgpool, maybe this node was a denerated master or maybe it was detached on purpose.</div><div><br></div><div>Also in the doc of follow_master it says: "<span><span style="color: rgb(0, 0, 0); font-family: verdana, sans-serif; font-size: medium;">Typically </span><tt class="ydp3f29ba0VARNAME" style="color: rgb(68, 0, 0);">follow_master_command</tt><span style="color: rgb(0, 0, 0); font-family: verdana, sans-serif; font-size: medium;"> command is used to recover the slave from the new primary by calling the pcp_recovery_node command", but this is not true. I believe the script will simply re-points the standby to the new primary, this does not require a full backup. In my case I use a script from repmgr, so the command is something like <span><span style="color: rgb(3, 47, 98); font-family: SFMono-Regular, Consolas, Liberation Mono, Menlo, Courier, monospace; font-size: 12px; white-space: pre-wrap;">/usr/pgsql-10/bin/repmgr --log-to-file -f /etc/repmgr/10/repmgr.conf -h </span><span class="ydpa5175e6dpl-smi" style="color: rgb(36, 41, 46); font-family: SFMono-Regular, Consolas, Liberation Mono, Menlo, Courier, monospace; font-size: 12px; white-space: pre-wrap;">${NEW_MASTER_HOST}</span><span style="color: rgb(3, 47, 98); font-family: SFMono-Regular, Consolas, Liberation Mono, Menlo, Courier, monospace; font-size: 12px; white-space: pre-wrap;"> -D </span><span class="ydpa5175e6dpl-smi" style="color: rgb(36, 41, 46); font-family: SFMono-Regular, Consolas, Liberation Mono, Menlo, Courier, monospace; font-size: 12px; white-space: pre-wrap;">${PGDATA}</span><span style="color: rgb(3, 47, 98); font-family: SFMono-Regular, Consolas, Liberation Mono, Menlo, Courier, monospace; font-size: 12px; white-space: pre-wrap;"> -U repmgr -d repmgr standby follow -v</span></span></span></span></div><div><span><span style="color: rgb(0, 0, 0); font-family: verdana, sans-serif; font-size: medium;"><span><span style="color: rgb(3, 47, 98); font-family: SFMono-Regular, Consolas, Liberation Mono, Menlo, Courier, monospace; font-size: 12px; white-space: pre-wrap;"><br></span></span></span></span></div><div><span><span style="color: rgb(0, 0, 0); font-family: verdana, sans-serif; font-size: medium;"><span><span style="color: rgb(3, 47, 98); font-family: SFMono-Regular, Consolas, Liberation Mono, Menlo, Courier, monospace; font-size: 12px; white-space: pre-wrap;">I would like to propose improvement to the documentation of the FAILOVER_ON_BACKEND_ERROR documentation, is that something I can do via a pull request ?</span></span></span></span></div><div><br></div><div>Regards, </div><div><br></div><div class="ydp2a756be9signature">Pierre</div></div>
<div><br></div><div><br></div>
</div><div id="ydpb3ebb34eyahoo_quoted_2106382586" class="ydpb3ebb34eyahoo_quoted">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;">
<div>
On Tuesday, March 5, 2019, 10:47:49 PM GMT+1, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div dir="ltr">I have updated follow master command description in the Pgpool-II<br></div><div dir="ltr">document to clarify what it actually does (will appear in next<br></div><div dir="ltr">Pgpool-II 4.0.4 release).<br></div><div dir="ltr"><br></div><div dir="ltr">In the mean time I have upload the HTML compiled version to my Github<br></div><div dir="ltr">page. Please take a look at and give comments if you like.<br></div><div dir="ltr"><br></div><div dir="ltr"><a href="http://localhost/~t-ishii/pgpool-II/html/runtime-config-failover.html" rel="nofollow" target="_blank">http://localhost/~t-ishii/pgpool-II/html/runtime-config-failover.html</a><br></div><div dir="ltr"><br></div><div dir="ltr">Best regards,<br></div><div dir="ltr">--<br></div><div dir="ltr">Tatsuo Ishii<br></div><div dir="ltr">SRA OSS, Inc. Japan<br></div><div dir="ltr">English: <a href="http://www.sraoss.co.jp/index_en.php" rel="nofollow" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br></div><div dir="ltr">Japanese:<a href="http://www.sraoss.co.jp" rel="nofollow" target="_blank">http://www.sraoss.co.jp</a><br></div><div dir="ltr"><br></div><div dir="ltr">From: Pierre Timmermans <<a href="mailto:ptim007@yahoo.com" rel="nofollow" target="_blank">ptim007@yahoo.com</a>><br></div><div dir="ltr">Subject: Re: [pgpool-general: 6435] Re: follow_master_command executed on node shown as down (one of unrecovered masters from previous failover)<br></div><div dir="ltr">Date: Fri, 1 Mar 2019 21:54:17 +0000 (UTC)<br></div><div dir="ltr">Message-ID: <<a href="mailto:654470371.7835532.1551477257916@mail.yahoo.com" rel="nofollow" target="_blank">654470371.7835532.1551477257916@mail.yahoo.com</a>><br></div><div dir="ltr"><br></div><div dir="ltr">> It is probably a good idea to force old primary to shut down but it is not always possible, if for example the primary node gets shutdown then the failover script will not be able to ssh into it and kill the old primary. If the old server comes back online then there is a degenerated master. I have a cron job that checks for degenerated master (and for detached standby) and re-instate it if possible, but I am sure there is always a risk of edge cases...<br></div><div dir="ltr">> Pierre <br></div><div dir="ltr">> <br></div><div dir="ltr">> On Friday, March 1, 2019, 7:35:12 PM GMT+1, Andre Piwoni <<a href="mailto:apiwoni@webmd.net" rel="nofollow" target="_blank">apiwoni@webmd.net</a>> wrote: <br></div><div dir="ltr">> <br></div><div dir="ltr">> I just realized that I already handled the case of re-start that triggered failover in another way. Mainly, before promoting new node to master in failover script I am forcing old primary to be shut down. So even if I do restart of the primary and failover occurs it will shut down restarted old primary.Anyway, it doesn't hurt to have that check in follow_master script in case rebooting machine restarts old primary etc.<br></div><div dir="ltr">> On Fri, Mar 1, 2019 at 9:58 AM Andre Piwoni <<a href="mailto:apiwoni@webmd.net" rel="nofollow" target="_blank">apiwoni@webmd.net</a>> wrote:<br></div><div dir="ltr">> <br></div><div dir="ltr">> I agree. This shouldn't be so complicated.<br></div><div dir="ltr">> Since I'm using sed to repoint slave in follow_master script by updating recovery.conf if the command fails I'm not re-starting and re-attaching the node. Kill two birds with one stone :-)<br></div><div dir="ltr">> Here'w what I'm testing now:ssh -o StrictHostKeyChecking=no -i /var/lib/pgsql/.ssh/id_rsa postgres@{detached_node_host} -T "sed -i 's/host=.*sslmode=/host=${new_master_node_host} port=5432 sslmode=/g' /var/lib/pgsql/10/data/recovery.conf" >> $LOGFILE<br></div><div dir="ltr">> repoint_status=$?if [ ${repoint_status} -eq 0 ]; then //restart //reattachelse // WARNING: this could be restarted master so there's no recovery.conf // CONSIDERATION: Should I shut it down since I don't want to have two masters running even though Pgpool load balances one???fi<br></div><div dir="ltr">> On Fri, Mar 1, 2019 at 9:44 AM Pierre Timmermans <<a href="mailto:ptim007@yahoo.com" rel="nofollow" target="_blank">ptim007@yahoo.com</a>> wrote:<br></div><div dir="ltr">> <br></div><div dir="ltr">> Thank you, it makes sense indeed and I also like to have a relatively long "grace" delay via the health check interval so that If the primary restarts quickly enough there is no failover<br></div><div dir="ltr">> For the case where there is a degenerated master, I have added this code in the follow_master script, it seems to work fine in my tests:<br></div><div dir="ltr">> <br></div><div dir="ltr">> ssh_options="ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no"<br></div><div dir="ltr">> <br></div><div dir="ltr">> in_reco=$( $ssh_options postgres@${HOSTNAME} 'psql -t -c "select pg_is_in_recovery();"' | head -1 | awk '{print $1}' )if [ "a${in_reco}" != "a" ] ; then<br></div><div dir="ltr">> echo "Node $HOSTNAME is not in recovery, probably a degenerated master, skip it" | tee -a $LOGFILE<br></div><div dir="ltr">> exit 0<br></div><div dir="ltr">> fi<br></div><div dir="ltr">> <br></div><div dir="ltr">> At the end I believe that pgpool algorithm to choose a primary node (always the node with the lowest id) is the root cause of the problem: pgpool should select the most adequate node (the node that is in recovery and with the lowest gap). Unfortunately I cannot code in "C", otherwise I would contribute.<br></div><div dir="ltr">> Pierre <br></div><div dir="ltr">> <br></div><div dir="ltr">> On Friday, March 1, 2019, 5:07:06 PM GMT+1, Andre Piwoni <<a href="mailto:apiwoni@webmd.net" rel="nofollow" target="_blank">apiwoni@webmd.net</a>> wrote: <br></div><div dir="ltr">> <br></div><div dir="ltr">> FYI,<br></div><div dir="ltr">> One of the things that I have done to minimize impact of restarting the primary is using health check where max_retries x retry_delay_interval allows enough time for the primary to be restarted without triggering failover which may take more time time than restart itself. This is with disabled fail_over_on_backend_error<br></div><div dir="ltr">> Andre<br></div><div dir="ltr">> On Fri, Mar 1, 2019 at 7:58 AM Andre Piwoni <<a href="mailto:apiwoni@webmd.net" rel="nofollow" target="_blank">apiwoni@webmd.net</a>> wrote:<br></div><div dir="ltr">> <br></div><div dir="ltr">> Hi Pierre,<br></div><div dir="ltr">> Hmmm? I have not covered the case you described which is restart of the primary on node 0, resulting failover ans subsequent restart of new primary on node 1 which results in calling follow_master on node 0. In my case I was shutting down node 0 which resulted in follow_master being called on it after second failover since I was not checking if node 0 was running. In your case, node 0 is running since it has been restarted.<br></div><div dir="ltr">> Here's part of my script that I have to improve given your case:<br></div><div dir="ltr">> ssh -o StrictHostKeyChecking=no -i /var/lib/pgsql/.ssh/id_rsa postgres@${detached_node_host} -T "/usr/pgsql-10/bin/pgctl -D /var/lib/pgsql/10/data status" | grep "is running"running_status=$?<br></div><div dir="ltr">> if [ ${running_status} -eq 0 ]; then // TODO: Check if recovery.conf exists or pg_is_in_recovery() on ${detached_node_host} and exit if this is not a slave node // repoint to new master ${new_master_node_host} // restart ${detached_node_host} // reattach restarted node with pcp_attach_nodeelse // do nothing since this could be old slave or primary that needs to be recovered or node in maintenance mode etc.fi<br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> On Fri, Mar 1, 2019 at 3:28 AM Pierre Timmermans <<a href="mailto:ptim007@yahoo.com" rel="nofollow" target="_blank">ptim007@yahoo.com</a>> wrote:<br></div><div dir="ltr">> <br></div><div dir="ltr">> Hi<br></div><div dir="ltr">> Same issue for me but I am not sure how to fix it. Andre can you tell exactly how you check ?<br></div><div dir="ltr">> I cannot add a test using pcp_node_info to check that the status is up, because then follow_master is never doing something. Indeed, in my case, when the follow_master is executed the status of the target node is always down, so my script does the standby follow command and then a pcp_attach_node.<br></div><div dir="ltr">> To solve the issue now I added a check that the command select pg_is_in_recovery(); returns "t" on the node, if it returns "f" then I can assume it is a degenerated master and I don't execute the follow_master command.<br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> So my use case is this<br></div><div dir="ltr">> <br></div><div dir="ltr">> 1. node 0 is primary, node 1 and node 2 are standby2. node 0 is restarted, node 1 becomes primary and node 2 follows the new primary (thanks to folllow_master). In follow_master of node 2 I have to do pcp_attach_node after because the status of the node is down 3. in the meantime node 0 has rebooted, the db is started on node 0 but it is down in pgpool and its role is standby (it is a degenerated master)4. node 1 is restarted, pgpool executes failover on node 2 and follow_master on node 0 => the follow_master on node 0 breaks everything because after that node 0 becomes a primary again <br></div><div dir="ltr">> Thanks and regards<br></div><div dir="ltr">> Pierre <br></div><div dir="ltr">> <br></div><div dir="ltr">> On Monday, February 25, 2019, 5:35:11 PM GMT+1, Andre Piwoni <<a href="mailto:apiwoni@webmd.net" rel="nofollow" target="_blank">apiwoni@webmd.net</a>> wrote: <br></div><div dir="ltr">> <br></div><div dir="ltr">> I have already put that check in place.<br></div><div dir="ltr">> Thank you for confirming.<br></div><div dir="ltr">> On Sat, Feb 23, 2019 at 11:56 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" rel="nofollow" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br></div><div dir="ltr">> <br></div><div dir="ltr">> Sorry, I was wrong. A follow_master_command will be executed against<br></div><div dir="ltr">> the down node as well. So you need to check whether target PostgreSQL<br></div><div dir="ltr">> node is running in the follow_master_commdn. If it's not, you can skip<br></div><div dir="ltr">> the node.<br></div><div dir="ltr">> <br></div><div dir="ltr">> Best regards,<br></div><div dir="ltr">> --<br></div><div dir="ltr">> Tatsuo Ishii<br></div><div dir="ltr">> SRA OSS, Inc. Japan<br></div><div dir="ltr">> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="nofollow" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br></div><div dir="ltr">> Japanese:<a href="http://www.sraoss.co.jp" rel="nofollow" target="_blank">http://www.sraoss.co.jp</a><br></div><div dir="ltr">> <br></div><div dir="ltr">>> I have added pg_ctl status check to ensure no action is taken when node is<br></div><div dir="ltr">>> down but I'll check 3.7.8 version.<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> Here's the Pgpool log from the time node2 is shutdown to time node1(already<br></div><div dir="ltr">>> dead old primary) received follow master command.<br></div><div dir="ltr">>> Sorry for double date logging. I'm also including self-explanatory<br></div><div dir="ltr">>> failover.log that I my failover and follow_master scripts generated.<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> Arguments passed to scripts for your reference.<br></div><div dir="ltr">>> failover.sh %d %h %p %D %M %P %m %H %r %R<br></div><div dir="ltr">>> follow_master.sh %d %h %p %D %M %P %m %H %r %R<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> Pool status before shutdown of node 2:<br></div><div dir="ltr">>> postgres=> show pool_nodes;<br></div><div dir="ltr">>> node_id | hostname | port | status | lb_weight | role<br></div><div dir="ltr">>> | select_cnt | load_balance_node | replication_delay<br></div><div dir="ltr">>> ---------+----------------------------+------+--------+-----------+---------+------------+-------------------+-------------------<br></div><div dir="ltr">>> 0 | pg-hdp-node1.kitchen.local | 5432 | down | 0.333333 | standby<br></div><div dir="ltr">>> | 0 | false | 0<br></div><div dir="ltr">>> 1 | pg-hdp-node2.kitchen.local | 5432 | up | 0.333333 | primary<br></div><div dir="ltr">>> | 0 | false | 0<br></div><div dir="ltr">>> 2 | pg-hdp-node3.kitchen.local | 5432 | up | 0.333333 | standby<br></div><div dir="ltr">>> | 0 | true | 0<br></div><div dir="ltr">>> (3 rows)<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> Pgpool log<br></div><div dir="ltr">>> Feb 22 10:43:27 pg-hdp-node3 pgpool[12437]: [126-1] 2019-02-22 10:43:27:<br></div><div dir="ltr">>> pid 12437: LOG: failed to connect to PostgreSQL server on<br></div><div dir="ltr">>> "pg-hdp-node2.kitchen.local:5432", getsockopt() detected error "Connection<br></div><div dir="ltr">>> refused"<br></div><div dir="ltr">>> Feb 22 10:43:27 pg-hdp-node3 pgpool[12437]: [127-1] 2019-02-22 10:43:27:<br></div><div dir="ltr">>> pid 12437: ERROR: failed to make persistent db connection<br></div><div dir="ltr">>> Feb 22 10:43:27 pg-hdp-node3 pgpool[12437]: [127-2] 2019-02-22 10:43:27:<br></div><div dir="ltr">>> pid 12437: DETAIL: connection to host:"pg-hdp-node2.kitchen.local:5432"<br></div><div dir="ltr">>> failed<br></div><div dir="ltr">>> Feb 22 10:43:37 pg-hdp-node3 pgpool[12437]: [128-1] 2019-02-22 10:43:37:<br></div><div dir="ltr">>> pid 12437: ERROR: Failed to check replication time lag<br></div><div dir="ltr">>> Feb 22 10:43:37 pg-hdp-node3 pgpool[12437]: [128-2] 2019-02-22 10:43:37:<br></div><div dir="ltr">>> pid 12437: DETAIL: No persistent db connection for the node 1<br></div><div dir="ltr">>> Feb 22 10:43:37 pg-hdp-node3 pgpool[12437]: [128-3] 2019-02-22 10:43:37:<br></div><div dir="ltr">>> pid 12437: HINT: check sr_check_user and sr_check_password<br></div><div dir="ltr">>> Feb 22 10:43:37 pg-hdp-node3 pgpool[12437]: [128-4] 2019-02-22 10:43:37:<br></div><div dir="ltr">>> pid 12437: CONTEXT: while checking replication time lag<br></div><div dir="ltr">>> Feb 22 10:43:37 pg-hdp-node3 pgpool[12437]: [129-1] 2019-02-22 10:43:37:<br></div><div dir="ltr">>> pid 12437: LOG: failed to connect to PostgreSQL server on<br></div><div dir="ltr">>> "pg-hdp-node2.kitchen.local:5432", getsockopt() detected error "Connection<br></div><div dir="ltr">>> refused"<br></div><div dir="ltr">>> Feb 22 10:43:37 pg-hdp-node3 pgpool[12437]: [130-1] 2019-02-22 10:43:37:<br></div><div dir="ltr">>> pid 12437: ERROR: failed to make persistent db connection<br></div><div dir="ltr">>> Feb 22 10:43:37 pg-hdp-node3 pgpool[12437]: [130-2] 2019-02-22 10:43:37:<br></div><div dir="ltr">>> pid 12437: DETAIL: connection to host:"pg-hdp-node2.kitchen.local:5432"<br></div><div dir="ltr">>> failed<br></div><div dir="ltr">>> Feb 22 10:43:45 pg-hdp-node3 pgpool[7786]: [6-1] 2019-02-22 10:43:45: pid<br></div><div dir="ltr">>> 7786: LOG: failed to connect to PostgreSQL server on<br></div><div dir="ltr">>> "pg-hdp-node2.kitchen.local:5432", getsockopt() detected error "Connection<br></div><div dir="ltr">>> refused"<br></div><div dir="ltr">>> Feb 22 10:43:45 pg-hdp-node3 pgpool[7786]: [7-1] 2019-02-22 10:43:45: pid<br></div><div dir="ltr">>> 7786: ERROR: failed to make persistent db connection<br></div><div dir="ltr">>> Feb 22 10:43:45 pg-hdp-node3 pgpool[7786]: [7-2] 2019-02-22 10:43:45: pid<br></div><div dir="ltr">>> 7786: DETAIL: connection to host:"pg-hdp-node2.kitchen.local:5432" failed<br></div><div dir="ltr">>> Feb 22 10:43:45 pg-hdp-node3 pgpool[7786]: [8-1] 2019-02-22 10:43:45: pid<br></div><div dir="ltr">>> 7786: LOG: health check retrying on DB node: 1 (round:1)<br></div><div dir="ltr">>> Feb 22 10:43:47 pg-hdp-node3 pgpool[12437]: [131-1] 2019-02-22 10:43:47:<br></div><div dir="ltr">>> pid 12437: ERROR: Failed to check replication time lag<br></div><div dir="ltr">>> Feb 22 10:43:47 pg-hdp-node3 pgpool[12437]: [131-2] 2019-02-22 10:43:47:<br></div><div dir="ltr">>> pid 12437: DETAIL: No persistent db connection for the node 1<br></div><div dir="ltr">>> Feb 22 10:43:47 pg-hdp-node3 pgpool[12437]: [131-3] 2019-02-22 10:43:47:<br></div><div dir="ltr">>> pid 12437: HINT: check sr_check_user and sr_check_password<br></div><div dir="ltr">>> Feb 22 10:43:47 pg-hdp-node3 pgpool[12437]: [131-4] 2019-02-22 10:43:47:<br></div><div dir="ltr">>> pid 12437: CONTEXT: while checking replication time lag<br></div><div dir="ltr">>> Feb 22 10:43:47 pg-hdp-node3 pgpool[12437]: [132-1] 2019-02-22 10:43:47:<br></div><div dir="ltr">>> pid 12437: LOG: failed to connect to PostgreSQL server on<br></div><div dir="ltr">>> "pg-hdp-node2.kitchen.local:5432", getsockopt() detected error "Connection<br></div><div dir="ltr">>> refused"<br></div><div dir="ltr">>> Feb 22 10:43:47 pg-hdp-node3 pgpool[12437]: [133-1] 2019-02-22 10:43:47:<br></div><div dir="ltr">>> pid 12437: ERROR: failed to make persistent db connection<br></div><div dir="ltr">>> Feb 22 10:43:47 pg-hdp-node3 pgpool[12437]: [133-2] 2019-02-22 10:43:47:<br></div><div dir="ltr">>> pid 12437: DETAIL: connection to host:"pg-hdp-node2.kitchen.local:5432"<br></div><div dir="ltr">>> failed<br></div><div dir="ltr">>> Feb 22 10:43:48 pg-hdp-node3 pgpool[7786]: [9-1] 2019-02-22 10:43:48: pid<br></div><div dir="ltr">>> 7786: LOG: failed to connect to PostgreSQL server on<br></div><div dir="ltr">>> "pg-hdp-node2.kitchen.local:5432", getsockopt() detected error "Connection<br></div><div dir="ltr">>> refused"<br></div><div dir="ltr">>> Feb 22 10:43:48 pg-hdp-node3 pgpool[7786]: [10-1] 2019-02-22 10:43:48: pid<br></div><div dir="ltr">>> 7786: ERROR: failed to make persistent db connection<br></div><div dir="ltr">>> Feb 22 10:43:48 pg-hdp-node3 pgpool[7786]: [10-2] 2019-02-22 10:43:48: pid<br></div><div dir="ltr">>> 7786: DETAIL: connection to host:"pg-hdp-node2.kitchen.local:5432" failed<br></div><div dir="ltr">>> Feb 22 10:43:48 pg-hdp-node3 pgpool[7786]: [11-1] 2019-02-22 10:43:48: pid<br></div><div dir="ltr">>> 7786: LOG: health check retrying on DB node: 1 (round:2)<br></div><div dir="ltr">>> Feb 22 10:43:51 pg-hdp-node3 pgpool[7786]: [12-1] 2019-02-22 10:43:51: pid<br></div><div dir="ltr">>> 7786: LOG: failed to connect to PostgreSQL server on<br></div><div dir="ltr">>> "pg-hdp-node2.kitchen.local:5432", getsockopt() detected error "Connection<br></div><div dir="ltr">>> refused"<br></div><div dir="ltr">>> Feb 22 10:43:51 pg-hdp-node3 pgpool[7786]: [13-1] 2019-02-22 10:43:51: pid<br></div><div dir="ltr">>> 7786: ERROR: failed to make persistent db connection<br></div><div dir="ltr">>> Feb 22 10:43:51 pg-hdp-node3 pgpool[7786]: [13-2] 2019-02-22 10:43:51: pid<br></div><div dir="ltr">>> 7786: DETAIL: connection to host:"pg-hdp-node2.kitchen.local:5432" failed<br></div><div dir="ltr">>> Feb 22 10:43:51 pg-hdp-node3 pgpool[7786]: [14-1] 2019-02-22 10:43:51: pid<br></div><div dir="ltr">>> 7786: LOG: health check retrying on DB node: 1 (round:3)<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7786]: [15-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7786: LOG: failed to connect to PostgreSQL server on<br></div><div dir="ltr">>> "pg-hdp-node2.kitchen.local:5432", getsockopt() detected error "Connection<br></div><div dir="ltr">>> refused"<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7786]: [16-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7786: ERROR: failed to make persistent db connection<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7786]: [16-2] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7786: DETAIL: connection to host:"pg-hdp-node2.kitchen.local:5432" failed<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7786]: [17-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7786: LOG: health check failed on node 1 (timeout:0)<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7786]: [18-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7786: LOG: received degenerate backend request for node_id: 1 from pid<br></div><div dir="ltr">>> [7786]<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7746]: [253-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7746: LOG: Pgpool-II parent process has received failover request<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7746]: [254-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7746: LOG: starting degeneration. shutdown host<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local(5432)<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7746]: [255-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7746: LOG: Restart all children<br></div><div dir="ltr">>> Feb 22 10:43:54 pg-hdp-node3 pgpool[7746]: [256-1] 2019-02-22 10:43:54: pid<br></div><div dir="ltr">>> 7746: LOG: execute command: /etc/pgpool-II/failover.sh 1<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local 5432 /var/lib/pgsql/10/data 1 1 2<br></div><div dir="ltr">>> pg-hdp-node3.kitchen.local 5432 /var/lib/pgsql/10/data<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [257-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: find_primary_node_repeatedly: waiting for finding a primary node<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [258-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: find_primary_node: checking backend no 0<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [259-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: find_primary_node: checking backend no 1<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [260-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: find_primary_node: checking backend no 2<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [261-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: find_primary_node: primary node id is 2<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [262-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: starting follow degeneration. shutdown host<br></div><div dir="ltr">>> pg-hdp-node1.kitchen.local(5432)<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [263-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: starting follow degeneration. shutdown host<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local(5432)<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [264-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: failover: 2 follow backends have been degenerated<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [265-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: failover: set new primary node: 2<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [266-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: failover: set new master node: 2<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[7746]: [267-1] 2019-02-22 10:43:55: pid<br></div><div dir="ltr">>> 7746: LOG: failover done. shutdown host pg-hdp-node2.kitchen.local(5432)<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12437]: [134-1] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12437: ERROR: Failed to check replication time lag<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12437]: [134-2] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12437: DETAIL: No persistent db connection for the node 1<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12437]: [134-3] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12437: HINT: check sr_check_user and sr_check_password<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12437]: [134-4] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12437: CONTEXT: while checking replication time lag<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12437]: [135-1] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12437: LOG: worker process received restart request<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12774]: [267-1] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12774: LOG: failback event detected<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12774]: [267-2] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12774: DETAIL: restarting myself<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12742]: [265-1] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12742: LOG: start triggering follow command.<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12742]: [266-1] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12742: LOG: execute command: /etc/pgpool-II/follow_master.sh 0<br></div><div dir="ltr">>> pg-hdp-node1.kitchen.local 5432 /var/lib/pgsql/10/data 1 1 2<br></div><div dir="ltr">>> pg-hdp-node3.kitchen.local 5432 /var/lib/pgsql/10/data<br></div><div dir="ltr">>> Feb 22 10:43:55 pg-hdp-node3 pgpool[12742]: [267-1] 2019-02-22 10:43:55:<br></div><div dir="ltr">>> pid 12742: LOG: execute command: /etc/pgpool-II/follow_master.sh 1<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local 5432 /var/lib/pgsql/10/data 1 1 2<br></div><div dir="ltr">>> pg-hdp-node3.kitchen.local 5432 /var/lib/pgsql/10/data<br></div><div dir="ltr">>> Feb 22 10:43:56 pg-hdp-node3 pgpool[12436]: [60-1] 2019-02-22 10:43:56: pid<br></div><div dir="ltr">>> 12436: LOG: restart request received in pcp child process<br></div><div dir="ltr">>> Feb 22 10:43:56 pg-hdp-node3 pgpool[7746]: [268-1] 2019-02-22 10:43:56: pid<br></div><div dir="ltr">>> 7746: LOG: PCP child 12436 exits with status 0 in failover()<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> Pgpool self-explanatory failover.log<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> 2019-02-22 10:43:54.893 PST Executing failover script ...<br></div><div dir="ltr">>> 2019-02-22 10:43:54.895 PST Script arguments:<br></div><div dir="ltr">>> failed_node_id 1<br></div><div dir="ltr">>> failed_node_host pg-hdp-node2.kitchen.local<br></div><div dir="ltr">>> failed_node_port 5432<br></div><div dir="ltr">>> failed_node_pgdata /var/lib/pgsql/10/data<br></div><div dir="ltr">>> old_primary_node_id 1<br></div><div dir="ltr">>> old_master_node_id 1<br></div><div dir="ltr">>> new_master_node_id 2<br></div><div dir="ltr">>> new_master_node_host pg-hdp-node3.kitchen.local<br></div><div dir="ltr">>> new_master_node_port 5432<br></div><div dir="ltr">>> new_master_node_pgdata /var/lib/pgsql/10/data<br></div><div dir="ltr">>> 2019-02-22 10:43:54.897 PST Primary node running on<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local host is unresponsive or have died<br></div><div dir="ltr">>> 2019-02-22 10:43:54.898 PST Attempting to stop primary node running on<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local host before promoting slave as the new primary<br></div><div dir="ltr">>> 2019-02-22 10:43:54.899 PST ssh -o StrictHostKeyChecking=no -i<br></div><div dir="ltr">>> /var/lib/pgsql/.ssh/id_rsa <a href="mailto:postgres@pg-hdp-node2.kitchen.local" rel="nofollow" target="_blank">postgres@pg-hdp-node2.kitchen.local</a> -T<br></div><div dir="ltr">>> /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data stop -m fast<br></div><div dir="ltr">>> 2019-02-22 10:43:55.151 PST Promoting pg-hdp-node3.kitchen.local host as<br></div><div dir="ltr">>> the new primary<br></div><div dir="ltr">>> 2019-02-22 10:43:55.153 PST ssh -o StrictHostKeyChecking=no -i<br></div><div dir="ltr">>> /var/lib/pgsql/.ssh/id_rsa <a href="mailto:postgres@pg-hdp-node3.kitchen.local" rel="nofollow" target="_blank">postgres@pg-hdp-node3.kitchen.local</a> -T<br></div><div dir="ltr">>> /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data promote<br></div><div dir="ltr">>> waiting for server to promote.... done<br></div><div dir="ltr">>> server promoted<br></div><div dir="ltr">>> 2019-02-22 10:43:55.532 PST Completed executing failover<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> 2019-02-22 10:43:55.564 PST Executing follow master script ...<br></div><div dir="ltr">>> 2019-02-22 10:43:55.566 PST Script arguments<br></div><div dir="ltr">>> detached_node_id 0<br></div><div dir="ltr">>> detached_node_host pg-hdp-node1.kitchen.local<br></div><div dir="ltr">>> detached_node_port 5432<br></div><div dir="ltr">>> detached_node_pgdata /var/lib/pgsql/10/data<br></div><div dir="ltr">>> old_primary_node_id 1<br></div><div dir="ltr">>> old_master_node_id 1<br></div><div dir="ltr">>> new_master_node_id 2<br></div><div dir="ltr">>> new_master_node_host pg-hdp-node3.kitchen.local<br></div><div dir="ltr">>> new_master_node_port 5432<br></div><div dir="ltr">>> new_master_node_pgdata /var/lib/pgsql/10/data<br></div><div dir="ltr">>> 2019-02-22 10:43:55.567 PST Checking if server is running on<br></div><div dir="ltr">>> pg-hdp-node1.kitchen.local host<br></div><div dir="ltr">>> 2019-02-22 10:43:55.569 PST ssh -o StrictHostKeyChecking=no -i<br></div><div dir="ltr">>> /var/lib/pgsql/.ssh/id_rsa <a href="mailto:postgres@pg-hdp-node1.kitchen.local" rel="nofollow" target="_blank">postgres@pg-hdp-node1.kitchen.local</a> -T<br></div><div dir="ltr">>> /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data status<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> <br></div><div dir="ltr">>> pg_ctl: no server running<br></div><div dir="ltr">>> 2019-02-22 10:43:55.823 PST Node on pg-hdp-node1.kitchen.local host is not<br></div><div dir="ltr">>> running. It could be old slave or primary that needs to be recovered.<br></div><div dir="ltr">>> 2019-02-22 10:43:55.824 PST Completed executing follow master script<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> 2019-02-22 10:43:55.829 PST Executing follow master script ...<br></div><div dir="ltr">>> 2019-02-22 10:43:55.830 PST Script arguments<br></div><div dir="ltr">>> detached_node_id 1<br></div><div dir="ltr">>> detached_node_host pg-hdp-node2.kitchen.local<br></div><div dir="ltr">>> detached_node_port 5432<br></div><div dir="ltr">>> detached_node_pgdata /var/lib/pgsql/10/data<br></div><div dir="ltr">>> old_primary_node_id 1<br></div><div dir="ltr">>> old_master_node_id 1<br></div><div dir="ltr">>> new_master_node_id 2<br></div><div dir="ltr">>> new_master_node_host pg-hdp-node3.kitchen.local<br></div><div dir="ltr">>> new_master_node_port 5432<br></div><div dir="ltr">>> new_master_node_pgdata /var/lib/pgsql/10/data<br></div><div dir="ltr">>> 2019-02-22 10:43:55.831 PST Detached node on pg-hdp-node2.kitchen.local<br></div><div dir="ltr">>> host is the the old primary node<br></div><div dir="ltr">>> 2019-02-22 10:43:55.833 PST Slave can be created from old primary node by<br></div><div dir="ltr">>> deleting PG_DATA directory under /var/lib/pgsql/10/data on<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local host and re-running Chef client<br></div><div dir="ltr">>> 2019-02-22 10:43:55.834 PST Slave can be recovered from old primary node by<br></div><div dir="ltr">>> running /usr/pgsql-10/bin/pg_rewind -D /var/lib/pgsql/10/data<br></div><div dir="ltr">>> --source-server="port=5432 host=pg-hdp-node3.kitchen.local" command on<br></div><div dir="ltr">>> pg-hdp-node2.kitchen.local host as postgres user<br></div><div dir="ltr">>> 2019-02-22 10:43:55.835 PST After successful pg_rewind run cp<br></div><div dir="ltr">>> /var/lib/pgsql/10/data/recovery.done /var/lib/pgsql/10/data/recovery.conf,<br></div><div dir="ltr">>> ensure host connection string points to pg-hdp-node3.kitchen.local, start<br></div><div dir="ltr">>> PostgreSQL and attach it to pgpool<br></div><div dir="ltr">>> 2019-02-22 10:43:55.836 PST Completed executing follow master script<br></div><div dir="ltr">>> <br></div><div dir="ltr">>> On Thu, Feb 21, 2019 at 4:47 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" rel="nofollow" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br></div><div dir="ltr">>> <br></div><div dir="ltr">>>> > Is this correct behavior?<br></div><div dir="ltr">>>> ><br></div><div dir="ltr">>>> > In 3-node setup, node1(primary) is shutdown, failover is executed and<br></div><div dir="ltr">>>> node2<br></div><div dir="ltr">>>> > becomes new primary and node3 follows new primary on node2.<br></div><div dir="ltr">>>> > Now, node2(new primary) is shutdown, failover is executed and node3<br></div><div dir="ltr">>>> becomes<br></div><div dir="ltr">>>> > new primary but fallow_master_command is executed on node1 even though it<br></div><div dir="ltr">>>> > is reported as down.<br></div><div dir="ltr">>>><br></div><div dir="ltr">>>> No. follow master command should not be executed on an already-down<br></div><div dir="ltr">>>> node (in this case node1).<br></div><div dir="ltr">>>><br></div><div dir="ltr">>>> > It happens that my script repoints node1 and restarts it which breaks<br></div><div dir="ltr">>>> hell<br></div><div dir="ltr">>>> > because node1 was never recovered after being shutdown.<br></div><div dir="ltr">>>> ><br></div><div dir="ltr">>>> > I'm on PgPool 3.7.4.<br></div><div dir="ltr">>>><br></div><div dir="ltr">>>> Can you share the log from when node2 was shutdown to when node1 was<br></div><div dir="ltr">>>> recovered by your follow master command?<br></div><div dir="ltr">>>><br></div><div dir="ltr">>>> In the mean time 3.7.4 is not the latest one. Can you try with the<br></div><div dir="ltr">>>> latest one? (3.7.8).<br></div><div dir="ltr">>>><br></div><div dir="ltr">>>> Best regards,<br></div><div dir="ltr">>>> --<br></div><div dir="ltr">>>> Tatsuo Ishii<br></div><div dir="ltr">>>> SRA OSS, Inc. Japan<br></div><div dir="ltr">>>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="nofollow" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br></div><div dir="ltr">>>> Japanese:<a href="http://www.sraoss.co.jp" rel="nofollow" target="_blank">http://www.sraoss.co.jp</a><br></div><div dir="ltr">>>><br></div><div dir="ltr">>> <br></div><div dir="ltr">>> <br></div><div dir="ltr">>> -- <br></div><div dir="ltr">>> <br></div><div dir="ltr">>> *Andre Piwoni*<br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> _______________________________________________<br></div><div dir="ltr">> pgpool-general mailing list<br></div><div dir="ltr">> <a href="mailto:pgpool-general@pgpool.net" rel="nofollow" target="_blank">pgpool-general@pgpool.net</a><br></div><div dir="ltr">> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="nofollow" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> -- <br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> -- <br></div><div dir="ltr">> <br></div><div dir="ltr">> Andre Piwoni<br></div><div dir="ltr">> <br></div><div dir="ltr">> Sr. Software Developer,BI/Database<br></div><div dir="ltr">> <br></div><div dir="ltr">> WebMD Health Services<br></div><div dir="ltr">> <br></div><div dir="ltr">> Mobile: 801.541.4722<br></div><div dir="ltr">> <br></div><div dir="ltr">> www.webmdhealthservices.com<br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> <br></div><div dir="ltr">> -- <br></div><div dir="ltr">> <br></div><div dir="ltr">> Andre Piwoni<br></div><div dir="ltr">> <br></div><div dir="ltr">> Sr. Software Developer,BI/Database<br></div><div dir="ltr">> <br></div><div dir="ltr">> WebMD Health Services<br></div><div dir="ltr">> <br></div><div dir="ltr">> Mobile: 801.541.4722<br></div><div dir="ltr">> <br></div><div dir="ltr">> www.webmdhealthservices.com<br></div><div dir="ltr">> </div></div>
</div>
</div></body></html>