<div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">Hi guys</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">After executed a manual failover I have been recovered the repmgr replication between s1 (master - read/write) and s2 (standby - read only):</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">repmgr cluster show</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">Role | Name | Upstream | Connection String</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">----------+------|----------|----------------------------------------------</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">* master | s1 | | host=192.168.0.1 dbname=repmgr user=repmgr</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;&nbsp;standby | s2 | s1 | host=192.168.0.2 dbname=repmgr user=repmgr</span></span></span></span><br></div><div><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">So, the problem is after swapping the active nodes using repmgr (1. stop postgres on standby, 2. promote the master, 3. clone the standby), pgpool can't recognize the nodes correctly and shows me the master node as down:</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">show pool_nodes;<span>&nbsp;</span></span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay<span>&nbsp;</span></span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;0 | 192.168.0.1 | 5432 | down | 0.500000 | standby | 0 | false | 0</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;1 | 192.168.0.2 | 5432 | up | 0.500000 | standby | 0 | true | 0</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">The replication is working fine and repmgr shows me everything is correct:</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">repmgr cluster show</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">Role | Name | Upstream | Connection String</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">----------+------|----------|----------------------------------------------</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">* master | s1 | | host=192.168.0.1 dbname=repmgr user=repmgr</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;&nbsp;standby | s2 | s1 | host=192.168.0.2 dbname=repmgr user=repmgr</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">So, I have tried to fix pgpool using pcp commands without success, and restarted pgpool service:</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">Detach command is not accepted:</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">pcp_detach_node 0 -h localhost -U postgres</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">ERROR: invalid degenerate backend request, node id : 0 status: [3] is not valid for failover</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">I can promote the node 0 (down) but nothing happens:</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">pcp_promote_node 0 -U postgres -h localhost</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">pcp_promote_node -- Command Successful</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">show pool_nodes</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay<span>&nbsp;</span></span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;0 | 192.168.0.1 | 5432 | down | 0.500000 | standby | 0 | false | 0</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">&nbsp;1 | 192.168.0.2 | 5432 | up | 0.500000 | standby | 3 | true | 0</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">(2 rows)</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">And I can't recovery node 1 (standby):</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">pcp_recovery_node 1 -U postgres -h localhost</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">ERROR: process recovery request failed</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">DETAIL: primary server cannot be recovered by online recovery.</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">Here is the main config on pgpool.conf</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">backend_flag0 = 'ALLOW_TO_FAILOVER'</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">backend_flag1 = 'ALLOW_TO_FAILOVER'</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">load_balance_mode = on</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">master_slave_mode = on</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">master_slave_sub_mode = 'stream'</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">failover_command = ''</span></span></span></span><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">recovery_1st_stage_command = ''</span></span></span></span><br></div><div><br></div><div><span class="highlight" style="background-color:rgb(255, 255, 255)"><span class="colour" style="color:rgb(57, 57, 57)"><span class="font" style="font-family:&quot;Open Sans&quot;"><span class="size" style="font-size:13px">Please, help me. I don't know what I am doing wrong</span></span></span></span><br></div>