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

[Mukesh Tanuku]

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/a331e932/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/a331e932/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/a331e932/attachment-0003.png>