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

[Nirav Bhatt]

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.

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

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.

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211107/1ae58a85/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgresql.conf
Type: application/octet-stream
Size: 29047 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211107/1ae58a85/attachment-0001.obj>