[pgpool-general: 7889] Re: PCP recovery node Error: Recovery request is only allowed in replication and streaming replication modes

Wed Nov 24 08:57:48 JST 2021

Thank you for that reply. I finally got some time to test this again and it works perfectly!

Jon Schewe

Principal Software Systems Technologist

C: +1 612.263.2718

O: +1 952.545.5720

jon.schewe at raytheon.com

Raytheon Technologies

Raytheon Intelligence & Space

5775 Wayzata Blvd. Suite 630

St. Louis Park, MN 55416

RTX.com<https://www.rtx.com/> | LinkedIn<https://www.linkedin.com/company/raytheontechnologies> | Twitter<https://twitter.com/raytheontech> | Instagram<https://www.instagram.com/raytheontechnologies>

________________________________
From: pgpool-general <pgpool-general-bounces at pgpool.net> on behalf of Bo Peng <pengbo at sraoss.co.jp>
Sent: Wednesday, November 10, 2021 02:27
To: Nirav Bhatt <niravbhatt.cpp at gmail.com>
Cc: pgpool-general <pgpool-general at pgpool.net>
Subject: [External] [pgpool-general: 7871] Re: PCP recovery node Error: Recovery request is only allowed in replication and streaming replication modes

Hello,

> thanks for the quick answer.
>
> I deleted pgpool_status and modified pgpool conf files for master slave
> streaming replication mode.
>
> Now still the 2 things are puzzling:
>
> 1-When I start pg and pgpool on all 3 servers, the status looks good in
> show pool nodes.
> However, when I try to do pcp_recovery_node for node 1 (41 tutorial section
> 8.3.7.1), it failed saying it node 1 is alive.

It seems you have already setup a PostgreSQL streaming replication cluster.
If all the PostgreSQL nodes are started, you don't need to run pcp_recovery_node.

pcp_recovery_node is used to create or recover standby nodes.

> Then, I tried stopping pg and pgpool on node 1, and tried again.
> This time, it failed again. The log on server1 shows this:
>
> ERROR:  replication slot "server2" does not exist
> failover.sh: drop replication slot server2 failed

Because the standby nodes are not created by pcp_recovery_node command,
the replication slots are not created.

Try the following steps to reset the cluster:

1. Start only PostgreSQL primary node (just one node)
2. Start Pgpool-II (If you are using watchdog, start all of the pgpool nodes)
3. Run "show pool_nodes" command to check the status of backend nodes.
   Please make sure, node0 (primary) is "up", and other nodes are "down".
4. Run pcp_recovery_node command to create PostgreSQL standby nodes.

> So either my postgresql setup is not correct, or I missed something in the
> order pgpool should be set up.
>
> (upon further investigation I found that this slot must have been created
> by follow_master script.
> I do not know where that script must be called during initialization. I do
> not see it being called in server1 (master) logs)
>
> I have done everything in the order that this tutorial (41) has described -
> but it is clear some things are not mentioned in that and I missed them
> several times.
> https://www.pgpool.net/docs/41/en/html/example-cluster.html
>
> What exactly is pcp_recovery_node - is not clear to me.
> Is it to bring up the fresh (never failed) standby node (see section #
> 8.3.7.1 in the tutorial), or a failed node back in the system (Tutorial
> section # 8.3.7.4)?
> Is it mandatory to be called manually during pgpool cluster initialization?
>
> Most importantly: When pcp_recovery_node is invoked, what services should
> be stopped on the soon-to-be standby node: pg or pgpool, both or no-one?
>
> 2-One more thing I observed is that past starting pgpool and pg on all 3,
> when I created a table on master (also inserted 2 rows into it) - I
> expected it to be replicated to other servers.
> But it didn't happen.
> So I am sure something is missing in my postgres replication setup, and it
> will be quite helpful if it is mentioned.
> I am attaching my postgresql.conf.

No. You don't need to configure postgresql replication setup by yourself.
At the initial state, you need to just create one PostgreSQL primary node.
pcp_recovery_node commadn will create standby nodes for you.

> Thanks for the support,
> -Nirav
>
> On Sun, Nov 7, 2021 at 5:04 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> > > Hello,
> > >
> > > After setting up pgpool2 4.1 based on below tutorial, I kept getting
> > > unreliable behavior.
> > >
> > > https://www.pgpool.net/docs/41/en/html/example-cluster.html
> > >
> > > So I followed all the steps and realized that I was not setting standbys
> > as
> > > suggested.
> > >
> > > First I start all 3 servers:
> > >
> > > systemctl start postgresql
> > > systemctl start pgpool2
> > >
> > > After, when I ran following command from my primary:
> > >
> > > pcp_recovery_node -h {VIRTUAL IP} -p 9898 -U pgpool  -n 1
> > >
> > > It gave me error:
> > >
> > > ERROR:  process recovery request failed
> > > DETAIL:  recovery request is only allowed in replication and streaming
> > > replication modes.
> > >
> > > The tutorial does not mention much about how to set up streaming
> > > replication mode. I set up all params in postgresql.conf as per the
> > > tutorial.
> > >
> > > I have 2 questions:
> > > 1 - what is the main issue? Is it the above error about streaming
> > > replication mode?
> > > 2 - If not, is my original setup is problematic?
> >
> > It is likely there's a mistake in your setup. The error says your
> > setup is not either streaming replication mode nor native replication
> > mode. You should check your pgpool.conf on *all* of the 3 nodes,
> > especially make sure that:
> >
> > master_slave_mode = on
> > master_slave_sub_mode = 'stream'
> >
> > > I have strong feeling it is the case:
> > > Even when I stop pgpool on all nodes, and start node0, show pool_nodes
> > show
> > > node 0 as down, and node 1 as master (even though pgpool service is not
> > > even running there!)
> > > I repeatedly stopped and started postgres and pgpool, but node 1
> > constantly
> > > remains the master.
> >
> > If your setup is a correct streaming replication mode, show pool_nodes
> > should show each PostgreSQL role as "primary" or "standby". So I
> > suspect there's a mistake in your setup.
> >
> > > Is there a way to reset all the previous runs of pgpool (maybe some old
> > > state causing issues)
> >
> > Yes. the status file is located as "/var/log/pgpool/pgpool_status".
> > It's a simple text file. It represents each PostgreSQL status
> > something like "up" or "down" in each row.  You can safely remove the
> > file while pgpool is stopping. The file will be automatically
> > recreated upon starting up of pgpool and the current status will be
> > reflected to the file.
> >
> > > Once I solve it, I also need to solve how to set up proper standby server
> > > using pcp_recovery_node
> > >
> > > This setup is driving me mad.
> > > -Nirav
> >
> > Please stay calm. Setting up a PostgreSQL cluster is not an easy task
> > for everyone.
> >
> > Best regards,
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> > English: http://www.sraoss.co.jp/index_en.php
> > Japanese:http://www.sraoss.co.jp
> >

--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
http://www.sraoss.co.jp/
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211123/0d7dffb3/attachment-0001.htm>