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

[Praveen Kumar K S]

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 *
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200831/1ed4b434/attachment.html>