<div dir="ltr">Hi Tatsuo,<div><br></div><div>Thanks for letting me know the process. Just curious to know what will happen if pg1 reboots and comes back and in the meanwhile pg2 got promoted as master. What is the expectation in this situation ? </div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Sep 1, 2020 at 10:32 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">> Hi Tatsuo,<br>
> <br>
> Thanks for your email. Yes, I have read the message and fixed the pcp<br>
> authentication issue.<br>
> <br>
> Now, I'm facing a different issue. I will try to explain it step by step.<br>
> <br>
> 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>
> <br>
> postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ pcp_recovery_node -h<br>
> localhost -p 9898 -n 0<br>
> Password:<br>
> ERROR: process recovery request failed<br>
> DETAIL: primary server cannot be recovered by online recovery.<br>
<br>
You shoud have not started pg1 postgres at this<br>
point. pcp_recovery_node should not be executed on running node. See<br>
the manual:<br>
<br>
Note: The recovery target PostgreSQL server must not be running for<br>
performing the online recovery. If the target PostgreSQL server has<br>
already started, you must shut it down before starting the online<br>
recovery.<br>
<br>
If you have not started pg1 then pcp_recovert_node would succeed.<br>
<br>
> Log says verify_backend_node_status: decided node 0 is the true primary<br>
> <br>
> This command hangs<br>
> psql -U postgres -h localhost -p 9999 --pset pager=off -c "show pool_nodes"<br>
> <br>
> I would like to know how pgpool is considering node 0 which is pg1 as true<br>
> primary ?<br>
<br>
Because pgpool thinks the first primary node found is the true primary<br>
node if there is<br>
<br>
> 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<br>
> pg_is_in_recovery()"<br>
> 2020-08-31 13:40:00: pid 2459: DEBUG: verify_backend_node_status: there's<br>
> 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>
> <br>
> This command executes now<br>
> postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost<br>
> -p 9999 --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-31 13:35:44<br>
> 1 | pg2 | 5432 | up | 0.500000 | primary | 0 |<br>
> true | 0 | 2020-08-31 13:35:44<br>
> (2 rows)<br>
> <br>
> I checked between pg1 and pg2 and see that streaming replication is working.<br>
> <br>
> Then I used pcp_attach_node to attach standby<br>
> <br>
> postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ pcp_attach_node -h localhost<br>
> -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<br>
> -p 9999 --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 | up | 0.500000 | standby | 0 |<br>
> true | 0 | 2020-08-31 14:09:31<br>
> 1 | pg2 | 5432 | up | 0.500000 | primary | 0 |<br>
> false | 0 | 2020-08-31 14:02:35<br>
> (2 rows)<br>
> <br>
> Then I tried to perform a test operation and it executed successfully.<br>
> postgres@ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost<br>
> -p 9999 --pset pager=off -c "create database covid"<br>
> CREATE DATABASE<br>
> <br>
> Am I missing something in this failover process ? Please let me know if you<br>
> need any additional details.<br>
> <br>
> On Tue, Sep 1, 2020 at 3:03 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> 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<br>
>> 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>
>> ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------<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<br>
>> 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>><br>
>> wrote:<br>
>> >><br>
>> >>> > I have 3 servers with two postgres (9.6) and one pgpool (4.0.9).<br>
>> 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<br>
>> to<br>
>> >>> be<br>
>> >>> > converted as slave, pgpool is not triggering<br>
>> 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>
>><br>
> <br>
> <br>
> -- <br>
> <br>
> <br>
> *Regards,*<br>
> <br>
> <br>
> *K S Praveen KumarM: +91-9986855625 *<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>