<div dir="ltr">Hi Tatsuo,<div><br></div><div>Thanks for your email. Yes, I have read the message and fixed the pcp authentication issue. </div><div><br></div><div>Now, I'm facing a different issue. I will try to explain it step by step.</div><div><br></div><div>Sequence:<br>1. pg1 is primary and pg2 is standby<br>2. Stop postgres service on pg1<br>3. Pgpool executes failover<br>4. Now pg2 is my primary. I verified all the configurations.<br>5. Started postgres service on pg1<br>6. Trying to recover failed node<br></div><div><br></div><div>postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ pcp_recovery_node -h localhost -p 9898 -n 0<br>Password:<br>ERROR: process recovery request failed<br>DETAIL: primary server cannot be recovered by online recovery.<br></div><div><br></div><div>Log says verify_backend_node_status: decided node 0 is the true primary<br></div><div><br></div><div>This command hangs<br>psql -U postgres -h localhost -p 9999 --pset pager=off -c "show pool_nodes"<br></div><div><br></div><div>I would like to know how pgpool is considering node 0 which is pg1 as true primary ?<br></div><div><br></div><div>Then what did I do ?<br>1. Cleaned everything on pg1 and manually configured it as standby.<br>2. Started postgres service<br>3. Log says<br>2020-08-31 13:40:00: pid 2459: DEBUG: do_query: extended:0 query:"SELECT pg_is_in_recovery()"<br>2020-08-31 13:40:00: pid 2459: DEBUG: verify_backend_node_status: there's no standby node<br>2020-08-31 13:40:00: pid 2459: DEBUG: node status[0]: 0<br>2020-08-31 13:40:00: pid 2459: DEBUG: node status[1]: 1<br></div><div><br></div><div>This command executes now<br>postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost -p 9999 --pset pager=off -c "show pool_nodes"<br> node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change <br>---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------<br> 0 | pg1 | 5432 | down | 0.500000 | standby | 0 | false | 0 | 2020-08-31 13:35:44<br> 1 | pg2 | 5432 | up | 0.500000 | primary | 0 | true | 0 | 2020-08-31 13:35:44<br>(2 rows)<br></div><div><br></div><div>I checked between pg1 and pg2 and see that streaming replication is working.<br></div><div><br></div><div>Then I used pcp_attach_node to attach standby</div><div><br></div><div>postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ pcp_attach_node -h localhost -p 9898 -n 0<br>Password:<br>pcp_attach_node -- Command Successful<br>postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost -p 9999 --pset pager=off -c "show pool_nodes"<br> node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change <br>---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------<br> 0 | pg1 | 5432 | up | 0.500000 | standby | 0 | true | 0 | 2020-08-31 14:09:31<br> 1 | pg2 | 5432 | up | 0.500000 | primary | 0 | false | 0 | 2020-08-31 14:02:35<br>(2 rows)<br></div><div><br></div><div>Then I tried to perform a test operation and it executed successfully.</div><div>postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost -p 9999 --pset pager=off -c "create database covid"<br>CREATE DATABASE<br></div><div><br></div><div>Am I missing something in this failover process ? Please let me know if you need any additional details.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Sep 1, 2020 at 3:03 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Praveen,<br>
<br>
Have you read this message? I wonder if you have fixed the issue or not.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
From: Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
Subject: [pgpool-general: 7208] Re: Query regarding failover and recovery<br>
Date: Thu, 20 Aug 2020 08:51:04 +0900 (JST)<br>
Message-ID: <<a href="mailto:20200820.085104.891242161358675858.t-ishii@sraoss.co.jp" target="_blank">20200820.085104.891242161358675858.t-ishii@sraoss.co.jp</a>><br>
<br>
> The contents of pcp.conf looks incorrect.<br>
> <br>
>> postgres:md53175bce1d3201d16594cebf9d7eb3f9d<br>
> <br>
> The hashed password must not start with "md5".<br>
> <br>
> To create proper pcp password, please follow the instruction in the manual:<br>
> <a href="https://www.pgpool.net/docs/40/en/html/configuring-pcp-conf.html" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/40/en/html/configuring-pcp-conf.html</a><br>
> <br>
>> Hello,<br>
>> <br>
>> Thanks for the clarification. I'm trying to execute and getting below<br>
>> error. I'm attaching configs for your reference. Can you please help ?<br>
>> <br>
>> postgres@pgp1:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost -p 9999<br>
>> --pset pager=off -c "show pool_nodes"<br>
>> node_id | hostname | port | status | lb_weight | role | select_cnt |<br>
>> load_balance_node | replication_delay | last_status_change<br>
>> ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------<br>
>> 0 | pg1 | 5432 | down | 0.500000 | standby | 0 |<br>
>> false | 0 | 2020-08-19 09:02:46<br>
>> 1 | pg2 | 5432 | up | 0.500000 | primary | 0 |<br>
>> true | 0 | 2020-08-19 09:02:46<br>
>> (2 rows)<br>
>> <br>
>> postgres@pgp1:/etc/pgpool2/4.0.9$<br>
>> postgres@pgp1:/etc/pgpool2/4.0.9$<br>
>> postgres@pgp1:/etc/pgpool2/4.0.9$<br>
>> postgres@pgp1:/etc/pgpool2/4.0.9$ pcp_recovery_node -h localhost -p 9898 -n<br>
>> 0<br>
>> Password:<br>
>> FATAL: authentication failed for user "postgres"<br>
>> DETAIL: username and/or password does not match<br>
>> <br>
>> postgres@pgp1:/etc/pgpool2/4.0.9$<br>
>> <br>
>> <br>
>> On Wed, Aug 19, 2020 at 10:22 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>> <br>
>>> > I have 3 servers with two postgres (9.6) and one pgpool (4.0.9). Postgres<br>
>>> > is configured with streaming replication.<br>
>>> > When I manually stop postgres service on primary node, failover has<br>
>>> > happened successfully.<br>
>>> > Now I started postgres service on old primary node which is expected to<br>
>>> be<br>
>>> > converted as slave, pgpool is not triggering recovery_1st_stage_command =<br>
>>> > 'recovery_1st_stage.sh'<br>
>>> > May I know what could be the reason ?<br>
>>><br>
>>> That is an expected behavior. The node previously brought down is left<br>
>>> as "down" by pgoool. This is intentional. You need to issue<br>
>>> pcp_recovery_node against the node (previous primary node in your<br>
>>> case) to make it online again.<br>
>>><br>
>>> When a node is brought down, there might be a reason: for example<br>
>>> needed to repair the hardware. So in general it's not safe to<br>
>>> automatically restart the previously down node.<br>
>>><br>
>>> Best regards,<br>
>>> --<br>
>>> Tatsuo Ishii<br>
>>> SRA OSS, Inc. Japan<br>
>>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>>><br>
>> <br>
>> <br>
>> -- <br>
>> <br>
>> <br>
>> *Regards,*<br>
>> <br>
>> <br>
>> *K S Praveen KumarM: +91-9986855625 *<br>
> _______________________________________________<br>
> pgpool-general mailing list<br>
> <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><font style="font-family:"courier new",monospace" size="1"><b style="color:rgb(102,102,102)">Regards,<br><br></b></font><div style="color:rgb(102,102,102)"><font size="1"><b><font face="'comic sans ms', sans-serif"><font style="font-family:"courier new",monospace" size="1">K S Praveen Kumar<br>M: +91-9986855625 </font><br></font></b></font></div></div>