Re: PGBouncer - Load balancing options

Hello, > On Oct 10, 2024, at 03:02, Ravi Varma Addala wrote: > > Hello Team,I wanted to reach out to provide some insight into a request from > my customer regarding their multiple PostgreSQL Flex instances with > PGbouncer. Currently, they are utilizing an Azure Load Balancer which > distrib

Re: pgbouncer best practices

On Sun, Jul 9, 2023 at 6:28 AM Rita wrote: > > Is there a way to see how many queued connections there are? Looking at the > stats I can't seem to figure that out. pgbouncer exposes quite a few statistics via the SHOW commands [1]. See if any of those help. [1]: https://www.pgbouncer.org/usage.

Re: pgbouncer best practices

Thanks for the tips. So far, I am very happy with PGbouncer. Just increased number of db connections. Great piece of software! Is there a way to see how many queued connections there are? Looking at the stats I can't seem to figure that out. On Sat, Jul 8, 2023 at 9:46 AM Ben Chobot wrote: > Ri

Re: pgbouncer best practices

Rita wrote on 7/8/23 4:27 AM: I am not sure if it allows transaction pooling. Well, take the time to figure it out. With transaction pooling enabled, you can get away with a much lower number of server connections. For example, our application regularly has thousands of clients connected to

Re: pgbouncer best practices

I am not sure if it allows transaction pooling. On Fri, Jul 7, 2023 at 12:37 PM Ben Chobot wrote: > Rita wrote on 7/7/23 9:23 AM: > > I have an application that does many db calls from a server farm. I've > increased my max connections on postgresql to 1000 and tuned the server > accordingly.

Re: pgbouncer best practices

On Fri, Jul 7, 2023 at 9:24 AM Rita wrote: > > My question are: is there a ratio of max connections and pool i should use in > my pgbouncer config? Short answer: No, there's no recommended ratio for PG max_connections and pgbouncer pool size. In pgbouncer, a client connection is matched (forwar

Re: pgbouncer best practices

Rita wrote on 7/7/23 9:23 AM: I have an  application that does many db calls  from a server farm. I've increased my max connections on postgresql to 1000 and tuned the server accordingly. However, I still get can't connect to postgresql some times. I installed pgbouncer on few servers in the fa

Re: pgbouncer

Laurenz Albe wrote on 6/28/23 5:27 AM: On Wed, 2023-06-28 at 07:19 -0400, Rita wrote: seems like I may need to deploy pgbouncer for my webapp. should i deploy it on the db server or on the webserver? On the database server. You don't want network latency between pgbouncer and PostgreSQL, so

Re: pgbouncer

