Successfully connecting postgreSQL 9.6.5 to pgbouncer 1.7.2

Question

Question:

I'm struggling to connect pgbouncer 1.7.2 to an operational postgresql 9.6.5 database (Django/Python web app, Ubuntu 14.04 OS).

Can you help me troubleshoot this?

---

Background:

PostgreSQL is working perfectly without pgbouncer. I'm using the canonical guide to setup pgbouncer. The DB was formerly restored from another machine.

Everything is on one machine. I'm trying a unix socket connection. Haven't tried TCP (but open to it).

The database is called mydb. My Django project is setup to connect to it via the user ubuntu.

---

What I've tried:

When I try psql -d mydb -p 6432 ubuntu (as the user ubuntu), I get: psql: ERROR: pgbouncer cannot connect to server

At the same time, pgbouncer.log shows:

01:39:56.428 78472 LOG C-0xfa51e0: mydb/ubuntu@unix(120837):6432 login attempt: db=mydb user=ubuntu tls=no
01:39:56.428 78472 LOG C-0xfa51e0: mydb/ubuntu@unix(120837):6432 closing because: pgbouncer cannot connect to server (age=0)
01:39:56.428 78472 WARNING C-0xfa51e0: mydb/ubuntu@unix(120837):6432 Pooler Error: pgbouncer cannot connect to server
01:40:11.428 78472 LOG S-0xfa0530: mydb/ubuntu@11.65.119.381:5432 closing because: connect failed (age=15)

Note that psql -d mydb -p 5432 ubuntu successfully logs me into mydb (without needing a password). Is the password creating the problem here?

---

Next if I do pgbouncer -d pgbouncer.ini (as the user ubuntu), I get a permission denied error:

2017-10-15 23:34:14.325 17606 FATAL Cannot open logfile: '/var/log/postgresql/pgbouncer.log': Permission denied

There are no corresponding log lines generated in pgbouncer.log. File perms are set as follows:

ubuntu@ip-xxx-xx-xx-xx:/var/log/postgresql$ ls -lh
total 59M
-rw-r--r-- 1 postgres postgres 1.8M Oct 15 23:35 pgbouncer.log
-rw-r----- 1 postgres adm       57M Oct 15 23:07 postgresql-9.6-main.log

---

What I've configured:

For the record, here's what I have in my Django app's settings.py file:

DATABASES = {
        'default': {
                'ENGINE': 'django.db.backends.postgresql_psycopg2',
                'NAME': 'mydb', 
                'USER': 'ubuntu',
                'PASSWORD': DB_PASSWORD,
                'HOST': '/var/run/postgresql',                 
                #'PORT': '6432',
        }

If I uncomment 'PORT': '6432', it still doesn't work.

---

Pgbouncer's pgbouncer.ini contains the following:

[databases]
mydb= host=11.65.119.381 port=5432 user=ubuntu dbname=mydb

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

; ip address or * which means all ip-s
listen_addr = *
listen_port = 6432

; unix socket is also used for -R.
; On debian it should be /var/run/postgresql
;unix_socket_mode = 0777
;unix_socket_group =
unix_socket_dir = /var/run/postgresql

auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = myuser, postgres, root
stats_users = myuser, postgres, root

Setting auth_type to any didn't work either.

---

/etc/pgbouncer/userlist.txt contains:

"ubuntu" "md565j6e98u1z098oiuyt7543poi4561yh3"

where I got the password string via SELECT usename, passwd FROM pg_shadow WHERE usename='ubuntu';. Note that this is different from DB_PASSWORD referenced in Django's settings.py (I've unsuccessfully tried that in userlist.txt too).

---

pg_hba.conf contains:

local   all             postgres                                peer

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 md5

---

postgresql.conf contains:

listen_addresses = '*'
port = 5432 
unix_socket_directories = '/var/run/postgresql'
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation

---

File perms:

/var/run/postgresql/ contains:

total 8.0K
drwxr-s--- 2 postgres postgres 120 Oct 16 00:06 9.6-main.pg_stat_tmp
-rw-r--r-- 1 postgres postgres   6 Oct 15 13:23 9.6-main.pid
-rw-r--r-- 1 postgres postgres   6 Oct 15 23:23 pgbouncer.pid

Answer 1

If you are successfully connecting to Postgres proper over a Unix socket, you can tell pgbouncer to do the same, by specifying the host as the directory where your socket files are (typically /tmp if you've compiled it yourself) though distros put it in various places.

So try something like this in your pgbouncer.ini:

[databases]
mydb= host=/tmp dbname=mydb

You probably don't need to set the username when pgbouncer is running as the user ubuntu (in your case) and you're using the default port number, so you don't need to set it explicitly.

In Postgres, do

show unix_socket_directories;

to see where your actual socket directory is.