On 7/16/25 08:40, Achilleas Mantzios wrote:
On 7/16/25 08:22, KK CHN wrote:
Hi,
I am facing an issue with PgBouncer-Postgres setup,
*PgBouncer (PgBouncer 1.23.1 running on VM instance FreeBSD 14.2) and
PostgreSQL DB cluster on a virtual machine (PG version 16.0, RHEL
9.4) *.
My application backend is nodeJS which throws the following Error in
the nodejs console log: when connecting through pgbouncer to the
backend database server...
ERROR thrown:
my-node>* Failed to connect to PostgreSQL database transaction_db :
unsupported startup parameter: statement_timeout *{"code":"08P01",
"length":69, "name": "error", xx xx
my-node> error:[object Object ] {"timestamp":"15-07-2025 10:14:26"} x
x xx
Note: The nodejs apps earlier directly establishing connections to
the PostgreSQL DB ( no such errors) now redirected via Pgbouncer
throws this error.
How? I am getting , connecting directly to postgresql, or pgpool, or
pgbouncer :
achill@smadevnu:~ % psql
"postgresql://localhost:5432?statement_timeout=10"
psql: error: invalid URI query parameter: "statement_timeout"
achill@smadevnu:~ % psql
"postgresql://localhost:9999?statement_timeout=10"
psql: error: invalid URI query parameter: "statement_timeout"
achill@smadevnu:~ % psql
"postgresql://localhost:6432?statement_timeout=10"
psql: error: invalid URI query parameter: "statement_timeout"
the error is within : src/interfaces/libpq/fe-connect.c
Ok looked a little bit further, in case nodejs driver sends this
(statement_timeout) via the options startup packet, as in :
PGOPTIONS=" -c statement_timeout=10" psql "postgresql://localhost:5432"
then yes, you will have to include this in pgbouncer's
|ignore_startup_parameters|
as explained here : https://www.pgbouncer.org/config.html
*The config parameters for pgbouncer as follows(correct me if any
mistakes made)
*
[root@pgbouncer ~]# grep ^[^';;;'] /usr/local/etc/pgbouncer.ini
[databases]
transaction_db = host=dbmain.mydomain.com
<http://dbmain.mydomain.com/> port=5444 dbname=transaction_db
[users]
[pgbouncer]
Logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5444
auth_type = md5
auth_file = /usr/local/etc/pgbouncer.users
admin_users = adminuser
stats_users = adminuser,
pool_mode = transaction
server_reset_query = DISCARD ALL
server_reset_query_always = 1
ignore_startup_parameters = extra_float_digits // I have added this
also or can I add these options too in the following line ?
;; ignore_startup_parameters = extra_float_digits, options,
statement_timeout, idle_in_transaction_session_timeout // doubt
options supported in latest versions ?
max_client_conn = 5000 // can I give this much high value ?
default_pool_size = 20
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 100
max_user_connections = 30
server_lifetime = 3600
server_idle_timeout = 600 // is this a low value or need to be
increased ?
[root@pgbouncer ~]#
The config params of N*odeJS application which uses nodejs
connection pooling in* code as follows
the Node JS application using the following nodejs pooling
configurations for the application level
*cat app_10072025/config/pg-pool-config.js*
.......
*const poolOptions = {
max: 10,
min: 2,
idleTimeoutMillis: 600000, //Idle for 5Min
connectionTimeoutMillis: 10000, //Reconnect 10sec
statement_timeout: 60000, //Query executiion 1 min
acquire: 20000,
maxUses: 1000 //reconnect after 1000 queries
};*
const pools = {
transaction_db: new Pool({
connectionString:
`postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
...poolOptions,
}),
};
..................................
Any hints and suggestions in the config params are most welcome.
Thank you,
Krishane
