[pgpool-general: 9312] Re: pgpool Connection issue: ERROR: unable to read message kind

[Mukesh Tanuku]

And one thing to notice here is, when i build a schema via hammerdb, this
is getting successful 1 or 2 times out of 10 iterations and mostly failing
due the above error message.

On Thu, Jan 2, 2025 at 2:11 PM Mukesh Tanuku <mukesh.postgres at gmail.com>
wrote:

> This is the error logged in pgpool log
>
> 2025-01-02 08:37:39.892: child pid 910993: LOG:  pool_read_kind: error
> message from 2 th backend:database "tpcc" does not exist
> 2025-01-02 08:37:39.892: child pid 910993: ERROR:  unable to read message
> kind
> 2025-01-02 08:37:39.892: child pid 910993: DETAIL:  kind does not match
> between main(53) slot[2] (45)
>
> On Thu, Jan 2, 2025 at 1:58 PM Mukesh Tanuku <mukesh.postgres at gmail.com>
> wrote:
>
>> Hello Tatsuo,
>>
>> *Have you set synchronous_standby_names parameter in postgresql.conf?*
>> YES. we set it. Please see below:
>>
>> [pgbigboss at azlpgN1hS-0 ~]$ cat
>> /u01/app/admin/Data/PG_DATA/postgresql.conf | grep -v '^#' |egrep
>> '^[^[:space:]]' | grep sync
>> synchronous_commit = remote_apply
>> synchronous_standby_names = 'ANY 1 (azlpgNPBC2, azlpgN1hS0, azlpgN6Xm1)'
>>
>>
>> *1) connect to pgpool and run:SHOW pool_nodes;*
>>
>> [image: image.png]
>>
>>
>> *2) connect to primary postgres and run:SELECT
>> application_name,client_addr,state,sync_state FROM pg_stat_replication;*
>> [image: image.png]
>>
>> Let me explain what we observe and how we are establishing connectivity.
>>
>> 1. We can able to connect successfully and do a load test if we connect
>> to postgreSQL server IP using postgreSQL port (either 10.35.8.30:5432 or
>> 10.35.8.31:5432), we don't have any issue with this.
>> 2. We are having an issue if we connect to postgreSQL server IP using
>> pgpool port (either 10.35.8.30: <http://10.35.8.30:5432/>9999 or
>> 10.35.8.31: <http://10.35.8.31:5432/>9999).
>>
>> Regards
>> Mukesh Tanuku
>>
>> On Thu, Jan 2, 2025 at 12:47 PM Tatsuo Ishii <ishii at postgresql.org>
>> wrote:
>>
>>> > I can able to connect directly to Postgresql (either 10.35.8.30:5432
>>> or
>>> > 10.35.8.31:5432), no issue with it. But when i use pgpool port 9999
>>> then i
>>> > see this error.
>>>
>>> Have you set synchronous_standby_names parameter in postgresql.conf?
>>> In my case I set it to 'server1' which is the application name set to
>>> the standby server.
>>>
>>> Also let's check the synchrnous streaming replication status. Can you
>>> show the ouput of following commands?
>>>
>>> 1) connect to pgpool and run:
>>> SHOW pool_nodes;
>>>
>>> 2) connect to primary postgres and run:
>>> SELECT application_name,client_addr,state,sync_state FROM
>>> pg_stat_replication;
>>>
>>> On my local environments (pgpool, PostgreSQL primary and PostgreSQL
>>> standby are running on my laptop. pgpool port=11000, PostgreSQL primary
>>> port=11002, PostgreSQL standby port=11003)
>>>
>>> #1:
>>> test=# show pool_nodes;
>>>  node_id | hostname  | port  | status | pg_status | lb_weight |  role
>>>  | pg_role | select_cnt | load_balance_node | replication_delay |
>>> replication_state | replication_sync_state | last_status_change
>>>
>>> ---------+-----------+-------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
>>>  0       | localhost | 11002 | up     | up        | 0.500000  | primary
>>> | primary | 0          | false             | 0                 |
>>>        |                        | 2025-01-02 15:58:20
>>>  1       | localhost | 11003 | up     | up        | 0.500000  | standby
>>> | standby | 0          | true              | 0                 | streaming
>>>        | sync                   | 2025-01-02 15:58:20
>>> (2 rows)
>>>
>>> #2:
>>> test=# select application_name,client_addr,state,sync_state  from
>>> pg_stat_replication;
>>>  application_name | client_addr |   state   | sync_state
>>> ------------------+-------------+-----------+------------
>>>  server1          | 127.0.0.1   | streaming | sync
>>> (1 row)
>>>
>>> Also I can run following command, which creates "test2" database, and
>>> then immediately connects the newly created database "test".
>>>
>>> t-ishii$ psql -p 11000 -c "create database test2" postgres;psql -p 11000
>>> -c "select 1" test2
>>> CREATE DATABASE
>>>  ?column?
>>> ----------
>>>         1
>>> (1 row)
>>>
>>> Best reagards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS K.K.
>>> English: http://www.sraoss.co.jp/index_en/
>>> Japanese:http://www.sraoss.co.jp
>>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250102/2bcf6bd7/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 245804 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250102/2bcf6bd7/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 80875 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250102/2bcf6bd7/attachment-0003.png>