MIME-Version: 1.0
From: KK CHN <kkchn.in@gmail.com>
Date: Wed, 16 Jul 2025 12:52:54 +0530
Message-ID: <CAKgGyB8iYj_JavbDD13-4PknNJPji+=tbmSjk=80ZtcZmr-2nw@mail.gmail.com>
Subject: PgBouncer-Postgres : un supported startup parameter statement_timeout
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f6f2f8063a06b6cd"
Archived-At: <https://www.postgresql.org/message-id/CAKgGyB8iYj_JavbDD13-4PknNJPji%2B%3DtbmSjk%3D80ZtcZmr-2nw%40mail.gmail.com>
Precedence: bulk

--000000000000f6f2f8063a06b6cd
Content-Type: text/plain; charset="UTF-8"

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.


*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 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

--000000000000f6f2f8063a06b6cd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<br><br><div>I am facing an issue with PgBouncer-Postgr=
es setup,=C2=A0<div><br><b>PgBouncer (PgBouncer 1.23.1 running on VM instan=
ce FreeBSD 14.2) and PostgreSQL DB cluster on a virtual machine (PG version=
 16.0, =C2=A0RHEL 9.4)=C2=A0</b>. =C2=A0<br><br><br>My=C2=A0application bac=
kend is nodeJS which throws the following Error in the nodejs=C2=A0 console=
 log: when connecting through pgbouncer to the backend database server...=
=C2=A0<br><br>ERROR thrown:</div><div><br></div><div>my-node&gt;<b>=C2=A0Fa=
iled to connect to PostgreSQL database transaction_db : unsupported startup=
 parameter: statement_timeout=C2=A0</b>{&quot;code&quot;:&quot;08P01&quot;,=
 &quot;length&quot;:69, &quot;name&quot;: &quot;error&quot;, xx xx<br>my-no=
de&gt; error:[object Object ] {&quot;timestamp&quot;:&quot;15-07-2025 10:14=
:26&quot;} x x xx<br><br><br>Note:=C2=A0 =C2=A0The nodejs apps =C2=A0earlie=
r directly establishing connections to the =C2=A0PostgreSQL DB ( no such er=
rors) =C2=A0now redirected via =C2=A0 Pgbouncer throws this error.<br>=C2=
=A0 =C2=A0<br><br><b>The config parameters for =C2=A0pgbouncer as follows(c=
orrect me if any mistakes made)<br></b><br>[root@pgbouncer ~]# grep ^[^&#39=
;;;;&#39;] /usr/local/etc/pgbouncer.ini<br>[databases]<br>transaction_db =
=3D host=3D<a href=3D"http://dbmain.mydomain.com/" target=3D"_blank">dbmain=
.mydomain.com</a>=C2=A0port=3D5444 dbname=3Dtransaction_db<br><br>[users]<b=
r>[pgbouncer]<br>Logfile =3D /var/log/pgbouncer/pgbouncer.log<br>pidfile =
=3D /var/run/pgbouncer/pgbouncer.pid<br>listen_addr =3D *<br>listen_port =
=3D 5444<br>auth_type =3D md5<br>auth_file =3D /usr/local/etc/pgbouncer.use=
rs<br>admin_users =3D adminuser<br>stats_users =3D adminuser,<br>pool_mode =
=3D transaction<br>server_reset_query =3D DISCARD ALL<br>server_reset_query=
_always =3D 1<br>ignore_startup_parameters =3D extra_float_digits =C2=A0// =
I have added this also or can I add these options too in the following line=
 =C2=A0?<br>;; ignore_startup_parameters =3D extra_float_digits, options, s=
tatement_timeout, idle_in_transaction_session_timeout =C2=A0// doubt option=
s supported in latest versions ?<br><br>max_client_conn =3D 5000 =C2=A0 // =
can I give this much high value ?<br>default_pool_size =3D 20<br>min_pool_s=
ize =3D 10<br>reserve_pool_size =3D 10<br>reserve_pool_timeout =3D 5<br>max=
_db_connections =3D 100<br>max_user_connections =3D 30<br>server_lifetime =
=3D 3600<br>server_idle_timeout =3D 600 // is this a low value or need to b=
e =C2=A0increased ?<br>[root@pgbouncer ~]#<br><br><br><br>The config params=
 of =C2=A0N<b>odeJS application which uses nodejs connection pooling in</b>=
=C2=A0code as follows<br><br>the Node JS application using the following =
=C2=A0nodejs pooling configurations for the application level<br><br><b>cat=
 app_10072025/config/pg-pool-config.js</b><br>.......<br><b>const poolOptio=
ns =3D {<br>=C2=A0 =C2=A0 max: 10,<br>=C2=A0 =C2=A0 min: 2,<br>=C2=A0 =C2=
=A0 idleTimeoutMillis: 600000, =C2=A0 //Idle for 5Min<br>=C2=A0 =C2=A0 conn=
ectionTimeoutMillis: 10000, =C2=A0//Reconnect 10sec<br>=C2=A0 =C2=A0 statem=
ent_timeout: 60000, =C2=A0 =C2=A0//Query executiion 1 min<br>=C2=A0 =C2=A0 =
acquire: 20000,<br>=C2=A0 =C2=A0 maxUses: 1000 //reconnect after 1000 queri=
es<br>};</b><br><br>const pools =3D {<br>=C2=A0 =C2=A0<br>=C2=A0 =C2=A0 tra=
nsaction_db: new Pool({<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 connectionString: `p=
ostgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME_TRANS=
ACTION_DB}`,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ...poolOptions,<br>=C2=A0 =C2=
=A0 }),<br>};<br><br>..................................<br><br>Any =C2=A0hi=
nts=C2=A0 =C2=A0and suggestions in the config params are most welcome.=C2=
=A0<br><br>Thank you,<br>Krishane</div></div></div>

--000000000000f6f2f8063a06b6cd--