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

Nirav Bhatt niravbhatt.cpp at gmail.com
Sun Oct 31 01:22:55 JST 2021


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


More information about the pgpool-general mailing list