Postgresql – PgBouncer not listening with no errors in logs

centos7pgbouncerpostgresql

I currently have Postgres working on my server want to move to connection pooling. Looking around and PgBouncer seems to be the tool of choice. I have set it up and it claims to start fine with the log file looking like

[databases]
xxxx = host=localhost port=5432 dbname=xxxx user=xxx
[pgbouncer]

logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

listen_addr = *
listen_port = 6434

auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

admin_users = postgres

stats_users = stats, postgres

pool_mode = session

server_reset_query = DISCARD ALL

max_client_conn = 100

default_pool_size = 20

And user list:

"xxx" "xxxxx"

which starts without any errors. Log file:

DEBUG parse_ini_file: 'logfile' = '/var/log/pgbouncer.log' ok:1
DEBUG parse_ini_file: 'pidfile' = '/var/run/pgbouncer/pgbouncer.pid'
DEBUG parse_ini_file: 'pidfile' = '/var/run/pgbouncer/pgbouncer.pid' ok:1
DEBUG parse_ini_file: 'listen_addr' = '*'
DEBUG parse_ini_file: 'listen_addr' = '*' ok:1
DEBUG parse_ini_file: 'listen_port' = '6434'
DEBUG parse_ini_file: 'listen_port' = '6434' ok:1
DEBUG parse_ini_file: 'auth_type' = 'trust'
DEBUG parse_ini_file: 'auth_type' = 'trust' ok:1
DEBUG parse_ini_file: 'auth_file' = '/etc/pgbouncer/userlist.txt'
DEBUG parse_ini_file: 'auth_file' = '/etc/pgbouncer/userlist.txt' ok:1
DEBUG parse_ini_file: 'admin_users' = 'postgres'
DEBUG parse_ini_file: 'admin_users' = 'postgres' ok:1
DEBUG parse_ini_file: 'stats_users' = 'stats, postgres'
DEBUG parse_ini_file: 'stats_users' = 'stats, postgres' ok:1
DEBUG parse_ini_file: 'pool_mode' = 'session'
DEBUG parse_ini_file: 'pool_mode' = 'session' ok:1
DEBUG parse_ini_file: 'server_reset_query' = 'DISCARD ALL'
DEBUG parse_ini_file: 'server_reset_query' = 'DISCARD ALL' ok:1
DEBUG parse_ini_file: 'max_client_conn' = '100'
DEBUG parse_ini_file: 'max_client_conn' = '100' ok:1
DEBUG parse_ini_file: 'default_pool_size' = '20'
DEBUG parse_ini_file: 'default_pool_size' = '20' ok:1
LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 130
DEBUG pktbuf_dynamic(128): 0xa451c0
DEBUG make_room(0xa451c0, 9): realloc newlen=256
DEBUG pktbuf_dynamic(128): 0xa45400

When I bring up netstat -lntp it doesn't list anything listening on 6434 and of course I can't connect to it. In one of my attempts and the config file it actually did start up and I could connect to it. But after a server restart it no longer work and I am doubtful that I changed the config. I've tried different auth types and ports with no luck.

EDIT

So I've reinstalled pgbouncer several times and it works with my current config until I stop it again. After I stop it I can't start it back up again, however I can restart it. I am running the startup command as root if that could affect it?

Best Answer

Sounds like a permissions issue on /tmp/.s.PGSQL.6434 if it occurs on restart. Make sure you're restarting as the same user or su - pgbounceruser -c "/usr/bin/pgbouncer -R -d /etc/pgbouncer/pgbouncer.ini" to have it take new settings.