[pgpool-general: 7369] Re: User "pgpool" does not have a valid SCRAM

Fri Dec 18 01:44:28 JST 2020

 8.2.7. /etc/sysconfig/pgpool Configuration

When starting Pgpool-II, if the pgpool_status file exists, Pgpool-II will
read the backend status (up/down) from the pgpool_status file.

If you want to ignore the pgpool_status file at startup of Pgpool-II, add
"- D" to the start option OPTS to /etc/sysconfig/pgpool.

[all servers]# vi /etc/sysconfig/pgpool
...
OPTS=" -D -n"

With this the cluster started correctly.

Thanks,
LA

On Thu, Dec 17, 2020 at 4:25 PM Luís Alves <luisalves00 at gmail.com> wrote:

> [1-1] 2020-12-17 17:23:59: pid 9732: LOG:  reading status file: 0 th
> backend is set to down status  <--- I think this is the problem
>
> Now I just need to find the file location.
>
> Regards,
> LA
>
> On Thu, Dec 17, 2020 at 4:19 PM Luís Alves <luisalves00 at gmail.com> wrote:
>
>> is there any file that store the index of the primary node?
>>
>> Regards,
>> LA
>>
>> On Thu, Dec 17, 2020 at 3:56 PM Luís Alves <luisalves00 at gmail.com> wrote:
>>
>>> The problem is not the recovery node index. Now I have:
>>>
>>> pcp_recovery_node -w -h 10.230.4.148 -p 9898 -U pgpool -n 1
>>>
>>> (I'm going to try to delay this action on ansible, as pgpool might not
>>> have enough time to find the primary node)
>>>
>>> Then:
>>>
>>> -bash-4.2$ psql -h 10.230.4.148 -p 9999 -U pgpool postgres -c "show
>>> pool_nodes"
>>> psql: FATAL:  failed to create a backend 1 connection
>>> DETAIL:  not executing failover because failover_on_backend_error is off
>>>
>>> checking node 0:
>>>
>>> -bash-4.2$ pcp_node_info -w -h 10.3.84.4 -p 9898 -U pgpool -n 0
>>> ERROR: connection to host "10.3.84.4" failed with error "Connection
>>> refused"
>>>
>>> But the server is running:
>>>
>>> qs-auth-01 ~]# service postgresql-11 status
>>> Redirecting to /bin/systemctl status postgresql-11.service
>>> ● postgresql-11.service - PostgreSQL 11 database server
>>>    Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service;
>>> disabled; vendor preset: disabled)
>>>   Drop-In: /etc/systemd/system/postgresql-11.service.d
>>>            └─override.conf
>>>    Active: active (running) since Thu 2020-12-17 16:43:48 CET; 8min ago
>>>
>>> but pgpool is only looking into qs-auth-02 (which is node index 1)
>>>
>>> Any ideas?
>>>
>>> Regards,
>>> LA
>>>
>>>
>>> On Thu, Dec 17, 2020 at 1:41 PM Luís Alves <luisalves00 at gmail.com>
>>> wrote:
>>>
>>>> I'm doing the initial installation using Ansible.
>>>> I think the problem was that on the initial installation where node 0
>>>> (qs-auth-01.dc.internal) should be the primary I was executing:
>>>>
>>>> pcp_recovery_node -w -h {{ delegate_virtual_ip }} -p 9898 -U pgpool -n 0
>>>>
>>>> instead of
>>>>
>>>> pcp_recovery_node -w -h {{ delegate_virtual_ip }} -p 9898 -U pgpool -n 1
>>>>
>>>> because my node index calculation had a bug.
>>>> I'll check it out during this afternoon and I'll update the thread if
>>>> the problem persists.
>>>>
>>>> Regards,
>>>> LA
>>>>
>>>>
>>>>
>>>> On Thu, Dec 17, 2020 at 12:21 PM Tatsuo Ishii <ishii at sraoss.co.jp>
>>>> wrote:
>>>>
>>>>> > Now they are correct and it can connect. Nevertheless, I'm having
>>>>> > other issues. Here is the Backend Connection Settings
>>>>> >
>>>>> >
>>>>> > # - Backend Connection Settings -
>>>>> > backend_hostname0 = 'qs-auth-01.dc.internal'
>>>>> > backend_port0 = 5432
>>>>> > backend_weight0 = 1
>>>>> > backend_data_directory0 = '/export/pgsql/data'
>>>>> > backend_flag0 = 'ALLOW_TO_FAILOVER'
>>>>> > backend_application_name0 = 'qs-auth-01.dc.internal'
>>>>> > backend_hostname1 = 'qs-auth-02.dc. internal '
>>>>> > backend_port1 = 5432
>>>>> > backend_weight1 = 1
>>>>> > backend_data_directory1 = '/export/pgsql/data'
>>>>> > backend_flag1 = 'ALLOW_TO_FAILOVER'
>>>>> > backend_application_name1 = 'qs-auth-02.dc.internal'
>>>>> >
>>>>> >
>>>>> > node qs-auth-01.dc.internal should be the primary and has postgres
>>>>> running
>>>>> > there, but pgpool seems to ignore it.
>>>>> > node qs-auth-02.dc.internal is shutdown as I want to apply
>>>>> replication from
>>>>> > node 1.
>>>>> >
>>>>> > Here is the log:
>>>>> >
>>>>> > Dec 17 09:18:32 qs-auth-02 pgpool[20471]: [915-1] 2020-12-17
>>>>> 09:18:32: pid
>>>>> > 20471: ERROR:  failed to make persistent db connection
>>>>> > Dec 17 09:18:32 qs-auth-02 pgpool[20471]: [915-2] 2020-12-17
>>>>> 09:18:32: pid
>>>>> > 20471: DETAIL:  connection to host:"qs-auth-02.dc.internal:5432"
>>>>> failed
>>>>> > Dec 17 09:18:32 qs-auth-02 pgpool[20471]: [916-1] 2020-12-17
>>>>> 09:18:32: pid
>>>>> > 20471: LOG:  health check retrying on DB node: 1 (round:2)
>>>>> > Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [917-1] 2020-12-17
>>>>> 09:18:33: pid
>>>>> > 20471: LOG:  failed to connect to PostgreSQL server on
>>>>> > "qs-auth-02.dc.internal:5432", getsockopt() detected error
>>>>> "Connection
>>>>> > refused"
>>>>> > Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [918-1] 2020-12-17
>>>>> 09:18:33: pid
>>>>> > 20471: ERROR:  failed to make persistent db connection
>>>>> > Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [918-2] 2020-12-17
>>>>> 09:18:33: pid
>>>>> > 20471: DETAIL:  connection to host:"qs-auth-02.dc.internal:5432"
>>>>> failed
>>>>> > Dec 17 09:18:33 qs-auth-02 pgpool[20471]: [919-1] 2020-12-17
>>>>> 09:18:33: pid
>>>>> > 20471: LOG:  health check retrying on DB node: 1 (round:3)
>>>>> > Dec 17 09:18:34 qs-auth-02 pgpool[20471]: [920-1] 2020-12-17
>>>>> 09:18:34: pid
>>>>> > 20471: LOG:  failed to connect to PostgreSQL server on
>>>>> > "qs-auth-02.dc.internal:5432", getsockopt() detected error
>>>>> "Connection
>>>>> > refused"
>>>>> >
>>>>> > I see nothing in the node qs-auth-01.dc.internal postgres logs. Is
>>>>> there
>>>>> > another place where pgpool looks to find postgres servers?
>>>>>
>>>>> Probably pgpool recognizes backend_hostname0 (qs-auth-01.dc.internal)
>>>>> as in "down" or "unused" status.  You can verify it by executing "show
>>>>> pool_nodes" from psql command connecting to pgpool.
>>>>>
>>>>> Best regards,
>>>>> --
>>>>> Tatsuo Ishii
>>>>> SRA OSS, Inc. Japan
>>>>> English: http://www.sraoss.co.jp/index_en.php
>>>>> Japanese:http://www.sraoss.co.jp
>>>>>
>>>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20201217/a216e026/attachment.htm>