MIME-Version: 1.0
References: <CAKgGyB8iYj_JavbDD13-4PknNJPji+=tbmSjk=80ZtcZmr-2nw@mail.gmail.com>
In-Reply-To: <CAKgGyB8iYj_JavbDD13-4PknNJPji+=tbmSjk=80ZtcZmr-2nw@mail.gmail.com>
From: =?UTF-8?Q?Robert_Sj=C3=B6blom?= <robert.sjoblom@fortnox.se>
Date: Wed, 16 Jul 2025 09:47:18 +0200
Message-ID: <CAA96TE2PPD9rdXYYGoGaw6AybAzNW8N_Oq0q+WuHfWtakR=YMg@mail.gmail.com>
Subject: Re: PgBouncer-Postgres : un supported startup parameter statement_timeout
To: KK CHN <kkchn.in@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d95162063a071a08"
Archived-At: <https://www.postgresql.org/message-id/CAA96TE2PPD9rdXYYGoGaw6AybAzNW8N_Oq0q%2BWuHfWtakR%3DYMg%40mail.gmail.com>
Precedence: bulk

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

There's an option to ignore parameters in pgbouncer's config. Here's an SO
answer that gives an example: https://stackoverflow.com/a/36911794

On Wed, 16 Jul 2025, 09:19 KK CHN, <kkchn.in@gmail.com> 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 x=
x
>
>
> 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 =3D host=3Ddbmain.mydomain.com port=3D5444 dbname=3Dtransa=
ction_db
>
> [users]
> [pgbouncer]
> Logfile =3D /var/log/pgbouncer/pgbouncer.log
> pidfile =3D /var/run/pgbouncer/pgbouncer.pid
> listen_addr =3D *
> listen_port =3D 5444
> auth_type =3D md5
> auth_file =3D /usr/local/etc/pgbouncer.users
> admin_users =3D adminuser
> stats_users =3D adminuser,
> pool_mode =3D transaction
> server_reset_query =3D DISCARD ALL
> server_reset_query_always =3D 1
> ignore_startup_parameters =3D extra_float_digits  // I have added this al=
so
> or can I add these options too in the following line  ?
> ;; ignore_startup_parameters =3D extra_float_digits, options,
> statement_timeout, idle_in_transaction_session_timeout  // doubt options
> supported in latest versions ?
>
> max_client_conn =3D 5000   // can I give this much high value ?
> default_pool_size =3D 20
> min_pool_size =3D 10
> reserve_pool_size =3D 10
> reserve_pool_timeout =3D 5
> max_db_connections =3D 100
> max_user_connections =3D 30
> server_lifetime =3D 3600
> server_idle_timeout =3D 600 // is this a low value or need to be  increas=
ed ?
> [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 configuration=
s
> for the application level
>
> *cat app_10072025/config/pg-pool-config.js*
> .......
>
>
>
>
>
>
>
>
> *const poolOptions =3D {    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 =3D {
>
>     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
>

--=20
Inneh=C3=A5llet i detta e-postmeddelande =C3=A4r konfidentiellt och avsett =
endast f=C3=B6r=20
adressaten.Varje spridning, kopiering eller utnyttjande av inneh=C3=A5llet =
=C3=A4r=20
f=C3=B6rbjuden utan till=C3=A5telse av avs=C3=A4ndaren.=C2=A0Om detta medde=
lande av misstag=20
g=C3=A5tt till fel adressat v=C3=A4nligen radera det ursprungliga meddeland=
et och=20
underr=C3=A4tta avs=C3=A4ndaren via e-post

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

<div dir=3D"auto">There&#39;s an option to ignore parameters in pgbouncer&#=
39;s config. Here&#39;s an SO answer that gives an example:=C2=A0<a href=3D=
"https://stackoverflow.com/a/36911794">https://stackoverflow.com/a/36911794=
</a></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"=
ltr" class=3D"gmail_attr">On Wed, 16 Jul 2025, 09:19 KK CHN, &lt;<a href=3D=
"mailto:kkchn.in@gmail.com">kkchn.in@gmail.com</a>&gt; wrote:<br></div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi,<br><br><=
div>I am facing an issue with PgBouncer-Postgres setup,=C2=A0<div><br><b>Pg=
Bouncer (PgBouncer 1.23.1 running on VM instance FreeBSD 14.2) and PostgreS=
QL 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 backend is nodeJS which throws t=
he following Error in the nodejs=C2=A0 console log: when connecting through=
 pgbouncer to the backend database server...=C2=A0<br><br>ERROR thrown:</di=
v><div><br></div><div>my-node&gt;<b>=C2=A0Failed to connect to PostgreSQL d=
atabase 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-node&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=A0earlier directly establishing conne=
ctions to the =C2=A0PostgreSQL DB ( no such errors) =C2=A0now redirected vi=
a =C2=A0 Pgbouncer throws this error.<br>=C2=A0 =C2=A0<br><br><b>The config=
 parameters for =C2=A0pgbouncer as follows(correct me if any mistakes made)=
<br></b><br>[root@pgbouncer ~]# grep ^[^&#39;;;;&#39;] /usr/local/etc/pgbou=
ncer.ini<br>[databases]<br>transaction_db =3D host=3D<a href=3D"http://dbma=
in.mydomain.com/" target=3D"_blank" rel=3D"noreferrer">dbmain.mydomain.com<=
/a>=C2=A0port=3D5444 dbname=3Dtransaction_db<br><br>[users]<br>[pgbouncer]<=
br>Logfile =3D /var/log/pgbouncer/pgbouncer.log<br>pidfile =3D /var/run/pgb=
ouncer/pgbouncer.pid<br>listen_addr =3D *<br>listen_port =3D 5444<br>auth_t=
ype =3D md5<br>auth_file =3D /usr/local/etc/pgbouncer.users<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>i=
gnore_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>;; ign=
ore_startup_parameters =3D extra_float_digits, options, statement_timeout, =
idle_in_transaction_session_timeout =C2=A0// doubt options supported in lat=
est versions ?<br><br>max_client_conn =3D 5000 =C2=A0 // can I give this mu=
ch high value ?<br>default_pool_size =3D 20<br>min_pool_size =3D 10<br>rese=
rve_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>serve=
r_idle_timeout =3D 600 // is this a low value or need to be =C2=A0increased=
 ?<br>[root@pgbouncer ~]#<br><br><br><br>The config params of =C2=A0N<b>ode=
JS application which uses nodejs connection pooling in</b>=C2=A0code as fol=
lows<br><br>the Node JS application using the following =C2=A0nodejs poolin=
g configurations for the application level<br><br><b>cat app_10072025/confi=
g/pg-pool-config.js</b><br>.......<br><b>const poolOptions =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 connectionTimeoutMillis: =
10000, =C2=A0//Reconnect 10sec<br>=C2=A0 =C2=A0 statement_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 queries<br>};</b><br><br=
>const pools =3D {<br>=C2=A0 =C2=A0<br>=C2=A0 =C2=A0 transaction_db: new Po=
ol({<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 connectionString: `postgresql://${DB_US=
ER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 ...poolOptions,<br>=C2=A0 =C2=A0 }),<br>};<br><br>=
..................................<br><br>Any =C2=A0hints=C2=A0 =C2=A0and s=
uggestions in the config params are most welcome.=C2=A0<br><br>Thank you,<b=
r>Krishane</div></div></div>
</blockquote></div>

<br>
<font face=3D"Arial, sans-serif" size=3D"1"><span style=3D"background-color=
:white">Inneh=C3=A5llet i detta e-postmeddelande =C3=A4r konfidentiellt och=
 avsett endast f=C3=B6r adressaten.</span></font><div><font size=3D"1"><fon=
t face=3D"Arial, sans-serif"><span style=3D"background-color:white">Varje s=
pridning, kopiering eller utnyttjande av inneh=C3=A5llet =C3=A4r f=C3=B6rbj=
uden utan till=C3=A5telse av avs=C3=A4ndaren.=C2=A0</span></font><span styl=
e=3D"background-color:white;font-family:Arial,sans-serif">Om detta meddelan=
de av misstag g=C3=A5tt till fel adressat v=C3=A4nligen radera det ursprung=
liga meddelandet och underr=C3=A4tta avs=C3=A4ndaren via e-post</span></fon=
t></div>
--000000000000d95162063a071a08--