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

[Luís Alves]

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/df3c1948/attachment-0001.htm>