I have a dedicated server. I have 3 additional physical application servers which hit the database. I periodically get the application server not able to reach the database server (is server on port 5432 listening? message). Looking at some metrics, seems like I am pulling a lot of tuples (300k or

Re: pgbouncer

On the DB server: the bouncer is made to optimize many and fast connections to the DB and fasten the throughput, so You've to have the shortest way to go to the data. One important thing: remember that different user raise different connections so be careful to choose which application you'll direc

Re: pgbouncer

On Wed, 2023-06-28 at 07:19 -0400, Rita wrote: > seems like I may need to deploy pgbouncer for my webapp. should i deploy it > on the db server or on the webserver? On the database server. You don't want network latency between pgbouncer and PostgreSQL, so that the connections can be active as

Re: pgbouncer

It greatly depends on what you want to achieve. Both have pro’s and con’s. What do you need pgbouncer for? Verstuurd vanaf mijn iPhone > Op 28 jun. 2023 om 13:19 heeft Rita het volgende > geschreven: > >  > > seems like I may need to deploy pgbouncer for my webapp. should i deploy it > on t

Re: PGBouncer logs explanation required

On 19.12.21 12:50, Shubham Mittal wrote: 2021-11-25 14:45:45.244 IST [18307] LOG C-0x6ae270: sit/postgres@abcdlogin attempt: db=sit user=postgres tls=no 2021-11-25 14:45:45.299 IST [18307] LOG S-0x6b4f48: sit/postgres@abcd new connection to server (from abcd) 2021-11-25 14:46:17.843 IST [18307]

Re: pgbouncer configuration

I had to install pam-devel before reinstalling pgbouncer. appears to be working now. thanks for pointing me in the right direction Laurenz! On Wed, Apr 28, 2021 at 7:09 AM Chris Stephens wrote: > huh. you are right. i originally installed pgbouncer with yum but removed > with just "yum remove pg

Re: pgbouncer configuration

huh. you are right. i originally installed pgbouncer with yum but removed with just "yum remove pgbouncer" before following "Building from Git" section at http://www.pgbouncer.org/install.html. i just ran "make uninstall" -> reinstalled with 403 21-04-28 06:58:32 git submodule init 404 21-0

Re: pgbouncer configuration

On Tue, 2021-04-27 at 19:46 -0500, Chris Stephens wrote: > I'm trying to run pgbouncer but am having trouble with what looks like a very > simple configuration. > > centos 7 > postgres 12 > pgbouncer 1.15 > > we are already using pam for database auth. pgbouncer was compiled with > --with-pam.

Re: pgbouncer installation example (Step by step)

On Wed, Oct 14, 2020 at 11:08 AM Atul Kumar wrote: > Hi Team, > > Please share a clean example of installing, https://www.pgbouncer.org/install.html > configuring https://www.pgbouncer.org/config.html > and testing pgBouncer. > https://www.pgbouncer.org/usage.html What else do you want to

Re: pgbouncer installation example (Step by step)

On Wed, Oct 14, 2020 at 10:08 AM Atul Kumar wrote: > Please share a clean example of installing, configuring and testing > pgBouncer. > > Your official links are not organized so I need an example of > PgBouncer with organized steps. > As I said on your exact same posting to the -admin list; not

Re: pgbouncer bug?

On 2020-08-21 19:49, Achilleas Mantzios wrote: On 21/8/20 7:56 μ.μ., greigwise wrote: Not sure if this is the right place to post this, but if not someone please point me in the right direction. My issue is with pgbouncer 1.14. This does not seem to happen on 1.13. If I do a service pgbounc

Re: pgbouncer bug?

On 21/8/20 7:56 μ.μ., greigwise wrote: Not sure if this is the right place to post this, but if not someone please point me in the right direction. My issue is with pgbouncer 1.14. This does not seem to happen on 1.13. If I do a service pgbouncer restart, then anytime I try to connect to my

Re: pgbouncer logrotate found error, skipping

On 6/11/20 1:47 AM, pgdba pgdba wrote: Hello , When I want to create a logrotate in postgresql, I get this error when I want to work later. Does anyone have any idea about this error? logrotate -f /etc/logrotate.conf error after error: pgbouncer: 1 unknown option 'var' - ignoring line error: p

Re: pgbouncer with ldap

On 23/9/19 9:10 π.μ., Ayub M wrote: Achilleas, I am struggling with libpam_ldap package install. Does its setup also involves sssd daemon with nss and pam? I am seeing many different ways of dealing with ldap with pam and different modules which I am not familiar with (nscld, sssd etc). If you ha

Re: pgbouncer with ldap

Please dont top-post. On 14/9/19 9:41 π.μ., Ayub M wrote: Yes I did set that, here is how pgbouncer looks like --- -rwsrwsr-x. 1 root root 2087504 Sep 13 00:45 pgbouncer If you had set the same password in the postgresql server for the user and in the pgbouncer local unix user it should wo

Re: pgbouncer with ldap

Yes I did set that, here is how pgbouncer looks like --- -rwsrwsr-x. 1 root root 2087504 Sep 13 00:45 pgbouncer On Fri, Sep 13, 2019 at 6:50 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 13/9/19 10:19 π.μ., Ayub M wrote: > > Stumbled in the first step - PAM authentica

Re: pgbouncer with ldap

On 13/9/19 10:19 π.μ., Ayub M wrote: Stumbled in the first step - PAM authentication via pgbouncer. After compiling pgbouncer with the pam plug-in, I am unable to login into the db - throws PAM error message. Please help. User created with the same password as linux user -- localhost:~$ psql -

Re: pgbouncer with ldap

Stumbled in the first step - PAM authentication via pgbouncer. After compiling pgbouncer with the pam plug-in, I am unable to login into the db - throws PAM error message. Please help. User created with the same password as linux user -- localhost:~$ psql -h dbhost -p 3306 -U admin -W db1 db1=> cr

Re: pgbouncer with ldap

Okay, thanks for the response. Unfortunately Aurora does not expose these files or I should say there is no concept of these files in AWS managed Aurora DB service. Anyway I will give a try and let you know. On Thu, Sep 12, 2019 at 1:52 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote:

Re: pgbouncer with ldap

On 11/9/19 2:47 μ.μ., Ayub M wrote: Achilleas, for this setup to work are changes to postgresql.conf and pg_hba.conf needed? I am trying to implement this for AWS rds Aurora where these files are not accessible. Those files are needed in any case if you work with postgresql. Unfortunately no

Re: pgbouncer with ldap

Achilleas, for this setup to work are changes to postgresql.conf and pg_hba.conf needed? I am trying to implement this for AWS rds Aurora where these files are not accessible. On Mon, Sep 9, 2019, 6:46 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 9/9/19 12:41 μ.μ., Laurenz Al

Re: pgbouncer with ldap

Thank you Achilleas and Laurenz for the points, will try this out. On Mon, Sep 9, 2019, 6:46 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 9/9/19 12:41 μ.μ., Laurenz Albe wrote: > > Christoph Moench-Tegeder wrote: > >>> It has hba and via hba file one can specify ldap connecti

Re: pgbouncer with ldap

On 9/9/19 12:41 μ.μ., Laurenz Albe wrote: Christoph Moench-Tegeder wrote: It has hba and via hba file one can specify ldap connections https://www.postgresql.org/docs/9.3/auth-pg-hba-conf.html https://pgbouncer.github.io/config.html#hba-file-format "Auth-method field: Only methods supported by

Re: pgbouncer with ldap

Christoph Moench-Tegeder wrote: > > It has hba and via hba file one can specify ldap connections > > > > https://www.postgresql.org/docs/9.3/auth-pg-hba-conf.html > > https://pgbouncer.github.io/config.html#hba-file-format > "Auth-method field: Only methods supported by PgBouncer’s auth_type > ar

Re: pgbouncer with ldap

It has hba and via hba file one can specify ldap connections https://www.postgresql.org/docs/9.3/auth-pg-hba-conf.html On Mon, Sep 9, 2019, 3:40 AM Christoph Moench-Tegeder wrote: > ## Ayub M (hia...@gmail.com): > > > Hello, I am trying to setup pgbouncer with ldap to connect with rds > > postg

Re: pgbouncer with ldap

## Ayub M (hia...@gmail.com): > It has hba and via hba file one can specify ldap connections > > https://www.postgresql.org/docs/9.3/auth-pg-hba-conf.html https://pgbouncer.github.io/config.html#hba-file-format "Auth-method field: Only methods supported by PgBouncer’s auth_type are supported", a

Re: pgbouncer with ldap

## Ayub M (hia...@gmail.com): > Hello, I am trying to setup pgbouncer with ldap to connect with rds > postgres. Downloaded latest version of pbbouncer (1.11) and using hba > auth_type. Getting below error when starting pgbouncer daemon. Am I > missing something? There's no "ldap" mentioned anywhe

Re: pgbouncer

Nicola, My previous mail was maybe misleading because when i mentioned 'connections' I actually meant active connections to the db, as in: doing a transaction. In that case, yes, the connections are shared. But as soon as they initiate a transaction, then they are not shared any longer and a new p

Re: pgbouncer

On Thu, Jan 17, 2019 at 9:06 AM Fabio Pardi wrote: > Are you sure? The behaviour I experienced is different from what you > described. 85%... > > On 17/01/2019 16:32, David G. Johnston wrote: > > On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu wrote: > >> > >> Hello, > >> I am a bit confused about

Re: pgbouncer

> If I set 3, and I tried to connect from 4 shells, I am still able to connect. That would be the point - pgbouncer is sharing the 3 connections it keeps with PostgreSQL between the 4 client connections made to it. Mmh, my pool_mode is per session. The 4 sessions were active, not doing any query,

Re: pgbouncer

David, Are you sure? The behaviour I experienced is different from what you described. On 17/01/2019 16:32, David G. Johnston wrote: > On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu wrote: >> >> Hello, >> I am a bit confused about the settings in pgbouncer >> >> What's exactly the pool_size? > >

Re: pgbouncer

On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu wrote: > > Hello, > I am a bit confused about the settings in pgbouncer > > What's exactly the pool_size? Roughly, the number of open connections pgbouncer will keep to PostgreSQL. > If I set 3, and I tried to connect from 4 shells, I am still able to

Re: Pgbouncer discard all

@Nicola, while I'm glad you got further help.. I was asking myself it you maybe missed my answer to your question? (mail from 10/10/2018 at 15:31) https://www.postgresql.org/message-id/a22f8385-2a49-30a7-b1d2-fc743c2f3245%40portavita.eu regards, fabio pardi On 16/10/2018 14:12, Nicola Contu

RE: Pgbouncer discard all

11:12 AM To: martin.marq...@2ndquadrant.com Cc: Scot Kreienkamp ; pgsql-general@lists.postgresql.org; Alessandro Aste Subject: Re: Pgbouncer discard all 2492534808 | dev| 7355 | 1833427130 | pgbouncer | | 10.151.2.145 | | 60570 | 2018

Re: Pgbouncer discard all

El 16/10/18 a las 12:12, Nicola Contu escribió: >  2492534808 | dev    |   7355 | 1833427130 | pgbouncer       |          >                | 10.151.2.145   |                 |       60570 | > 2018-10-16 14:13:05.151015+00 |                               | > 2018-10-16 15:10:40.309993+00 | 2018-10-1

Re: Pgbouncer discard all

2492534808 | dev| 7355 | 1833427130 | pgbouncer | | 10.151.2.145 | | 60570 | 2018-10-16 14:13:05.151015+00 | | 2018-10-16 15:10:40.309993+00 | 2018-10-16 15:10:40.310038+00 | Client | ClientRead | idle |

Re: Pgbouncer discard all

El 16/10/18 a las 09:59, Scot Kreienkamp escribió: > Are you sure they’re actually waiting?  Don’t forget 10.5 will show the > last query executed even if the connection is idle.  I believe discard > all would be the last command the pgbouncer would send to the database > when the client is done as

RE: Pgbouncer discard all

, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com From: Nicola Contu [mailto:nicola.co...@gmail.com] Sent: Tuesday, October 16, 2018 8:12 AM To: pgsql-general@lists.postgresql.org Cc: Alessandro Aste Subject: Re: Pgbouncer discard all Hello, is this

Re: Pgbouncer discard all

Hello, is this normal? can anyone help? Thanks a lot for your help in advance. Nicola Il giorno mer 10 ott 2018 alle ore 17:03 Nicola Contu < nicola.co...@gmail.com> ha scritto: > Hello, > we are running pgbouncer 1.9.1 connected to postgres 10.5 > > Sometimes we are seeing a lot of waiting con

Re: Pgbouncer discard all

Hi Nicola, I am of the impression that the problem of having waiting clients does not depend from server_idle_timeout How is the load on both ends of pgbouncer? High? Low? No activity? A lot of content switch perhaps? I think that a pool_size of 120 is very high, unless you have an XXL databas

Re: Pgbouncer and postgres

I tried to compile 1.8.1 on my (now) up to date Centos 7 and everything went ok. I can run pgbouncer and login to it. this is the relevant part of the diff between config.log files > config.status:1112: lib/usual/config.h is unchanged 4676c4689 < CPPFLAGS='' --- > CPPFLAGS=' -I/usr/local/lib/in

Re: Pgbouncer and postgres

On 9/19/18 8:39 AM, ncontu1 wrote: So unexpectedly with the rpms, it installed it on a different place and it is working from command line [root@cmd-dev1 pgbouncer]# /bin/pgbouncer /etc/pgbouncer/pgbouncer.ini -u postgres 2018-09-19 15:38:36.081 46322 LOG File descriptor limit: 1024 (H:4096), ma

Re: Pgbouncer and postgres

So unexpectedly with the rpms, it installed it on a different place and it is working from command line [root@cmd-dev1 pgbouncer]# /bin/pgbouncer /etc/pgbouncer/pgbouncer.ini -u postgres 2018-09-19 15:38:36.081 46322 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible

Re: Pgbouncer and postgres

On 19/09/18 15:34, Adrian Klaver wrote: > On 9/19/18 5:40 AM, ncontu wrote: >> Hi Fabio, >> It used to work before, even without installing updates. > > Exactly, the suspicion is that something in the updates changed the behavior > of your system. > Yes, that's the suspicion. Or at least was..

Re: Pgbouncer and postgres

On 9/19/18 5:40 AM, ncontu wrote: Hi Fabio, It used to work before, even without installing updates. Exactly, the suspicion is that something in the updates changed the behavior of your system. I got two interfaces : private IP and localhost Selinux is disabled. I tried installing it from

Re: Pgbouncer and postgres

Hi Fabio, It used to work before, even without installing updates. I got two interfaces : private IP and localhost Selinux is disabled. I tried installing it from source, from git repo. Can't find the RPM, do you have any link for centos7? I tried both /etc/init.d and command line, same issue. T

Re: Pgbouncer and postgres

Hi Nicola, I tried to reproduce the problem using pgbouncer 1.8.1 from RPM on CentOS 7 and (an almost equal to) your configuration file but I m not able tobring it to the same error-state. Your problem might be tied either to the way you compiled it, or to some package installed/reconfigured w

Re: Pgbouncer and postgres

On 9/17/18 9:09 AM, Nicola Contu wrote: Yes I can connect to port 5432 without any problem. The thing is that I am not seeing anything on the postgres log when connecting to 6543. Pretty sure you won't as 6543 is controlled by pgbouncer and pgbouncer seems to be failing before it gets around

Re: Pgbouncer and postgres

Yes I can connect to port 5432 without any problem. The thing is that I am not seeing anything on the postgres log when connecting to 6543. Sure, thanks for your help. I will create an issue there. Il giorno lun 17 set 2018 alle ore 18:07 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto:

Re: Pgbouncer and postgres

On 9/17/18 8:59 AM, Nicola Contu wrote: Yeah, I replaced the IP of the machine with the word IP_ADDR. On the pgbouncer log I get this while trying to connect via psql: 2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed: Operation not supported 2018-09-17 15:56:49.452 67611 NOISE saf

Re: Pgbouncer and postgres

Yeah, I replaced the IP of the machine with the word IP_ADDR. On the pgbouncer log I get this while trying to connect via psql: 2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed: Operation not supported 2018-09-17 15:56:49.452 67611 NOISE safe_close(16) = 0 2018-09-17 15:56:49.452 6761

Re: Pgbouncer and postgres

On 9/17/18 8:39 AM, Nicola Contu wrote: Please reply to list also. Ccing list. No wait, IP_ADDR has been modified by me, but there is the IP of the machine 10.151.x. To be clear in your posts you are replacing the actual IP with IP_ADDR, correct? Here is the psql command . [root@cmd-dev

Re: Pgbouncer and postgres

On 9/17/18 7:26 AM, Nicola Contu wrote: [Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid 140586929567488] [client 10.160.41.3:51764 ] PHP WARNING: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused\n\tIs

Re: Pgbouncer and postgres

The log is from pgbouncer. I installed updates after this problem, but they did not resolve anything. I compiled 1.9.0 from source on the same path of the previous one. Then back to 1.8.1 on the same path. I also tried changing the listening port on pgbouncer, but that does not change too much.

Re: Pgbouncer and postgres

[Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid 140586929567488] [client 10.160.41.3:51764] PHP WARNING: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused\n\tIs the server running on host "IP_ADDR" and accepting\n\tTCP/IP connection

Re: Pgbouncer and postgres

On 9/17/18 7:19 AM, Nicola Contu wrote: I'm not aware of any update installed. There were like 600+ updates in fact. These are the logs when connecting via telnet : How about those from when Apache tries to connect and pgbouncer crashes? Connection closed by foreign host. -- Adrian Klaver

Re: Pgbouncer and postgres

I'm not aware of any update installed. There were like 600+ updates in fact. These are the logs when connecting via telnet : 2018-09-17 14:18:18.933 65617 NOISE safe_accept(11) = 14 (10.151.2.145:39478 ) 2018-09-17 14:18:18.933 65617 NOISE new fd from accept=14 2018-09-17 14:18:18.933 65617 NOISE

Re: Pgbouncer and postgres

On 9/17/18 7:05 AM, Nicola Contu wrote: The log is from pgbouncer. I installed updates after this problem, but they did not resolve anything. I was thinking more of updates that where done between the last time it worked and the time it started failing. Along that line, when was the last ti

Re: Pgbouncer and postgres

On 9/17/18 5:33 AM, Nicola Contu wrote: Hello, I'm trying to get pgbouncer working but I'm getting this error : What is generating the log below? To me it looks like everything is on track until this: 2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed: Operation not supported 2