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

Nirav Bhatt niravbhatt.cpp at gmail.com
Sun Oct 31 01:24:34 JST 2021 

I also suspect if following line is true for 4.1 setup:


*From Pgpool-II 4.2, now all configuration parameters are identical on all
hosts*

-Nirav

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

> Hello
>
> I am following this link, which is pgpool2 v4.1.
>
> https://www.pgpool.net/docs/41/en/html/example-cluster.html
>
> On my Ubuntu setup also that is the latest I can get:
> *4.1.4-3.pgdg20.04+2*
>
> If you still insist, I will try to set it up, but I guess you are quoting
> from this link:
>
>
> https://www.pgpool.net/docs/latest/en/html/example-cluster.html#EXAMPLE-CLUSTER-PGPOOL-NODE-ID
>
> It is a more recent version of pgpool2 than what I have.
>
> -Nirav
>
> On Sat, Oct 30, 2021 at 7:03 PM Luca Maranzano <liuk001 at gmail.com> wrote:
>
>> 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/2783fa56/attachment-0001.htm>

More information about the pgpool-general mailing list