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

[Tatsuo Ishii]

> 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