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

Nguyen, Tu tnguyen3 at honolulu.gov
Sat Oct 24 18:04:00 JST 2020


Looking back at our emails, looks like I sent it on Wednesday, Oct. 21.  Don't remember what time, maybe several hours before you responded.

-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
Sent: Friday, October 23, 2020 10:58 PM
To: Nguyen, Tu <tnguyen3 at honolulu.gov>
Cc: pgpool-general at pgpool.net
Subject: Re: Setup Pgpool2 with Postgresql Streaming Replication

The log started at 2020-10-18 22:42:46.989 HST and ended at 2020-10-23 06:46:54 HST.
When did you execute pcp_recovery_node exactly?

> Hi Tatsuo,
> 
> Here are the logs.
> 
> Thanks,
> Alan
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
> Sent: Friday, October 23, 2020 10:30 PM
> To: Nguyen, Tu <tnguyen3 at honolulu.gov>
> 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?
> 
> If you provide the log I requested, I can examine what's wrong.
> 
>> 1) I need the PostgreSQL log while you are getting:
>>>> 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
> 
>>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-c
>>>> l
>>>> u
>>>> 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
>>>> 
>>>> 


More information about the pgpool-general mailing list