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
    When using transaction pooling mode, a client can be active (= someone is connected to it) but doesn't need a server until you start a transaction.
    tobwen
    @tobwen
    Hi. I'd like to peer-auth the user www-data on pgbouncer and on the database. Since pgbouncer runs as user postgres on Debian by default, this doesn't work. Is there another way to do this, maybe using auth_query or do I need to run pgbouncer was user www-data?
    1 reply
    tobwen
    @tobwen
    If anyone is into pgbouncer, please drop me a message. I need help and I'm willing to pay for it.
    Mike
    @wildengineer
    Hello. I am using pgbouncer to perform a DB migration. I want to be able to switch databases live. The source and target DBs are synced and I can pause connections in pgbouncer while I make the switch. Anyone here have info on how to do this?
    1 reply
    Stephen Chu
    @stephenchu
    Q: what git branch was release 1.17.0 built off of? i don't see a branch for 1.7 on https://github.com/pgbouncer/pgbouncer/branches
    1 reply
    AaitDutta
    @AaitDutta
    Hello friends , we are using pgbouncer 1.16 and we are running a spring boot application, we have set the pool_mode = Transaction .
    We are facing a problem when we are trying to call a stored procedure from java using a callable statement , we are getting an error message
    "unknown pkt from client: 79/0x4f" . I am sharing the error messages in pgbouncer logs and also a snippet of java code which is trying
    to call the pgbouncer service and failing .
    1 reply
    image.png
    image.png
    Any help is much appreciated , we found after browsing that pgbouncer wont support callable statements , just wanted to know if in 1.15 version callable statements were supported so that we can go back to v 1.15 or change our code approach .
    Daniel Ortiz Costa
    @danoc93
    Hey all, any advice on what's the most ideal place to hijack the open connection for a user so we can make a SQL request to fetch some metadata before an actual query runs? I have found a couple places where this could be done
    1 reply
    (Patching pgbouncer for some custom functionality)
    Ashvin Vinodh
    @Avinodh
    Hi, I'm looking into using the comma-separated host list feature introduced in v-1.17 to round-robin across replicas. If the host list in pgbouncer.ini is updated at regular intervals with a healthy set by an external process, would RELOAD; need to be run for pgbouncer to pick-up the new host list? If so, what are the performance implications of this?
    5 replies
    Stephen Chu
    @stephenchu
    Q: what exactly does "closing because: client unexpected eof (age=59s)" log message mean? is it the client or the server who initiated the close?
    1 reply
    awlamb
    @awlamb
    I have a database behind a pgbouncer instance. If i'm performing a full version upgrade of pg, is it best practice to do a pgbouncer PAUSE beforehand, and then RESUME once we're back online?
    1 reply
    naveenchowdaryon
    @naveenchowdaryon
    I have database running with postgresql 11v, and 2 pgbouncers with 1.17v, with pool_mode=session, max_client_conn=2000 for each pgbouncer, default_pool_size=900,
    server_idle_timeout | 600
    server_lifetime | 14400
    server_login_retry | 15
    server_reset_query | DISCARD ALL, as per discription if server connection has been idle more than 600 seconds it will be closed. But in DB it is not heppning. there are idle sessions running more than 20mins in DB. and almost 400 connections which are idle more than 20mins. still these idle connections are not closing in DB, can somebofy help.
    3 replies
    Donal Byrne
    @byrnedo
    Hi, we're using pgbouncer in session pooling mode, and notice that when we reload the config via SIGHUP, if a client queries at exactly the same time, the connection (from the client P.O.V.) just seems to hang and timeout after 15 seconds. Other queries before and after are unaffected. Anyone know what could be causing this?
    3 replies
    coleman-rik
    @coleman-rik:matrix.org
    [m]
    evening everyone
    Does anyone know if pgbouncher-rr is still being maintained? Or where else would I ask about it? thanks.
    1 reply
    Jonathan H. Wage
    @jwage

    If I have pool_mode=transaction configured, should I see the connection closed between the client and pgbouncer each time I commit a transaction? or anyway to confirm in the logs that pool_mode=transaction is working differently compared to pool_mode=session?

    Also, will the session based features fail with an exception if you use them in transaction mode? Or will they just behave differently than you expect?

    Michael Vitale
    @MichaelDBA
    Transaction mode means that the server connection is released back to the pool after the transaction finishes, but your client handle to pgbouncer continues to exist.
    Jonathan H. Wage
    @jwage
    Thank you! that makes sense.
    Jonathan H. Wage
    @jwage
    Are there any logs or anything I can check to see the connection being released back to the pool each time I commit a transaction?
    Peter Eisentraut
    @petere
    Not normally, but if you use the command line option -v -v (twice), then it turns on "noise"-level debug output, and you'd see a message from release_server every time the state changes.
    Jonathan H. Wage
    @jwage
    Perfect. I just want to verify my configuration is correct and I see the behavior I expect. Thanks!
    Jonathan H. Wage
    @jwage
    I currently use 2 features that are not supported in transaction pooling mode. They are session advisory locks and notify/listen. I am trying to understand what the behavior will be if I use those features with transaction pooling enabled? I am working towards eliminating those features from my codebase but I am curious if there is a way I can launch pgbouncer with transaction pooling before I have eliminated those features...or if I need to first launch with session pooling only?
    3 replies
    c-a-b
    @c-a-b:matrix.org
    [m]
    @petere: what would it take to add GSS Encryption to pgbouncer? I've modeled my code off of the servertls* functionality, but I think I'm missing some things. For some reason it's not calling my gssenc_sbufio_ops functions sometimes.
    @petere: I'm able to share my (not-fully-functional) code if you are able to look at it, with a goal of eventually including it in pgbouncer.
    2 replies
    awlamb
    @awlamb
    Do versions < 1.17 contain the ability to specify multiple hosts in the database definition?
    Or was it introduced in 1.17
    1 reply
    Eudald Valcarcel
    @eudaldv_gitlab
    Hello everybody! I'd like to create a script that adds new databases and their respective users in the pgbouncer configuration files.
    I've managed to do so, but I'm a bit concerned about the best way to apply the new configuration.
    Is the restart daemon dropping all existing connections? Since I'm running a web app, would an authenticated user or a current query get affected?
    If so, is there any workaround? I've read about pgbouncer -R, but I'm not sure that's the best approach.
    By the way, I'm running pgbouncer through systemd and running on Ubuntu 22.04.
    Thank you!
    2 replies
    Aathith Rajendran
    @AathithRajendran

    Hi,

    Transaction pooling with prepared statement false, binary_parameters yes, advisory_locks false still we are getting errors
    Caused by: PG::DuplicatePstatement: ERROR: prepared statement "a1" already exists [893ea1d9-ab2e-40d4-bcbc-afedf4206bd4] PG::Error (server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

    Aathith Rajendran
    @AathithRajendran
    While deploying, accessing the Rails console and also when we get the error while using the app!
    Aathith Rajendran
    @AathithRajendran
    What could be the reason for this?
    Lautaro Puican Bertone
    @lawttaro_gitlab
    hello All! A question. I have pgbouncer installed on a vm and I want to make connections to several different databases, on different platforms (other vm, cloudsql). Does anyone here have experience in this regard? what is the recommended database limit that pgbouncer can manage? Or is a connection installation of pgbouncer better?
    c-a-b
    @c-a-b:matrix.org
    [m]
    petere: I just created pgbouncer/pgbouncer#743 with my working GSSAPI code
    Ashvin Vinodh
    @Avinodh

    @petere - Running into a very strange issue with the new comma-separated host list feature in pgbouncer v-1.17.

    pgbouncer.ini file:
    [databases]
    clientDb1 = host=endpoint1 port=5432 dbname=db1 pool_mode=transaction pool_size=18
    clientDb2 = host=endpoint-rr-1,endpoint-rr-2,endpoint-rr-3,endpoint-rr-4,endpoint-rr-5,endpoint-rr-6 dbname=db1 pool_mode=transaction pool_size=240

    When I connect to clientDB2:

    psql -h localhost -d clientDb2 -U <username>
    and run 'select inet_server_addr();' in the connected shell, I see that this is in fact the IP of 'endpoint1' defined for clientDb1.

    Also, in pgbouncer shell, running:
    SHOW CLIENTS;
    returns a row indicating:

    type: C
    user: <user>
    database: db1
    state: active
    addr: unix
    port: 5432
    local_addr: unix
    local_port: 5432
    remote_pid: <pgbouncer_pid>

    So it almost seems like the connections to clientDb2 which has the comma-separated host list is being re-routed by pgbouncer to the UNIX socket (/tmp) to the primary DB (which might be the default db for this purpose here?)

    Enabling verbose logging also seems to hit the code here: https://github.com/pgbouncer/pgbouncer/blob/275c7cca9209881aa9b045bf6ca908e76fdb359e/src/objects.c#L1100
    slog_noise(server, "unix socket: %s", sa_un.sun_path);

    This block of code is right after the block of code added for the comma-separated host list code. It is not immediately clear, however, how we can even enter this block of code.

    2 replies
    Ashvin Vinodh
    @Avinodh

    @petere Regarding the above ^, it seems like this is in fact a bug, specifically here: https://github.com/pgbouncer/pgbouncer/blob/275c7cca9209881aa9b045bf6ca908e76fdb359e/src/objects.c#L1078

            for (host = strtok(host_copy, ","), n = 0; host; host = strtok(NULL, ","), n++)
                if (server->pool->rrcounter % count == n)
                    break;
            Assert(host);
    
            server->pool->rrcounter++;
        } else {
            host = db->host;
        }
    
        if (!host || host[0] == '/' || host[0] == '@') {
            const char *unix_dir;
           . . .

    rrcounter is int16_t and is never initialized. If it starts off with a negative value (or overflows), server->pool->rrcounter % count == n this will only ever be TRUE for the first replica in the host list. In all other cases, host = nullptr and will end up going into the UNIX socket logic.

    I will work on a patch for this.

    1 reply
    Abhishek Chanda
    @achanda
    Hi all, I am trying to test a scenario where prepared statements fail to execute with pgbouncer in transaction mode. For this, I started pgbouncer with pool_mode = transaction and connected to it using psql. But I could prepare queries and execute those in transactions in the same session as well as different psql sessions. Does this mean prepared statements can work randomly in transaction mode depending on how pgbouncer selects a backend connection? How do I find a reliable failure case for prepared statements in transaction mode?
    1 reply
    axsuul
    @axsuul:matrix.org
    [m]
    What are the negative implications of setting a low client_idle_timeout on PgBouncer (e.g. 30s)? The reason why I'd want to do this is because I'm running 4 PgBouncer instances and I'd like to distribute connections across these instances as evenly as possible, that way connections don't just pile up on one PgBouncer instance and stay that way
    Michael Vitale
    @MichaelDBA
    I am using AWS EC2 Amazon Linux 2 distro. The highest version of pgbouncer I can find is v 1.14. Can I install the latest there, 1.17?
    Seems PG and Amazon Linux 2 are NOT seeing things eye to eye these days...
    Ashvin Vinodh
    @Avinodh

    Is there a recommended way to use different auth_users for various hosts in the comma-separated host list?

    auth_file.ini:

    my_auth_user=<token>

    pgbouncer.ini:

    [databases]
    clientName = host=host-name-1,host-name-2,host-name-3 dbname=db 
    
    [pgbouncer]
    . . .
    auth_file=auth_file.ini
    auth_user=my_auth_user
    auth_query=<auth query>

    In the above case, connections to each host in the host-list above will use the same auth user (my_auth_user) to run the auth query. This is not desirable, since in certain use-cases, there could be a 1-1 mapping between an auth user and an db endpoint.

    Ideally, I'm looking for someway to map a host in the comma-separated list to a specific auth user, and have a different auth user per host.

    I could locally patch this to add some sort of host -> auth user mapping in the [databases] row above, but i would like to avoid this if there is already a supported way to do this.

    1 reply
    Jose Molina-Melendez πŸ‡ΊπŸ‡ΈπŸ‡ΈπŸ‡»
    @molinamelendezj_twitter

    PgBouncer 1.17.0 released: http://www.pgbouncer.org/2022/03/pgbouncer-1-17-0

    did SCRAM auth become hard default in 1.17.0? I upgraded and now getting this error cannot do SCRAM authentication: wrong password type server login failed: wrong password type if I'm looking at the code right I see get_password_type(user->passwd) would always return PASSWORD_TYPE_MD5 for my existing passwords that aren't SCRAM compatible yet. and my auth_type = md5 is still set and NOT set to SCRAM-SHA-256 yet. so i'm not sure what else is causing it to go down a SCRAM auth path.

    1 reply
    Abhishek Chanda
    @achanda
    Hi, how are you all online restarting pgbouncer using an init system? I know that the -R switch does not work under systemd. For other init systems, do you create a new pgbouncer service with the -R flag and then stop the old one?
    1 reply
    Philippe Sabourin
    @pip8786
    Has anyone been able to get PGBouncer working on Google Cloud? I've been trying for a few days but can't quite get it working. I've tried using this terraform module but get a wrong password type error: christippett/terraform-google-cloud-sql-pgbouncer#3. Do I need to configure the Auth Proxy in google to get it working?
    3 replies
    AmanKumar666
    @AmanKumar666

    Hi, we are facing an issue with enabling cert based authentication between client and pgbouncer 1.17.0.
    Pgbouncer 1.17.0 and postgresql 14 are on the same host in different containers. There is TLS enabled between client and pgbouncer. The issue is that we want client to connect to pgbouncer with client key and certificate, the certificate will have a Common Name (lets say "abc") which wont be same as the user "joe" which is trying to login to pgbouncer, so here we want a mapping of the user "joe" to "abc". If we try to login as joe , Immediately we get error because pgbouncer will check the user ("joe") and Common Name ("abc") in cert and they doesn't match. What is the right way of solving this issue? otherwise how can different users login to pgBouncer?

    On postgreSQL, this mapping can be done using pg_ident.conf but that support is not available in pgBouncer. What is the most secured way of achieving this authentication?

    jasimul
    @jasimul

    Hi ,Pgbouncer log showing some unexpected warning message of previously configured database.
    thats kind of poluting the log file and i couldn't see if any real issue is happening or not.
    pgbouncer details :
    psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1), server 1.12.0/bouncer)

    pgbouncer.ini

    `
    databases]
    ;;perfdev=dbname=perfdev host=xxx.cluster-xxx.us-west-2.rds.amazonaws.com port=5432 pool_size=1000
    pgbouncerpoc=dbname=pgbouncerpoc host=xxx.cluster-xxx.us-west-2.rds.amazonaws.com port=5432 pool_size=1000
    [users]

    ;user1 = pool_mode=transaction max_user_connections=10

    [pgbouncer]
    ;;;
    ;;; Administrative settings
    ;;;

    logfile = /var/log/postgresql/pgbouncer.log
    ;logfile = pgbouncer.log
    pidfile = /var/run/postgresql/pgbouncer.pid

    listen_addr = *
    listen_port = 6432
    unix_socket_dir = /var/run/postgresql
    auth_type = md5
    auth_file = /etc/pgbouncer/userlist.txt
    admin_users = pgbouncer_admin
    max_client_conn = 4000
    max_db_connections = 4000
    max_user_connections = 4000
    pool_mode = transaction
    query_timeout = 300
    query_wait_timeout = 300
    `
    the warning message its throwing
    WARNING C-0x56208f2dd950: (nodb)/(nouser)@10.46.105.161:58302 pooler error: no such database: perfdev

    Fong Vang
    @vamfoom
    I'm trying to determine how many connections pgbouncer makes to the backend postgres servers. I understand that a backend connection is made based on the user/database pair. If a thousand application servers connect to pgbouncer using the same user/database pair, would pgbouncer only use a single connection to the backend?
    1 reply
    Ashvin Vinodh
    @Avinodh

    In Pgbouncer v-1.17 when using comma-separated host-lists, what is the behavior of the pool_size when specified on the client name?
    For example:

    [databases]
    database-rr = host=rr-1-endpoint,rr-2-endpoint,rr-3-endpoint,rr-4-endpoint,rr-5-endpoint port=5432 dbname=db pool_size=200 pool_mode=transaction

    In this setup, I have 5 hosts being round-robinned across by the database-rr client. Looking at the pgbouncer admin database:

      database   |   user    | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us |  pool_mode
    -------------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-------------
    database-rr | user_1  |        22 |          0 |             0 |         2 |       2 |       0 |         0 |        0 |       0 |          0 | transaction
     pgbouncer   | pgbouncer |         2 |          0 |             0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement
    (2 rows)

    It seems like a single pool is created for database-rr client. Does this mean that the above pool_size of 200 is divided across the 5 hosts in the list (so ~40 per host ) ?

    1 reply
    jurim76
    @jurim76
    I discovered its likely that Knex https://knexjs.org/ pooling and pgbouncer wont work together very well. For some reason pgbouncer dosent return server connection to pool after knex has completed transaction in its connection. Some how its can hold the server connection open.
    This could be overcome with cascading two pgbouncers as a quick fix, but I don't like this solution.
    Perhaps someone has the similar problem?
    1 reply