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

Nguyen, Tu tnguyen3 at honolulu.gov
Sat Oct 24 17:06:55 JST 2020 

Hi Anssi,

How did you fix your issue?  Here’s my pgpool log.  Is says it started recovery, but it hangs and doesn’t return “pcp_recovery_node -- command successful”.

Thanks,
Tu

From: pgpool-general-bounces at pgpool.net [mailto:pgpool-general-bounces at pgpool.net] On Behalf Of Anssi Kanninen
Sent: Friday, October 23, 2020 9:46 PM
To: pgpool-general at pgpool.net
Subject: [pgpool-general: 7317] Re: Setup Pgpool2 with Postgresql Streaming Replication

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<mailto: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<mailto:ishii at sraoss.co.jp>>
Cc: pgpool-general at pgpool.net<mailto: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<mailto:tnguyen3 at honolulu.gov>>
Cc: pgpool-general at pgpool.net<mailto: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<mailto: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<mailto: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<mailto:tnguyen3 at honolulu.gov>>
Cc: pgsql-general at postgresql.org<mailto: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<mailto: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/602a6758/attachment-0001.html>

More information about the pgpool-general mailing list