[pgpool-general: 7877] Re: PCP recovery node Error: Recovery request is only allowed in replication and streaming replication modes
Luca Maranzano
liuk001 at gmail.com
Mon Nov 15 03:21:27 JST 2021
Hi Nirav,
it's strange that server2 and server3 are UP and in standby state if you
did not perform pcp_recovery_node or if you did not configure them manually
as standby.
Usually my steps are:
- start pg on server1, pg on server2 and server3 are not running and will
be erased later during pcp_recover
- start pgpool on all nodes and check that the pgpool cluster is formed and
VIP is up on one of the 3 nodes (not necessarily on the primary postgres!)
- perform pcp_recover_node for node server2 and server3
Remember that pgpool cluster is separate from postgres running on 3
servers, starting from a running cluster if you stop 1 pgpool, you still
will have a pgpool cluster with quorum, if you stop instead the pg primary
then pgpool will perform failover and will promote one of the 2 standby as
primary.
HTH
Regards
Luca
On Sat, Nov 13, 2021 at 4:53 PM Nirav Bhatt <niravbhatt.cpp at gmail.com>
wrote:
> Thanks, I tried exactly same as you said (note that I am on Ubuntu 20.04.3
> LTS)
>
> 1 - start postgresql on server1
> 2 - start pgpool2 on all servers: server1, server2, server3 (pg is in
> stopped status)
>
> Past this, show pool nodes shows all nodes as up, without starting
> postgres on server2 and server3.
>
> (Basically: postgresql sevice got started on server2 and server3 as a
> result of starting pgpool2, then exited.
> systemctl status postgresql shows active (exited) status)
>
> root at ip-10-192-10-10:/etc/pgpool2# psql -h 20.20.20.20 -p 9999 -U pgpool
> postgres -c "show pool_nodes";
> Password for user pgpool:
>
> node_id | hostname | port | status | lb_weight | role | select_cnt |
> load_balance_node | replication_delay | replication_state |
> replication_sync_state | last_status_change
>
> ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
> 0 | server1 | 5432 | up | 0.333333 | primary | 0 |
> false | 0 | |
> | 2021-11-13 15:05:48
> 1 | server2 | 5432 | up | 0.333333 | standby | 0 |
> true | 0 | |
> | 2021-11-13 15:05:48
> 2 | server3 | 5432 | up | 0.333333 | standby | 0 |
> false | 0 | |
> | 2021-11-13 15:05:48
>
> Is this something expected?
>
> I tried creating a table on server1, but it was not replicated to server2
> and server3.
>
> 1 - Now I do not know if I should run pcp_recovery_node for node1 and
> node2 or not.
> 2 - The exact order of starting pg and pgpool is highly important, but is
> not mentioned in the tutorial.
> The tutorial mentions that pgpool2 should be stopped before pg.
> But what order of starting/stopping should be followed between servers?
> When I stopped first pgpool2 then pg on primary server, the virtual IP got
> shifted to server2 (something I was not expecting as I first stopped
> pgpool2, not pg)
>
> Also when I reset + stopped everything and started pg + pgpool2 on
> server1, virtual IP became up on server1. (something I had done already)
>
> I see that this setup is difficult but at the same time lot of clarity is
> missing in the tutorial.
>
> Tutorial is really good in explaining the meaning of each config variable.
> But it will be helpful only if someone could list steps (the ones past
> conf files, ssh and password files setup) exactly in the order with server
> names and command, the expected state of the system after every command,
> and how replication will actually take effect.
> When we succeed, we also like to contribute back our findings, so more
> people could benefit from the same.
>
> -Nirav
>
> On Wed, Nov 10, 2021 at 10:27 AM Bo Peng <pengbo at sraoss.co.jp> wrote:
>
>> 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/20211114/9670b262/attachment.htm>
More information about the pgpool-general
mailing list