Обсуждение: Pgbouncer and postgres

On 9/17/18 5:33 AM, Nicola Contu wrote:
> Hello,
> 
> I'm trying to get pgbouncer working but I'm getting this error :
> 
> 

What is generating the log below?

To me it looks like everything is on track until this:

> 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

Have there been updates to CentOS during this?

> 
> 
> 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

Did you compile 1.9.0 from source or install from package?

> 
> 
> 
> 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
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/17/18 7:05 AM, Nicola Contu wrote:
> The log is from pgbouncer.
> 
> I installed updates after this problem, but they did not resolve anything.

I was thinking more of updates that where done between the last time it 
worked and the time it started failing.

Along that line, when was the last time it worked?

> 
> I compiled 1.9.0 from source on the same path of the previous one. Then 
> back to 1.8.1 on the same path.
> 
> I also tried changing the listening port on pgbouncer, but that does not 
> change too much.

Yeah, your log shows the port/socket coming up:

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

> I can connect via telnet on that port, but we I try with anything else 
> (like Apache) it makes pgbouncer crashing.

Are there log entries from that?

> 
> 
> Il giorno lun 17 set 2018 alle ore 15:44 Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> ha scritto:
> 
>     On 9/17/18 5:33 AM, Nicola Contu wrote:
>      > Hello,
>      >
>      > I'm trying to get pgbouncer working but I'm getting this error :
>      >
>      >
> 
>     What is generating the log below?
> 
>     To me it looks like everything is on track until this:
> 
>      > 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
> 
>     Have there been updates to CentOS during this?
> 
>      >
>      >
>      > 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
> 
>     Did you compile 1.9.0 from source or install from package?
> 
>      >
>      >
>      >
>      > 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
>      >
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/17/18 7:19 AM, Nicola Contu wrote:
> I'm not aware of any update installed. There were like 600+ updates in fact.
> 
> These are the logs when connecting via telnet :

How about those from when Apache tries to connect and pgbouncer crashes?

