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.