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

Nirav Bhatt niravbhatt.cpp at gmail.com
Sun Oct 31 00:42:24 JST 2021 

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/734d4900/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Archive.zip
Type: application/zip
Size: 13594 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211030/734d4900/attachment-0001.zip>

More information about the pgpool-general mailing list