Re: Pgbouncer and postgres

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 tobring 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 versionof 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 <http://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 <http://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 <http://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(at)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
>
>