by

Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    matrixbot
    @matrixbot
    @golanv:privacytools.io I can open a bug report if this seems appropriate.
    matrixbot
    @matrixbot
    @golanv:privacytools.io Actually, I could just create a PR I guess.
    matrixbot
    @matrixbot
    @golanv:privacytools.io Also, adding an appropriate pam file seems to fix the need to run pgbouncer as root (using setuid: https://severalnines.com/database-blog/one-security-system-application-connection-pooling-and-postgresql-case-ldap)
    Peter Eisentraut
    @petere
    These PAM configurations are highly OS-specific, so shipping a file with PgBouncer itself would probably do more harm than good. One option to make it easier to get started might be to get packagers to ship a sample configuration.
    matrixbot
    @matrixbot
    @golanv:privacytools.io Well, some Linux distributions may tweak PAM files, but there are some upstream projects that include PAM configurations also. I don't see why a default couldn't be added upstream that aligns with how PgBouncer devs feel it will be used. Omitting that file does break PAM out of the gate though (and apparenlty lead some to run PgBouncer as root on Debian/Ubuntu to compensate, although that isn't PgBouncer devs' fault). Anyway, that's just my opinion.
    Shoaib Burq
    @sabman

    Hi all
    I am new to pgbouncer and just got it running however I'm struggling to get a minimum configuration working.

    I am trying the following

    psql -p 6432 -h 127.0.0.1 -U postgres testdb
    # psql: error: could not connect to server: ERROR:  no such database: testdb
    
    # logs
    2020-02-14 21:11:08.455 UTC [11021] LOG C-0x562433541470: (nodb)/(nouser)@127.0.0.1:44722 login failed: db=testdb user=postgres
    
    # config ini
    [databases]
    testdb = host=127.0.0.1 dbname=template1 user=postgres

    Any ideas or tips?

    Updated the above with the relevant log line
    2020-02-14 21:11:08.455 UTC [11021] LOG C-0x562433541470: (nodb)/(nouser)@127.0.0.1:44722 login failed: db=testdb user=postgres
    Shoaib Burq
    @sabman
    OK I got it working after killing the pgbouncer process and restarting it. For some reason service pgbouncer reload isn't reloading the ini file.
    Another question I have is: Is it possible to have a dynamic configuration for pgbouncer. Our use case is such that we have a multi-tenanted application which creates a database per user. This is done when a user signs up. This means we can't setup [database] setting in /etc/pgbouncer.inifile manually. Is there are way to have a wildcard or dynamic setting?
    Shoaib Burq
    @sabman
    sorry another dumb question what does this mean? auth_type=any requires forced user - I assume it's asking me to put in a user=xxx in the database config. but what if I don't know the user ahead of time? is there any way to configure pgbouncer for this?
    Peter Eisentraut
    @petere
    @matrixbot Well, you could submit a pull request and we can discuss the details.
    @sabman You can have a * wildcard, but nothing more fancy than that.
    @sabman Then you need to use an auth_type setting other than any.
    matrixbot
    @matrixbot
    @golanv:privacytools.io Peter Eisentraut (Gitter): As for now, I won't submit a pull request. I'm thinking you may be right. Although those PAM configurations seem appropriate for some projects, it seems like in this case, the PAM files may be too distro-specific for a generic pgbouncer PAM file to work well. Thanks for following up!
    Callam
    @cal97g
    guys, where does pgbouncer go?
    peterwainah
    @peterwainah
    Hello i have this error when connecting to Pgbouncer "ERROR: unsupported startup parameter: options"
    Stephen
    @sdemontfort
    Hey @petere, I contributed to PgBouncer with this PR: pgbouncer/pgbouncer#428
    Thanks for adjusting and merging my PR. Is it possible for us to release a new version of Pgbouncer that includes the TCP_USER_TIMEOUT change?
    benchub
    @benchub
    Has anybody gotten the per-user max_user_connections to work? Using pgbouncer 1.9.0, we're seeing that a per-user specified max_user_connection is ignored and the default_pool_size is used instead.
    Debjit
    @debjitk
    Does pgbouncer supports 'scram-sha-256' ?
    Peter Eisentraut
    @petere
    @peterwainah What client are you using? The client appears to add the startup parameter "options", but PgBouncer doesn't support that. What are you trying to do?
    @sdemontfort Yeah, absolutely I'm trying to get that out. Currently working on finishing up PostgreSQL 13, so after that. :)
    @benchub max_user_connections does work, but I have found that it is sometimes confusing and doesn't do what you think it does. So perhaps show us your configuration and what you're doing.
    @debjitk Yes, as of version 1.11.0.
    waleedelbahr
    @waleedelbahr
    Hi all, I am new to pgbouncer I need to run it with openerp, any recommended article or something will be appreciated.
    ppapishe
    @ppapishe

    Hello, I am having a weird issue, we are running out pgb on a ec2 and below is the config
    max_client_conn = 1300 default_pool_size = 50 min_pool_size = 25 reserve_pool_size = 23 idle_transaction_timeout = 300

    we ran a load test ranging from 200 rps to 450 rps, around 300-350 rps we started seeing query_wait_timeout for about 20 min and when the load was at 400-450 rps , we did not see any time outs,
    also, when we started getting the timeouts all 73 pools were actively user and the query latency at the postgres is under 3ms all the time.
    at peak load, not all pools were busy and no time outs,

    I am trying to figure out why at medium load, the requests were waiting and on what. Please let me know if anyone else faced same kind of issue?

    Debjit
    @debjitk
    @petere OK
    Peter Eisentraut
    @petere
    @ppapishe What's rps?
    ppapishe
    @ppapishe
    @petere requests per second.
    ppapishe
    @ppapishe
    @petere , Please let me know if you have any thought on this
    Nurettin Onur TUĞCU
    @nurettin
    hi, I made a successful connection to database using pgbouncer, but when I run a django command to drop database, it says "no such database template1" everything else works fine including migrations and seeds
    I can also connect to template1 with my user
    through pgbouncer (using pgcli to test)
    Puneet Arora
    @puneetarora_07_twitter
    Hello, I stumbled upon this chat channel while looking for avenues to ask from the experts about a pgBouncer problem we are facing.
    Puneet Arora
    @puneetarora_07_twitter
    We are running pgBouncer with NodeJs (Sequalize) as the client.
    Occasionally, we are seeing client_login_timeout errors in out logs.
    While investigating that we realized that the pgBouncer is not utilizing the connection pool fully, but somehow caping itself to only 20% of the pool. Any pointers on what could be the reason? Or how we could go about debugging this?
    Here is the config we have for pgBouncer:
    pool_mode = transaction
    max_client_conn = 5000
    min_pool_size = 10
    
    # Fail fast for initial connection issues 
    # (seconds)
    client_login_timeout = 5
    server_connect_timeout = 5
    
    # Max time a query can wait in backlog before being executed by server. If the query isn't
    # assigned to a server during this time, the client is disconnected
    # (seconds)
    query_wait_timeout = 10
    
    # The pooler will close an unused server connection that has been connected longer than this
    # (seconds)
    server_lifetime = 60
    
    # Kill idle connections early
    # (seconds)
    server_idle_timeout = 60
    
    # Increase size of request queue backlog
    listen_backlog = 4096
    
    # Disable noisy logging
    log_connections = 0
    log_disconnections = 0
    
    # The same as tcp_retries2 but at the socket level
    # (milliseconds)
    tcp_user_timeout = 12500
    
    # Keep alive settings combined with the above will mean dud connections are killed at 12.5s,
    # after the 3rd keep alive probe is sent (https://blog.cloudflare.com/when-tcp-sockets-refuse-to-die/)
    # (seconds)
    tcp_keepalive = 1
    tcp_keepidle = 1
    tcp_keepintvl = 11
    tcp_keepcnt = 3
    
    # Log stats more frequently
    # (seconds)
    stats_period = 30
    DURGA PRASAD REDDY
    @patlolladurgaprasad8_gitlab

    Hi every one !!!

    I have established pgbouncer-rr for redshift. I used docker to generate image of pgbouncer-rr and run the container with that image in AWS ECS, and my container is attached with a load balancer which listen on PORT 6432 and forwards request directly to pgbouncer container. Service is running fine without any error but when i'm trying to connect to redshift through pgbouncer-rr using loadbalancer DNS, it's throwing me "(Jdbc)(11380)Null Pointer Exception" in SQLworkbench

    [databases]
    * = host=analytics-XXX.XXXXX.us-west-2.redshift.amazonaws.com port=5439 dbname=gravtyrs auth_user=gravty password=ABC
    [pgbouncer]
    logfile = /var/log/postgresql/pgbouncer.log
    pidfile = /var/run/postgresql/pgbouncer.pid
    ;listen_addr = *
    listen_addr = 0.0.0.0
    listen_port = 6432
    unix_socket_dir = /var/run/postgresql
    auth_type = trust
    admin_users = gravty
    auth_file = /etc/pgbouncer/userlist.txt
    pool_mode = session
    server_reset_query = DISCARD ALL
    max_client_conn = 1000
    default_pool_size = 20
    ignore_startup_parameters = extra_float_digits
    server_idle_timeout = 60

    and my userlist.txt container username as gravty along with my password
    My DockerFIle is https://gitlab.com/qixtand/dockerhub/blob/master/debian/jessie/pgbouncer-rr/1.7.2/Dockerfile

    I know your time is super valuable please help me in solving this issue

    Peter Eisentraut
    @petere
    @puneetarora_07_twitter You don't show what you have set for pool_size. What are you basing your "20% of the pool" on?
    @patlolladurgaprasad8_gitlab If you are getting Java exceptions in SQLworkbench, then you need to look there. PgBouncer doesn't cause Java exceptions.
    matrixbot
    @matrixbot
    Paul gz
    Stephen
    @sdemontfort
    @petere, I work with @puneetarora_07_twitter and we have pool sizes per database (3 databases).
    the average pool_size per DB is around 30
    It doesn’t look like we’re exhausting the pools so we’re wondering what else could be causing the client_login_timeout
    Joe Schafer
    @jschaf
    I have 2 pools, a write pool and a query pool. If the write pool and its reserve get full, my understanding is that PGBouncer keeps listen_backlog number of pending connections in a queue. If listen_backlog reaches the limit, does that mean connections for both the write and query pools are dropped?
    1 reply
    Matthew Jacobs
    @mjacobs

    Hi all, I'm curious about a statement on the pgbouncer FAQ: upgrading pgbouncer w/o dropping cxns, it says: "This cannot be done with TLS connections."

    Does anyone know if there is a fundamental problem with doing this, or is it a matter of work that hasn't been done (presumably it's not trivial :) )?

    1 reply
    Stephen
    @sdemontfort
    Hey @jschaf, I think the listen_backlog works in conjunction with query_wait_timeout. If a query is in the backlog for longer than the query_wait_timeout, it will get dropped at that point
    DURGA PRASAD REDDY
    @patlolladurgaprasad8_gitlab

    Hi all,
    I'm using pgbouncer-rr to connect Redshift, but during connection, I come across some issue it's giving me
    2020-05-13 16:16:24.577 1 WARNING lookup failed: port=: result=-2
    2020-05-13 16:16:24.577 1 LOG S-0x9db890: gravtyrs/bol@(bad-af):0 closing because: server dns lookup failed (age=0)

    I have created empty pgbouncer.ini and userlist.txt and passing the configurations from entrypoint.sh file

    my entrypoint.sh file

    #!/bin/bash
    set -e
    
    
    PG_PORT_5439_TCP_ADDR=${PG_PORT_5432_TCP_ADDR:-}
    PG_PORT_5439_TCP_PORT=${PG_PORT_5432_TCP_PORT:-}
    PG_ENV_POSTGRESQL_USER=${PG_ENV_POSTGRESQL_USER:-}
    PG_ENV_POSTGRESQL_PASS=${PG_ENV_POSTGRESQL_PASS:-}
    PG_ENV_POSTGRESQL_POOL_MODE=${PG_ENV_POSTGRESQL_POOL_MODE:-}
    PG_ENV_POSTGRESQL_DB_NAME=${PG_ENV_POSTGRESQL_DB_NAME:-}
    PG_ENV_POSTGRESQL_MAX_CLIENT_CONN=${PG_ENV_POSTGRESQL_MAX_CLIENT_CONN:-}
    PG_ENV_POSTGRESQL_DEFAULT_POOL_SIZE=${PG_ENV_POSTGRESQL_DEFAULT_POOL_SIZE:-}
    PG_LOG=/var/log/postgresql/
    PG_USER=postgres
    PG_GROUP=postgres
    PG_CONFIG_DIR=/etc/pgbouncer
    PG_CONFIG=pgbouncer.ini
    PG_USERS=userlist.txt
    
    
    
    
    if [ -f /etc/pgbouncer/pgbouncer.ini ]
    then
    cat << EOF > /etc/pgbouncer/pgbouncer.ini
    [databases]
    * = host=${PG_PORT_5439_TCP_ADDR} port=${PG_PORT_5439_TCP_PORT} dbname=${PG_ENV_POSTGRESQL_DB_NAME} auth_user=${PG_ENV_POSTGRESQL_USER} password=${PG_ENV_POSTGRESQL_PASS}
    
    [pgbouncer]
    logfile = /var/log/postgresql/pgbouncer.log
    pidfile = /var/run/postgresql/pgbouncer.pid
    ;listen_addr = *
    listen_addr = 0.0.0.0
    listen_port = 6432
    unix_socket_dir = /var/run/postgresql
    auth_type = trust
    admin_users = ${PG_ENV_POSTGRESQL_USER}
    auth_file = /etc/pgbouncer/userlist.txt
    pool_mode = ${PG_ENV_POSTGRESQL_POOL_MODE}
    server_reset_query = DISCARD ALL
    max_client_conn = ${PG_ENV_POSTGRESQL_MAX_CLIENT_CONN}
    default_pool_size = ${PG_ENV_POSTGRESQL_DEFAULT_POOL_SIZE}
    ignore_startup_parameters = extra_float_digits
    server_idle_timeout = 60
    
    EOF
    fi
    if [ -f /etc/pgbouncer/userlist.txt ]
    then
            echo '"'"${PG_ENV_POSTGRESQL_USER}"'" "'"${PG_ENV_POSTGRESQL_PASS}"'"'  > /etc/pgbouncer/userlist.txt
    fi
    
    mkdir -p ${PG_LOG}
    chmod -R 0755 ${PG_LOG}
    chown -R ${PG_USER}:${PG_GROUP} ${PG_LOG}
    chmod 0640 ${PG_CONFIG_DIR}/${PG_CONFIG} ${PG_CONFIG_DIR}/${PG_USERS}
    chown ${PG_USER}:${PG_GROUP} ${PG_CONFIG_DIR}/${PG_CONFIG} ${PG_CONFIG_DIR}/${PG_USERS}
    
    echo "Starting pgbouncer"
    exec pgbouncer -u ${PG_USER} ${PG_CONFIG_DIR}/${PG_CONFIG}
    blbrblbr
    @blbrblbr1_twitter
    I don't understand pgbouncer logs. For example, when it's printing "LOG stats: 548 xacts/s, 548 queries/s, in 125426 B/s, out 183366 B/s, xact 714 us, query 714 us, wait 23 us", I don't believe that the average query time is 714 microseconds -- explain analyze select 1; requested over a unix socket yields 0.036 ms total, which is 36 microseconds. What am I missing?
    Rich Niemi
    @jrniemijr
    @patlolladurgaprasad8_gitlab did you find a solution to the "bad-af" server dns lookup failed? i have hit similar when deploying a pgbouncer v1.9 container. I had pgbouncer v1.8 container before that worked fine. after getting the bad-af server dns error, i even tried going back to the previous version but get the same bad-af error now.