Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    Steve
    @steve789_gitlab
    (both db1 and db2 are catched by the same line in pg_hba of postgresql)
    at same point a restart fixed this problem for some users, but 15 minutes or so after the restart other users experienced the same problem
    Steve
    @steve789_gitlab
    what is more: making a direct connection (with pooling) always works (so the password is ok, and there free connections available)
    Steve
    @steve789_gitlab
    if it helps:
    pgbouncer=# show config;
                key            |                         value                          |                        default                         | changeable 
    ---------------------------+--------------------------------------------------------+--------------------------------------------------------+------------
     admin_users               | pgbouncer, postgres, admin                             |                                                        | yes
     application_name_add_host | 1                                                      | 0                                                      | yes
     auth_file                 | /etc/pgbouncer/userlist.txt                            |                                                        | yes
     auth_hba_file             |                                                        |                                                        | yes
     auth_query                | SELECT * FROM  pgbouncer.user_lookup($1);              | SELECT usename, passwd FROM pg_shadow WHERE usename=$1 | yes
     auth_type                 | md5                                                    | md5                                                    | yes
     auth_user                 |                                                        |                                                        | yes
     autodb_idle_timeout       | 3600                                                   | 3600                                                   | yes
     client_idle_timeout       | 0                                                      | 0                                                      | yes
     client_login_timeout      | 60                                                     | 60                                                     | yes
     client_tls_ca_file        |                                                        |                                                        | yes
     client_tls_cert_file      | /etc/cert/fullchain.pem |                                                        | yes
     client_tls_ciphers        | secure                                                 | fast                                                   | yes
     client_tls_dheparams      | auto                                                   | auto                                                   | yes
     client_tls_ecdhcurve      | auto                                                   | auto                                                   | yes
     client_tls_key_file       | /etc/cert/privkey.pem   |                                                        | yes
     client_tls_protocols      | secure                                                 | secure                                                 | yes
     client_tls_sslmode        | require                                                | disable                                                | yes
     conffile                  | /etc/pgbouncer/pgbouncer.ini                           |                                                        | yes
     default_pool_size         | 20                                                     | 20                                                     | yes
     disable_pqexec            | 0                                                      | 0                                                      | no
     dns_max_ttl               | 15                                                     | 15                                                     | yes
     dns_nxdomain_ttl          | 15                                                     | 15                                                     | yes
     dns_zone_check_period     | 0                                                      | 0                                                      | yes
     idle_transaction_timeout  | 0                                                      | 0                                                      | yes
     ignore_startup_parameters | extra_float_digits                                     |
     job_name                  | pgbouncer                                              | pgbouncer                                              | no
     listen_backlog            | 128                                                    | 128                                                    | no
     listen_port               | 62122                                                  | 6432                                                   | no
     log_connections           | 1                                                      | 1                                                      | yes
     log_disconnections        | 1                                                      | 1                                                      | yes
     log_pooler_errors         | 1                                                      | 1                                                      | yes
     log_stats                 | 1                                                      | 1                                                      | yes
     logfile                   | /var/log/postgresql/pgbouncer.log                      |                                                        | yes
     max_client_conn           | 2048                                                   | 100                                                    | yes
     max_db_connections        | 0                                                      | 0                                                      | yes
     max_packet_size           | 2147483647                                             | 2147483647                                             | yes
     max_user_connections      | 64                                                     | 0                                                      | yes
     min_pool_size             | 0                                                      | 0                                                      | yes
     pidfile                   | /var/run/postgresql/pgbouncer.pid                      |                                                        | no
     pkt_buf                   | 4096                                                   | 4096                                                   | no
     pool_mode                 | transaction                                            | session                                                | yes
     query_timeout             | 0                                                      | 0                                                      | yes
     query_wait_timeout        | 120                                                    | 120                                                    | yes
     reserve_pool_size         | 2                                                      | 0                                                      | yes
     reserve_pool_timeout      | 5                                                      | 5                                                      | yes
     resolv_conf               |                                                        |                                                        | no
     sbuf_loopcnt              | 5                                                      | 5                                                      | yes
     server_check_delay        | 30                                                     | 30                                                     | yes
     server_check_query        | select 1                                               | select 1                                               | yes
     server_connect_timeout    | 15                                                     | 15                                                     | yes
     server_fast_close         | 0                                                      | 0                                                      | yes
     server_idle_timeout       | 60                                                     | 600
     server_lifetime           | 3600                                                   | 3600                                                   | yes
     server_login_retry        | 15                                                     | 15                                                     | yes
     server_reset_query        | DISCARD ALL                                            | DISCARD ALL                                            | yes
     server_reset_query_always | 0                                                      | 0                                                      | yes
     server_round_robin        | 0                                                      | 0                                                      | yes
     server_tls_ca_file        |                                                        |                                                        | yes
     server_tls_cert_file      |                                                        |                                                        | yes
     server_tls_ciphers        | fast                                                   | fast                                                   | yes
     server_tls_key_file       |                                                        |                                                        | yes
     server_tls_protocols      | secure                                                 | secure                                                 | yes
     server_tls_sslmode        | allow                                                  | disable                                                | yes
     so_reuseport              | 0                                                      | 0                                                      | no
     stats_period              | 60                                                     | 60                                                     | yes
     stats_users               | pgbouncer, stats, root, admin                          |                                                        | yes
     suspend_timeout           | 10                                                     | 10                                                     | yes
     syslog                    | 0                                                      | 0                                                      | yes
     syslog_facility           | daemon                                                 | daemon                                                 | yes
     syslog_ident              | pgbouncer                                              | pgbouncer                                              | yes
     tcp_defer_accept          | 1                                                      |                                                        | yes
     tcp_keepalive             | 1                                                      | 1                                                      | yes
     tcp_keepcnt               | 0                                                      | 0                                                      | yes
     tcp_keepidle              | 0                                                      | 0                                                      | yes
     tcp_keepintvl             | 0                                                      | 0                                                      | yes
     tcp_socket_buffer         | 0                                                      | 0                                                      | yes
     tcp_user_timeout          | 0                                                      | 0                                                      | yes
     unix_socket_dir           | /var/run/postgresql                                    | /tmp                                                   | no
     unix_socket_group         |                                                        |                                                        | no
     unix_socket_mode          | 511                                                    | 0777
    Steve
    @steve789_gitlab
    version 1.16 of pgbouncer
    Jared Rohe
    @JaredRohe

    @here Hello pgbouncer folks!

    I have a question about what is the best practice for a PG bouncer installation? My question is essentially: client-side or server-side connection pooling, or Both??
    This answer in the FAQ gives me some idea, but still leaves me with some questions. I would be extremely grateful for some more clarification!

    If anyone could check my understanding in the below statements, that would be immensely helpful.

    Let's say I have a NodeJS web application that scales horizontally but has a minimum of 3 web servers. Each web server has client side connection pool of size 20. This means that there could be 60 connections to Postgres which seems fine. Now as auto-scaling kicks in, and more web servers are spun up (let's say 3 more) it's possible that another 60 connections are made to Postgres bringing out total to 120 which would put us over the default limit of 100 connections.

    It's my understanding that the best way to avoid flooding postgres with connections is to have server side connection pooling. This way the pool will only make as many connections to postgres as its configured, regardless of client connections to the pool.

    However, it also seems advantageous to have client side connection pooling in this scenario. Having a pool client side prevents a client from creating an unbounded number of connections.

    So it seems like both client and server side connection pooling help throttle the number of connections being created. With this understanding, one would conclude that having both connection and server side connection pooling is desired.

    Could someone please verify if my understanding is correct? Thanks so much!!!

    2 replies
    Ludovic Toinel
    @ltoinel
    Hi, do you have a good article that describes how to tune PGBouncer in production ? Is-it better to have a lot of small instances ? What are the recommendations for a classic TypeORM app in production that uses 250 to 500 SQL connections ? Thank-you in advance for your feedbacks.
    1 reply
    Bruno Gomes
    @BdeUtra
    Hi! We run around 20 pgbouncers in kubernetes. It's often the case when a new pgbouncer pod is created, it struggles and we observe an unusually high rate of clients_waiting. The pod never actually recovers from this "struggle" and even though it has fewer clients_active than other pods, its clients_waiting is much higher.
    When digging further we observe that at times when we see a high number of clients waiting for a server conn to a database, that pgbouncer pod actually has several idle server connections available but 0 active server connections (active and idle server conns are a gauge and not a continuous measurement sadly). Has anyone seen this behaviour before?
    MD ARQUM FAROOQUI
    @ArqamFarooqui110719

    Hi all,
    PostgreSQL: v9.6.8
    pgbouncer : v1.5.4
    I have installed pgbouncer by following link : https://medium.com/swlh/pgbouncer-installation-configuration-and-use-cases-for-better-performance-1806316f3a22
    Pgbouncer has successfully installed and iys service is running, we have confirmed the same as:

    root@d0c40748b537:/etc/pgbouncer# service pgbouncer status
    [ ok ] pgbouncer is running.

    But When I try to access pgbouncer shell psql --host=/tmp --username=postgres --no-password pgbouncer it is returning below error:

    psql: could not connect to server: No such file or directory
            Is the server running locally and accepting
            connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

    Any help will be appreciated, Thank you:)

    Peter Eisentraut
    @petere
    @BdeUtra Could be related to cancel request processing. See fixes in recent releases.
    @ArqamFarooqui110719 pgbouncer runs on port 6432.
    Dharavath Dayakar
    @dayakar_13_twitter
    I am trying to run below command
    I am trying to run below command
    I am trying to run below command pgbench -c -C -T 60 -h localhost -p 6432 pgbouncer getting error as ERROR: invalid command ' select count(*) from pgbench_branches', use SHOW HELP
    Peter Eisentraut
    @petere
    @dayakar_13_twitter You cannot run pgbench against the pgbouncer database. You need to pick a "real" database.
    Dharavath Dayakar
    @dayakar_13_twitter
    If try to use auth_type=plain in pgbouncer.ini
    And try to run psql -U bouncer -p 6432 -h localhost test_pgb getting error as connection to database "test_pgb" failed. Error: password authentication failed. Eventhough if i use same password present in userlist.txt
    Peter Eisentraut
    @petere
    @dayakar_13_twitter Check the log file to see if there are any clues.
    Peter Eisentraut
    @petere
    merarischroeder
    @merarischroeder
    Hi everyone! I am trying to improve PostgreSQL by making it possible to change the user of an existing connection. They only use mailing lists, so if this interests you, I could benefit from your support on pgsql-hackers@lists.postgresql.org for Subject:"Feature Proposal: Connection Pool Optimization - Change the Connection User". See my proposal GDoc here - https://docs.google.com/document/d/1u6mVKEHfKtR80UrMLNYrp5D6cCSW1_arcTaZ9HcAKlw/edit?usp=sharing
    Selman AY
    @SelmanAY
    image.png
    Selman AY
    @SelmanAY

    Hi I have a strange experience.
    Above screenshot is our pgbouncer configs (which are different then default values)
    Almost randomly pgbouncer becomes irresponsive.

    The only log we can see is client_login_timeout. Log verbosity is 0.
    No new connections can be made to pgbouncer. when i have an open psql session to pgbouncer database it can not even respond to my internal commands like show pools;
    It recovers from this state in about 5-10 minutes or we restart it.
    Any ideas are welcome
    Thanks

    David Fetter
    @davidfetter
    sorry, can't read screenshots. Any chance you can paste it to a paste site?
    Selman AY
    @SelmanAY
    David Fetter
    @davidfetter
    still a screenshot. Please to assume that the people helping you are using a screen reader.
    Selman AY
    @SelmanAY
    yes. sorry about that. I was trying to format them
    David Fetter
    @davidfetter
    hrm. I recall there's a command that interrupts the event loop and so is not great to use when pgbouncer is on line. lemme check...
    SHOW fds
    David Fetter
    @davidfetter
    could something be doing that?
    Selman AY
    @SelmanAY
    the only person connecting to pgbouncer database is me and i am not executing this command
    don't know much about this command
    (correctiong) my monitoring tool (pgwatch2) executes pg_stats every 60 seconds against pgbouncer database
    David Fetter
    @davidfetter
    I don't suppose pgwatch2 does anything along that line...
    Selman AY
    @SelmanAY
    2021-11-23 11:04:16.360 [41045] LOG S-0x2025d68: ####/applicationuser@####@(bad-af):0 closing because: connect timeout (age=154s)
    2021-11-23 11:04:16.360 [41045] LOG C-0x7f3b7bd12930: ####/applicationuser@####@aaa.bb.cc.ddd:45180 closing because: client_login_timeout (age=154s)
    2021-11-23 11:04:16.360 [41045] WARNING C-0x7f3b7bd12930: ####/applicationuser@####@aaa.bb.cc.ddd:45180 pooler error: client_login_timeout
    2021-11-23 11:04:16.360 [41045] LOG C-0x1fdfe20: ####/applicationuser@####@aaa.bb.cc.ddd:45182 closing because: client_login_timeout (age=154s)
    2021-11-23 11:04:16.360 [41045] WARNING C-0x1fdfe20: ####/applicationuser@####@aaa.bb.cc.ddd:45182 pooler error: client_login_timeout
    2021-11-23 11:04:16.360 [41045] LOG C-0x200d368: ####/applicationuser@####@aaa.bb.cc.ddd:45184 closing because: client_login_timeout (age=154s)
    2021-11-23 11:04:16.360 [41045] WARNING C-0x200d368: ####/applicationuser@####@aaa.bb.cc.ddd:45184 pooler error: client_login_timeout
    2021-11-23 11:04:16.360 [41045] LOG C-0x1ff8230: ####/applicationuser@####@aaa.bb.cc.ddd:50800 closing because: client_login_timeout (age=154s)
    2021-11-23 11:04:16.360 [41045] WARNING C-0x1ff8230: ####/applicationuser@####@aaa.bb.cc.ddd:50800 pooler error: client_login_timeout
    2021-11-23 11:04:16.360 [41045] LOG C-0x7f3b7bd02360: ####/applicationuser@####@aaa.bb.cc.ddd:50806 closing because: client_login_timeout (age=154s)
    everytime we have this bad-af ... error. and then we have these client_login_timeouts
    can not find anything regarding to bad-af in google. (except for some DNS related ones)
    David Fetter
    @davidfetter
    client login timeout. interesting.
    vignesh ravichandran
    @vira1228_twitter

    Hi folks, running into something weird problem.

    We see pgbouncer_pools_cl_waitinghowever we have sufficient default_pool_size and max_client_conn set. Anything else that could cause clients to be waiting?

    vignesh ravichandran
    @vira1228_twitter
    Is this the recommended Prometheus exporter btw https://github.com/prometheus-community/pgbouncer_exporter?
    vignesh ravichandran
    @vira1228_twitter
    We have max_user_connections, min_pool_size, max_db_connections set to 0
    We do see this issue after a spike. Reading about min_pool_size seems like something that could help spikes.
    mrhankey911
    @mrhankey911

    Hi guys, I am new in this chat, has anyone had a problem with restarting pgbouncer like this ?
    When i do systemctl restart pgbouncer i got error like this:

    ● pgbouncer.service - LSB: start pgbouncer
         Loaded: loaded (/etc/init.d/pgbouncer; generated)
         Active: failed (Result: exit-code) since Thu 2021-12-02 09:50:53 UTC; 5min ago
           Docs: man:systemd-sysv-generator(8)
        Process: 22215 ExecStart=/etc/init.d/pgbouncer start (code=exited, status=1/FAILURE)
          Tasks: 2 (limit: 154459)
         Memory: 4.5M
         CGroup: /system.slice/pgbouncer.service
                 └─21363 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
    
    Dec 02 09:50:53 lnd-pgbouncer-106 systemd[1]: Starting LSB: start pgbouncer...
    Dec 02 09:50:53 lnd-pgbouncer-106 pgbouncer[22215]:  * Starting PgBouncer pgbouncer
    Dec 02 09:50:53 lnd-pgbouncer-106 pgbouncer[22215]:    ...fail!

    And process of pgb still working:

    root@lnd-pgbouncer-106:/etc/pgbouncer# ps aux | grep -i p[g]b
    postgres   21363  0.0  0.0  16084  2632 ?        Sl   09:48   0:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini

    journalctl -xe

    -- The unit pgbouncer.service has entered the 'failed' state with result 'exit-code'.
    Dec 02 10:00:44 lnd-pgbouncer-106 systemd[1]: Failed to start LSB: start pgbouncer.
    -- Subject: A start job for unit pgbouncer.service has failed
    -- Defined-By: systemd
    -- Support: http://www.ubuntu.com/support
    --
    -- A start job for unit pgbouncer.service has finished with a failure.
    --
    -- The job identifier is 3970 and the job result is failed.

    pgbouncer version:

    pgbouncer/focal,now 1.12.0-3 amd64 [installed]
    mrhankey911
    @mrhankey911
    and even if i do systemctl stop pgbouncer.service, it stops successfully but process still running:
    ● pgbouncer.service - LSB: start pgbouncer
         Loaded: loaded (/etc/init.d/pgbouncer; generated)
         Active: inactive (dead) since Thu 2021-12-02 10:11:54 UTC; 18s ago
           Docs: man:systemd-sysv-generator(8)
        Process: 22695 ExecStart=/etc/init.d/pgbouncer start (code=exited, status=0/SUCCESS)
        Process: 22707 ExecStop=/etc/init.d/pgbouncer stop (code=exited, status=0/SUCCESS)
          Tasks: 2 (limit: 154459)
         Memory: 4.4M
         CGroup: /system.slice/pgbouncer.service
                 └─22701 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
    
    Dec 02 10:11:47 lnd-pgbouncer-106 systemd[1]: Starting LSB: start pgbouncer...
    Dec 02 10:11:47 lnd-pgbouncer-106 pgbouncer[22695]:  * Starting PgBouncer pgbouncer
    Dec 02 10:11:47 lnd-pgbouncer-106 pgbouncer[22695]:    ...done.
    Dec 02 10:11:47 lnd-pgbouncer-106 systemd[1]: Started LSB: start pgbouncer.
    Dec 02 10:11:54 lnd-pgbouncer-106 systemd[1]: Stopping LSB: start pgbouncer...
    Dec 02 10:11:54 lnd-pgbouncer-106 pgbouncer[22707]:  * Stopping PgBouncer pgbouncer
    Dec 02 10:11:54 lnd-pgbouncer-106 pgbouncer[22707]:    ...done.
    Dec 02 10:11:54 lnd-pgbouncer-106 systemd[1]: pgbouncer.service: Succeeded.
    Dec 02 10:11:54 lnd-pgbouncer-106 systemd[1]: Stopped LSB: start pgbouncer.
    root@lnd-pgbouncer-106:/etc/pgbouncer# ps uax  | grep -i p[g]b
    postgres   22701  0.0  0.0  15744  1872 ?        Sl   10:11   0:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
    Peter Eisentraut
    @petere
    @mrhankey911 Check the log file of pgbouncer.
    mrhankey911
    @mrhankey911

    @petere Thanks!! There was an error:

     FATAL @src/main.c:893 in function main(): unix socket is in use, cannot continue

    Solution here - pgbouncer/pgbouncer#143

    tamnguyenvt
    @tamnvt_gitlab
    Hi, can i suggest PGBouncer to implement a feature like Binlog reader (ProxySQL - Mysql)
    Here the link: https://proxysql.com/blog/proxysql-gtid-causal-reads/
    So their solution is really convinced:
    • Binlog reader is a process installed/run along with db servers (master and slave)
    • Once new GTID (like LSN in posgres) is generated (means a write is performed)-> GTID will be streamed to Proxy servers (both master and slave)
    • When client want to read, they will poll a wait point (here GTID or LSN).
    • Because Proxy server has all GTID/LSN of slave/master in their local storage, so proxy server will immediately know where to query
    • In case no slave satisfy, query will be routed to master which is incredible.
      Im a fan of Postgres so i really hope this feature is available.
      Thank you so much
    actually i joined Postgre Build 2021 and i understand that we want to keep PGBouncer as simple as possible but this feature is really practical and should be considered as basic/background features .