[pgpool-general: 7239] Re: Query regarding failover and recovery

[Praveen Kumar K S]

Hello,

I have managed to do it.

postgres at ip-172-31-39-241:/etc/pgpool2/4.0.9$ pcp_attach_node -h localhost
-p 9898 -n 0
Password:
pcp_attach_node -- Command Successful
postgres at ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost
-p 9999 --pset pager=off -c "show pool_nodes"
 node_id | hostname | port | status | lb_weight |  role   | select_cnt |
load_balance_node | replication_delay | last_status_change
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0       | pg1      | 5432 | up     | 0.500000  | standby | 0          |
true              | 0                 | 2020-08-31 14:09:31
 1       | pg2      | 5432 | up     | 0.500000  | primary | 0          |
false             | 0                 | 2020-08-31 14:02:35
(2 rows)

postgres at ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h localhost
-p 9999 --pset pager=off -c "create database covid"
CREATE DATABASE

If you could help me with the previous questions, it would be helpful.


On Mon, Aug 31, 2020 at 7:36 PM Praveen Kumar K S <praveenssit at gmail.com>
wrote:

> Hello,
>
> I repeated the same procedure.
>
> postgres at ip-172-31-39-241:/etc/pgpool2/4.0.9$ pcp_recovery_node -h
> localhost -p 9898 -n 0
> Password:
> ERROR:  process recovery request failed
> DETAIL:  primary server cannot be recovered by online recovery.
>
> Sequence:
> 1. pg1 is primary and pg2 is standby
> 2. Stop postgres service on pg1
> 3. Pgpool executes failover
> 4. Now pg2 is my primary
> 5. Started postgres service on pg1
>
> Log says verify_backend_node_status: decided node 0 is the true primary
>
> This command hangs
> psql -U postgres -h localhost -p 9999 --pset pager=off -c "show pool_nodes"
>
> I would like to know how pgpool is considering node 0 which is pg1 as true
> primary.
>
> Then what did I do ?
> 1. Cleaned everything on pg1 and manually configured it as standby.
> 2. Started postgres service
> 3. Log says
> 2020-08-31 13:40:00: pid 2459: DEBUG:  do_query: extended:0 query:"SELECT
> pg_is_in_recovery()"
> 2020-08-31 13:40:00: pid 2459: DEBUG:  verify_backend_node_status: there's
> no standby node
> 2020-08-31 13:40:00: pid 2459: DEBUG:  node status[0]: 0
> 2020-08-31 13:40:00: pid 2459: DEBUG:  node status[1]: 1
>
> This command executes now
> postgres at ip-172-31-39-241:/etc/pgpool2/4.0.9$ psql -U postgres -h
> localhost -p 9999 --pset pager=off -c "show pool_nodes"
>  node_id | hostname | port | status | lb_weight |  role   | select_cnt |
> load_balance_node | replication_delay | last_status_change
>
> ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
>  0       | pg1      | 5432 | down   | 0.500000  | standby | 0          |
> false             | 0                 | 2020-08-31 13:35:44
>  1       | pg2      | 5432 | up     | 0.500000  | primary | 0          |
> true              | 0                 | 2020-08-31 13:35:44
> (2 rows)
>
> I checked between pg1 and pg2 and see that streaming replication is
> working.
>
> Now, how can I mark this standby node as up in pgpool without executing
> the pcp_recovery_node command ?
>
> Thanks!
>
>
> On Mon, Aug 31, 2020 at 6:41 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
>> > I'm not sure I am understanding.
>> > If a old primary node will resume it has to become a new standby if a
>> > primary was promoted with an automatic failover.
>> >
>> > Let me explain my use case.
>> > I'm building a new repmgr cluster with one primary and two standbys.
>> >  During the night dumps of virtual machines are automatically triggered
>> and
>> > it could happen that one virtual machine could be unavailable for a
>> while
>> > during the dump. If this happens, Pgpool will make a failover and a
>> standby
>> > node will become primary. After some minutes the former primary will
>> become
>> > available again, but it would be a false primary. In this case I'd
>> issue a
>> > "repmgr node rejoin" command to let it become a new standby.
>> >
>> > I would like this to happen automatically.
>>
>> I am not fammilar with repmgr. What does "repmgr node rejoin"? Why do
>> you need repmgr in the first place? Doe it have a feature which
>> Pgpool-II does not have?
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>
>
>
> --
>
>
> *Regards,*
>
>
> *K S Praveen KumarM: +91-9986855625 *
>


-- 


*Regards,*


*K S Praveen KumarM: +91-9986855625 *
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200831/a5271fa6/attachment-0001.html>