Re: Pgbouncer and postgres

Fabio Pardi <f.pardi@xxxxxxxxxxxx> · Tue, 18 Sep 2018 10:25:12 +0200



    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 to bring it to the same error-state.
        

      Your
          problem might be tied either to the way you compiled it, or to some package
              installed/reconfigured with the updates (my CentOS
                  is not up to date to the latest packages)

      
      What
        if you try to use the RPM version
          of pgbouncer? Can you try that?

          
          How many interfaces to you have on the system?  Did you check if maybe some port/interface
              is in use?  You can stop pgbouncer
          and then run 'lsof -n | grep
            6543'. No line should return. 

                
                Are  you running pgbouncer from systemctl or command
                line? Maybe is a long shot, but one more thing you can
                        try is to remove
                          the line 'user=postgres'
                            and let it run as 'pgbouncer' user from root/systemctl.

                                  
                                  Is selinux
                                    enabled
                                      or disabled on the system?

                                  
                                  Regards,

                                    
                                    fabio
                                      pardi

                                  
    On 17/09/18 14:33, Nicola Contu wrote:

    
        Hello,
        I'm
          trying to get pgbouncer working but I'm getting this error :
        

        2018-09-17
          12:20:15.304 87772 NOISE safe_accept(12) = Resource
          temporarily unavailable
        2018-09-17
          12:20:36.183 87772 NOISE safe_accept(11) = 13 (127.0.0.1:59100)
        2018-09-17
          12:20:36.183 87772 NOISE new fd from accept=13
        2018-09-17
          12:20:36.183 87772 NOISE safe_accept(11) = Resource
          temporarily unavailable
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'logfile' =
          '/var/log/pgbouncer.log' ok:1
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' =
          '/home/postgres/pgbouncer.pid'
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' =
          '/home/postgres/pgbouncer.pid' ok:1
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' =
          'admin'
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' =
          'admin' ok:1
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres'
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres'
          ok:1
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections'
          = '220'
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections'
          = '220' ok:1
        2018-09-17
          12:21:14.412 88424 DEBUG parse_ini_file: 'log_connections' =
          '0'
        2018-09-17
          12:21:14.413 88424 DEBUG parse_ini_file: 'log_connections' =
          '0' ok:1
        2018-09-17
          12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections'
          = '0'
        2018-09-17
          12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections'
          = '0' ok:1
        2018-09-17
          12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3'
        2018-09-17
          12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3' ok:1
        2018-09-17
          12:21:14.413 88424 DEBUG loading auth_file:
          "/etc/pgbouncer/users.txt"
        2018-09-17
          12:21:14.417 88424 NOISE event: 128, SBuf: 192, PgSocket: 400,
          IOBuf: 4108
        2018-09-17
          12:21:14.417 88424 LOG File descriptor limit: 1024 (H:4096),
          max_client_conn: 100, max fds possible: 230
        2018-09-17
          12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x25277c0
        2018-09-17
          12:21:14.417 88424 DEBUG make_room(0x25277c0, 4): realloc
          newlen=256
        2018-09-17
          12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x252ebb0
        2018-09-17
          12:21:14.418 88424 NOISE connect(4, unix:/tmp/.s.PGSQL.6543) =
          Connection refused
        2018-09-17
          12:21:14.418 88424 NOISE safe_close(4) = 0
        2018-09-17
          12:21:14.418 88424 LOG Stale pidfile, removing
        2018-09-17
          12:21:14.419 88424 DEBUG adns_create_context: evdns2
        2018-09-17
          12:21:14.421 88424 DEBUG add_listen: 127.0.0.1:6543
        2018-09-17
          12:21:14.422 88424 NOISE old TCP_DEFER_ACCEPT on 11 = 0
        2018-09-17
          12:21:14.422 88424 NOISE install TCP_DEFER_ACCEPT on 11
        2018-09-17
          12:21:14.422 88424 LOG listening on 127.0.0.1:6543
        2018-09-17
          12:21:14.422 88424 DEBUG add_listen: unix:/tmp/.s.PGSQL.6543
        2018-09-17
          12:21:14.422 88424 LOG listening on unix:/tmp/.s.PGSQL.6543
        2018-09-17
          12:21:14.423 88424 LOG process up: pgbouncer 1.8.1, libevent
          2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips 
          26 Jan 2017
        2018-09-17
          12:21:14.756 88424 DEBUG loading auth_file:
          "/etc/pgbouncer/users.txt"
        2018-09-17
          12:21:48.917 88424 NOISE safe_accept(12) = 13 (unix:)
        2018-09-17
          12:21:48.917 88424 NOISE new fd from accept=13
        2018-09-17
          12:21:48.917 88424 WARNING tune_socket(13) failed: Operation
          not supported
        2018-09-17
          12:21:48.917 88424 NOISE safe_close(13) = 0
        2018-09-17
          12:21:48.918 88424 NOISE safe_accept(12) = Resource
          temporarily unavailable
        2018-09-17
          12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0
          B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
        2018-09-17
          12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0
          B/s, out 0 B/s, xact 0 us, query 0 us wait time 0 us
        2018-09-17
          12:23:26.561 88424 LOG Got SIGINT, shutting down
        2018-09-17
          12:23:26.872 88424 LOG server connections dropped, exiting
        2018-09-17
          12:23:26.872 88424 NOISE safe_close(11) = 0
        2018-09-17
          12:23:26.872 88424 NOISE safe_close(12) = 0
        

        Postgres
          version : 10.5
        Libevent
          : 2.0.5
        Pgbouncer
          : 1.8.1
        OS
          : Centos 7
        

        It
          used to work. Now after trying to install latest version of
          pgbouncer (1.9.0) does not work anymore. Then I re-compiled
          the v 1.8.1
        

        Content
          of the pgbouncer.ini
        [databases]
        DB_NAME
          = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer
          pool_size=120
        

        [pgbouncer]
        listen_port
          = 6543
        listen_addr
          = *
        auth_type
          = md5
        auth_file
          = /etc/pgbouncer/users.txt
        auth_query
          = select uname,phash from user_lookup($1)
        logfile
          = /var/log/pgbouncer.log
        pidfile
          = /home/postgres/pgbouncer.pid
        admin_users
          = admin
        user=postgres
        max_db_connections
          = 220
        log_connections=0
        log_disconnections=0
        verbose=3
        

        Content
          of the users .txt
        [root@cmd-dev1
          pgbouncer]# cat users.txt
        "pgbouncer"
          "***************"
        

        I
          tried to change the listen address to 127.0.0.1 or to the IP
          of the VM. Nothing changed.
        

        Can
          anyone help?
        

        Thanks