Re: Pgbouncer and postgres - Mailing list pgsql-general

On 9/19/18 5:40 AM, ncontu wrote:

Hi Fabio,
It used to work before, even without installing updates.

Exactly, the suspicion is that something in the updates changed the behavior of your system.

Yes, that's the suspicion. Or at least was..

I tried to update all the available packages available as of today for Centos7-OS and Centos7-updates hosted at:


http://ftp.nluug.nl/ftp/pub/os/Linux/distr/CentOS/7/os/x86_64

and

http://ftp.nluug.nl/ftp/pub/os/Linux/distr/CentOS/7/updates/x86_64/


With an up to date system, I m still able to use pgbouncer.

For the records, I m also posting the log lines showing up when pgbouncer is asked to start:


2018-09-19 15:49:42.574 9979 DEBUG parse_ini_file: 'verbose' = '3' ok:1
2018-09-19 15:49:42.575 9979 DEBUG loading auth_file: "/etc/pgbouncer/users.txt"
2018-09-19 15:49:42.576 9979 NOISE event: 136, SBuf: 200, PgSocket: 408, IOBuf: 4108
2018-09-19 15:49:42.577 9979 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 140
2018-09-19 15:49:42.577 9979 DEBUG pktbuf_dynamic(128): 0x82a790
2018-09-19 15:49:42.577 9979 DEBUG make_room(0x82a790, 4): realloc newlen=256
2018-09-19 15:49:42.577 9979 DEBUG pktbuf_dynamic(128): 0x82a970
2018-09-19 15:49:42.577 9979 NOISE connect(4, unix:/tmp/.s.PGSQL.6543) = No such file or directory
2018-09-19 15:49:42.577 9979 NOISE safe_close(4) = 0
2018-09-19 15:49:42.578 9981 DEBUG adns_create_context: c-ares 1.10.0
2018-09-19 15:49:42.578 9981 DEBUG add_listen: 0.0.0.0:6543
2018-09-19 15:49:42.578 9981 NOISE old TCP_DEFER_ACCEPT on 7 = 0
2018-09-19 15:49:42.578 9981 NOISE install TCP_DEFER_ACCEPT on 7
2018-09-19 15:49:42.578 9981 LOG listening on 0.0.0.0:6543
2018-09-19 15:49:42.578 9981 DEBUG add_listen: ::/6543
2018-09-19 15:49:42.578 9981 NOISE old TCP_DEFER_ACCEPT on 8 = 0
2018-09-19 15:49:42.578 9981 NOISE install TCP_DEFER_ACCEPT on 8
2018-09-19 15:49:42.578 9981 LOG listening on ::/6543
2018-09-19 15:49:42.578 9981 DEBUG add_listen: unix:/tmp/.s.PGSQL.6543
2018-09-19 15:49:42.579 9981 LOG listening on unix:/tmp/.s.PGSQL.6543
2018-09-19 15:49:42.579 9981 LOG process up: pgbouncer 1.8.1, libevent 2.0.21-stable (epoll), adns: c-ares 1.10.0, tls: OpenSSL 1.0.2k-fips  26 Jan 2017


and this part shows up when the user connects:

psql -h /tmp -p 6543 -U someuser mydb


2018-09-19 16:02:12.125 10457 NOISE C-0x1391120: (nodb)/(nouser)@unix(10465):6543 pkt='!' len=77
2018-09-19 16:02:12.125 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10465):6543 got var: user=someuser
2018-09-19 16:02:12.125 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10465):6543 got var: database=mydb
2018-09-19 16:02:12.125 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10465):6543 using application_name: psql
2018-09-19 16:02:12.125 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10465):6543 got var: client_encoding=UTF8
2018-09-19 16:02:12.125 10457 NOISE safe_send(10, 13) = 13
2018-09-19 16:02:12.125 10457 NOISE resync: done=77, parse=77, recv=77
2018-09-19 16:02:12.125 10457 NOISE resync: done=0, parse=0, recv=0
2018-09-19 16:02:12.125 10457 NOISE safe_recv(10, 4096) = 0
2018-09-19 16:02:12.126 10457 NOISE safe_close(10) = 0
2018-09-19 16:02:19.094 10457 NOISE safe_accept(9) = 10 (unix:)
2018-09-19 16:02:19.094 10457 NOISE new fd from accept=10
2018-09-19 16:02:19.094 10457 NOISE unix peer uid: 0
2018-09-19 16:02:19.094 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10470):6543 P: got connection: unix->unix
2018-09-19 16:02:19.094 10457 NOISE safe_accept(9) = Resource temporarily unavailable
2018-09-19 16:02:19.094 10457 NOISE resync: done=0, parse=0, recv=0
2018-09-19 16:02:19.094 10457 NOISE safe_recv(10, 4096) = 77
2018-09-19 16:02:19.094 10457 NOISE C-0x1391120: (nodb)/(nouser)@unix(10470):6543 pkt='!' len=77
2018-09-19 16:02:19.094 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10470):6543 got var: user=someuser
2018-09-19 16:02:19.094 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10470):6543 got var: database=mydb
2018-09-19 16:02:19.094 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10470):6543 using application_name: psql
2018-09-19 16:02:19.094 10457 DEBUG C-0x1391120: (nodb)/(nouser)@unix(10470):6543 got var: client_encoding=UTF8
2018-09-19 16:02:19.094 10457 NOISE safe_send(10, 13) = 13
2018-09-19 16:02:19.094 10457 NOISE resync: done=77, parse=77, recv=77
2018-09-19 16:02:19.094 10457 NOISE resync: done=0, parse=0, recv=0
2018-09-19 16:02:19.094 10457 NOISE safe_recv(10, 4096) = 0
2018-09-19 16:02:19.095 10457 NOISE safe_close(10) = 0



after providing the password, connection works as usual.

As mentioned earlier by Adrian, the offending line is probably:

I tried installing it from source, from git repo. Can't find the RPM, do you
have any link for centos7?

I believe Fabio is talking about the Postgres community repo:

https://yum.postgresql.org/

I m pointing to:

https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/

At the time of writing 1.9.0 is available, while the mirror i have in house (and i m pointing to) only has 1.8.1

This would be a repo change if you are currently using the CentOS repos.