[pgpool-general: 7835] Re: pgpool-general Digest, Vol 120, Issue 45

Luca Maranzano liuk001 at gmail.com
Sun Oct 31 01:02:49 JST 2021


Hello,
I think that the pgpool_node_id file is mandatory since it is used by every
server to identify itself in the cluster with its node id.
Where is it documented that it is not necessary?

Try to configure it

HTH
Regards
Luca





On Sat, Oct 30, 2021 at 5:42 PM Nirav Bhatt <niravbhatt.cpp at gmail.com>
wrote:

> Hello
> Sorry for the late answer.
>
> I now made pgpool.conf identical on all 3 servers, then restarted
> postgresql and pgpool2 services.
>
> I opened ports on each server: 22, 9999/tcp, 9898/tcp, 9000/tcp, 9694/udp,
> 5433, 5432
>
> Yet the problem persists on server2 and server3 - it can not connect to
> 9000 port (on themselves)
> Server1 pgpool seems to be running without problem.
>
> I am attaching my pgpool.conf file. Also, I am attaching pgpool2 service
> logs from all 3 servers.
>
> 10.192.10.10 server1
> 10.192.11.20 server2
> 10.192.12.30 server3
>
> Virtual IP is 20.20.20.20
>
> I am running the following pgpool2 version so I do not think I need
> pgpool_node_id file.
>
> 4.1.4-3.pgdg20.04+2
>
> Thanks for the support,
> -Nirav
>
>
> On Wed, Oct 27, 2021 at 8:48 AM Luca Maranzano <liuk001 at gmail.com> wrote:
>
>> Hi, still not clear to me, from the doc:
>>
>> 8.2.5. Create pgpool_node_id
>>
>> From Pgpool-II 4.2, now all configuration parameters are identical on
>> all hosts. If watchdog feature is enabled, to distingusish which host is
>> which, a pgpool_node_id file is required. You need to create a
>> pgpool_node_id file and specify the pgpool (watchdog) node number (e.g.
>> 0, 1, 2 ...) to identify pgpool (watchdog) host.
>>
>> In our systems the pgpool.conf are *identical *
>>
>>
>>
>> On Tue, Oct 26, 2021 at 8:09 PM Nirav Bhatt <niravbhatt.cpp at gmail.com>
>> wrote:
>>
>>> The difference lies in “other” server host names.
>>>
>>> -Nirav
>>>
>>> On Tue, 26 Oct 2021 at 21.07, Luca Maranzano <liuk001 at gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> AFAIK the pgpool.conf file must be identical on all cluster nodes.
>>>> Which are the differences in detail?
>>>>
>>>> Cheers
>>>> Luca
>>>>
>>>>
>>>> On Mon, Oct 25, 2021 at 9:46 PM Nirav Bhatt <niravbhatt.cpp at gmail.com>
>>>> wrote:
>>>>
>>>>> I added all ports via ufw on server1: 22,9999,9898,9000,9694,5433,5432
>>>>>
>>>>> I enabled firewall past that point.
>>>>>
>>>>> I diffed all conf files across servers - all files are same, including
>>>>> hba and .pgpass and .pcppass.
>>>>> Only pgpool.conf files differ, that too in parameters like server
>>>>> hosts for pgpool and watchdog, no other differences.
>>>>>
>>>>> No error is observed on primary server1. The last log lines on server1
>>>>> says: new watchdog node connection is received from <server2> or <server3>
>>>>>
>>>>> But secondary server pgpool2 keeps exiting with:
>>>>>
>>>>> Add to watchdog cluster request is rejected by node "server1:9000"
>>>>>
>>>>> I somehow suspect it has to do with wd_authkey which I leave empty:
>>>>> https://www.sraoss.jp/pipermail/pgpool-hackers/2017-August/002497.html
>>>>>
>>>>> But it is an old issue, so I can't say for sure.
>>>>>
>>>>> -Nirav
>>>>>
>>>>>
>>>>> On Mon, Oct 25, 2021 at 8:07 PM Luca Maranzano <liuk001 at gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Firewall-cmd has to be used on Redhat derived flavours, on Ubuntu
>>>>>> standard is ufw, so "ufw disable" should open all ports
>>>>>>
>>>>>> On Mon, Oct 25, 2021 at 6:59 PM Nirav Bhatt <niravbhatt.cpp at gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> I ran sudo ufw status and firewall is already inactive.
>>>>>>>
>>>>>>> Not sure what firewall-cmd did for all the pgpool ports. (they are
>>>>>>> showing as list of open ports)
>>>>>>>
>>>>>>> Should add them all via ufw also?
>>>>>>>
>>>>>>> -Nirav
>>>>>>>
>>>>>>> On Mon, Oct 25, 2021 at 7:46 PM Luca Maranzano <liuk001 at gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Try to disable firewall to see it it's related, all the pgpool
>>>>>>>> configuration files are identical on the 3 nodes?
>>>>>>>>
>>>>>>>> On Mon, Oct 25, 2021 at 6:41 PM Nirav Bhatt <
>>>>>>>> niravbhatt.cpp at gmail.com> wrote:
>>>>>>>>
>>>>>>>>> Hello,
>>>>>>>>>
>>>>>>>>> Thanks for the response.
>>>>>>>>>
>>>>>>>>> I restarted pgpool2 service on all 3 nodes (with some delay, and
>>>>>>>>> in the order: primary, secondary1, secondary2).
>>>>>>>>> My primary pgpool2 service logs do show
>>>>>>>>>
>>>>>>>>> LOG:  new watchdog node connection is received from <secondary
>>>>>>>>> host>
>>>>>>>>>
>>>>>>>>> But my secondary pgpool2 service logs (among other normal
>>>>>>>>> messages) show:
>>>>>>>>>
>>>>>>>>> FATAL:  Add to watchdog cluster request is rejected by node
>>>>>>>>> "server1:9000"
>>>>>>>>> FATAL:  watchdog child process exit with fatal error. exiting
>>>>>>>>> pgpool-II
>>>>>>>>> LOG:  watchdog node state changed from [DEAD] to [LOADING]
>>>>>>>>> LOG:  new outbound connection to server1:9000
>>>>>>>>> FATAL:  Add to watchdog cluster request is rejected by node
>>>>>>>>> "server1:9000"
>>>>>>>>> HINT:  check the watchdog configurations.
>>>>>>>>> LOG:  Watchdog is shutting down
>>>>>>>>>
>>>>>>>>> I checked that server1 has 9000 port open, along with 9999, 9898
>>>>>>>>> and 9694:
>>>>>>>>>
>>>>>>>>> > firewall-cmd --list-ports
>>>>>>>>> 9999/tcp 9898/tcp 9000/tcp 9694/udp
>>>>>>>>>
>>>>>>>>> What could be going on?
>>>>>>>>>
>>>>>>>>> -Nirav
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Mon, Oct 25, 2021 at 7:13 PM Luca Maranzano <liuk001 at gmail.com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>> if you cannot connect to 20.20.20.20 -p 9999 it means that hte
>>>>>>>>>> PGPool cluster is not well formed and that the VIP has not been enabled.
>>>>>>>>>>
>>>>>>>>>> You can check that with pcp_watchdog_info.
>>>>>>>>>> The logs from pgpool should also help to diagnose the problem.
>>>>>>>>>>
>>>>>>>>>> HTH
>>>>>>>>>> Regards
>>>>>>>>>> Luca
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> ---------- Forwarded message ---------
>>>>>>>>>> From: <pgpool-general-request at pgpool.net>
>>>>>>>>>> Date: Mon, Oct 25, 2021 at 5:00 AM
>>>>>>>>>> Subject: pgpool-general Digest, Vol 120, Issue 45
>>>>>>>>>> To: <pgpool-general at pgpool.net>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Send pgpool-general mailing list submissions to
>>>>>>>>>>         pgpool-general at pgpool.net
>>>>>>>>>>
>>>>>>>>>> To subscribe or unsubscribe via the World Wide Web, visit
>>>>>>>>>>         https://www.pgpool.net/mailman/listinfo/pgpool-general
>>>>>>>>>> or, via email, send a message with subject or body 'help' to
>>>>>>>>>>         pgpool-general-request at pgpool.net
>>>>>>>>>>
>>>>>>>>>> You can reach the person managing the list at
>>>>>>>>>>         pgpool-general-owner at pgpool.net
>>>>>>>>>>
>>>>>>>>>> When replying, please edit your Subject line so it is more
>>>>>>>>>> specific
>>>>>>>>>> than "Re: Contents of pgpool-general digest..."
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Today's Topics:
>>>>>>>>>>
>>>>>>>>>>    1. [pgpool-general: 7822] Pgpool2 example with watchdog: Unable
>>>>>>>>>>       to test (Nirav Bhatt)
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> ----------------------------------------------------------------------
>>>>>>>>>>
>>>>>>>>>> Message: 1
>>>>>>>>>> Date: Sun, 24 Oct 2021 20:18:28 +0300
>>>>>>>>>> From: Nirav Bhatt <niravbhatt.cpp at gmail.com>
>>>>>>>>>> To: pgpool-general at pgpool.net
>>>>>>>>>> Subject: [pgpool-general: 7822] Pgpool2 example with watchdog:
>>>>>>>>>> Unable
>>>>>>>>>>         to test
>>>>>>>>>> Message-ID:
>>>>>>>>>>         <CABz_77PMu9ksuYM2QGBaAuf=m_edGoKcidmWqC=K7Rpr=
>>>>>>>>>> J_ZGw at mail.gmail.com>
>>>>>>>>>> Content-Type: text/plain; charset="utf-8"
>>>>>>>>>>
>>>>>>>>>> After doing all the config (config files, passwordless ssh,
>>>>>>>>>> firewall-cmd
>>>>>>>>>> etc) as given in the following link, I am unable to test my
>>>>>>>>>> setup. My OS is
>>>>>>>>>> Ubuntu 20.04.3 LTS.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> https://www.pgpool.net/docs/41/en/html/example-cluster.html#EXAMPLE-CLUSTER-STRUCTURE
>>>>>>>>>>
>>>>>>>>>> All the port numbers are as listed in the example.
>>>>>>>>>>
>>>>>>>>>> The part in example section: 8.3.7.1 and onwards is not working.
>>>>>>>>>>
>>>>>>>>>> When I run on server1 (my primary):
>>>>>>>>>> psql -h 20.20.20.20 -p 9999 -U pgpool postgres -c "show
>>>>>>>>>> pool_nodes"
>>>>>>>>>>
>>>>>>>>>> I got error:
>>>>>>>>>>
>>>>>>>>>> psql: error: connection to server at "20.20.20.20", port 9999
>>>>>>>>>> failed:
>>>>>>>>>> Connection refused
>>>>>>>>>> Is the server running on that host and accepting TCP/IP
>>>>>>>>>> connections?
>>>>>>>>>>
>>>>>>>>>> I restarted postgresql and pgpool2 services time and again.
>>>>>>>>>>
>>>>>>>>>> pgpool2 service most recent logs:
>>>>>>>>>>
>>>>>>>>>> server1 (current primary):
>>>>>>>>>> Oct 24 17:01:41 ip-10-192-10-10 pgpool[104691]: 2021-10-24
>>>>>>>>>> 17:01:41: pid
>>>>>>>>>> 104691: LOG:  new watchdog node connection is received from
>>>>>>>>>> "<server1
>>>>>>>>>> host>:16553"
>>>>>>>>>> Oct 24 17:01:41 ip-10-192-10-10 pgpool[104691]: 2021-10-24
>>>>>>>>>> 17:01:41: pid
>>>>>>>>>> 104691: LOG:  new watchdog node connection is received from
>>>>>>>>>> "<server1
>>>>>>>>>> host>:17577"
>>>>>>>>>> Oct 24 17:04:02 ip-10-192-10-10 pgpool[104691]: 2021-10-24
>>>>>>>>>> 17:04:02: pid
>>>>>>>>>> 104691: LOG:  new watchdog node connection is received from
>>>>>>>>>> "<server2
>>>>>>>>>> host>:49304"
>>>>>>>>>> Oct 24 17:04:02 ip-10-192-10-10 pgpool[104691]: 2021-10-24
>>>>>>>>>> 17:04:02: pid
>>>>>>>>>> 104691: LOG:  new watchdog node connection is received from
>>>>>>>>>> "<server2
>>>>>>>>>> host>:50328"
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-10-10 pgpool[104691]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 104691: LOG:  new watchdog node connection is received from
>>>>>>>>>> "<server3
>>>>>>>>>> host>:51864"
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-10-10 pgpool[104691]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 104691: LOG:  new watchdog node connection is received from
>>>>>>>>>> "<server3
>>>>>>>>>> host>:52888"
>>>>>>>>>>
>>>>>>>>>> server2 and server3 (looks like pgpool2 service is failing):
>>>>>>>>>>
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  setting the local watchdog node name to
>>>>>>>>>> "server3:5433 Linux
>>>>>>>>>> ip-<server 3 host>"
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  watchdog cluster is configured with 2 remote nodes
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  watchdog remote node:0 on server1:9000
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  watchdog remote node:1 on server2:9000
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  interface monitoring is disabled in watchdog
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  watchdog node state changed from [DEAD] to [LOADING]
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  new outbound connection to server1:9000
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: FATAL:  Add to watchdog cluster request is rejected by
>>>>>>>>>> node
>>>>>>>>>> "server1:9000"
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: HINT:  check the watchdog configurations.
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125018]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125018: LOG:  Watchdog is shutting down
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125014]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125014: LOG:  watchdog child process with pid: 125018 exits with
>>>>>>>>>> status 768
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125014]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125014: FATAL:  watchdog child process exit with fatal error.
>>>>>>>>>> exiting
>>>>>>>>>> pgpool-II
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  setting the local watchdog node name to
>>>>>>>>>> "server3:5433 Linux
>>>>>>>>>> ip-<server 3 host>"
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  watchdog cluster is configured with 2 remote nodes
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  watchdog remote node:0 on server1:9000
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  watchdog remote node:1 on server2:9000
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  interface monitoring is disabled in watchdog
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  watchdog node state changed from [DEAD] to [LOADING]
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  new outbound connection to server1:9000
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 pgpool[125019]: 2021-10-24
>>>>>>>>>> 17:11:12: pid
>>>>>>>>>> 125019: LOG:  Watchdog is shutting down
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 systemd[1]: pgpool2.service: Main
>>>>>>>>>> process
>>>>>>>>>> exited, code=exited, status=3/NOTIMPLEMENTED
>>>>>>>>>> Oct 24 17:11:12 ip-10-192-12-30 systemd[1]: pgpool2.service:
>>>>>>>>>> Failed with
>>>>>>>>>> result 'exit-code'.
>>>>>>>>>>
>>>>>>>>>> What steps am I missing?
>>>>>>>>>>
>>>>>>>>>> Thank you for the assistance,
>>>>>>>>>> -Nirav
>>>>>>>>>> -------------- next part --------------
>>>>>>>>>> An HTML attachment was scrubbed...
>>>>>>>>>> URL: <
>>>>>>>>>> http://www.pgpool.net/pipermail/pgpool-general/attachments/20211024/98ab6d68/attachment-0001.htm
>>>>>>>>>> >
>>>>>>>>>>
>>>>>>>>>> ------------------------------
>>>>>>>>>>
>>>>>>>>>> Subject: Digest Footer
>>>>>>>>>>
>>>>>>>>>> _______________________________________________
>>>>>>>>>> pgpool-general mailing list
>>>>>>>>>> pgpool-general at pgpool.net
>>>>>>>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> ------------------------------
>>>>>>>>>>
>>>>>>>>>> End of pgpool-general Digest, Vol 120, Issue 45
>>>>>>>>>> ***********************************************
>>>>>>>>>>
>>>>>>>>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211030/588b037e/attachment-0001.htm>


More information about the pgpool-general mailing list