[pgpool-general: 7317] Re: Setup Pgpool2 with Postgresql Streaming Replication

Anssi Kanninen anssi at iki.fi
Sat Oct 24 16:46:19 JST 2020


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.

On 24 October 2020 10:04:25 EEST, "Nguyen, Tu" <tnguyen3 at honolulu.gov> wrote:
>Hi Tatsuo,
>
>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.  
>
>Should I follow the instructions from this link:  
>https://www.pgpool.net/docs/latest/en/html/example-cluster.html
>
>or
>
>This Link:
>https://www.pgpool.net/docs/40/en/html/example-cluster.html
>
>
>Thanks,
>Alan
>
>-----Original Message-----
>From: Nguyen, Tu 
>Sent: Friday, October 23, 2020 12:06 PM
>To: 'Tatsuo Ishii' <ishii at sraoss.co.jp>
>Cc: pgpool-general at pgpool.net
>Subject: RE: Setup Pgpool2 with Postgresql Streaming Replication
>
>
>Hi all,
>
>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.
>
>Thanks,
>Alan
>
>-----Original Message-----
>From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>Sent: Thursday, October 22, 2020 1:43 AM
>To: Nguyen, Tu <tnguyen3 at honolulu.gov>
>Cc: pgpool-general at pgpool.net
>Subject: Re: Setup Pgpool2 with Postgresql Streaming Replication
>
>> 2020-10-19 10:06:44.941 HST [24277] LOG:  starting PostgreSQL 12.3 
>> (Ubuntu 12.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc 
>> (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
>
>The document is for CentOS, while you are using Ubuntu. I am not sure
>the documentation is useful for you or not. Anyway...
>
>1) I need the PostgreSQL log while you are getting:
>
>>> I'm at step 7.3.8.1 Set up PostgreSQL standby server:
>>> Ran this command on the primary server: pcp_recovery_node -h
>>> 192.168.80.90 -p 9898 -U postgres -n 1 And received this error:
>>> ERROR:  recovery is checking if postmaster is started
>>> DETAIL:  postmaster on hostname:"ltpgsql12" database:"template1"
>>> user:"postgres" failed to start in 90 second
>
>2) Do you follow the step "7.3.4. Before Starting"? I am asking because
>you got this error:
>
>2020-10-19 13:10:23.521 HST [6757] postgres at postgres DETAIL:  User
>"postgres" has no password assigned.
>	Connection matched pg_hba.conf line 8: "host    all             all   
>         0.0.0.0/0               md5"
>
>Have you executed below in the documentation?
>
>  [server1]# psql -U postgres -p 5432
>       postgres=# SET password_encryption = 'scram-sha-256';
>       postgres=# CREATE ROLE pgpool WITH LOGIN;
>       postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
>       postgres=# \password pgpool
>       postgres=# \password repl
>       postgres=# \password postgres
>
>
>
>From: "Nguyen, Tu" <tnguyen3 at honolulu.gov>
>Subject: RE: Setup Pgpool2 with Postgresql Streaming Replication
>Date: Thu, 22 Oct 2020 09:35:36 +0000
>Message-ID:
><F152F79048C4864E9825A767687750E404E82E97F0 at CchExMail2.cchnl.hnl>
>
>> Hi Tatsuo,
>> 
>> Yes I'm using Pgpool-II version 4.0.
>> 
>> Ssh doesn't seem to be the issue, I was able to test ssh successfully
>
>> to both servers using this command from the new documentation:  ssh 
>> postgres at serverX -i ~/.ssh/id_rsa_pgpool
>> 
>> I have attached the postgresql-12-main.log file.
>> 
>> 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.
>> 
>> On another topic, can the Pgpool2 Connection Pooling feature handle
>3,000-4,000 simultaneous connections effectively?
>> 
>> Thanks,
>> Alan
>> 
>> 
>> -----Original Message-----
>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>> Sent: Wednesday, October 21, 2020 10:21 PM
>> To: Nguyen, Tu <tnguyen3 at honolulu.gov>
>> Cc: pgsql-general at postgresql.org
>> Subject: Re: Setup Pgpool2 with Postgresql Streaming Replication
>> 
>> Hi Alan,
>> 
>> This is not the best forum to discuss Pgpool-II related topics. I
>advice you to go to the Pgpool-II dedicated forum:
>> 
>> https://www.pgpool.net/mailman/listinfo/pgpool-general
>> 
>>> Hi,
>>> 
>>> I'm following the steps from:
>>> 
>>>
>https://access.crunchydata.com/documentation/pgpool/4.0.0/example-clu
>>> s
>>> ter.html
>> 
>> 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):
>> 
>> 
>> 
>> 
>> https://www.pgpool.net/docs/40/en/html/example-cluster.html
>> 
>> I strongly suggest to look into this.
>> 
>>> I'm at step 7.3.8.1 Set up PostgreSQL standby server:
>>> Ran this command on the primary server: pcp_recovery_node -h
>>> 192.168.80.90 -p 9898 -U postgres -n 1 And received this error:
>>> ERROR:  recovery is checking if postmaster is started
>>> DETAIL:  postmaster on hostname:"ltpgsql12" database:"template1"
>>> user:"postgres" failed to start in 90 second
>>> 
>>> How can I get this command to run successfully?
>> 
>> 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.
>> 
>> 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).
>> 
>>> Also, when I ran this command: psql -p 5433 -c "show pool_nodes"
>>> It shows the following pgpool2 node status, but when I did a listing
>
>>> of databases on the primary and standby servers, I don't see the 
>>> databases on the primary replicated to the standby.  How can I setup
>
>>> the Postgresql Streaming Replication and check if it's working?
>> 
>> Standby status is down because you failed to execute online recovery.
>You need to fix it.
>> 
>>> node_id | hostname  | port | status | lb_weight |  role   |
>select_cnt |
>>> load_balance_node | replication_delay | replication_state | 
>>> replication_sync_state | last_status_change
>>>
>---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
>>>  0       | ltpgsql11 | 6432 | up     | 0.500000  | primary | 0      
>   |
>>> true              | 0                 |                   |         
>             
>>> | 2020-10-21 11:56:48
>>>  1       | ltpgsql12 | 6432 | down   | 0.500000  | standby | 0      
>   |
>>> false             | 0                 |                   |         
>             
>>> | 2020-10-21 11:56:48
>>> (2 rows)
>>> 
>>> 
>>> Thanks,
>>> Alan
>>> 
>>> 
>>> 
>>> --
>>> Sent from: 
>>> https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>>> 
>>> 
>_______________________________________________
>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.sraoss.jp/pipermail/pgpool-general/attachments/20201024/b9abe4aa/attachment-0001.html>


More information about the pgpool-general mailing list