<html><head></head><body>I had a similar situation. The problem was that the data directory replication was successful but the script was waiting for DB remote restart. Check your pgpool log.<br><br><div class="gmail_quote">On 24 October 2020 10:04:25 EEST, "Nguyen, Tu" <tnguyen3@honolulu.gov> wrote:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<pre class="k9mail">Hi Tatsuo,<br><br>Can you tell me exactly which parameters in the "recovery_1st_stage" and "pgpool_remote_start" files I need to replace with my own server information? I think it may be some settings in there that is causing it to just hang when I run "pcp_recovery_node -h 192.168.94.100 -p 9898 -U postgres" from the primary server. It just stalls on the next line and doesn't do anything until I hit Ctrl +C to cancel out of it. <br><br>Should I follow the instructions from this link: <br><a href="https://www.pgpool.net/docs/latest/en/html/example-cluster.html">https://www.pgpool.net/docs/latest/en/html/example-cluster.html</a><br><br>or<br><br>This Link:<br><a href="https://www.pgpool.net/docs/40/en/html/example-cluster.html">https://www.pgpool.net/docs/40/en/html/example-cluster.html</a><br><br><br>Thanks,<br>Alan<br><br>-----Original Message-----<br>From: Nguyen, Tu <br>Sent: Friday, October 23, 2020 12:06 PM<br>To: 'Tatsuo Ishii' <ishii@sraoss.co.jp><br>Cc: pgpool-general@pgpool.net<br>Subject: RE: Setup Pgpool2 with Postgresql Streaming Replication<br><br><br>Hi all,<br><br>Looks like I may have been able to run the pcp_recovery_node command, but after I entered the password, it goes to the next line and just stays there, no errors or anything. Maybe it's stuck? I looked on the standby server node I'm trying to recover, with the "top" command and it says CPU usage is around 35% for the pg_basebackup command. It's been almost 5 hours like this. Does anyone know how long it takes? I heard that the standby recovery takes a long time, but does it really take this long? I have around 15 databases that totals about 300-500 MB of data that Pgpool2 is supposedly trying to recover right now.<br><br>Thanks,<br>Alan<br><br>-----Original Message-----<br>From: Tatsuo Ishii [mailto:ishii@sraoss.co.jp]<br>Sent: Thursday, October 22, 2020 1:43 AM<br>To: Nguyen, Tu <tnguyen3@honolulu.gov><br>Cc: pgpool-general@pgpool.net<br>Subject: Re: Setup Pgpool2 with Postgresql Streaming Replication<br><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #729fcf; padding-left: 1ex;">2020-10-19 10:06:44.941 HST [24277] LOG: starting PostgreSQL 12.3 <br>(Ubuntu 12.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc <br>(Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit<br></blockquote><br>The document is for CentOS, while you are using Ubuntu. I am not sure the documentation is useful for you or not. Anyway...<br><br>1) I need the PostgreSQL log while you are getting:<br><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #729fcf; padding-left: 1ex;"><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #ad7fa8; padding-left: 1ex;">I'm at step 7.3.8.1 Set up PostgreSQL standby server:<br>Ran this command on the primary server: pcp_recovery_node -h<br>192.168.80.90 -p 9898 -U postgres -n 1 And received this error:<br>ERROR: recovery is checking if postmaster is started<br>DETAIL: postmaster on hostname:"ltpgsql12" database:"template1"<br>user:"postgres" failed to start in 90 second<br></blockquote></blockquote><br>2) Do you follow the step "7.3.4. Before Starting"? I am asking because you got this error:<br><br>2020-10-19 13:10:23.521 HST [6757] postgres@postgres DETAIL: User "postgres" has no password assigned.<br> Connection matched pg_hba.conf line 8: "host all all 0.0.0.0/0 md5"<br><br>Have you executed below in the documentation?<br><br> [server1]# psql -U postgres -p 5432<br> postgres=# SET password_encryption = 'scram-sha-256';<br> postgres=# CREATE ROLE pgpool WITH LOGIN;<br> postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;<br> postgres=# \password pgpool<br> postgres=# \password repl<br> postgres=# \password postgres<br><br><br><br>From: "Nguyen, Tu" <tnguyen3@honolulu.gov><br>Subject: RE: Setup Pgpool2 with Postgresql Streaming Replication<br>Date: Thu, 22 Oct 2020 09:35:36 +0000<br>Message-ID: <F152F79048C4864E9825A767687750E404E82E97F0@CchExMail2.cchnl.hnl><br><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #729fcf; padding-left: 1ex;">Hi Tatsuo,<br><br>Yes I'm using Pgpool-II version 4.0.<br><br>Ssh doesn't seem to be the issue, I was able to test ssh successfully <br>to both servers using this command from the new documentation: ssh <br>postgres@serverX -i ~/.ssh/id_rsa_pgpool<br><br>I have attached the postgresql-12-main.log file.<br><br>After we get the online recovery task fixed, are there recommended step by step instructions on how to best setup Postgresql Streaming Replication for Pgpool-II 4.0 clustering? When I execute the "show pool_nodes" command, the replication_state and replication_sync_state values are blank and data is not being replicated.<br><br>On another topic, can the Pgpool2 Connection Pooling feature handle 3,000-4,000 simultaneous connections effectively?<br><br>Thanks,<br>Alan<br><br><br>-----Original Message-----<br>From: Tatsuo Ishii [mailto:ishii@sraoss.co.jp]<br>Sent: Wednesday, October 21, 2020 10:21 PM<br>To: Nguyen, Tu <tnguyen3@honolulu.gov><br>Cc: pgsql-general@postgresql.org<br>Subject: Re: Setup Pgpool2 with Postgresql Streaming Replication<br><br>Hi Alan,<br><br>This is not the best forum to discuss Pgpool-II related topics. I advice you to go to the Pgpool-II dedicated forum:<br><br><a href="https://www.pgpool.net/mailman/listinfo/pgpool-general">https://www.pgpool.net/mailman/listinfo/pgpool-general</a><br><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #ad7fa8; padding-left: 1ex;">Hi,<br><br>I'm following the steps from:<br><br><a href="https://access.crunchydata.com/documentation/pgpool/4.0.0/example-clu">https://access.crunchydata.com/documentation/pgpool/4.0.0/example-clu</a><br>s<br>ter.html<br></blockquote><br>This documentation looks pretty old. The original and the latest documentation for Pgpool-II 4.0 is here (I assume you are using Pgpool-II 4.0):<br><br><br><br><br><a href="https://www.pgpool.net/docs/40/en/html/example-cluster.html">https://www.pgpool.net/docs/40/en/html/example-cluster.html</a><br><br>I strongly suggest to look into this.<br><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #ad7fa8; padding-left: 1ex;">I'm at step 7.3.8.1 Set up PostgreSQL standby server:<br>Ran this command on the primary server: pcp_recovery_node -h<br>192.168.80.90 -p 9898 -U postgres -n 1 And received this error:<br>ERROR: recovery is checking if postmaster is started<br>DETAIL: postmaster on hostname:"ltpgsql12" database:"template1"<br>user:"postgres" failed to start in 90 second<br><br>How can I get this command to run successfully?<br></blockquote><br>Probably you have a problem with ssh settings. The newer and original document describes far detailed steps to set up ssh settings. Please take a look at "7.3.2. Requirements" section in the newer document.<br><br>In the mean time to confirm that the problem is related to ssh, we need to look into the PostgreSQL log (not Pgpool-II log) on primary PostgreSQL node. Please share it (again, you'd better to post messages to the pgpool-general mailing list).<br><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #ad7fa8; padding-left: 1ex;">Also, when I ran this command: psql -p 5433 -c "show pool_nodes"<br>It shows the following pgpool2 node status, but when I did a listing <br>of databases on the primary and standby servers, I don't see the <br>databases on the primary replicated to the standby. How can I setup <br>the Postgresql Streaming Replication and check if it's working?<br></blockquote><br>Standby status is down because you failed to execute online recovery. You need to fix it.<br><br><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #ad7fa8; padding-left: 1ex;">node_id | hostname | port | status | lb_weight | role | select_cnt |<br>load_balance_node | replication_delay | replication_state | <br>replication_sync_state | last_status_change<br>---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<br> 0 | ltpgsql11 | 6432 | up | 0.500000 | primary | 0 |<br>true | 0 | | <br>| 2020-10-21 11:56:48<br> 1 | ltpgsql12 | 6432 | down | 0.500000 | standby | 0 |<br>false | 0 | | <br>| 2020-10-21 11:56:48<br>(2 rows)<br><br><br>Thanks,<br>Alan<br><br><br><br>--<br>Sent from: <br><a href="https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html">https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html</a><br><br><br></blockquote></blockquote><hr>pgpool-general mailing list<br>pgpool-general@pgpool.net<br><a href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br></pre></blockquote></div></body></html>