> Connection closed by foreign host.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/17/18 7:26 AM, Nicola Contu wrote:
> [Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid 
> 140586929567488] [client 10.160.41.3:51764 <http://10.160.41.3:51764>] 
> PHP WARNING: pg_connect(): Unable to connect to PostgreSQL server: could 
> not connect to server: Connection refused\n\tIs the server running on 
> host "IP_ADDR" and accepting\n\tTCP/IP connections on port 
> 6543? in /home/cmd3/adodb5/drivers/adodb-postgres64.inc.php on line 727

To me it looks like IP_ADDR is not resolving to a host.

In your pgbouncer.ini I see:

DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer 
pool_size=120

but the error is about connecting to port 6543 which is your pgbouncer 
'server'.

Are you sure your connection string is pointing at the correct thing?

> 
> So basically the same I get when connecting via psql on port 6543

What is the full psql command?

> 
> Il giorno lun 17 set 2018 alle ore 16:23 Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> ha scritto:
> 
>     On 9/17/18 7:19 AM, Nicola Contu wrote:
>      > I'm not aware of any update installed. There were like 600+
>     updates in fact.
>      >
>      > These are the logs when connecting via telnet :
> 
>     How about those from when Apache tries to connect and pgbouncer crashes?
> 
>      > Connection closed by foreign host.
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/17/18 8:39 AM, Nicola Contu wrote:
Please reply to list also.
Ccing list.

> No wait, IP_ADDR has been modified by me, but there is the IP of the 
> machine 10.151.x.

To be clear in your posts you are replacing the actual IP with IP_ADDR, 
correct?

> 
> 
> Here is the psql command .
> 
> [root@cmd-dev1 ~]# psql -U ncontu -p 6543 cmd3dev
> psql: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.

So what does the pgbouncer log show when you do above?

> 
> 
> 
> 
> Il giorno lun 17 set 2018 alle ore 17:35 Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> ha scritto:
> 
>     On 9/17/18 7:26 AM, Nicola Contu wrote:
>      > [Mon Sep 17 10:24:59.041589 2018] [php7:notice] [pid 63893:tid
>      > 140586929567488] [client 10.160.41.3:51764
>     <http://10.160.41.3:51764> <http://10.160.41.3:51764>]
>      > PHP WARNING: pg_connect(): Unable to connect to PostgreSQL
>     server: could
>      > not connect to server: Connection refused\n\tIs the server
>     running on
>      > host "IP_ADDR" and accepting\n\tTCP/IP connections on port
>      > 6543? in /home/cmd3/adodb5/drivers/adodb-postgres64.inc.php on
>     line 727
> 
>     To me it looks like IP_ADDR is not resolving to a host.
> 
>     In your pgbouncer.ini I see:
> 
>     DB_NAME = host=IP_ADDD port=5432 dbname=DB_NAME auth_user=pgbouncer
>     pool_size=120
> 
>     but the error is about connecting to port 6543 which is your pgbouncer
>     'server'.
> 
>     Are you sure your connection string is pointing at the correct thing?
> 
>      >
>      > So basically the same I get when connecting via psql on port 6543
> 
>     What is the full psql command?
> 
>      >
>      > Il giorno lun 17 set 2018 alle ore 16:23 Adrian Klaver
>      > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> ha scritto:
>      >
>      >     On 9/17/18 7:19 AM, Nicola Contu wrote:
>      >      > I'm not aware of any update installed. There were like 600+
>      >     updates in fact.
>      >      >
>      >      > These are the logs when connecting via telnet :
>      >
>      >     How about those from when Apache tries to connect and
>     pgbouncer crashes?
>      >
>      >      > Connection closed by foreign host.
>      >
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/17/18 8:59 AM, Nicola Contu wrote:
> Yeah, I replaced the IP of the machine with the word IP_ADDR.
> 
> On the pgbouncer log I get this while trying to connect via psql:
> 
> 2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed: Operation 
> not supported
> 2018-09-17 15:56:49.452 67611 NOISE safe_close(16) = 0
> 2018-09-17 15:56:49.452 67611 NOISE safe_accept(13) = Resource 
> temporarily unavailable
> 2018-09-17 15:56:50.728 67611 NOISE safe_accept(13) = 16 (unix:)
> 2018-09-17 15:56:50.729 67611 NOISE new fd from accept=16
> 2018-09-17 15:56:50.729 67611 WARNING tune_socket(16) failed: Operation 
> not supported
> 2018-09-17 15:56:50.729 67611 NOISE safe_close(16) = 0
> 2018-09-17 15:56:50.729 67611 NOISE safe_accept(13) = Resource 
> temporarily unavailable
> 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = 16 
> (10.151.2.145:58696 <http://10.151.2.145:58696>)
> 2018-09-17 15:56:56.418 67611 NOISE new fd from accept=16
> 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = Resource 
> temporarily unavailable

Can you connect to the actual database running on port 5432?

Deciphering the above log is beyond my capabilities. I think it is time 
to file an issue here:

https://github.com/pgbouncer/pgbouncer/issues



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/17/18 9:09 AM, Nicola Contu wrote:
> Yes I can connect to port 5432 without any problem.
> 
> The thing is that I am not seeing anything on the postgres log when 
> connecting to 6543.

Pretty sure you won't as 6543 is controlled by pgbouncer and pgbouncer 
seems to be failing before it gets around to connecting to the actual 
database.

> 
> Sure, thanks for your help.
> I will create an issue there.
> 
> 
> Il giorno lun 17 set 2018 alle ore 18:07 Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> ha scritto:
> 
>     On 9/17/18 8:59 AM, Nicola Contu wrote:
>      > Yeah, I replaced the IP of the machine with the word IP_ADDR.
>      >
>      > On the pgbouncer log I get this while trying to connect via psql:
>      >
>      > 2018-09-17 15:56:49.452 67611 WARNING tune_socket(16) failed:
>     Operation
>      > not supported
>      > 2018-09-17 15:56:49.452 67611 NOISE safe_close(16) = 0
>      > 2018-09-17 15:56:49.452 67611 NOISE safe_accept(13) = Resource
>      > temporarily unavailable
>      > 2018-09-17 15:56:50.728 67611 NOISE safe_accept(13) = 16 (unix:)
>      > 2018-09-17 15:56:50.729 67611 NOISE new fd from accept=16
>      > 2018-09-17 15:56:50.729 67611 WARNING tune_socket(16) failed:
>     Operation
>      > not supported
>      > 2018-09-17 15:56:50.729 67611 NOISE safe_close(16) = 0
>      > 2018-09-17 15:56:50.729 67611 NOISE safe_accept(13) = Resource
>      > temporarily unavailable
>      > 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = 16
>      > (10.151.2.145:58696 <http://10.151.2.145:58696>
>     <http://10.151.2.145:58696>)
>      > 2018-09-17 15:56:56.418 67611 NOISE new fd from accept=16
>      > 2018-09-17 15:56:56.418 67611 NOISE safe_accept(11) = Resource
>      > temporarily unavailable
> 
>     Can you connect to the actual database running on port 5432?
> 
>     Deciphering the above log is beyond my capabilities. I think it is time
>     to file an issue here:
> 
>     https://github.com/pgbouncer/pgbouncer/issues
> 
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

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

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

I got two interfaces : private IP and localhost

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

I tried both /etc/init.d and command line, same issue.
Tried with a new user called pgbouncer, same issue 

This is the result of lsof : 

[root@cmd-dev1 /]# lsof -n | grep 6543
postmaste  80762               postgres   10r     FIFO                0,9       
0t0     665436 pipe
postmaste  80762               postgres   11w     FIFO                0,9       
0t0     665436 pipe

Thanks




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

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.

> 
> I got two interfaces : private IP and localhost
> 
> Selinux is disabled.
> 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/

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


> 
> I tried both /etc/init.d and command line, same issue.
> Tried with a new user called pgbouncer, same issue
> 
> This is the result of lsof :
> 
> [root@cmd-dev1 /]# lsof -n | grep 6543
> postmaste  80762               postgres   10r     FIFO                0,9
> 0t0     665436 pipe
> postmaste  80762               postgres   11w     FIFO                0,9
> 0t0     665436 pipe
> 
> Thanks
> 
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

So unexpectedly with the rpms, it installed it on a different place and it is
working from command line

[root@cmd-dev1 pgbouncer]# /bin/pgbouncer /etc/pgbouncer/pgbouncer.ini -u
postgres 
2018-09-19 15:38:36.081 46322 LOG File descriptor limit: 1024 (H:4096),
max_client_conn: 100, max fds possible: 230
2018-09-19 15:38:36.082 46322 LOG Stale pidfile, removing
2018-09-19 15:38:36.082 46322 LOG listening on 0.0.0.0:6543
2018-09-19 15:38:36.082 46322 LOG listening on ::/6543
2018-09-19 15:38:36.083 46322 WARNING Cannot listen on
unix:/tmp/.s.PGSQL.6543: bind(): Address already in use
2018-09-19 15:38:36.083 46322 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

[root@cmd-dev1 ~]# psql -U ncontu -p 6543 cmd3dev -h IP_ADD
Password for user ncontu:
psql (10.5)
Type "help" for help.

cmd3dev=# \q


So at this point... I don't really know why compiled from source it does not
work.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 9/19/18 8:39 AM, ncontu1 wrote:
> So unexpectedly with the rpms, it installed it on a different place and it is
> working from command line
> 
> [root@cmd-dev1 pgbouncer]# /bin/pgbouncer /etc/pgbouncer/pgbouncer.ini -u
> postgres
> 2018-09-19 15:38:36.081 46322 LOG File descriptor limit: 1024 (H:4096),
> max_client_conn: 100, max fds possible: 230
> 2018-09-19 15:38:36.082 46322 LOG Stale pidfile, removing
> 2018-09-19 15:38:36.082 46322 LOG listening on 0.0.0.0:6543
> 2018-09-19 15:38:36.082 46322 LOG listening on ::/6543
> 2018-09-19 15:38:36.083 46322 WARNING Cannot listen on
> unix:/tmp/.s.PGSQL.6543: bind(): Address already in use

Is the compiled version still running?

Or is something else sitting on the 6543 socket?

> 2018-09-19 15:38:36.083 46322 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
> 
> [root@cmd-dev1 ~]# psql -U ncontu -p 6543 cmd3dev -h IP_ADD
> Password for user ncontu:
> psql (10.5)
> Type "help" for help.
> 
> cmd3dev=# \q
> 
> 
> So at this point... I don't really know why compiled from source it does not
> work.

Not sure.

For those that might know it would help to provide the complete 
./configure used and the config.log(or equivalent)

> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

I tried to compile 1.8.1 on my (now) up to date Centos 7 and everything
went ok. I can run pgbouncer and login to it.


this is the relevant part of the diff between config.log files


> config.status:1112: lib/usual/config.h is unchanged
4676c4689
< CPPFLAGS=''
---
> CPPFLAGS=' -I/usr/local/lib/include'

4690c4703
< LDFLAGS='-g  -Wl,--as-needed'
---
> LDFLAGS='-g  -Wl,--as-needed -L/usr/local/lib/lib'


together with the socket problem mentioned earlier makes me think that
we are overlooking something or we miss some important piece of information.

regards,

fabio pardi


On 09/19/2018 06:11 PM, Nicola Contu wrote:
> No the old one is stopped, so not sure why it says the port is in use.
> 
> Attached the config.log
> 
> Thanks
> 
> Il giorno mer 19 set 2018 alle ore 18:02 Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> ha scritto:
> 
>     On 9/19/18 8:39 AM, ncontu1 wrote:
>     > So unexpectedly with the rpms, it installed it on a different
>     place and it is
>     > working from command line
>     >
>     > [root@cmd-dev1 pgbouncer]# /bin/pgbouncer
>     /etc/pgbouncer/pgbouncer.ini -u
>     > postgres
>     > 2018-09-19 15:38:36.081 46322 LOG File descriptor limit: 1024
>     (H:4096),
>     > max_client_conn: 100, max fds possible: 230
>     > 2018-09-19 15:38:36.082 46322 LOG Stale pidfile, removing
>     > 2018-09-19 15:38:36.082 46322 LOG listening on 0.0.0.0:6543
>     <http://0.0.0.0:6543>
>     > 2018-09-19 15:38:36.082 46322 LOG listening on ::/6543
>     > 2018-09-19 15:38:36.083 46322 WARNING Cannot listen on
>     > unix:/tmp/.s.PGSQL.6543: bind(): Address already in use
> 
>     Is the compiled version still running?
> 
>     Or is something else sitting on the 6543 socket?
> 
>     > 2018-09-19 15:38:36.083 46322 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
>     >
>     > [root@cmd-dev1 ~]# psql -U ncontu -p 6543 cmd3dev -h IP_ADD
>     > Password for user ncontu:
>     > psql (10.5)
>     > Type "help" for help.
>     >
>     > cmd3dev=# \q
>     >
>     >
>     > So at this point... I don't really know why compiled from source
>     it does not
>     > work.
> 
>     Not sure.
> 
>     For those that might know it would help to provide the complete
>     ./configure used and the config.log(or equivalent)
> 
>     >
>     >
>     >
>     > --
>     > Sent from:
>     http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>     >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>