<div dir="ltr">This is the error logged in pgpool log<div><br></div><div>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<br>2025-01-02 08:37:39.892: child pid 910993: ERROR: unable to read message kind<br>2025-01-02 08:37:39.892: child pid 910993: DETAIL: kind does not match between main(53) slot[2] (45)</div></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Thu, Jan 2, 2025 at 1:58 PM Mukesh Tanuku <<a href="mailto:mukesh.postgres@gmail.com">mukesh.postgres@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div>Hello Tatsuo,</div><div><br></div><div><i>Have you set synchronous_standby_names parameter in postgresql.conf?</i></div><div>YES. we set it. Please see below:</div><div><br></div><div>[pgbigboss@azlpgN1hS-0 ~]$ cat /u01/app/admin/Data/PG_DATA/postgresql.conf | grep -v '^#' |egrep '^[^[:space:]]' | grep sync<br>synchronous_commit = remote_apply <br>synchronous_standby_names = 'ANY 1 (azlpgNPBC2, azlpgN1hS0, azlpgN6Xm1)'</div><div dir="ltr"><br></div><div><i>1) connect to pgpool and run:<br>SHOW pool_nodes;</i></div><div dir="ltr"><br><div><img src="cid:ii_m5f1zbbb0" alt="image.png" width="1064" height="238" style="margin-right: 0px;"><br></div><div><br></div><div><i>2) connect to primary postgres and run:<br>SELECT application_name,client_addr,state,sync_state FROM pg_stat_replication;</i></div><div><img src="cid:ii_m5f2197h1" alt="image.png" width="562" height="110"><br></div><div><br></div><div>Let me explain what we observe and how we are establishing connectivity.</div><div><br></div><div>1. We can able to connect successfully and do a load test if we connect to postgreSQL server IP using postgreSQL port (<span style="color:rgb(80,0,80)">either </span><a href="http://10.35.8.30:5432/" rel="noreferrer" target="_blank">10.35.8.30:5432</a><span style="color:rgb(80,0,80)"> or </span><a href="http://10.35.8.31:5432/" rel="noreferrer" target="_blank">10.35.8.31:5432</a>), we don't have any issue with this.</div><div>2. We are having an issue if we connect to postgreSQL server IP using pgpool port<span style="background-color:rgb(255,255,255)"> <font color="#cc0000">(either <a href="http://10.35.8.30:5432/" rel="noreferrer" target="_blank">10.35.8.30:</a>9999 or <a href="http://10.35.8.31:5432/" rel="noreferrer" target="_blank">10.35.8.31:</a>9999). </font></span></div><div><span style="background-color:rgb(255,255,255)"><font color="#cc0000"><br></font></span></div><div><span style="background-color:rgb(255,255,255)"><font color="#000000">Regards</font></span></div><div><span style="background-color:rgb(255,255,255)"><font color="#000000">Mukesh Tanuku</font></span></div></div></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jan 2, 2025 at 12:47 PM Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> I can able to connect directly to Postgresql (either <a href="http://10.35.8.30:5432" rel="noreferrer" target="_blank">10.35.8.30:5432</a> or<br>
> <a href="http://10.35.8.31:5432" rel="noreferrer" target="_blank">10.35.8.31:5432</a>), no issue with it. But when i use pgpool port 9999 then i<br>
> see this error.<br>
<br>
Have you set synchronous_standby_names parameter in postgresql.conf?<br>
In my case I set it to 'server1' which is the application name set to<br>
the standby server.<br>
<br>
Also let's check the synchrnous streaming replication status. Can you<br>
show the ouput of following commands?<br>
<br>
1) connect to pgpool and run:<br>
SHOW pool_nodes;<br>
<br>
2) connect to primary postgres and run:<br>
SELECT application_name,client_addr,state,sync_state FROM pg_stat_replication;<br>
<br>
On my local environments (pgpool, PostgreSQL primary and PostgreSQL<br>
standby are running on my laptop. pgpool port=11000, PostgreSQL primary port=11002, PostgreSQL standby port=11003)<br>
<br>
#1:<br>
test=# show pool_nodes;<br>
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 <br>
---------+-----------+-------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<br>
0 | localhost | 11002 | up | up | 0.500000 | primary | primary | 0 | false | 0 | | | 2025-01-02 15:58:20<br>
1 | localhost | 11003 | up | up | 0.500000 | standby | standby | 0 | true | 0 | streaming | sync | 2025-01-02 15:58:20<br>
(2 rows)<br>
<br>
#2:<br>
test=# select application_name,client_addr,state,sync_state from pg_stat_replication;<br>
application_name | client_addr | state | sync_state <br>
------------------+-------------+-----------+------------<br>
server1 | 127.0.0.1 | streaming | sync<br>
(1 row)<br>
<br>
Also I can run following command, which creates "test2" database, and<br>
then immediately connects the newly created database "test".<br>
<br>
t-ishii$ psql -p 11000 -c "create database test2" postgres;psql -p 11000 -c "select 1" test2<br>
CREATE DATABASE<br>
?column? <br>
----------<br>
1<br>
(1 row)<br>
<br>
Best reagards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS K.K.<br>
English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
</blockquote></div>
</blockquote></div>