0

I have a running streaming replication between two "debian 11" virtualBoxes (with bridged network adaptators) running a master/slave postgreSQL configuration. That configuration is correctly accessed through pgpool2. The main configurations points are:

MASTER (v. 15.2): 192.168.1.57:5432
SLAVE (v. 15.2): 192.168.1.50:5432
PGPOOL (v. 4.3.5): 192.168.1.57:5443 (so same VM than master, but different port)

As said, pgpool2 accessing that configuration perfectly well with load balancing tested though (launched through pgpool installed VM):

pgbench -p 5443 -c 10 -j 10 -S -T 60 bench_test

with bench_replication created/fed though:

createdb -p 5443 bench_test
pgbench -i -p 5443 bench_test

and, specified in pgpool.conf:

sr_check_database = 'bench_test'

The result, using the following command, shows an effective (+-55%/45%) load balancing between the master and its slave:

psql -p 5443 -c "SHOW POOL_PROCESSES" bench_replication

So everything works perfectly...except when I try to do tests through nodejs (launched from Windows host):

import { Connection } from 'postgresql-client';

let connection = new Connection('postgres://postgres@192.168.1.57:5432');    --> direct connection to postgresql
await connection.connect();
let result = await connection.query('select * from base where id < 100');    --> returns 100 rows
console.log("MASTER: ", result);
await connection.close();
//-----
connection = new Connection('postgres://postgres@192.168.1.57:5443');    --> connection to pgpool2
await connection.connect();
result = await connection.query('select * from base where id < 100');    --> never returns at all
console.log("POOL: ", result);
await connection.close();

As you see, pgpool2 query never returns, while direct access to postgreSQL succeeds. If I kill the nodejs process when it is blocked on the pgpool2 query, then I directly see, in pgpool2 logs:

[unknown] pid 4538: ERROR:  unable to read data from frontend
[unknown] pid 4538: DETAIL:  socket read failed with error "Connection reset by peer"

proving that a connection with pgpool2 was indeed created, but as if it was not fed with the SQL query. Even stranger, the query/connection never times out ! Does it ring a bell to anyone ? My guess goes toward the connection string (protocol wouldn't be postgres ?), but I have no clue on how to check it...

EDIT (possible fix): it seems that the problem comes from postgresql-client package query() function (which permits to use prepared statements). If I use execute() function instead (which can't process prepared statements) and adapt statements accordingly, everything runs fine.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
MamorukunBE
  • 120
  • 1
  • 9

0 Answers0