Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    Peter Eisentraut
    @petere
    @a-ra0227 From the documentation:
    Note that setting logfile does not by itself turn off logging to stderr.
    Use the command-line option -q or -d for that.
    anirudh-deepsource
    @anirudh-deepsource
    Hi, I'm trying to setup Telegraf's Pgbouncer input plugin (https://github.com/influxdata/telegraf/tree/master/plugins/inputs/pgbouncer), but on connecting to the Pgbouncer instance, I see:
    E! [inputs.pgbouncer] Error in plugin: ERROR: unrecognized configuration parameter "stats" (SQLSTATE 42704)
    Peter Eisentraut
    @petere
    @anirudh-deepsource It seems your plugin is connecting to PostgreSQL, not PgBouncer. (SHOW STATS is a PgBouncer command.) Check your connection string.
    anirudh-deepsource
    @anirudh-deepsource
    @petere Yep! I realized soon after. Thanks.
    naveenchowdaryon
    @naveenchowdaryon
    Hello All, Can you please suggest session pooling or transaction pooling
    How to decide which pool-method is right one?
    Peter Eisentraut
    @petere
    @naveenchowdaryon You need to construct your application differently depending on the pool mode. For session pooling to be effective, you need to close connections in the client when you don't need it, so that the pooler can use the outgoing connections for other clients. For transaction pooling, you don't need to do that, so it's easier if the client is built in a framework that doesn't allow this kind of control of the database connection. But then you need to avoid using features that set session state.
    skurra01
    @skurra01
    As @sezaru mentioned i have a use case to read credentials from vault we are using aws PostgreSQL so we don’t have access to give pgbouncer to read from PostgreSQL system table pg_shadow. Is there a way to integrate pgbouncer with vault to read credentials and saving them while bootstrapping and when i issue reload command each time. Any suggestions might help thank you in advance
    yinan8128
    @yinan8128
    Hi guys
    We use postgres user to run pgbouncer and set /var/run/pgbouncer folder owner to postgres
    but after the vm reboot, /var/run/pgbouncer folder automatically change owner to pgbouncer and service can not startup
    Could you please indicate where can config after reboot folder owner still postgres no change please?
    Peter Eisentraut
    @petere
    @skurra01 You can update userlist.txt from your vault.
    @yinan8128 That seems be part of your packaging, not something that pgbouncer itself does.
    ipsdileeprakesh
    @ipsdileeprakesh
    hello everyone, please help me on posegresql architectural health check, if you have any documentation for best practices, share with me, thank you
    sezaru
    @sezaru

    Hi guys, for some reason, my pgbouncer installation keeps spamming this message in the logs lots of time per second (my log is more than 4gb only of this by now):

    2021-02-24 17:21:30.731 UTC [798900] LOG S-0x55a5fa41b060: data_db_binance_prod/candles_user@127.0.0.1:5437 new connection to server (from 127.0.0.1:44832)
    2021-02-24 17:21:30.738 UTC [798900] LOG S-0x55a5fa41b060: data_db_binance_prod/candles_user@127.0.0.1:5437 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
    2021-02-24 17:21:30.839 UTC [798900] LOG S-0x55a5fa41ac00: data_db_binance_prod/candles_user@127.0.0.1:5437 closing because: evicted (age=2s)

    What does the evicted closing reason mean? It seems like every connection is being closed right after its creation.

    1 reply
    Peter Eisentraut
    @petere
    @sezaru The "evicted" message appears when max_db_connections or max_user_connections is reached and an idle connection is closed to make room for a new connection. It's not really a problem by itself, but if it happens too often, then of course it wastes resources. The fix, if any, is to adjust some of the max_* settings or pool sizes.
    Stephen Chu
    @stephenchu
    Q: can a single pgbouncer process only use 1 TLS server_tls_* cert to talk to a DB node? or can pgbouncer use a different cert, per pgbouncer client, to connect to a DB node?
    Peter Eisentraut
    @petere
    @stephenchu Right now, only one certificate per pgbouncer instance is possible.
    Stephen Chu
    @stephenchu
    Q: in what format does pgbouncer pass a client-supplied password to a postgres DB, if i have auth_type = md5 and my auth_file uses "md5xxxxxx" syntax? does pgbouncer pass the straight up "md5xxxxxx" hash to the DB node? or does pgbouncer pass the original password value to postgres? (any link to src code would also be helpful). thank you so much
    sezaru
    @sezaru
    @petere Thanks for the response and sorry for taking too long to reply.. So, since I don't set the max_user_connections this means that the issue would be my max_db_connections = 20 right? In my case, I have 3 users that can use the pgbouncer pool to access the database. So if I understood it correctly, the issue is that they would compete with each other for these 20 DB connections, pgbouncer would need to evict these connections a lot.
    I could increase max_db_connections but this would mean that I would need to increase my connections in the database configuration too. And the whole point I started using pgbouncer was to decrease that number because of memory constraints.
    So, is there any other way to fix that? Is it possible to have a specific pool configuration per client user? so I have different pools for each user?
    If not, can I at least disable that log message so it doesn't bloat my log file?
    Peter Eisentraut
    @petere
    @sezaru You could make the pool size 7. Then each user can use 7 connections and with 3 users you get 21 in total. What you have set up now is, each user can use 20 connections but only 20 are allowed in total. So that way you will always have conflicts, requiring pgbouncer to close and open backend connections quickly, which wastes resources.
    Ghost
    @ghost~60425bdb6da037398465a0d6
    Hi.
    is there any way to check setting of "pool_mode" for specific connection?
    I have situation like this: MANY app servers, each connects to one of 3 bouncer servers, and these in turn connect to bouncer on database server and this in turn connect to pg
    everywhere I use transaction pooling.
    but i see cl_waiting on the first layer of bouncers, while in db, i see idle connections with state_change that is like 16 hours agao.
    is there any way to debug why pgbouncer-on-dbserver didn't reuse them?>
    serhade
    @serhade
    Hi everyone , hope you are doing well , I wonder how to delete pgbouncer.log log file without interrupting the pgbouncer service in linux , thanks. Serhad Erdem.
    Peter Eisentraut
    @petere
    @depesz_gitlab With a combination of show servers, show clients, and show pools, you should be able to get that information.
    @serhade Delete the file and then send SIGHUP (or RELOAD command) to make pgbouncer close and reopen the file.
    Ghost
    @ghost~60425bdb6da037398465a0d6
    @petere well, perhaps I should, but I didn't. Specifically - I don't see why we had connections with idle over 16 hours, that weren't reused. For now, we simply robokill all connections with idle over 30 minutes
    Robert McNeil
    @porty

    Hey gang! I've been tasked with limiting the number of connections for a specific user when their application goes bananas and eats all the connections, so that other users can continue to access the database.
    This is to be done during an incident, so I'm working on an SOP.
    I've seen max_user_connections and figured that's what I'm after, however when I set:

    [users]
    user1 = max_user_connections=2

    and send SIGHUP to pgbouncer, that user can no longer log in: psql: ERROR: password authentication failed

    The config file:

    [databases]
    %include /etc/pgbouncer/something-staging/databases.plain.ini
    %include /etc/pgbouncer/something-staging/databases.secret.ini
    
    [users]
    %include /etc/pgbouncer/something-staging/users.ini
    
    [pgbouncer]
    admin_users = pgbouncer-stats
    application_name_add_host = 1
    auth_file = /etc/pgbouncer/something-staging/userlist.txt
    auth_hba_file = /etc/pgbouncer/something-staging/hba.conf
    auth_type = hba
    auth_user = pgbouncer
    ...
    max_client_conn = 500
    pool_mode = session

    It seems that I cannot set max_user_connections within [users] without clobbering their password.
    Does anyone know how to limit connections for a specific user (using auth_type = hba?)

    Peter Eisentraut
    @petere
    @porty Not having confirmed anything, but some of that functionality is certainly within the areas where I wouldn't be surprised to find a bug. Which version are you using?
    Robert McNeil
    @porty
    1.14.0
    I see fixes for some auth_user settings in 1.15.0 but I don't know if they're related (thanks for looking in to this btw :+1:)
    Praveen Kumar Barli
    @barpr01_twitter
    when we use auth_user, auth_query will pgbouncer send the username, password to PG from psql client parameters or the md5 password obtained from auth_query (typicall read from pg_sahdow)?
    Peter Eisentraut
    @petere
    @barpr01_twitter The second. It will fetch the password from the server and then use it as if it were stored in userlist.txt.
    Praveen Kumar Barli
    @barpr01_twitter
    @petere Thank you! Curious, is there any tweak like config change we can do to make it use the text password we send as psql client parameter or env variable (PGPASSWORD).. i want to prvent it from using MD5 format it gets from the server (pg_shadow).
    johnleggio
    @johnleggio
    Hi all, I need some advice. I have one primary postgres database server and two proxy servers. I currently have pgBouncer installed on one proxy server. Can I also install pgBouncer on the other proxy server and possibly the database server as well? I remember reading something about this configuration but I cannot find the article. Thanks
    Robert McNeil
    @porty

    Hello, me again - asking my question a different way:

    • in our setup, PgBouncer has no application users defined; authn credentials for incoming connections is pulled from the backend Postgres servers (or delegated?)
    • under high client load, we'd like to set max_user_connections for particular badly behaving applications/users

    It seems the relevant code for user authentication is at https://github.com/pgbouncer/pgbouncer/blob/master/src/client.c#L333-L360
    Under normal circumstances find_user() will return NULL and PgBouncer will hit the backend server to get user information :+1: .
    When a user is defined in [users] as user_app1 = max_user_connections=10 then find_user() will return this as the user, and PgBouncer will reject the authn request, probably because that user struct/object has no password.

    Questions:

    • have I missed anything in the [users] section that would make PgBouncer pull credentials from the backend server and apply max_user_connections on top?
    • can the backend Postgres server return a max_user_connections back to PgBouncer that PgBouncer will enforce?
    • am I missing some other potentially obvious way to achieve a similar result?

    Thanks for reading my second wall of text :smile:

    Фёдор Ерастов
    @erastov
    Hi guys! Can you tell me what is the difference between server_lifetime and server_idle_timeout, specifically what is the difference between "close an unused" and "idle will be dropped"?
    Фёдор Ерастов
    @erastov
    I think I understand that if the connection lives for a long time, that is, it was not killed by server_idle_timeout, because it was periodically or actively used for an hour, then after an hour, it will try to close it anyway. But who will explain why now?)
    Vruttant1403
    @Vruttant1403

    I am using CentOS 7 and running multiple pgbouncer on the same port for TCP/IP connections using the so_reuseport option.

    I have started 4 pgbouncer processes on the same host and port. The problem is that when logging in as admin user (pgbouncer user) to check active pools or clients, I am not able to determine which 1 of the 4 processes I am connected to. Is there a way to specify which processes of the pgbouncer I'm connecting to?
    Some observations:
    1 - I'm connecting to different processes if I log in as admin multiple times. Just not able to determine which one.
    2 - I know this as the show servers; command gives different output for different logins.

    Reference - https://www.2ndquadrant.com/en/blog/running-multiple-pgbouncer-instances-with-systemd/#:~:text=Since%20version%201.12%2C%20PgBouncer%20supports,instance%20of%20PgBouncer%20via%20systemd.

    Sarath Chandra Reddy
    @screddy1313
    @Vruttant1403 Hey we are also facing this issue. Please revert if you find any solution
    Peter Eisentraut
    @petere
    @barpr01_twitter In the general case, this wouldn't even work, because what the client sends is not the same as what is stored on the server, because of salting before hashing.
    @johnleggio Sure, you can install as many as you like. :)
    @porty As I mentioned earlier, I wouldn't be surprised if that stuff doesn't work correctly. If you have some sort of test case, then we can use that to try to fix things.
    Peter Eisentraut
    @petere
    @erastov Your understanding is correct. server_lifetime wouldn't be necessary in theory, but in practice it's probably good to have in case there are memory leaks or something like that. Once in a while it's just good to make a fresh start. :)
    @Vruttant1403 You need to create a separate socket that is not shared between the instances, for example a Unix-domain socket, or use systemd socket activation to create additional sockets. That is described in the blog post you linked to.
    Vruttant1403
    @Vruttant1403

    @Vruttant1403 You need to create a separate socket that is not shared between the instances, for example a Unix-domain socket, or use systemd socket activation to create additional sockets. That is described in the blog post you linked to.

    @petere I tried that but due to some issue with RHEL 7 systemd version, its not running on the same socket and giving an error saying socket is in use when using unix domain socket. So, to run it currently I have disabled unix socket in the pgbouncer.ini file. Is there a workaround for this?

    Peter Eisentraut
    @petere
    @Vruttant1403 not that I know