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

[Nguyen, Tu]

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-clus
> 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
> 
> 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgresql-12-main.log
Type: application/octet-stream
Size: 908146 bytes
Desc: postgresql-12-main.log
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20201022/69360069/attachment-0001.obj>