[pgpool-general: 7314] Re: Setup Pgpool2 with Postgresql Streaming Replication
Tatsuo Ishii
ishii at sraoss.co.jp
Thu Oct 22 20:43:19 JST 2020
> 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-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
>>
>>
More information about the pgpool-general
mailing list