[pgpool-general: 3177] Re: Cannot run 'show pool_nodes'
John Scalia
jayknowsunix at gmail.com
Thu Sep 18 23:46:35 JST 2014
I've made significant progress on this matter. Most of problem turned out to be zombie processes which were interfering with operations. I have since double-checked the entries in
iptables, and I've looked at "netstat -anp", Here's some of what it says:
tcp 0 0 0.0.0.0:9999 0.0.0.0:* LISTEN 23369/pgpool
tcp 0 127.0.0.1:5432 0.0.0.0:* LISTEN 23175/postmaster
tcp 0 172.17.1.3:5432 0.0.0.0:* LISTEN 23175/postmaster
tcp 0 10.10.1.129:5432 0.0.0.0:* LISTEN 23175/postmaster
tcp 0 0.0.0.0:9000 0.0.0.0:* LISTEN 23384/pgpool
tcp 0 0.0.0.0:9898 0.0.0.0:* LISTEN 23369/pgpool
Now, I can connect to the database with psql on port 5432 on any of these IP addresses, but if specify the 10.10.1.140 VIP address using:
/usr/pgsql-9.3/bin/psql -h 10.10.1.140 -p 9999 csg_db
The command immediately returns to the shell prompt, and I now see this in the /var/log/pgpool.log:
2014-09-18 14:29:48 LOG: pid 23401: connection received: host=10.10.1.129 port=45532
2014-09-18 14:29:48 ERROR: pid 23401: pool_read_message_length: message length (12) in slot 2 does not match with slot 0(8)
2014-09-18 14:29:48 ERROR: pid 23401: Failed to read the authentication packet length. This is likely caused by the inconsistency of auth method among DB nodes. In this case
you can check the previous error messages (hint: length field) from pool_read_message_length and recheck the pg_hba.conf settings.
OK, all well and good, and I have checked my pg_hba.conf file. I even tried changing the authentication method from md5 to password with same result. I'm really not understanding
what the first error message is telling me: what slots? what messages(?) But at least it's consistent. What do I have to alter and how can I tell what it needs changed to?
--
Jay
On 9/17/2014 7:36 PM, Tatsuo Ishii wrote:
> I suspect a network setting problem. psql without -p defaults to port
> 5432 which is bind to by PostgreSQL. If you are sure that port 9999 is
> bind to by pgpool-II and still you cannot access the port, you'd
> better to check the systems's firewall setting.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
>> The commands I've attempted are:
>>
>> /usr/pgsql-9.3/bin/psql -h 10.10.1.140 -p 9999 csg_db
>>
>> Where 10.10.1.140 is the VIP and csg_db is the database. That command returns immediately to a shell prompt and does not invoke any interpreter for SQL commands. If I do not use the -p flag, then I do receive an SQL interpreter, but of course, pgpool is not intercepting.
>>
>> Sent from my iPad
>>
>>> On Sep 17, 2014, at 1:41 PM, Ryan DeShone <rdeshone at liquidweb.com> wrote:
>>>
>>> The IP you're connecting to isn't the issue. It's the port you're connecting to. Assuming postgresql and pgpool are both running on the default port, if you do not explicitly tell psql to connect to the pgpool port (default: 9999) then you will end up connecting directly to postgresql.
>>>
>>> So, once again assuming that you did not change the default port numbers, you would need to add '-p 9999' when you execute psql to connect to pgpool.
>>>
>>>> On 09/17/2014 11:13 AM, jayknowsunix at gmail.com wrote:
>>>> I am connecting to the VIP which was brought up by pgpool. I have even restarted pgpool on each machine in the cluster and verified that the VIP was being managed by pgpool (the VIP disappeared while pgpool was off and came back when it was restarted.) i have also verified that connection pooling is on for each system. Is there something else I can check?
>>>>
>>>> Sent from my iPad
>>>>
>>>>> On Sep 17, 2014, at 10:51 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>
>>>>> You need to connect pgpool-II, not connect directry to PostgreSQL
>>>>> because "show pool_nodes" is a pgpool-II specific SQL command.
>>>>> Here is an example session.
>>>>>
>>>>> test=# show pool_nodes;
>>>>> node_id | hostname | port | status | lb_weight | role
>>>>> ---------+----------+-------+--------+-----------+---------
>>>>> 0 | /tmp | 11000 | 2 | 0.500000 | primary
>>>>> 1 | /tmp | 11001 | 2 | 0.500000 | standby
>>>>> (2 rows)
>>>>>
>>>>> Best regards,
>>>>> --
>>>>> Tatsuo Ishii
>>>>> SRA OSS, Inc. Japan
>>>>> English: http://www.sraoss.co.jp/index_en.php
>>>>> Japanese:http://www.sraoss.co.jp
>>>>>
>>>>>> In response to my question yesterday, Ishii-san suggested running 'show pool_nodes;' I tried running this command this morning in a psql session, but was unsuccessful. The error was a simple not found one. Apparently, I've missed a step during setup, and I've been going back through the online tutorial, but I'm not seeing what I need. Also, looking online at what this command does, it doesn't appear to give more information than pcp_node_info does. That utility does work for me, btw. Am I mistaken? I'm just trying to determine which pgpool instance is the master in a master-slave configuration after a failover attempt.
>>>>>> --
>>>>>> Jay
>>>>>>
>>>>>> Sent from my iPad
>>>>>> _______________________________________________
>>>>>> pgpool-general mailing list
>>>>>> pgpool-general at pgpool.net
>>>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>> _______________________________________________
>>>> pgpool-general mailing list
>>>> pgpool-general at pgpool.net
>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>> --
>>> Ryan DeShone
>>>
More information about the pgpool-general
mailing list