<div dir="ltr">Thanks, I tried exactly same as you said (note that I am on Ubuntu 20.04.3 LTS)<div><br><div>1 - start postgresql on server1</div><div>2 - start pgpool2 on all servers: server1, server2, server3 (pg is in stopped status)</div><div><br></div><div>Past this, show pool nodes shows all nodes as up, without starting postgres on server2 and server3.</div><div><br></div><div>(Basically: postgresql sevice got started on server2 and server3 as a result of starting pgpool2, then exited.</div><div>systemctl status postgresql shows active (exited) status)<br></div><div><br></div><div>root@ip-10-192-10-10:/etc/pgpool2# psql -h 20.20.20.20 -p 9999 -U pgpool postgres -c "show pool_nodes"; <br>Password for user pgpool:</div><div><br> node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change<br>---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<br> 0 | server1 | 5432 | up | 0.333333 | primary | 0 | false | 0 | | | 2021-11-13 15:05:48<br> 1 | server2 | 5432 | up | 0.333333 | standby | 0 | true | 0 | | | 2021-11-13 15:05:48<br> 2 | server3 | 5432 | up | 0.333333 | standby | 0 | false | 0 | | | 2021-11-13 15:05:48<br></div><div><br></div><div>Is this something expected?</div><div><br></div><div>I tried creating a table on server1, but it was not replicated to server2 and server3.</div><div><br></div><div>1 - Now I do not know if I should run pcp_recovery_node for node1 and node2 or not.</div><div>2 - The exact order of starting pg and pgpool is highly important, but is not mentioned in the tutorial. </div><div>The tutorial mentions that pgpool2 should be stopped before pg. </div><div>But what order of starting/stopping should be followed between servers?</div><div>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)</div><div><br></div><div>Also when I reset + stopped everything and started pg + pgpool2 on server1, virtual IP became up on server1. (something I had done already) </div><div><br></div><div>I see that this setup is difficult but at the same time lot of clarity is missing in the tutorial.</div><div><br></div><div>Tutorial is really good in explaining the meaning of each config variable. </div><div>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.</div><div>When we succeed, we also like to contribute back our findings, so more people could benefit from the same.</div><div><br></div><div>-Nirav</div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Nov 10, 2021 at 10:27 AM Bo Peng <<a href="mailto:pengbo@sraoss.co.jp">pengbo@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hello,<br>
<br>
> thanks for the quick answer.<br>
> <br>
> I deleted pgpool_status and modified pgpool conf files for master slave<br>
> streaming replication mode.<br>
> <br>
> Now still the 2 things are puzzling:<br>
> <br>
> 1-When I start pg and pgpool on all 3 servers, the status looks good in<br>
> show pool nodes.<br>
> However, when I try to do pcp_recovery_node for node 1 (41 tutorial section<br>
> 8.3.7.1), it failed saying it node 1 is alive.<br>
<br>
It seems you have already setup a PostgreSQL streaming replication cluster.<br>
If all the PostgreSQL nodes are started, you don't need to run pcp_recovery_node.<br>
<br>
pcp_recovery_node is used to create or recover standby nodes.<br>
<br>
> Then, I tried stopping pg and pgpool on node 1, and tried again.<br>
> This time, it failed again. The log on server1 shows this:<br>
> <br>
> ERROR: replication slot "server2" does not exist<br>
> failover.sh: drop replication slot server2 failed<br>
<br>
Because the standby nodes are not created by pcp_recovery_node command,<br>
the replication slots are not created.<br>
<br>
Try the following steps to reset the cluster:<br>
<br>
1. Start only PostgreSQL primary node (just one node)<br>
2. Start Pgpool-II (If you are using watchdog, start all of the pgpool nodes)<br>
3. Run "show pool_nodes" command to check the status of backend nodes.<br>
Please make sure, node0 (primary) is "up", and other nodes are "down".<br>
4. Run pcp_recovery_node command to create PostgreSQL standby nodes.<br>
<br>
> So either my postgresql setup is not correct, or I missed something in the<br>
> order pgpool should be set up.<br>
> <br>
> (upon further investigation I found that this slot must have been created<br>
> by follow_master script.<br>
> I do not know where that script must be called during initialization. I do<br>
> not see it being called in server1 (master) logs)<br>
> <br>
> I have done everything in the order that this tutorial (41) has described -<br>
> but it is clear some things are not mentioned in that and I missed them<br>
> several times.<br>
> <a href="https://www.pgpool.net/docs/41/en/html/example-cluster.html" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/41/en/html/example-cluster.html</a><br>
> <br>
> What exactly is pcp_recovery_node - is not clear to me.<br>
> Is it to bring up the fresh (never failed) standby node (see section #<br>
> 8.3.7.1 in the tutorial), or a failed node back in the system (Tutorial<br>
> section # 8.3.7.4)?<br>
> Is it mandatory to be called manually during pgpool cluster initialization?<br>
> <br>
> Most importantly: When pcp_recovery_node is invoked, what services should<br>
> be stopped on the soon-to-be standby node: pg or pgpool, both or no-one?<br>
> <br>
> 2-One more thing I observed is that past starting pgpool and pg on all 3,<br>
> when I created a table on master (also inserted 2 rows into it) - I<br>
> expected it to be replicated to other servers.<br>
> But it didn't happen.<br>
> So I am sure something is missing in my postgres replication setup, and it<br>
> will be quite helpful if it is mentioned.<br>
> I am attaching my postgresql.conf.<br>
<br>
No. You don't need to configure postgresql replication setup by yourself.<br>
At the initial state, you need to just create one PostgreSQL primary node.<br>
pcp_recovery_node commadn will create standby nodes for you.<br>
<br>
> Thanks for the support,<br>
> -Nirav<br>
> <br>
> On Sun, Nov 7, 2021 at 5:04 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
> > > Hello,<br>
> > ><br>
> > > After setting up pgpool2 4.1 based on below tutorial, I kept getting<br>
> > > unreliable behavior.<br>
> > ><br>
> > > <a href="https://www.pgpool.net/docs/41/en/html/example-cluster.html" rel="noreferrer" target="_blank">https://www.pgpool.net/docs/41/en/html/example-cluster.html</a><br>
> > ><br>
> > > So I followed all the steps and realized that I was not setting standbys<br>
> > as<br>
> > > suggested.<br>
> > ><br>
> > > First I start all 3 servers:<br>
> > ><br>
> > > systemctl start postgresql<br>
> > > systemctl start pgpool2<br>
> > ><br>
> > > After, when I ran following command from my primary:<br>
> > ><br>
> > > pcp_recovery_node -h {VIRTUAL IP} -p 9898 -U pgpool -n 1<br>
> > ><br>
> > > It gave me error:<br>
> > ><br>
> > > ERROR: process recovery request failed<br>
> > > DETAIL: recovery request is only allowed in replication and streaming<br>
> > > replication modes.<br>
> > ><br>
> > > The tutorial does not mention much about how to set up streaming<br>
> > > replication mode. I set up all params in postgresql.conf as per the<br>
> > > tutorial.<br>
> > ><br>
> > > I have 2 questions:<br>
> > > 1 - what is the main issue? Is it the above error about streaming<br>
> > > replication mode?<br>
> > > 2 - If not, is my original setup is problematic?<br>
> ><br>
> > It is likely there's a mistake in your setup. The error says your<br>
> > setup is not either streaming replication mode nor native replication<br>
> > mode. You should check your pgpool.conf on *all* of the 3 nodes,<br>
> > especially make sure that:<br>
> ><br>
> > master_slave_mode = on<br>
> > master_slave_sub_mode = 'stream'<br>
> ><br>
> > > I have strong feeling it is the case:<br>
> > > Even when I stop pgpool on all nodes, and start node0, show pool_nodes<br>
> > show<br>
> > > node 0 as down, and node 1 as master (even though pgpool service is not<br>
> > > even running there!)<br>
> > > I repeatedly stopped and started postgres and pgpool, but node 1<br>
> > constantly<br>
> > > remains the master.<br>
> ><br>
> > If your setup is a correct streaming replication mode, show pool_nodes<br>
> > should show each PostgreSQL role as "primary" or "standby". So I<br>
> > suspect there's a mistake in your setup.<br>
> ><br>
> > > Is there a way to reset all the previous runs of pgpool (maybe some old<br>
> > > state causing issues)<br>
> ><br>
> > Yes. the status file is located as "/var/log/pgpool/pgpool_status".<br>
> > It's a simple text file. It represents each PostgreSQL status<br>
> > something like "up" or "down" in each row. You can safely remove the<br>
> > file while pgpool is stopping. The file will be automatically<br>
> > recreated upon starting up of pgpool and the current status will be<br>
> > reflected to the file.<br>
> ><br>
> > > Once I solve it, I also need to solve how to set up proper standby server<br>
> > > using pcp_recovery_node<br>
> > ><br>
> > > This setup is driving me mad.<br>
> > > -Nirav<br>
> ><br>
> > Please stay calm. Setting up a PostgreSQL cluster is not an easy task<br>
> > for everyone.<br>
> ><br>
> > Best regards,<br>
> > --<br>
> > Tatsuo Ishii<br>
> > SRA OSS, Inc. Japan<br>
> > English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
> > Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
> ><br>
<br>
<br>
-- <br>
Bo Peng <<a href="mailto:pengbo@sraoss.co.jp" target="_blank">pengbo@sraoss.co.jp</a>><br>
SRA OSS, Inc. Japan<br>
<a href="http://www.sraoss.co.jp/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/</a><br>
</blockquote></div>